Appending Pandas dataframe to sqlite table by primary key
pandas, python, sqlite
Solution
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()
Problem
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.