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. On Sep 19, 2012, at 12:12 AM, Michael Bayer wrote: > 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. -- 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.
