comparing pandas dataframe with sqlite table via sqlquery

Question

I have a sqlite database with the below columns, and 1000 rows

con.execute('CREATE TABLE "table" (Fruit TEXT, Texture TEXT, Code TEXT, Buy TEXT )'

I want to compare a pandas.Dataframe with this table, and if the value in the column sqlite "Code" is not in df['Code'], then update sqlite "Buy" with the word "Yes".

Initially I tried using the below code to see if I can get all the results from df['Code'] to print if it exists, then I could just add in a NOT into the sqlite query to flip the results, but currently, the below code prints out 1000 rows of empty lists, and occasionally prints out some of the Code instead...

for each_code in df['Code']:
    z = con.execute('SELECT EXISTS(SELECT 1 FROM "table" WHERE Code IN ({c}))'
                     .format(c=each_code)).fetchall()
    print(z)

Not sure where my error lies? I thought of doing this at the database level since it might be faster.

Answer 1

Assuming we have the following SQLite table:

sqlite> select * from tab;
Fruit       Texture     Code        Buy
----------  ----------  ----------  ----------
apple                   code1
orange                  code2
mango                   code3
banana                  code4
peach                   code5

and the following DF:

In [37]: df
Out[37]:
    Code
0  code1
1  code3
2  code5
3  code7

I'd do it in the following way:

first save DF into the same SQLite file with a different table name:

import sqlite3    
con = sqlite3.connect('d:/temp/a.db')    
df.to_sql('tmp', con, index=False)

Result (SQLite):

sqlite> select * from tmp;
Code
----------
code1
code3
code5
code7

now we can efficiently update our tab table directly in the SQLite:

sqlite> update tab set buy='Yes' where not exists (select 1 from tmp where tmp.code=tab.code);
sqlite> select * from tab;
Fruit       Texture     Code        Buy
----------  ----------  ----------  ----------
apple                   code1
orange                  code2       Yes
mango                   code3
banana                  code4       Yes
peach                   code5

Of course you can do the last step in Python:

con.execute("update tab set buy='Yes' where not exists (select 1 from tmp where tmp.code=tab.code)")
# optional clean up
con.execute("drop table tmp")
con.commit()