如何在将数据框传递给SQL时检查记录是否存在？

Question

背景

我正在开发一个应用程序，用来把CSV文件中的数据传到MS SQL数据库里。这个数据库是我公司用来存储所有这类记录（电话记录）的地方。当我运行这个应用时，它会读取CSV文件，并把数据转换成一个Pandas数据框，然后我用SQLAlchemy和pyodbc把记录添加到SQL表中。

不过，由于我处理的数据的特殊性，有时候表里已经有我们之前导入过的数据。我想找个办法，在把每条记录添加到表之前，先检查一下我的主键（在SQL表和数据框中的一列）是否已经存在。

当前代码

# 将数据框保存到mssql数据库 engine = sql.create_engine('mssql+pyodbc://CTR-HV-DEVSQL3/MasterCallDb') df.to_sql('Calls', engine, if_exists='append')

示例数据

我的CSV文件被导入为一个Pandas数据框（主键是FileName，它总是唯一的），然后传递到MS SQL。这是我的数据框（df）：

+---+------------+-------------+
|   |  FileName  |    Name     |
+---+------------+-------------+
| 1 | 123.flac   | Robert      |
| 2 | 456.flac   | Michael     |
| 3 | 789.flac   | Joesph      |
+---+------------+-------------+

有什么想法吗？谢谢！

Answer 1

你可以设置参数 index=False，下面有个例子

data.to_sql('book_details', con = engine, if_exists = 'append', chunksize = 1000, index=False)**

如果不设置这个参数，命令会自动添加一个 index 列

book_details 是我们想把数据框插入的表格名称。

结果

[SQL: INSERT INTO book_details (`index`, book_id, title, price) VALUES (%(index)s, %(book_id)s, %(title)s, %(price)s)]
[parameters: ({'index': 0, 'book_id': 55, 'title': 'Programming', 'price': 29},
{'index': 1, 'book_id': 66, 'title': 'Learn', 'price': 23},
{'index': 2, 'book_id': 77, 'title': 'Data Science', 'price': 27})]

所以，它必须在表格里！！！

Answer 2

你可以这样检查是否为空：

sql = "SELECT pk_1, pk_2, pk_3 FROM my_table"
sql_df = pd.read_sql(sql=sql, con=con)
    
if sql_df.empty:
    print("Is empty")
else:
     print("Is not empty")

Answer 3

假设你没有内存限制，并且你不插入空值，你可以这样做：

sql = "SELECT pk_1, pk_2, pk_3 FROM my_table"
sql_df = pd.read_sql(sql=sql, con=con)
df = pd.concat((df, sql_df)).drop_duplicates(subset=['pk_1', 'pk_2', 'pk_3'], keep=False)
df = df.dropna()
df.to_sql('my_table', con=con, if_exists='append')

根据你的应用需求，你也可以通过修改查询来减小sql_df的大小。

更新 - 整体效果更好，并且可以插入空值：

sql = "SELECT pk_1, pk_2, pk_3 FROM my_table"
sql_df = pd.read_sql(sql=sql, con=con)
df = df.loc[df[pks].merge(sql_df[pks], on=pks, how='left', indicator=True)['_merge'] == 'left_only']
# df = df.drop_duplicates(subset=pks) # add it if you want to drop any duplicates that you may insert
df.to_sql('my_table', con=con, if_exists='append')

Answer 4

如果你通过行来遍历一个数据表（可以用 DataFrame.iterrows() 这个方法），然后在每次遍历时，使用 ON DUPLICATE 这个功能来处理你的关键值 FileName，这样就可以避免重复添加同样的内容了。