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.

CL. · Accepted Answer · 2017-10-22 11:45:18Z

5

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.

edited Oct 22, 2017 at 11:45

answered Oct 22, 2017 at 6:31

CL.

182k18 gold badges241 silver badges282 bronze badges

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

3 Comments

Ilja Everilä Over a year ago

Minor nit, but since the filename in ATTACH is a string expression, not an identifier, could you not pass it using placeholders – compared to using string formatting. Makes little to no difference here, though.

Prithviraj Mitra Over a year ago

Many thanks for your help. It worked. I didn't understand couple of things. Why is it main.fdetail and not db1.fdetail in first query

CL. Over a year ago

@IljaEverilä I assumed the database name was not an expression, but testing shows it actually is. Thanks!

metmirr · Accepted Answer · 2017-10-21 20:33:28Z

1

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.

answered Oct 21, 2017 at 20:33

metmirr

4,3222 gold badges23 silver badges36 bronze badges

1 Comment

Prithviraj Mitra Over a year ago

Thanks. Is it possible so that if keyid matches then donot include the row in the list.

progmatico · Accepted Answer · 2017-10-21 20:32:25Z

0

You can convert your lists into sets and use the available set operations to your intents.

answered Oct 21, 2017 at 20:32

progmatico

4,9841 gold badge19 silver badges31 bronze badges

Collectives™ on Stack Overflow

Comparing two sqlite databases using Python

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related