skip to content

Bulk Statement Writes

ORM-enabled insert, update and delete statements write many rows without loading objects. Interviewers ask what synchronize_session does and why add_all is slower for big batches.

on this pageshow

explore

questions

6

With a SQLAlchemy 2.1 Session, how do you update or delete every Product row that matches a condition without loading the objects?

level: juniorimportance: should knowfreq 35%

answer

  1. a statement, not objects
  2. update() and delete() on the class
  3. .where() picks rows, .values() sets them
  4. Session.execute() inside the transaction

basics

~10 s

Build update(Product).where(...).values(...) or delete(Product).where(...) and pass it to Session.execute(). SQLAlchemy sends one UPDATE or DELETE for all matching rows, loads no objects, and commits with the session's transaction.

solid answer

~40 s

Construct the statement against the mapped class, for example `update(Product).where(Product.sku.in_(skus)).values(discontinued=True)` or `delete(Product).where(Product.discontinued)`, and run it with `session.execute()` inside the session's transaction, such as `with Session(engine) as session, session.begin():`. The ORM renders one statement, so 200,000 matching rows cost one round trip instead of 200,000 loaded objects. The result is a `CursorResult` whose `rowcount` is the number of rows the WHERE clause matched. The Session autoflushes pending changes first and afterwards synchronizes objects it already holds, according to `synchronize_session` (default `'auto'`). It does not run relationship cascades or per-object mapper events, so child rows follow only through `ON DELETE CASCADE` in the schema.

code

python · 38 lines
python
from decimal import Decimal

from sqlalchemy import Numeric, create_engine, delete, 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))
    discontinued: Mapped[bool] = mapped_column(default=False)


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
feed_dropped = ["SKU-2", "SKU-3"]

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

with Session(engine) as session, session.begin():
    soft = session.execute(
        update(Product)
        .where(Product.sku.in_(feed_dropped))
        .values(discontinued=True)
    )
    hard = session.execute(
        delete(Product).where(Product.discontinued, Product.price < Decimal("25"))
    )
    print("soft-deleted:", soft.rowcount, "hard-deleted:", hard.rowcount)

go deeper

for a junior

Recall the two constructs, update(Model).where().values() and delete(Model).where(), and that Session.execute() runs them inside the session's transaction.

for a middle

Explain what the statement skips: cascades, mapper events and @validates, while onupdate column defaults still apply and autoflush still runs first.

for a senior

Show you check loaded objects after the statement and know that child rows depend on ON DELETE CASCADE in the schema, not on relationship settings.

for a principal

Be ready to say when a team should allow statement-based writes in service code, given that they bypass the object-level rules the mapping encodes.

