将 pandas 插入到PostgreSQL表中，并带有“发生冲突"消息.更新 [英] Insert into postgreSQL table from pandas with "on conflict" update

问题描述

我有一个需要存储到数据库中的pandas DataFrame.这是我当前要插入的代码行:

I have a pandas DataFrame that I need to store into the database. Here's my current line of code for inserting:

df.to_sql(table,con=engine,if_exists='append',index_label=index_col)

如果我的表中不存在df中的任何行，则此方法很好.如果已经存在一行，则会出现此错误:

This works fine if none of the rows in df exist in my table. If a row already exists, I get this error:

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) duplicate key
value violates unique constraint "mypk"
DETAIL:  Key (id)=(42) already exists.
 [SQL: 'INSERT INTO mytable (id, owner,...) VALUES (%(id)s, %(owner)s,...']
 [parameters:...] (Background on this error at: http://sqlalche.me/e/gkpj)

什么也没插入.

PostgreSQL具有可选的ON CONFLICT子句，该子句可用于UPDATE现有表行.我阅读了整个 pandas.DataFrame.to_sql手册页，我找不到在DataFrame.to_sql()函数中使用ON CONFLICT的任何方式.

PostgreSQL has optional ON CONFLICT clause, which could be used to UPDATE the existing table rows. I read entire pandas.DataFrame.to_sql manual page and I couldn't find any way to use ON CONFLICT within DataFrame.to_sql() function.

我已经考虑过根据db表中已有的内容将DataFrame分为两部分.所以现在我有了两个DataFrame，分别是insert_rows和update_rows，并且我可以安全地执行

I have considered spliting my DataFrame in two based on what's already in the db table. So now I have two DataFrames, insert_rows and update_rows, and I can safely execute

insert_rows.to_sql(table, con=engine, if_exists='append', index_label=index_col)

但是，似乎没有与DataFrame.to_sql()等效的UPDATE.那么如何使用DataFrame update_rows更新表?

But then, there seems to be no UPDATE equivalent to DataFrame.to_sql(). So how do I update the table using DataFrame update_rows?

推荐答案

如果您在to_sql文档中注意到，提到了method参数，该参数带有可调用对象.创建此可调用对象应允许您使用所需的Postgres子句.这是他们在文档中提到的可赎回债券的示例:

If you notice in the to_sql docs there's mention of a method argument that takes a callable. Creating this callable should allow you to use the Postgres clauses you need. Here's an example of a callable they mentioned in the docs: https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html#io-sql-method

它与您所需要的完全不同，但是请遵循传递给此可调用对象的参数.它们将允许您构造常规的SQL语句.

It's pretty different from what you need, but follow the arguments passed to this callable. They will allow you to construct a regular SQL statement.

这篇关于将 pandas 插入到PostgreSQL表中，并带有“发生冲突"消息.更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！