Appending Pandas dataframe to sqlite table by primary key

Question

I want to append the Pandas dataframe to an existing table in a sqlite database called 'NewTable'. NewTable has three fields (ID, Name, Age) and ID is the primary key. My database connection:

import sqlite3
DB='<path>'
conn = sqlite3.connect(DB)  

The dataframe I want to append:

test=pd.DataFrame(columns=['ID','Name','Age'])
test.loc[0,:]='L1','John',17  
test.loc[1,:]='L11','Joe',30  

As mentioned above, ID is the primary key in NewTable. The key 'L1' is already in NewTable, but key 'L11' is not. I try to append the dataframe to NewTable.

from pandas.io import sql 
sql.write_frame(test,name='NewTable',con=conn,if_exists='append')

This throws an error:

IntegrityError: column ID is not unique

The error is likely to the fact that key 'L1' is already in NewTable. Neither of the entries in the dataframe are appended to NewTable. But, I can append dataframes with new keys to NewTable without problem.

Is there a simple way (e.g., without looping) to append Pandas dataframes to a sqlite table such that new keys in the dataframe are appended, but keys that already exist in the table are not?

Thanks.

Answer 1

You can use SQL functionality insert or replace

query=''' insert or replace into NewTable (ID,Name,Age) values (?,?,?) '''
conn.executemany(query, test.to_records(index=False))
conn.commit()