将数据框保存到带约束的sqlite表中

Question

我在把一个数据框保存到sqlite数据库时遇到了问题。

我有一个sqlite数据库，里面有一个叫做 assignments 的表，这个表的主键是 employee_id 和 project_id 的组合。

我想把一个数据框里的数据存进去，但这个数据框里可能会有一些行是已经在表里的（也就是有相同的 employee_id 和 project_id）。当我尝试保存这个数据框时，如果发现有重复的行，就会出现以下错误：
UNIQUE constraint failed: assignments.employee_id , assignments.project_id。
我不想让这个操作中断，而是希望能丢掉那些重复的行，保存其他的行。

我试过用 if_exists='append'，但还是出现了之前提到的错误：

以下是我的代码。

import pandas as pd
import sqlalchemy

engine = sqlalchemy.create_engine('sqlite:///my_database.db')
data = {
    'employee_id': [1, 2],
    'project_id': ['ABC123', 'DEF456']
}
df_new = pd.DataFrame(data)

df_new.to_sql('assignments', engine, if_exists='append', index=False)

Answer 1

根据官方文档，if_exists这个参数是和表格有关的，而不是和行有关的（这决定了当表格已经存在时的行为）。

一种解决办法是把数据框中已经存在于表格里的记录删除。可以这样做：

# read data from table
table_assignments_df = pd.read_sql(
    'SELECT employee_id, project_id FROM assignments', conn
)

# exclude existing rows from the new data frame 
df_new = df_new.loc[
    ~df_new['employee_id'].isin(table_assignments_df['employee_id'])
    & ~df_new['project_id'].isin(table_assignments_df['project_id'])
]

# then, store new data to table
df_new.to_sql('assignments', engine, if_exists='append', index=False)