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

Martijn Pieters · Accepted Answer · 2016-11-03 18:21:27Z

12

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.

answered Nov 3, 2016 at 18:21

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Alex Over a year ago

Thanks Martjin, is there any way you know of to attach a database like I am trying to do? I want a nested table structure.

Martijn Pieters Over a year ago

@AlexG: just execute your ATTACH statements each time you connect.

Martijn Pieters Over a year ago

@AlexG: however, if you are always need for those tables to exist, why are you putting them into a separate database? Just put them all in the same database if you need access to them each and every time.

Alex Over a year ago

Good advice, I'll do one of those two options

Collectives™ on Stack Overflow

Attach database in sqlite3 with python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related