SQLAlchemy 'on_conflict_do_update' does not update

Question

I have the following code which I would like to do an upsert:

def add_electricity_reading(
    *, period_usage, period_started_at, is_estimated, customer_pk
):
    from sqlalchemy.dialects.postgresql import insert

    values = dict(
        customer_pk=customer_pk,
        period_usage=period_usage,
        period_started_at=period_started_at,
        is_estimated=is_estimated,
    )
    insert_stmt = insert(ElectricityMeterReading).values(**values)
    do_update_stmt = insert_stmt.on_conflict_do_update(
        constraint=ElectricityMeterReading.__table_args__[0].name,
        set_=dict(
            period_usage=period_usage,
            period_started_at=period_started_at,
            is_estimated=is_estimated,
        )
    )
    conn = DBSession.connection()
    conn.execute(do_update_stmt)

    return DBSession.query(ElectricityMeterReading).filter_by(**dict(
        period_usage=period_usage,
        period_started_at=period_started_at,
        customer_pk=customer_pk,
        is_estimated=is_estimated,
    )).one()

 def test_updates_existing_record_for_started_at_if_already_exists():
    started_at = datetime.now(timezone.utc)
    existing = add_electricity_reading(
        period_usage=0.102,
        customer_pk=customer.pk,
        period_started_at=started_at,
        is_estimated=True,
    )
    started_at = existing.period_started_at
    reading = add_electricity_reading(
        period_usage=0.200,
        customer_pk=customer.pk,
        period_started_at=started_at,
        is_estimated=True,
    )

    # existing record was updated
    assert reading.period_usage == 0.200
    assert reading.id == existing.id

In my test when I add an existing record with period_usage=0.102 and then execute the query again but change to period_usage=0.2. When the final query at the bottom returns the record the period_usage is still 0.102.

Any idea why this could be happening?

user_2681632 · Answer

This behaviour is explained in "Session Basics" under "What does the Session do?" The session holds references to objects it has loaded in a structure called the identity map, and so ensures that only 1 unique object per primary key value exists at a time during a session's lifetime. You can verify this with the following assertion in your own code:

assert existing is reading

The Core insert (or update) statements you are executing do not keep the session in sync with the changes taking place in the database the way for example Query.update() does. In order to fetch the new values you can expire the ORM loaded state of the unique object:

DBSession.expire(existing)  # or reading, does not matter

# existing record was updated
assert reading.period_usage == 0.200
assert reading.id == existing.id

SQLAlchemy 'on_conflict_do_update' does not update

1 Answers