CODEWITHSUNDEEP
pythonpostgresqlsqlalchemypsycopg2
giser_yugang
giser_yugang

Reputation: 6176

How to use pandas to do upsert in SqlAlchemy

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

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)

But show this error:

(psycopg2.ProgrammingError) can't adapt type 'dict'

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

Upvotes: 4

Views: 7146

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 53017

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".

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)

Upvotes: 4

Related Questions