SQLiteExecutionContext._translate_colname bug when using attached databases

[Tony Roberts]

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

[Mike Bayer]

OK!

[Mike Bayer]

• 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>>

[Mike Bayer]

• 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>>

[Mike Bayer]

thanks for tracking that one down, this is a deeper error that was diagnosed very well.