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.
x
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: db2Advertisement
Answer
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';