Double quoted name column problem (Sqlite)

[Massi]

Hi everyone, I'm using sqlalchemy 0.7.8 with sqlite and I'm encountering a problem trying to retrieve data from a table having a column named "input"_1. If I run this simple code:

from sqlalchemy import *

db = create_engine('sqlite:///test.db')
db.echo = False
metadata = MetaData(db)
t = Table('my_table', metadata, autoload=True)
print t.select().execute()

i get the following error:
OperationalError: (OperationalError) no such column: my_table.input"_1 u'SELECT my_table.id, my_table.input"_1 FROM my_table'. It seems that the first double quotes are truncated by the query function. Of course I can arrange things such that no double quotes are present in column names, but I would like to know if SA can somehow handle this situation automatically and in a platform-independent way.
Thanks in advance for your help!

[Massi]

After a little search, I found that the problem is due to line 684 of the file C:\Python27\Lib\site-packages\sqlalchemy\dialects\sqlite\base.py. The column names of the table are processed with this regular expression command:

name = re.sub(r'^\"|\"$', '', name)

which substitutes the first double quotes with a blank. Is this really necessary? Does there exist any workaround to overcome this problem?

[Michael Bayer]

I'm surprised that I can get you a really good chronology for why this is here, as it is some very old stuff.

Here's the SQLAlchemy changeset which added that logic, including the test, which was failing at that time:

http://hg.sqlalchemy.org/sqlalchemy/rev/cf5fbf20da45

The issue at that time was specifically the PRAGMA foreign_key_list(), for a table setup like the one in that test, would come back like this (I'm able to reproduce on a very old sqlite I was lucky enough to find):

sqlite> pragma foreign_key_list(django_admin_log);

0|0|"django_content_type"|content_type_id|id

on a modern sqlite, we get the correct result without the quotes:

sqlite> pragma foreign_key_list(django_admin_log);

0|0|django_content_type|content_type_id|id|NO ACTION|NO ACTION|NONE

aaaaand, the bug can also be traced to sqlite, where it was fixed in April of 2009, so this would be fixed as of sqlite 3.6.14:

http://www.sqlite.org/src/info/600482d161

So one thing to note is, the change we made was overly defensive; the quoting issue, per my testing on that old sqlite version just now, is limited to just the foreign_key_list().

I've summed this up in http://www.sqlalchemy.org/trac/ticket/2568 and an adjustment is short, only do the regexp for the tablename in foreign_key_list, and only if we are on sqlite 3.6.13 or earlier.

 I would like to know if SA can somehow handle this situation automatically and in a platform-independent way. 

i dont think any other dialects have something like this going on, though using the column quote character *in* the column name is sure to not be supported by many other databases.    while I can adjust SQLA for this particular bit of history, it's generally poor form to have the quotes in the name like that.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/0aCst9j8XcgJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

OK that ticket is fixed so you can get at tip or rel_0_7  (see http://www.sqlalchemy.org/download.html) for a fix.  

However, note that SQLite's "fix" of the foreign_key_list() pragma, the issue that led us to have these workarounds in the first place,  still doesn't work for a case that is very close to what you have; that is, a table name with quotes in it (like ' "mytable" ') will triple up the quotes in the result of foreign_key_list(), so their fix does not take this case into account.  The test I created for your issue can't actually do a full reflection of table/column/foreign key when all the names have quotes around them as part of the name.    The old version of the code can't handle it either, so it's nothing SQLAlchemy can improve upon, but this is also further argument towards how it's really a bad idea to have quotes as part of a table or column identifier name.