#255 createTable Fails When Table is Named "Transaction"

closed-invalid
Oleg Broytman
None
5
2009-09-25
2009-09-24
Brendan Doms
No

Bare bones, just try this (I'm working off of the easy_install version on Ubuntu, which is currently 0.11.0):

from sqlobject import SQLObject, connectionForURI, sqlhub

db_file = os.path.join(os.path.abspath(os.path.dirname(__file__)), 'finance.db')

connection = connectionForURI('sqlite:' + db_file)

sqlhub.processConnection = connection

class Transaction(SQLObject):
pass

Transaction.createTable()

If the table is named anything else (as far as I can find), then it works fine. But as it is, this generates the following error:

File "/usr/local/lib/python2.6/dist-packages/SQLObject-0.11.0-py2.6.egg/sqlobject/sqlite/sqliteconnection.py", line 183, in _executeRetry
raise OperationalError(ErrorMessage(e))
sqlobject.dberrors.OperationalError: near "transaction": syntax error

Not very helpful right? Either this error should be changed to point out the fact that "Transaction" is invalid as a table name, or the preferred solution would be to eliminate the error altogether and allow this table name. Considering that the import list contains no * and nothing called "Transaction" I see no reason why SQLObject's name space should interfere here.

Discussion

  • Oleg Broytman
    Oleg Broytman
    2009-09-25

    • assigned_to: nobody --> phd
    • status: open --> closed-invalid
     
  • Oleg Broytman
    Oleg Broytman
    2009-09-25

    If you add '?debug=1' to DB URI you find the following SQLObject debugging output:

    1/QueryR : CREATE TABLE transaction (
    id INTEGER PRIMARY KEY
    )

    This is where the error came from - from SQLite, not from SQLObject! SQLObject's namespace has nothing to do here.

    Traceback (most recent call last):
    [skip]
    raise OperationalError(ErrorMessage(e))
    sqlobject.dberrors.OperationalError: near "transaction": syntax error

    Let's test it without SQLObject:

    $ sqlite3 test
    SQLite version 3.5.9
    Enter ".help" for instructions
    sqlite> CREATE TABLE transaction (id INTEGER PRIMARY KEY);
    SQL error: near "transaction": syntax error

    SQLite (like any other DB) doesn't allow keywords to be used as names.

     
  • Brendan Doms
    Brendan Doms
    2009-09-25

    Ah, nice job getting to the root of the issue.

    I guess I just assumed that because SQLObject is an abstraction layer it would take the pain away from dealing with databases and so none of SQLite's (or any other database) reserved keywords would be in conflict. My bad.

    Since that's not the case, I would still recommend making the error message more clear here if possible. Specifically mentioning the reserved keyword thing would have helped me track down the bug faster.

     
  • jakimak
    jakimak
    2010-04-25

    just came accross this.

    Using sqlite3, you can simply put the keyword into quotes, then it works fine.
    This could also apply for SQLObject, but didn't test it.

    e.g.
    CREATE TABLE "transaction" (id INTEGER PRIMARY KEY);
    or
    CREATE TABLE "transaction" ("transaction" INTEGER PRIMARY KEY);
    and
    DROP TABLE "transaction";

     
  • Oleg Broytman
    Oleg Broytman
    2010-04-25

    jakmak, you can do this in SQLite, but you will have problems later dealing with in in SQLObject.