In SQLAlchemy, a Subscription.total_cents column_property sums its charges with a correlated scalar_subquery(); why does it still show the old total after a new charge is flushed?
answer
- loaded with the row, then kept
- the subquery rides in the parent SELECT
- flush writes, it does not re-read
- refresh with an attribute list
- deferred() or a hybrid instead
basics
~20 sA column_property is computed inside the SELECT that loads Subscription and then held like any column value. Flushing a Charge only INSERTs the charge, so total_cents stays stale until it is expired and reloaded, for example by Session.refresh(sub, ['total_cents']).
solid answer
~40 s`column_property()` maps a SQL expression as if it were a column: the correlated `select(func.coalesce(func.sum(Charge.amount_cents), 0)).where(Charge.subscription_id == id).scalar_subquery()` is added to every SELECT that loads `Subscription`, and its result is stored in the object's state like an ordinary column value. Nothing re-runs it afterwards. A flush that adds a `Charge` emits only `INSERT INTO charge`; SQLAlchemy does not link the subquery's inputs to the attribute, so `sub.total_cents` keeps what it loaded. It is reloaded only once expired: `session.refresh(sub, ['total_cents'])`, `session.expire(sub, ['total_cents'])` and then access, or a commit under the default `expire_on_commit=True`. Assigning to it marks the object dirty but writes nothing. If code must see unflushed charges, a `hybrid_property` that sums the loaded `charges` in Python and uses the subquery only at class level fits better.
code
python · 42 linesfrom sqlalchemy import ForeignKey, create_engine, func, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, column_property, mapped_column
class Base(DeclarativeBase):
pass
class Charge(Base):
__tablename__ = "charge"
id: Mapped[int] = mapped_column(primary_key=True)
subscription_id: Mapped[int] = mapped_column(ForeignKey("subscription.id"))
amount_cents: Mapped[int]
class Subscription(Base):
__tablename__ = "subscription"
id: Mapped[int] = mapped_column(primary_key=True)
total_cents: Mapped[int] = column_property(
select(func.coalesce(func.sum(Charge.amount_cents), 0))
.where(Charge.subscription_id == id)
.correlate_except(Charge)
.scalar_subquery()
)
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as session:
sub = Subscription()
session.add(sub)
session.flush()
print(sub.total_cents) # 0
session.add(Charge(subscription_id=sub.id, amount_cents=999))
session.flush() # INSERT INTO charge ... and nothing else
print(sub.total_cents) # still 0: the loaded value is kept
session.refresh(sub, ["total_cents"])
print(sub.total_cents) # 999go deeper
Recall that a column_property is a SQL expression loaded with the row and read like a normal column.
Explain that the subquery runs in the parent SELECT, what refresh() and expiry do, and why assigning to it writes nothing.
Diagnose the stale total from the SQL log: one INSERT, no reload. Pick refresh, deferral or a hybrid to fix it.
Decide where derived totals live for a team: computed per load, stored and maintained by writes, or calculated in service code.
## What column_property maps `sqlalchemy.orm.column_property()` maps a **SQL expression** onto a class as though it were a column. The expression can be arithmetic on the row's own columns, or a **correlated scalar subquery** reaching into another table: ```python total_cents: Mapped[int] = column_property( select(func.coalesce(func.sum(Charge.amount_cents), 0)) .where(Charge.subscription_id == id) .correlate_except(Charge) .scalar_subquery() ) ``` `scalar_subquery()` turns the `Select` into a single-value column expression. `correlate_except(Charge)` lets every FROM element except `charge` correlate to the enclosing SELECT; the documentation calls it not strictly necessary, but it keeps `charge` in the subquery's own FROM list when the outer query also involves that table. ## The life of the value 1. **Load.** Every SELECT that loads `Subscription` carries the subquery as an extra column, `(SELECT coalesce(sum(charge.amount_cents), :coalesce_2) ...) AS anon_1`. The database computes it per row. 2. **Hold.** The result is stored in the object's state, exactly like `id`. Reading `sub.total_cents` again runs no SQL. 3. **Flush.** Adding a `Charge` and flushing emits `INSERT INTO charge ...` and nothing else. 4. **Stale.** `sub.total_cents` still holds the old number, because nothing expired it. 5. **Reload.** Once the attribute is expired, the next access or the refresh re-selects it with the subquery. ## Why the flush does not refresh it - The unit of work writes **changes to mapped columns**; a `column_property` over a subquery has no column to write. - SQLAlchemy does not track which rows an expression depends on, so inserting a `Charge` says nothing to it about `Subscription.total_cents`. - Expiry is driven by the Session (commit, `expire()`, `refresh()`), not by the contents of other tables. ## Getting a current value | Technique | SQL | Effect | |---|---|---| | `session.refresh(sub, ["total_cents"])` | a SELECT of that attribute now | current value immediately | | `session.expire(sub, ["total_cents"])` | a SELECT on next access | lazy reload | | `session.commit()` with `expire_on_commit=True` (the default) | a SELECT on next access | every attribute reloads | | a fresh `select(Subscription)` | the subquery runs again, but an object already in the identity map keeps its loaded value unless the statement sets the `populate_existing` execution option | not a refresh by itself | ## column_property versus hybrid_property | | `column_property` | `hybrid_property` with a correlated expression | |---|---|---| | When computed | in the SELECT that loads the row | on each access on an instance; in SQL when used on the class | | Sees unflushed charges | no | yes, if it sums the loaded `charges` collection | | Cost on load | the subquery runs for every loaded row | none until accessed; reading it may load `charges` | | Use in `where()` / `order_by()` | yes | yes, through the expression form | ## Reading the SQL log With `echo=True` the stale total is easy to confirm: - the SELECT that loaded the subscription contains `(SELECT coalesce(sum(charge.amount_cents), ...) ...) AS anon_1`; - the flush shows only `INSERT INTO charge (subscription_id, amount_cents) VALUES (?, ?)`; - reading `sub.total_cents` afterwards emits no SQL at all, which shows the old value is served from the object; - after `session.refresh(sub, ["total_cents"])` a new SELECT appears, carrying the subquery again. The same pattern explains totals that look right after a commit but wrong inside one transaction: the commit expired every attribute, while the read inside the transaction did not reload anything. ## Costs and pitfalls - The subquery runs for **every row** of every query that loads the class, including lists that never read the total. Mapping it with `deferred()` (or `column_property(..., deferred=True)`) leaves it out of the main SELECT and loads it on first access. - **Assigning** `sub.total_cents = 7` marks the object dirty, but the flush emits no SQL for it and the value stays in memory until expired. - A `column_property` must fit into the SELECT emitted for the class as a whole. - After a bulk change to `charge` rows, expire or refresh the affected subscriptions before trusting the totals.
- How do you stop the subquery from running every time Subscription rows load?Map it with `deferred()` instead of `column_property()`, or pass `deferred=True`: the expression is left out of the main SELECT and loaded by a separate SELECT on first access to `total_cents`. When only some statements need the total, `query_expression()` combined with the `with_expression()` loader option supplies it per statement.
- When is a hybrid_property the better choice for the total?When code reads the total right after changing `charges` in memory. The instance side can sum the loaded collection in Python, so it reflects unflushed changes, while the class side returns the correlated subquery for filters and ORDER BY. The cost is loading `charges` on access; `column_property` gives one value per row with no collection load but can go stale.
- A column_property re-runs its subquery every time the attribute is read
- Session.flush() refreshes column_property values that depend on the flushed rows
- Assigning to a column_property updates the rows behind its expression
- column_property can only use columns of its own table
- Without correlate_except the subquery cannot correlate at all