In SQLAlchemy Core, how do you deactivate stale products with update() and get their ids back, and when can you trust CursorResult.rowcount?
answer
- where, values, returning
- matched rows, not changed rows
- one parameter set only
- RETURNING may break the count
basics
~10 sChain 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 sBuild `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 linesfrom 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) # 2go deeper
Recall the chain update(table).where(...).values(...), that delete(table).where(...) mirrors it, and that returning() hands back columns of the affected rows.
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.
Show you design cleanup jobs that report exactly what they touched, guard the WHERE clause in review, and handle backends without RETURNING.
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.