skip to content

How does SQLAlchemy 2.1's ORM bulk UPDATE by primary key work when Session.execute() gets update(Product) and a list of dicts?

level: middleimportance: nice to knowfreq 18%

answer

  1. no .values(), usually no .where()
  2. every dict carries the key
  3. one executemany per key set
  4. only evaluate synchronizes
  5. Session.connection() to opt out

basics

~20 s

Session.execute(update(Product), [dicts]) runs UPDATE ... WHERE product.id = ? as an executemany, one parameter set per dict. Each dict must hold the full primary key; there is no RETURNING, and the 'fetch' sync strategy is refused.

solid answer

~40 s

An ORM `update(Product)` with **no** `.values()` and usually no `.where()`, executed by a `Session` with a list of dicts, switches on **bulk UPDATE by primary key**. SQLAlchemy builds `UPDATE product SET price=? WHERE product.id = ?` and runs it as an executemany; dicts with different key sets become separate batches. Every dict must contain the full primary key, otherwise it raises `InvalidRequestError` ("No primary key value supplied..."). RETURNING is not available, the result has no `rowcount`, and on drivers that report executemany row counts a key that matches no row raises `StaleDataError`. For objects already loaded, `'auto'` resolves to `'evaluate'`, copying the new values onto them; `'fetch'` raises. To run a many-parameter UPDATE keyed on another column, execute it on `session.connection()`.

code

python · 39 lines
python
from decimal import Decimal

from sqlalchemy import Numeric, create_engine, insert, update
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column


class Base(DeclarativeBase):
    pass


class Product(Base):
    __tablename__ = "product"
    id: Mapped[int] = mapped_column(primary_key=True)
    sku: Mapped[str] = mapped_column(unique=True)
    price: Mapped[Decimal] = mapped_column(Numeric(10, 2))


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)

with Session(engine) as session, session.begin():
    session.execute(
        insert(Product),
        [{"sku": f"SKU-{n}", "price": Decimal("1.00")} for n in range(1, 4)],
    )

with Session(engine) as session, session.begin():
    first = session.get(Product, 1)
    # no .values(), no .where(): each dict carries the full primary key
    session.execute(
        update(Product),
        [
            {"id": 1, "price": Decimal("11.00")},
            {"id": 2, "price": Decimal("12.00")},
            {"id": 3, "price": Decimal("13.00")},
        ],
    )
    # UPDATE product SET price=? WHERE product.id = ?   (one executemany)
    print(first.price)  # 11.00: the default sync copied the new value

go deeper

for a junior

Recall the call shape: update(Model) with no values, a list of dicts, and the primary key in every dict.

for a middle

Explain how the mode is chosen, the SQL it emits, why fetch is refused and how evaluate updates loaded objects.

for a senior

Show you guard against missing or stale ids in a feed, handle StaleDataError, and know when to drop to session.connection().

for a principal

Judge whether a feed-driven job should resolve keys up front so it can use this mode, against the cost of a Core path with no synchronization.

