How does SQLAlchemy 2.1's ORM bulk UPDATE by primary key work when Session.execute() gets update(Product) and a list of dicts?
answer
- no .values(), usually no .where()
- every dict carries the key
- one executemany per key set
- only evaluate synchronizes
- Session.connection() to opt out
basics
~20 sSession.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 sAn 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 linesfrom 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 valuego deeper
Recall the call shape: update(Model) with no values, a list of dicts, and the primary key in every dict.
Explain how the mode is chosen, the SQL it emits, why fetch is refused and how evaluate updates loaded objects.
Show you guard against missing or stale ids in a feed, handle StaleDataError, and know when to drop to session.connection().
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.