通过主键将 Pandas 数据框附加到 sqlite 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23574614/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Appending Pandas dataframe to sqlite table by primary key
提问by lmart999
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:
我想将 Pandas 数据框附加到名为“NewTable”的 sqlite 数据库中的现有表。NewTable 有三个字段（ID、Name、Age），ID 是主键。我的数据库连接：
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.
如上所述，ID 是 NewTable 中的主键。键 'L1' 已经在 NewTable 中，但键 'L11' 不在。我尝试将数据框附加到 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 中了。数据框中的任何条目都不会附加到 NewTable。但是，我可以毫无问题地将带有新键的数据帧附加到 NewTable。
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?
是否有一种简单的方法（例如，不循环）将 Pandas 数据帧附加到 sqlite 表，以便附加数据帧中的新键，但表中已存在的键不是？
Thanks.
谢谢。
采纳答案by Happy001
You can use SQL functionality insert or replace
您可以使用 SQL 功能 insert or replace
query=''' insert or replace into NewTable (ID,Name,Age) values (?,?,?) '''
conn.executemany(query, test.to_records(index=False))
conn.commit()
回答by sudhir
http://pandas.pydata.org/pandas-docs/version/0.13.1/generated/pandas.io.sql.write_frame.html
http://pandas.pydata.org/pandas-docs/version/0.13.1/generated/pandas.io.sql.write_frame.html
curious if you tried replace?
好奇你是否尝试过replace？
if_exists: {‘fail', ‘replace', ‘append'}, default ‘fail'
