How to access attached database's tables using python's sqlite3?-CodePudding

I am attempting to attach a second database to an sqlite3 connection to a database. However when I attempt to retrieve the tables from the connected database sqlite crashes as there is no such table. Even though the PRAGMA database_list sqlite command displays the connected database file, implying that the attachment was successful.

db_1_path = "./db.sqlite"
db_2_path = "./db2.sqlite"

db_2_name = "db2"

connection = sqlite3.connect(db_1_path, check_same_thread=False)
cursor = connection.cursor()
cursor.execute(f'ATTACH DATABASE "{db_2_path}" AS {db_2_name};')

data = cursor.execute("PRAGMA database_list").fetchall()
print(data)
>>> [(0, 'main', '/home/dir/subdir/db.sqlite'), (2, 'db2', '/home/dir/subdir/db2.sqlite')]

tables = cursor.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall()
print(tables)
>>> [('col1',), ('col2',), ('col3',)]
tables = cursor.execute(f"SELECT name FROM {db_2_name} WHERE type='table';").fetchall()
print(tables)
>>> sqlite3.OperationalError: no such table: db2

CodePudding user response：

db_2_name's value is a string that is the alias of the attached database.

You should use it as the qualifier to access its sqlite_master table:

tables = cursor.execute(f"SELECT name FROM {db_2_name}.sqlite_master WHERE type='table';").fetchall()

The above statement will be interpreted as:

SELECT name FROM db2.sqlite_master WHERE type='table';