skip to content

In a SQLAlchemy 2.1 Session, why can a query's loader options seem ignored for objects already loaded, and what makes them apply?

level: seniorimportance: should knowfreq 30%

answer

  1. identity map wins over the new row
  2. options stick to the instance
  3. an execution option that overwrites
  4. shallowest path in 2.1

basics

~10 s

A row matching an object already in the Session is not re-applied, so loaded attributes stay and loader options attached earlier stay too. execution_options(populate_existing=True) overwrites those objects and applies the new options.

solid answer

~40 s

SQLAlchemy loads an object once per Session. When a later `select(Order)` returns a row whose primary key is already in the identity map, the ORM hands back the existing object and does not apply the row, to protect unflushed changes. So an attribute that is already loaded is not reloaded, even when the new option adds criteria, and the loader options an earlier query attached to the object, such as `raiseload(Order.lines)`, stay in force; the docs call them sticky. An eager option will still fill a relationship that is unloaded. `execution_options(populate_existing=True)` refreshes every matched object from its row and applies the statement's loader options, erasing in-memory state. In 2.1, an object loaded twice in one query, at the top and under a deeper path, follows the top-level path's options for its later loads.

code

python · 47 lines
python
from sqlalchemy import ForeignKey, create_engine, select
from sqlalchemy.exc import InvalidRequestError
from sqlalchemy.orm import (DeclarativeBase, Mapped, Session, lazyload, mapped_column,
                            raiseload, relationship, selectinload)


class Base(DeclarativeBase):
    pass


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    lines: Mapped[list["Line"]] = relationship()


class Line(Base):
    __tablename__ = "lines"
    id: Mapped[int] = mapped_column(primary_key=True)
    order_id: Mapped[int] = mapped_column(ForeignKey("orders.id"))
    qty: Mapped[int]


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as s, s.begin():
    s.add(Order(id=1, lines=[Line(qty=1), Line(qty=5)]))


with Session(engine) as s:
    # an earlier call in this Session loaded the orders with lines set to raise;
    # `earlier` keeps them alive in the identity map (it holds weak references)
    earlier = s.scalars(select(Order).options(raiseload(Order.lines))).all()

    order = s.scalars(select(Order).options(lazyload(Order.lines))).one()
    try:
        order.lines  # same object, so the earlier raise loader is still attached
    except InvalidRequestError as err:
        print(err)  # 'Order.lines' is not available due to lazy='raise'

    refresh = (
        select(Order)
        .options(selectinload(Order.lines.and_(Line.qty > 2)))
        .execution_options(populate_existing=True)
    )
    order = s.scalars(refresh).one()
    print(len(order.lines))  # 1: the row is re-applied and the new option wins

go deeper

for a junior

Recall that a Session returns the same object for the same primary key, so a second query does not rebuild objects it already holds.

for a middle

Explain that already-loaded attributes are not reloaded and earlier options stick, and that populate_existing=True overwrites and re-applies options.

for a senior

Diagnose why an option seems ignored from the log and inspect(obj).unloaded, choose between populate_existing and a fresh Session, and apply the 2.1 shallowest-path rule.

for a principal

Keep loading decisions close to the read that needs them, so long units of work do not inherit sticky options from unrelated earlier queries.

