skip to content

In SQLAlchemy Core, how do you deactivate stale products with update() and get their ids back, and when can you trust CursorResult.rowcount?

level: middleimportance: should knowfreq 35%

answer

  1. where, values, returning
  2. matched rows, not changed rows
  3. one parameter set only
  4. RETURNING may break the count

basics

~10 s

Chain update(products).where(...).values(active=False).returning(products.c.id) and read the returned rows. CursorResult.rowcount counts rows matched; rely on it for a single-parameter-set UPDATE or DELETE, not with executemany or RETURNING.

solid answer

~30 s

Build `update(products).where(products.c.active.is_(True), products.c.last_seen_at < cutoff).values(active=False).returning(products.c.id, products.c.sku)` and run it in `engine.begin()`; `result.all()` or `result.scalars().all()` gives the ids. `where()` criteria are ANDed, and an `update()` with no `where()` touches every row. `CursorResult.rowcount` reports rows **matched** by the WHERE clause, not rows whose values changed. It is meant for an UPDATE or DELETE run with one parameter set; for an executemany most drivers return -1, and a statement with RETURNING may not report a usable count, so count the returned rows instead. `delete(products).where(...).returning(...)` works the same way. `returning(sort_by_parameter_order=True)` is INSERT-only and raises `ArgumentError` on UPDATE or DELETE.

code

python · 43 lines
python
from datetime import datetime, timedelta

from sqlalchemy import (Boolean, Column, DateTime, Integer, MetaData, String,
                        Table, create_engine, insert, update)

metadata = MetaData()
products = Table(
    "products", metadata,
    Column("id", Integer, primary_key=True),
    Column("sku", String(32), unique=True, nullable=False),
    Column("active", Boolean, nullable=False, default=True),
    Column("last_seen_at", DateTime, nullable=False),
)

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

now = datetime(2026, 10, 10, 3, 0)
with engine.begin() as conn:
    conn.execute(insert(products), [
        {"sku": "SKU-1", "last_seen_at": now},
        {"sku": "SKU-2", "last_seen_at": now - timedelta(days=40)},
        {"sku": "SKU-3", "last_seen_at": now - timedelta(days=90)},
    ])

cutoff = now - timedelta(days=30)
with engine.begin() as conn:
    result = conn.execute(
        update(products)
        .where(products.c.active.is_(True), products.c.last_seen_at < cutoff)
        .values(active=False)
        .returning(products.c.id, products.c.sku)
    )
    print(result.all())  # [(2, 'SKU-2'), (3, 'SKU-3')]

with engine.begin() as conn:
    result = conn.execute(
        update(products)
        .where(products.c.last_seen_at < cutoff)
        .values(active=False)
    )
    # Both stale rows are already inactive, yet they still match.
    print(result.rowcount)  # 2

go deeper

for a junior

Recall the chain update(table).where(...).values(...), that delete(table).where(...) mirrors it, and that returning() hands back columns of the affected rows.

for a middle

Explain that rowcount counts matched rows, is meant for one parameter set, and may be -1 with executemany or RETURNING, so returned rows are the safer count.

for a senior

Show you design cleanup jobs that report exactly what they touched, guard the WHERE clause in review, and handle backends without RETURNING.

for a principal

Set the rule for data-fix scripts: every mass UPDATE or DELETE returns or logs the affected keys, and nobody asserts on rowcount where the driver cannot supply it.

