按主键将熊猫数据存储器附加到sqlite表

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:

我想将熊猫数据aframe附加到一个名为“NewTable”的sqlite数据库中的现有表。NewTable有三个字段(ID、名称、年龄)，ID是主键。我的数据库连接:

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

The dataframe I want to append:

我想要添加的dataframe:

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.

如上所述，ID是NewTable中的主键。键'L1'已经在NewTable中，但是键'L11'不是。我尝试将dataframe附加到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.

这个错误可能是因为键“L1”已经在NewTable中了。dataframe中的任何一个条目都没有附加到NewTable。但是，我可以毫无问题地向NewTable添加带有新键的dataframes。

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?

是否有一种简单的方法(例如，不使用循环)将熊猫数据aframes附加到一个sqlite表，这样就可以添加dataframe中的新键，但是表中已经存在的键不是吗?

Thanks.

谢谢。

2 个解决方案

#1

You can use SQL functionality insert or replace

您可以使用SQL功能插入或替换

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

#2

http://pandas.pydata.org/pandas-docs/version/0.13.1/generated/pandas.io.sql.write_frame.html

curious if you tried replace?

好奇你是否尝试过替换?

if_exists: {‘fail’, ‘replace’, ‘append’}, default ‘fail’

#1