Comparing two sqlite databases using Python

Question

I am new in Python. I am trying to compare two sqlite databases having the same schema. The table structure is also same in both db but the data is different. I want to pickup the rows from both tables from both databases which are not present in either db1.fdetail or db2.fdetail

DB1 -

Table - fdetail

id    name    key
1     A       k1
2     B       K2
3     C       K3

DB2 -

Table - fdetail

id    name    keyid
1     A       k1
2     D       K4
3     E       K5
4     F       K6

Expected Output

id    name    keyid
1     B       k2
2     C       K3
3     D       K4
4     E       K5
5     F       K6

My code is

import sqlite3

db1 = r"C:\Users\X\Documents\sqlitedb\db1.db"
db2 = r"C:\Users\X\Documents\sqlitedb\db2.db"

tblCmp = "SELECT * FROM fdetail order by id"

conn1 = sqlite3.connect(db1)
conn2 = sqlite3.connect(db2)

cursor1 = conn1.cursor()
result1 = cursor1.execute(tblCmp)
res1 = result1.fetchall()

cursor2 = conn2.cursor()
result2 = cursor2.execute(tblCmp)
res2 = result2.fetchall()

So I have got two lists res1 and res2. How can I compare the lists based on the column Keyid.

Any help is highly appreciated.

Answer 1

If both databases are opened in the same connection (which requires ATTACH), you can do the comparison in SQL:

import sqlite3

db1 = r"C:\Users\X\Documents\sqlitedb\db1.db"
db2 = r"C:\Users\X\Documents\sqlitedb\db2.db"

conn = sqlite3.connect(db1)
conn.execute("ATTACH ? AS db2", [db2])

res1 = conn.execute("""SELECT * FROM main.fdetail
                       WHERE keyid NOT IN
                         (SELECT keyid FROM db2.fdetail)
                    """).fetchall()
res2 = conn.execute("""SELECT * FROM db2.fdetail
                       WHERE keyid NOT IN
                         (SELECT keyid FROM main.fdetail)
                    """).fetchall()

You can also get a single result by combining the queries with UNION ALL.

Answer 2

You can use python ‘set’:

res1 = set(res1)
res2 = set(res2)
result = res1.symmetric_difference(res2)

The symmetric difference of two sets is the set of elements which are in one of either set, but not in both.

Or you can iterate over both list respectively check for exists or not.