## When the mode switches on SQLAlchemy 2.1 uses **ORM bulk UPDATE by primary key** automatically when all three hold: 1. The UPDATE is built against an ORM entity, `update(Product)`. 2. It is executed by a `Session`, not by a Core `Connection`. 3. The parameters are a **list of dictionaries**. The statement itself carries **no `.values()`** and normally no `.where()`; the dicts supply both the new values and the row identity. It replaces the legacy `Session.bulk_update_mappings()`, which shares the implementation but lacks session synchronization. ## What it emits ```python session.execute( update(Product), [ {"id": 1, "price": Decimal("11.00")}, {"id": 2, "price": Decimal("12.00")}, ], ) ``` ```sql UPDATE product SET price=? WHERE product.id = ? -- one executemany: [(11.00, 1), (12.00, 2)] ``` The primary key keys become the WHERE clause; every other key becomes part of SET. A `mapped_column(onupdate=...)` Python default is added to SET as well. ## Rules the dicts must follow - **Full primary key in every dict.** A dict without it raises `InvalidRequestError: No primary key value supplied for column(s) product.id; per-row ORM Bulk UPDATE by Primary Key requires that records contain primary key values`. - **Keys are attribute names**, as for bulk INSERT. - **Different key sets are allowed**; the ORM groups them into sub-batches, so a feed that changes price on some rows and price plus name on others yields two UPDATE statements. - **Unmatched keys are checked** where the driver reports executemany row counts reliably, as the default SQLite and PostgreSQL drivers do: an id that matches no row raises `StaleDataError` ("expected to update 2 row(s); 1 were matched"). ## Synchronizing loaded objects | `synchronize_session` | Effect in this mode | |---|---| | `'auto'` (default) | Resolves to `'evaluate'` | | `'evaluate'` | For each dict, looks up the object by primary key in the identity map and copies the new values onto unmodified attributes, with no extra SQL | | `'fetch'` | Raises `InvalidRequestError`: not available for bulk updates | | `False` | Leaves loaded objects untouched | `'fetch'` is refused because an executemany does not return rows in its usual form, so there is no RETURNING to read matched keys from. ## Adding criteria, or opting out Since 2.0.11 you may add `.where()` to a bulk-by-primary-key UPDATE, for example `.where(Product.discontinued == False)`. The criteria are **ANDed** with the primary key match, never replacing it. With extra criteria the ORM refuses to synchronize: the default raises `InvalidRequestError` asking you to bypass synchronization, so pass `synchronize_session=False` and expire any affected objects yourself. When you want a classic many-parameter UPDATE keyed on another column, step down to Core on the same transaction: ```python session.connection().execute( update(Product).where(Product.sku == bindparam("b_sku")), [{"b_sku": "SKU-1", "price": 5}, {"b_sku": "SKU-2", "price": 6}], ) ``` `Session.connection()` returns the transaction's `Connection`, so the ORM bulk mode does not engage and no objects are synchronized. ## Failure modes to plan for - **A stale id in the feed.** A product deleted since the feed was produced makes the call raise `StaleDataError` where row counts are checked, and inside a `session.begin()` block the whole transaction rolls back. Either filter the feed against `select(Product.id)` first, or catch the error and retry without the missing ids. - **Fragmented batches.** A feed that sometimes sends `price` and sometimes `price` plus `name` creates one sub-batch per key set, in input order. Normalize the dicts if you want one executemany. - **Expecting rows back.** `.returning()` is not available in this mode; if the job needs the updated rows, run the WHERE form per group or select them afterwards. - **Very large lists.** All 200,000 dicts are held in memory and sent in one call. Splitting the list into chunks of, say, 10,000 per `session.execute()` keeps memory flat and makes progress visible, inside the same transaction or one transaction per chunk. - **No `rowcount`.** The result of this mode is not a `CursorResult`, so you cannot read `rowcount` from it; the `StaleDataError` check is what tells you a key was missing. ## Where it fits For the pricing job, the feed already knows each product's id, so one call with 200,000 dicts updates every price without loading a single object. When the feed carries only SKUs, either resolve the ids first with one `select(Product.sku, Product.id)` or use the Core form above.

  • What happens if you add `.where(Product.discontinued == False)` to a SQLAlchemy bulk UPDATE by primary key?
    Since 2.0.11 the extra criteria are allowed and ANDed with each row's primary key match, so a discontinued product is skipped. But the ORM will not synchronize in that case: with the default `synchronize_session` it raises `InvalidRequestError`. Pass `synchronize_session=False` and expire or refresh affected objects yourself.
  • Why does SQLAlchemy reject `synchronize_session='fetch'` for a bulk UPDATE by primary key?
    `'fetch'` learns which rows changed from RETURNING or a SELECT of matched keys. The bulk form runs as an executemany, which does not return rows, and the keys are already in the dicts anyway. So the ORM raises `InvalidRequestError`; the default `'auto'` resolves to `'evaluate'` instead, which copies each dict's values onto the object with that primary key.

  • The dicts can identify rows by any unique column, such as sku.
  • Bulk UPDATE by primary key supports RETURNING like the WHERE form does.
  • Without .where(), update(Product) with a list of dicts updates every row.
  • synchronize_session='fetch' is the safe choice for bulk updates by key.
  • Session.bulk_update_mappings() is the recommended 2.1 API for this.

Assumes SQLAlchemy 2.1; bulk UPDATE by primary key through Session.execute() arrived in 2.0, replacing bulk_update_mappings(), and extra .where() criteria are allowed since 2.0.11.