SQLITE3 + Python (I need to ask bank 1 table if its data exists in bank 2 table)

Question

I have a doubt about python and sqlite3.

import sqlite3
conna= sqlite3.connect('db_a')
a = conna.cursor()

connb= sqlite3.connect('db_b')
b = conna.cursor()

I don't know how to ask the relational question between banks, can someone instruct me? I don't want to use DEF, just the SELECT code for a variable to assume

query = """SELECT COL1 FROM TABLE1.DB_A WHERE NOT EXISTS (SELECT COL1 FROM TABLE2.DB_B WHERE COL1.TABLE2.DE_B = COL1.TABLE1.DE_A)"""
cursor.execute(query)
records = cursor.fetchall()
for row in records:
    print(row[0])

Can someone help me?

Answer 1

If the tables exist in different databases you need the ATTACH DATABASE statement to use the 2nd database with the connection object that you connect to the 1st database:

import sqlite3

conn = sqlite3.connect('db_a')
cursor = conn.cursor()

attach = "ATTACH DATABASE 'db_b' AS db_b;"
cursor.execute(attach)

query = """
SELECT t1.COL1 
FROM TABLE1 AS t1 
WHERE NOT EXISTS (
            SELECT t2.COL1 
            FROM db_b.TABLE2 AS t2 
            WHERE t2.COL1 = t1.COL1
        )
"""
cursor.execute(query)

records = cursor.fetchall()
for row in records:
    print(row[0])

detach = "DETACH DATABASE db_b;"
cursor.execute(detach)

Also, instead of EXISTS you could use EXCEPT with the difference being that EXCEPT returns only distinct results:

query = """
SELECT COL1 FROM TABLE1 
EXCEPT
SELECT COL1 FROM db_b.TABLE2
"""