## Building the cleanup UPDATE `update(table)` returns an `Update` construct. Three methods shape it: - `where(*criteria)` adds WHERE criteria. Several arguments, or several `where()` calls, are combined with AND. - `values(**kwargs)` sets the SET clause, by column name, with literal values or SQL expressions such as `products.c.price_cents * 2`. - `returning(*cols)` adds a RETURNING clause. SQLAlchemy's PostgreSQL, SQLite, SQL Server and Oracle dialects support it on UPDATE and DELETE; MariaDB only on DELETE; MySQL not at all. For the cleanup that deactivates products not seen for thirty days: ```python stmt = ( update(products) .where(products.c.active.is_(True), products.c.last_seen_at < cutoff) .values(active=False) .returning(products.c.id, products.c.sku) ) with engine.begin() as conn: stale = conn.execute(stmt).all() ``` An `update()` with **no** `where()` is legal and updates every row in the table; the construct never adds a guard for you. ## Getting the ids back With `returning()`, the result yields one row per row the UPDATE matched, carrying the listed columns as they stand after the update. Use `result.all()` for `Row` objects or `result.scalars().all()` for a flat list of ids when one column is returned. The same works on `delete(products).where(...).returning(products.c.id)` when the cleanup deletes instead of deactivating. ## What `rowcount` reports `execute()` returns a `CursorResult`, and its `rowcount` attribute copies the driver's `cursor.rowcount` before the cursor closes. The facts that matter: 1. It counts rows **matched** by the WHERE clause. A row whose `active` was already `False` still counts; MySQL is configured to report matches too. 2. It is meant for an UPDATE or DELETE executed with **one** parameter set. That is the case every driver is expected to support. 3. With an **executemany** (a list of parameter dicts) most drivers do not sum the counts and return -1. 4. With **RETURNING** the value may be -1 or otherwise not meaningful. The SQLite dialect declares `supports_sane_rowcount_returning = False`, and there an UPDATE ... RETURNING that returned two rows reported a `rowcount` of 0. 5. For INSERT or SELECT, SQLAlchemy does not memoize it unless you pass the `preserve_rowcount=True` execution option. 6. `CursorResult.supports_sane_rowcount` tells you whether the dialect reports it at all. | Statement | Reliable count | |---|---| | `update(...).where(...)`, one parameter set | `result.rowcount` | | `update(...).where(...).returning(...)` | `len(result.all())` | | executemany UPDATE with a list of dicts | neither; restructure into one set-based statement or count separately | ## Errors and limits to remember - `returning(..., sort_by_parameter_order=True)` raises `ArgumentError` ("only applies to INSERT statements") on an `Update` or `Delete`; the ordering promise belongs to the insertmanyvalues feature. - MySQL has no RETURNING, and MariaDB has it only for DELETE; there you run the UPDATE, use `rowcount` for the count, and select the affected ids separately if you need them. - `Session.execute()` returns a `CursorResult` only when the statement uses Core `Table` objects directly; ORM-enabled statements return a plain `Result`. ## Why `rowcount` exists at all The documentation names two consumers: application code that asserts an UPDATE or DELETE touched the expected number of rows, and the ORM, whose unit of work checks `rowcount` against the rows it meant to change and whose version-counter feature depends on it. That is why SQLAlchemy memoizes it for UPDATE and DELETE but not, by default, for other statements. When a driver cannot provide a meaningful value for a kind of statement, the DB-API convention is -1, and `rowcount` passes that through rather than guessing. ## A checklist for a Core cleanup job - Put every condition in `where()` and print the statement once during review: `print(stmt)` shows the SQL with bound parameter names. - Return the key columns you will report, rather than re-selecting them afterwards. - Compare `rowcount` with an expected range only for single-parameter-set statements without RETURNING. - Run it inside `engine.begin()` so the change commits only when the block exits without an error.

  • Why did the second UPDATE in the example report 2 when nothing changed?
    `CursorResult.rowcount` counts rows matched by the WHERE clause. Both stale products matched `last_seen_at < cutoff` even though `active` was already `False`, so the count is 2. Add `products.c.active.is_(True)` to the WHERE clause when you want the count to mean rows that actually flipped.
  • How would the cleanup report ids on MySQL, which has no RETURNING?
    Select the stale ids first, then update by those ids in the same transaction, ideally with `select(...).with_for_update()` so concurrent writers cannot change the set in between. `rowcount` from the single-parameter UPDATE then confirms how many rows matched.

  • rowcount counts only rows whose values actually changed.
  • rowcount after an executemany UPDATE is a reliable total on every driver.
  • update(products).values(active=False) without where() is rejected or updates nothing.
  • returning(sort_by_parameter_order=True) orders the rows of an UPDATE.
  • UPDATE ... RETURNING always fills rowcount with the number of returned rows.