joining across databases with sqlite3 / pysqlite

Question

I have two separate database files each with tables with matching primary keys, but different data. I want to pull out rows from one table based on values in the other. In the CLI for sqlite3, I would do this like:

.open data.db
.open details.db
attach 'data.db' as data;
attach 'details.db' as details;

select details.A.colA from data.A join details.A using ('key') where data.A.colB = 0 and data.A.colC = 1;

How can I recreate such a cross-database join using pysqlite?

score 6 · Accepted Answer · answered Feb 11 '15 at 18:48

You can attach additional databases with ATTACH DATABASE:

conn = sqlite3.connect('data.db')
conn.execute('ATTACH DATABASE details.db AS details')

For query purposes, the first database is known as main:

cursor = conn.cursor()
cursor.execute('''
    select details.A.colA
    from main.A
    join details.A using ('key')
    where main.A.colB = 0 and main.A.colC = 1
    ''')

joining across databases with sqlite3 / pysqlite

1 Answers1