如何使用pandas在SqlAlchemy中执行upsert

Question

我使用 SqlAlchemy 在 PostgreSQL 中创建了一张表：

my_table = Table('test_table', meta,
                         Column('id', Integer,primary_key=True,unique=True),
                         Column('value1', Integer),
                         Column('value2', Integer)
                         )

我想通过数据框来更新此表：

   id  value1  value2
0   1    32.0       1
1   2     2.0      32
2   3     NaN       3
3   4   213.0      23

我尝试使用SqlAlchemy中的on_conflict_do_update来进行代码更新，代码如下：

insert_statement = sqlalchemy.dialects.postgresql.insert(my_table,).values(df.to_dict(orient='records'))
upsert_statement = insert_statement.on_conflict_do_update(
                                    index_elements=['id'],
                                    set_= df.to_dict(orient='dict')
                                )
conn.execute(upsert_statement)

但是显示以下错误：

(psycopg2.ProgrammingError) 无法适应类型 'dict'

我的 SqlAlchemy 版本是 1.2.10，而 psycopg2 版本是 2.7.5。有人能帮我吗？

- giser_yugang

---

你的表格有整数列，但你试图插入包含NaN的浮点值。这将会是一个问题。 - Ilja Everilä

Answer 1

set_ 参数需要一个以列名为键、表达式或字面值为值的映射，但您传递了一个包含嵌套字典的映射，即 df.to_dict(orient='dict')。错误 "can't adapt type 'dict'" 是 SQLAlchemy 将这些字典作为“字面值”传递给 Psycopg2 的结果。

因为您正试图在使用 VALUES 子句插入多行数据时，应该在 SET 操作中使用 excluded。EXCLUDED 是一个特殊的表，表示要插入的行。

insert_statement = postgresql.insert(my_table).values(df.to_dict(orient='records'))
upsert_statement = insert_statement.on_conflict_do_update(
    index_elements=['id'],
    set_={c.key: c for c in insert_statement.excluded if c.key != 'id'})
conn.execute(upsert_statement)

- Ilja Everilä

---

如果DataFrame包含NaN，则会显示：sqlalchemy.exc.DataError：（psycopg2.DataError）整数超出范围。 如果不包含NaN，则会显示：sqlalchemy.exc.ProgrammingError：(psycopg2.ProgrammingError) 不能适应类型“numpy.int64”。 - giser_yugang

这些都是真实的，但是分别是不同的问题，一个单一的问题应该只涉及到一个具体的问题或错误。例如，numpy.int64适配问题是由列value2引起的，如果您使用错误消息进行搜索，您会很容易地找到多个覆盖该问题的答案。NaN问题是数据类型不匹配。考虑迁移您的表格，或者使用类似DataFrame.fillna()的东西。 - Ilja Everilä

这是否意味着我不能使用NaN来upsert dataframe？在postgresql中，我能否将NaN作为null进行upsert操作？ - giser_yugang

是的，如果您的表列可为空，您可以插入NULL，但从长远来看，修复插入之前的数据或表模式将是更好的解决方案。 - Ilja Everilä

如何在postgresql中使用dataframe将NaN更新为null？即使我修改了Column('value1', Integer,nullable=True)，它也是无用的。 - giser_yugang

回复晚了，但是Pandas在NaN和NULL之间的转换已经在这里进行了讨论：https://dev59.com/dWAg5IYBdhLWcg3wpMSz - Ilja Everilä