如何在SQLAlchemy中使用 pandas 进行upsert

本文介绍了如何在SQLAlchemy中使用 pandas 进行upsert的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我通过 SqlAlchemy 在PostgreSQL中创建了一个表：

I created a table in postgresql by SqlAlchemy:

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

我想通过像这样的数据框来向上插入此表：

And I want to upsert this table by a dataframe like:

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

我尝试用我的代码按<$ c升序插入在 SqlAlchemy 中的$ c> on_conflict_do_update 如下：

I tried my code to upsert it by on_conflict_do_update in SqlAlchemy as follows:

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'(psycopg2.ProgrammingError) can't adapt type 'dict'

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

My SqlAlchemy version is 1.2.10, and psycopg2 version is 2.7.5. Can someone help me?

推荐答案

set _ 参数需要一个以列名作为键，以表达式或文字作为值进行映射，但是您要传递一个嵌套字典作为值的映射，即 df.to_dict（orient ='dict'） 。错误无法适应类型'dict'是SQLAlchemy将这些字典作为文字传递给Psycopg2的结果。

The set_ parameter expects a mapping with column names as keys and expressions or literals as values, but you're passing a mapping with nested dictionaries as values, i.e. df.to_dict(orient='dict'). The error "can't adapt type 'dict'" is the result of SQLAlchemy passing those dictionaries to Psycopg2 as "literals".

因为您尝试插入多行在使用VALUES子句的单个INSERT中，您应该使用 已排除 。 EXCLUDED是代表要插入的行的特殊表。

Because you are trying to insert multiple rows in a single INSERT using the VALUES clause, you should use excluded in the SET actions. EXCLUDED is a special table representing the rows meant to be inserted.

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)