如何在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

我尝试用我的代码按 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)

如何在SQLAlchemy中使用 pandas 进行upsert

相关推荐