lmart999 lmart999 - 3 years ago 317
Python Question

Appending Pandas dataframe to sqlite table by primary key

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 Source

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()
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download