bulk insert sqlalchemy core with on conflict update

Question

I want to insert multiple items into a table and upsert the table on conflict. This is what I came up with the following

from sqlalchemy.dialects.postgresql import insert
meta = MetaData()
jobs_table = Table('jobs', meta, autoload=True, autoload_with=engine)
stmt = insert(jobs_table).values(jobs)
stmt.on_conflict_do_update(
    index_elements=['j_id'],
    set_= dict(active=True)
)
result = engine.execute(stmt)
return result.is_insert

The j_id is a unique field and I am trying to update the row if it already exists. I get the following error if the row already exists.

(psycopg2.IntegrityError) duplicate key value violates unique constraint "j_id"
DETAIL:  Key (j_id)=(0ea445da-bd1d-5571-9906-0694fa85728a) already exists.

Is there something that I am missing here ?

Answer 1

stmt.on_conflict_do_update returns a new statement. If you change to the following it should work:

from sqlalchemy.dialects.postgresql import insert
meta = MetaData()
jobs_table = Table('jobs', meta, autoload=True, autoload_with=engine)
stmt = insert(jobs_table).values(jobs)
stmt = stmt.on_conflict_do_update(index_elements=['j_id'],
                                  set_= dict(active=True))
result = engine.execute(stmt)
return result.is_insert

You can print(stmt) the statements to see the resulting SQL query. This can be useful to see if the statement which you are going to execute has the expected expression. Also adding echo=True to create_engine can be helpful to detect issues!