sqlite get_unique_constraints() does not work on temporary tables

[Johannes Erdfelt]

When attempting to use get_unique_constraints() on a temporary table in sqlite, it will fail with an exception similar to this:

Traceback (most recent call last):
  File "test_sqlite_temporary.py", line 28, in <module>
    print insp.get_unique_constraints('test_temp_reflection')
  File "/home/johannes/openstack/sqlalchemy/lib/sqlalchemy/engine/reflection.py", line 410, in get_unique_constraints
    self.bind, table_name, schema, info_cache=self.info_cache, **kw)
  File "<string>", line 2, in get_unique_constraints
  File "/home/johannes/openstack/sqlalchemy/lib/sqlalchemy/engine/reflection.py", line 54, in cache
    ret = fn(self, con, *args, **kw)
  File "/home/johannes/openstack/sqlalchemy/lib/sqlalchemy/dialects/sqlite/base.py", line 1109, in get_unique_constraints
    table_data = c.fetchone()[0]

The attached script shows that a non-temporary table works fine, but a temporary table fails.

The bug is caused by the fact the sqlite implementation of get_unique_constraints() uses sqlite_master only, whereas code like get_indexes() does a union between sqlite_master and sqlite_temp_master. sqlite_temp_master being the version of sqlite_master that contains information on temporary tables.

[Mike Bayer]

oh on a temp table, see I had no idea.

[Mike Bayer]

well surprise, none of the other dialects support any reflection of temp tables right now. so SQLite is inconsistent in this regard that we do get them back. I need to go through everything and establish a consistent pattern here for 1.0.

[Mike Bayer]

see #3204. that thing with the DBAPIError is some old code.

[Johannes Erdfelt]

It's not a bug that I particularly care about (since I don't use temporary tables) except for the fact that there are two unit tests that are tripped up by this behavior when I implemented my other patch for calling get_unique_constraints() during table reflection.

In particular, test_temp_table_reflection() uses a temporary table and then reflects it (like the name of the test implies). It seems that someone intended for temporary table reflection to work in sqlite at least.

Also, test_attached_as_schema() tries to reflect sqlite_master which is not included in either sqlite_master or sqlite_temp_master.

Any suggestions on the path forward?

[Mike Bayer]

• changelog for #3203; fixes #3203

→ <<cset c926df36173e>>

[Mike Bayer]

check out the fix, I just moved out that confusing code to its own methods. the unique constraint logic you sent is merged as is.