## The symptom An order page works in isolation, but inside a larger unit of work the same `select(Order).options(...)` behaves differently: a collection that should be filtered is not, or `order.lines` raises `InvalidRequestError: 'Order.lines' is not available due to lazy='raise'` even though this query asked for `lazyload(Order.lines)`. The statement is fine. The objects it returned were already in the Session. ## Why the options did not apply A SQLAlchemy `Session` keeps an **identity map**: one Python object per primary key. When a query returns a row whose key is already there, the ORM returns the existing object and, by default, **does not apply the row to it**. The query guide gives two reasons: to keep pending, unflushed changes on the object, and to avoid refreshing data that is already present. Three consequences follow: - **Loaded attributes are not reloaded.** If `order.lines` was loaded earlier, `selectinload(Order.lines.and_(Line.qty > 2))` does not replace it; the collection keeps all its lines. - **Earlier loader options stay attached.** The docs call options applied to an object **sticky**. If an earlier query used `raiseload(Order.lines)`, a later query's `lazyload(Order.lines)` does not change it, and the read still raises. - **Unloaded relationships can still be filled.** An eager option in the later query, such as `selectinload(Order.lines)`, does fill a relationship that is still unloaded on an existing object. So the behaviour looks inconsistent until you check what was already loaded. The identity map holds objects through **weak references**. If nothing else references the objects from the first query, they can be garbage-collected, and the next query builds fresh objects with its own options. That is why the problem can appear and disappear with an unrelated change, such as keeping a list of results alive longer. ## How to make the options apply | Approach | Effect | |---|---| | `execution_options(populate_existing=True)` | refreshes every matched object from its row and applies this statement's options | | `session.expunge(obj)` | removes the object, so the next query builds a new one with its own options | | a fresh `Session` for the read | no earlier objects, so every option applies as written | `populate_existing` is the targeted tool. The guide states its cost plainly: it **erases existing data within the objects, including pending changes**, and replaces it with the loaded rows. Use it on reads whose purpose is to refresh, not in the middle of edits you have not flushed. ## The 2.1 rule for an object loaded twice in one query One statement can load the same object along two paths. With `select(Order).options(joinedload(Order.customer).joinedload(Customer.orders).raiseload("*"))`, order 1 is loaded at the top and again as a member of its customer's `orders`. SQLAlchemy remembers which path an object was loaded under and uses that path's options whenever more SQL is emitted for it later: unexpiring attributes, loading deferred columns, loading unloaded relationships. 1. In 2.0, which of the two paths was remembered varied with the loader strategy, so a `raiseload("*")` meant for the deep path could end up applied to the top-level object. 2. In 2.1 the **shallowest path wins**, a deterministic rule. Order 1 follows the top-level options and its `lines` load normally; order 2, loaded only under the deep path, raises. 3. Code that relied on overlapping paths may need adjusting, so keep overlapping option paths simple and cover them with a test. ## Where it bites in practice The pattern needs two reads of the same rows in one Session, which happens more often than it sounds: - A request first loads the current customer's orders and their lines for a permission check, and then runs the page query with a filtered `selectinload(Order.lines.and_(...))`; the filter does nothing for the collections the check already loaded. - A long-running task keeps one Session across many steps, and an early step used `raiseload("*")` as a guard; a later step that expects lazy loading meets `InvalidRequestError` instead. - A test fixture loads objects into the same Session the code under test uses, so the test passes or fails for reasons production never sees. In each case the fix is to make the read independent of what came before, either by refreshing with `populate_existing` or by giving the read its own Session. ## Diagnosing it - Log the SQL: if the expected eager SELECT is missing, check whether the objects were loaded earlier in the same Session. - Inspect the object with `sqlalchemy.inspect(order).unloaded` to see which attributes are still unloaded. - Decide whether the read should refresh (`populate_existing`) or whether the unit of work should not have loaded those objects first.

  • Why does the problem sometimes vanish when you keep a reference to the first query's results, or appear when you drop it?
    The identity map references objects weakly. If nothing else holds the first query's objects, they can be garbage-collected, and the next query builds new objects with its own options. Holding a reference keeps the old objects, and their sticky options, alive.
  • What does populate_existing cost you?
    It overwrites every matched object from the new rows, erasing in-memory state including pending changes, as the query guide warns. It is right for a deliberate refresh and wrong in the middle of edits that should survive.

  • The newest query's loader options always replace the ones on existing objects
  • A later eager option never touches objects already in the Session
  • populate_existing only reloads relationships and keeps column edits
  • In SQLAlchemy 2.1 the deepest option path governs an object loaded twice
  • Sticky options disappear as soon as a second query returns the object

Assumes SQLAlchemy 2.1; before 2.1, which path an object loaded twice in one query remembered varied with the loader strategy, and 2.1 makes the shallowest path win.