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. On Sep 18, 2012, at 7:52 PM, Massi wrote: > 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? > > Il giorno martedì 18 settembre 2012 18:21:36 UTC+2, Massi ha scritto: > 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! > > -- > 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 sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
