Attach database in sqlite3 with python

Question

For this example I have db_master.sqlite and db_1.sqlite in the working directory.

Everything seems to be working fine when I do this:

import sqlite3
conn = sqlite3.connect('db_master.sqlite')
c = conn.cursor()
c.execute('ATTACH DATABASE "db_1.sqlite" AS db_1')
c.execute('SELECT * FROM db_1.my_table')
conn.commit()
c.fetchall()

I get the column data back as expected. But when I close the connection and re-open it, the database no longer appears to be attached.

conn.close()
conn = sqlite3.connect('db_master.sqlite')
c = conn.cursor()
c.execute('SELECT * FROM db_1.my_table')
c.fetchall()

OperationalError: no such table: db_1.my_table

Answer 1

You attach databases to your connection, not to a specific database. You'll have to re-attach each time you create a new database connection.

From the ATTACH DATABASE documentation:

The ATTACH DATABASE statement adds another database file to the current database connection.

Emphasis mine.