## The statement form SQLAlchemy 2.1 writes many rows at once through **ORM-enabled DML**: the Core constructs `update()` and `delete()` built against a mapped class instead of a `Table`, and executed by a `Session` instead of a `Connection`. - `update(Product).where(<criteria>).values(<attribute>=<value>)` changes every matching row. - `delete(Product).where(<criteria>)` removes every matching row. - The `.where()` criteria are ordinary SQL expressions on mapped attributes, such as `Product.sku.in_(skus)` or `Product.price < 5`. - The `.values()` keys are mapped attribute names, and a value may be an expression such as `Product.price * 1.05`. A **soft delete** is just an UPDATE that sets a flag, so the pricing job's "discontinue what the feed dropped" step is `update(...).values(discontinued=True)`, while purging old discontinued rows is a real `delete(...)`. ## Running it through the Session ```python with Session(engine) as session, session.begin(): result = session.execute( update(Product) .where(Product.sku.in_(dropped_skus)) .values(discontinued=True) ) print(result.rowcount) # rows the WHERE clause matched ``` What happens on that call: 1. The Session **autoflushes** first, so a pending `Product` added earlier in the same transaction is in the table before the UPDATE runs. 2. SQLAlchemy emits a single `UPDATE product SET discontinued=? WHERE product.sku IN (...)`. 3. It **synchronizes** any `Product` objects already in its identity map (see below). 4. Nothing is committed until the transaction ends; `session.begin()` commits on a clean exit and rolls back on an exception. The returned object is a `CursorResult`; its `rowcount` reports rows **matched**, not rows whose values actually changed. ## What the statement skips ORM-enabled UPDATE and DELETE deliberately bypass the unit of work, so several object-level features do not run: - **Relationship cascades**: `cascade="all, delete-orphan"` is applied by `Session.delete()` at flush time, not by a `delete()` statement. Configure `ForeignKey(..., ondelete="CASCADE")` in the schema, or delete child rows with their own statement first. - **Per-object mapper events** such as `before_update` and `before_delete`, and `@validates` methods, because there are no objects to pass to them. Use the `do_orm_execute` session event to intercept the statement itself. - **Joined-table inheritance** is not handled automatically; a single-table subclass does get its discriminator criteria added for you. Python-side column defaults declared with `mapped_column(onupdate=...)` are still rendered into the UPDATE, because they belong to the Core column, not to an ORM event. ## Objects the Session already holds If the job loaded a `Product` earlier, the UPDATE changes the row in the database but not, by itself, the object in memory. The `synchronize_session` execution option decides how the Session catches up. The default `'auto'` first tries to evaluate the WHERE clause in Python against loaded objects and falls back to fetching the matched primary keys. After a DELETE, matched objects are marked deleted and leave the session. The other strategies and their trade-offs are a later step in this topic; at this level, know that the default keeps loaded objects consistent. ## Reading what it sent Turn on `create_engine(..., echo=True)` and the soft delete shows up as exactly one statement with its bound values, which is the quickest proof that no objects were loaded: ```sql UPDATE product SET discontinued=? WHERE product.sku IN (?, ?) -- parameters: (1, 'SKU-2', 'SKU-3') ``` If you see a `SELECT product.id, product.sku, ...` per row followed by one UPDATE per row instead, the code is loading objects and letting the unit of work flush them, which is the path this statement exists to avoid. A trailing `RETURNING id` with no `.returning()` in your code means the synchronization step fell back to fetching the matched keys, which is normal for criteria it cannot evaluate in Python. ## A checklist before running it in a job 1. Run it inside a transaction block, `with Session(engine) as session, session.begin():`, so a later failure rolls the change back. 2. Compare `result.rowcount` with what you expected, for example the number of SKUs the feed dropped, and raise before commit when they disagree. 3. Make sure the foreign keys of child tables say what should happen to child rows, because no ORM cascade will run. 4. Add `.returning(Product.id)` when the job must log or publish exactly which rows changed. 5. Remember that listeners on `before_update` or `before_delete` will not see these rows; put any audit value into `.values()` instead. ## Legacy forms you will still read | Legacy code | 2.1 form | |---|---| | `session.query(Product).filter(...).update({...})` | `session.execute(update(Product).where(...).values(...))` | | `session.query(Product).filter(...).delete()` | `session.execute(delete(Product).where(...))` | | `engine.execute(...)` | removed in 2.0; use a `Session` or `Connection` | `session.query()` still works in 2.1 but is the legacy API; new code builds statements and runs them through `Session.execute()`.

  • How do you get the changed rows back from an ORM UPDATE in SQLAlchemy 2.1?
    Add `.returning()` to the statement: `.returning(Product.id, Product.price)` gives rows, and `.returning(Product)` run through `session.scalars()` gives `Product` objects. It needs a backend with UPDATE ... RETURNING, such as PostgreSQL or SQLite; MySQL does not support it.
  • Why can child rows survive, or the DELETE fail, after `delete(Product).where(...)` when the relationship has `cascade="all, delete-orphan"`?
    That cascade is applied by `Session.delete()` while flushing objects. An ORM-enabled DELETE skips the unit of work, so the database alone decides what happens to child rows: without `ON DELETE CASCADE` they are orphaned or the foreign key rejects the delete. Declare `ondelete="CASCADE"` on the `ForeignKey`, or delete the children with their own statement first.

  • You must load every Product, set the attribute and commit to change many rows.
  • session.query(Product).filter(...).update() is the current 2.1 idiom.
  • A delete(Product) statement runs the relationship's delete-orphan cascade.
  • Session.execute() commits the UPDATE immediately, outside the transaction.
  • update() needs a list of parameter dicts before it can touch many rows.

Assumes SQLAlchemy 2.1; update() and delete() run through Session.execute() are the 2.0-style form, Query.update() and Query.delete() are legacy, and Engine.execute() was removed in 2.0.

open as a page

In SQLAlchemy 2.1, why is Session.add_all() slower than an ORM bulk insert for 200,000 Product rows?

level: middleimportance: should knowfreq 32%

basics

~20 s

add_all() builds and tracks an instrumented Product per row; flush then sorts the unit of work, fires per-object events and reads every generated key back, with RETURNING where supported. An ORM bulk insert sends plain dicts in executemany batches and skips all of that.

open as a page

In SQLAlchemy 2.1, what does session.execute(insert(Product), list_of_dicts) do, and what does it skip compared with adding Product objects?

level: middleimportance: should knowfreq 30%

basics

~20 s

Passing insert(Product) and a separate list of dicts to Session.execute() selects ORM bulk INSERT mode: rows go out as executemany batches keyed by mapped attribute names, with no Product objects, relationship cascades or mapper events.

open as a page

A SQLAlchemy pricing job runs update(Product).where(...).values(...) with synchronize_session=False, and a Product loaded earlier still shows its old price. Why, and what should it use?

level: seniorimportance: should knowfreq 28%

basics

~20 s

Loaded objects change only when synchronize_session tells the Session which ones an UPDATE hit; False skips that, so the Product keeps its old price until expired or refreshed. The default 'auto' evaluates the WHERE in Python, using 'fetch' when it cannot.

open as a page

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%

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.

open as a page

How does SQLAlchemy's yield_per execution option change an ORM select(Product) over 200,000 rows, and what will it not combine with?

level: middleimportance: nice to knowfreq 22%

basics

~20 s

By default the ORM fetches every row and builds every Product before returning. With execution_options(yield_per=1000) it streams, using a server-side cursor where the driver supports one, building 1,000 objects at a time; unique() and joined-eager collections raise.

open as a page