SQLiteExecutionContext._translate_colname bug when using attached databases
Issue #3211
resolved
Hi,
if using a sqlite database with attached databases, eg
def on_connect(dbapi_connection, connection_record):
dbapi_connection.execute("ATTACH DATABASE 'test.db' AS 'test'")
engine = create_engine("sqlite+pysqlite:///:memory:")
event.listen(engine, "connect", on_connect)
when selecting columns from tables from attached databases the column format is "database.tablename.colname".
In SQLiteExecutionContext._translate_colname it is assumed the format is always "tablename.colname" and so selects always result in the wrong column names in the ResultMetaData.
A possible fix is to change SQLiteExecutionContext._translate_colname to take the last part of the column name split on "." instead of the second:
def _translate_colname(self, colname):
# adjust for dotted column names. SQLite
# in the case of UNION may store col names as
# "tablename.colname" or "database.tablename.colname"
# in cursor.description
if not self._preserve_raw_colnames and "." in colname:
return colname.split(".")[-1], colname
else:
return colname, None
thanks, Tony
Comments (4)
-
repo owner -
repo owner - changed status to resolved
- rework tests for attached databases into individual tests, test both memory and file-based
- When selecting from a UNION using an attached database file,
the pysqlite driver reports column names in cursor.description
as 'dbname.tablename.colname', instead of 'tablename.colname' as
it normally does for a UNION (note that it's supposed to just be
'colname' for both, but we work around it). The column translation
logic here has been adjusted to retrieve the rightmost token, rather
than the second token, so it works in both cases. Workaround
courtesy Tony Roberts.
fixes
#3211
→ <<cset 4da020dae324>>
-
repo owner - rework tests for attached databases into individual tests, test both memory and file-based
- When selecting from a UNION using an attached database file,
the pysqlite driver reports column names in cursor.description
as 'dbname.tablename.colname', instead of 'tablename.colname' as
it normally does for a UNION (note that it's supposed to just be
'colname' for both, but we work around it). The column translation
logic here has been adjusted to retrieve the rightmost token, rather
than the second token, so it works in both cases. Workaround
courtesy Tony Roberts.
fixes
#3211
→ <<cset 49f4d98c04f2>>
-
repo owner thanks for tracking that one down, this is a deeper error that was diagnosed very well.
- Log in to comment
OK!