通过主键将Pandas数据框追加到sqlite表 [英] Appending Pandas dataframe to sqlite table by primary key

问题描述

我想将Pandas数据框附加到名为"NewTable"的sqlite数据库中的现有表上. NewTable具有三个字段(ID，名称，年龄)，ID是主键.我的数据库连接:

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)  

我要附加的数据框:

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

如上所述，ID是NewTable中的主键.键"L1"已在NewTable中，但键"L11"不在中.我尝试将数据框追加到NewT​​able.

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')

这会引发错误:

IntegrityError: column ID is not unique

该错误很可能是因为键'L1'已经在NewTable中.数据框中的两个条目均未附加到NewT​​able.但是，我可以将带有新键的数据帧附加到NewT​​able上.

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.

是否有一种简单的方法(例如，不循环)将Pandas数据帧追加到sqlite表中，以便在数据帧中追加新的键，但是不存在表中已经存在的键?

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?

谢谢.

推荐答案

您可以使用SQL功能insert or replace

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()

这篇关于通过主键将Pandas数据框追加到sqlite表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！