How to use pandas to do upsert in SqlAlchemy

Question

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?

Your table has integer columns, but you're trying to insert floating point values containing NaN. That's going to be a problem. — Ilja Everilä, Aug 20 '18 at 17:12

score 3 · Accepted Answer · answered Aug 20 '18 at 17:09

3

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)

answered Aug 20 '18 at 17:09

Ilja Everilä

50,538
7
126
127

If dataframe contain NaN, it shows:`sqlalchemy.exc.DataError: (psycopg2.DataError) integer out of range`. If not contain NaN, it shows: `sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'numpy.int64'` – giser_yugang Aug 21 '18 at 10:12
Those are real, but separate issues and a single question should be about a specific problem or error. For example the `numpy.int64` adaptation issue is caused by column `value2` and you'll find multiple answers covering that fairly easily from SO, if you search using the error message. The NaN issue is a data type mismatch. Consider migrating your table, or use something like [`DataFrame.fillna()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.fillna.html). – Ilja Everilä Aug 21 '18 at 10:29
Does this mean that I can't upsert dataframe with Nan? Can I upsert Nan as `null` in postgresql? – giser_yugang Aug 21 '18 at 10:54
Yes, you could insert NULL, if your table column is nullable, but fixing either the data before inserting, or the schema of your table would be the better solution in the long run. – Ilja Everilä Aug 21 '18 at 11:06
How I upsert NaN as `null` with dataframe in postgresql ? It's useless even though I have modified `Column('value1', Integer,nullable=True)` – giser_yugang Aug 21 '18 at 11:43
A late reply, but Pandas' conversions between NaN and NULL are covered for example here: https://stackoverflow.com/questions/23353732/python-pandas-write-to-sql-with-nan-values – Ilja Everilä Aug 25 '18 at 05:51

How to use pandas to do upsert in SqlAlchemy

1 Answers1

Linked