A SQLAlchemy Product returned after its Session closed raises DetachedInstanceError when a report reads product.stock; why, and how do you fix it?
answer
- no Session to load from
- commit expired every attribute
- refresh versus lazy-load messages
- load before close, or reattach
- add() or merge() into a new Session
basics
~20 sA detached object cannot load anything, and commit() expired its attributes by default, so reading product.stock needs a SELECT that has no Session to run in. Load what the report needs before the Session closes, or reattach the object to a new Session.
solid answer
~40 s`DetachedInstanceError` (error code `bhk3`) means an object outside any Session was asked to load data. A detached object keeps its loaded values and identity key, so reading loaded attributes works; reading anything unloaded fails. Two messages are common: "Instance <Product ...> is not bound to a Session; attribute refresh operation cannot proceed" for expired columns, usually expired by `commit()` under the default `expire_on_commit=True`, and "Parent instance <Product ...> is not bound to a Session; lazy load operation of attribute 'moves' cannot proceed" for a relationship never loaded. Fixes, roughly in order of preference: finish the work while the Session is open; load everything the caller will read before closing; configure the factory with `expire_on_commit=False` when objects must outlive their Session; or reattach with `session.add(product)` or `product = session.merge(product)` in a new Session.
code
python · 44 linesfrom sqlalchemy import ForeignKey, String, create_engine
from sqlalchemy.orm import (DeclarativeBase, Mapped, mapped_column, relationship,
sessionmaker)
from sqlalchemy.orm.exc import DetachedInstanceError
class Base(DeclarativeBase):
pass
class Product(Base):
__tablename__ = "product"
id: Mapped[int] = mapped_column(primary_key=True)
sku: Mapped[str] = mapped_column(String(20), unique=True)
stock: Mapped[int]
moves: Mapped[list["StockMove"]] = relationship(back_populates="product")
class StockMove(Base):
__tablename__ = "stock_move"
id: Mapped[int] = mapped_column(primary_key=True)
product_id: Mapped[int] = mapped_column(ForeignKey("product.id"))
qty: Mapped[int]
product: Mapped[Product] = relationship(back_populates="moves")
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
SessionFactory = sessionmaker(engine)
with SessionFactory() as session, session.begin():
session.add(Product(id=5, sku="A-5", stock=10, moves=[StockMove(qty=-2)]))
with SessionFactory() as session:
product = session.get(Product, 5)
session.commit() # expires every attribute (expire_on_commit=True)
try:
product.stock # needs a SELECT, but no Session
except DetachedInstanceError as err:
print(err) # Instance <Product at 0x...> is not bound to a Session; attribute refresh ...
with SessionFactory() as session:
session.add(product) # re-attach: persistent again, no SQL yet
print(product.stock, len(product.moves)) # 10 1, loaded inside the new Sessiongo deeper
Recall that an object outside a Session keeps its loaded values but cannot load anything, and that commit() expires attributes by default.
Explain the refresh, lazy-load and deferred-load variants of the message and which Session behaviour produces each.
Diagnose the error from the message and inspect(), then choose between keeping the Session open, loading up front, expire_on_commit=False and reattaching.
Set a team rule for what a service layer returns, ORM objects inside a Session's scope or plain data, so this error cannot reach a report.
## What detached means here An object is **detached** when it has a database identity (an identity key such as `(Product, (5,), None)`) but belongs to no `Session`. It gets there when its Session is closed, when it is expunged, or when the Session is committed after it was deleted. A detached object is an ordinary Python object as far as its **loaded** attributes go: `product.sku` returns the value already in its `__dict__`. What it has lost is the ability to run SQL, because SQL runs through a Session's connection. Any attribute that would need a SELECT raises `sqlalchemy.orm.exc.DetachedInstanceError`, whose error code is `bhk3`. ## The three messages and what triggers them | Message fragment | Trigger | |---|---| | `Instance <Product ...> is not bound to a Session; attribute refresh operation cannot proceed` | a column attribute was **expired**, most often by `commit()`, or by `expire()` / `rollback()` | | `Parent instance <Product ...> is not bound to a Session; lazy load operation of attribute 'moves' cannot proceed` | a lazy-loaded relationship was never loaded before the Session closed | | `Parent instance <Product ...> is not bound to a Session; deferred load operation of attribute ... cannot proceed` | a deferred column was never loaded | The scenario in the question is the first row. A typical service function does `product = session.get(Product, 5)`, adjusts it, calls `session.commit()` and returns `product` as the `with` block closes the Session. `commit()` expired every attribute under the default `expire_on_commit=True`, so the report's first read of `product.stock` needs a refresh, and there is no Session to run it. ## Diagnosing it 1. Read the attribute name and the operation in the message: *attribute refresh* means expired columns, *lazy load* means a relationship, *deferred load* means a deferred column. 2. Look for a `commit()` (or `rollback()`) before the Session closed; that is what expired the columns. 3. Run `inspect(product).unloaded` or `inspect(product).expired_attributes` right after the Session closes to see exactly which attributes would need SQL. For a `Product` with a `moves` relationship that was committed and then closed, every mapped attribute is listed: ```python >>> inspect(product).detached, sorted(inspect(product).expired_attributes) (True, ['id', 'moves', 'sku', 'stock']) ``` 4. Check whether the code that reads the object runs after the `with Session(...)` block, in a serializer, a template or another thread. 5. Look for an explicit `session.expunge()` or `expunge_all()`, which detach objects on purpose; `inspect(product).session` returning `None` confirms the object belongs to no Session. ## Fixes and their costs - **Keep the Session open for the object's useful life.** Build the report inside the block that owns the Session. This is the fix the documentation prefers, because it keeps lazy loading available. - **Load everything the caller needs before closing.** Read the needed columns after `commit()` while the Session is still open, or eager-load the relationships the caller will touch with a loader option. This makes the data contract explicit. - **`expire_on_commit=False`** on the `sessionmaker` stops commit from expiring loaded columns, so `product.stock` stays readable after close. It does **not** help an unloaded relationship, which still needs a lazy load, and it means objects keep pre-commit values. - **Reattach to a new Session.** `session.add(product)` puts that very object back as persistent; its expired attributes load on access. It raises `InvalidRequestError` if the new Session already holds another object for the same key ("Can't attach instance ...; another instance with key ... is already present in this session"), and if the object is still attached to a different open Session. `product = session.merge(product)` instead returns the new Session's own copy and leaves the original detached. - **Refresh before closing**: `session.refresh(product)` inside the block reloads the columns once, at the cost of a SELECT. ## Habits that keep it away - Return plain values or data-transfer objects from a service layer, rather than ORM objects that outlive their Session. - Test the code path with the Session closed before the caller reads the result, so the error appears in a test and not in a report. - Treat the error as a design signal: something reads more data than the code that owned the Session loaded. - Prefer one fix per code base. Mixing `expire_on_commit=False` in some factories with reattaching in others makes it hard to know which objects are safe to read after their Session ends.
- In SQLAlchemy, why does expire_on_commit=False fix product.stock after close but not product.moves?`expire_on_commit=False` keeps the column values loaded before `commit()`, so reading `product.stock` needs no SQL. `product.moves` was never loaded; loading an unloaded lazy relationship always goes through a Session, so it still raises the lazy-load variant of `DetachedInstanceError` unless it was loaded before the Session closed.
- How does reattaching with session.add(product) differ from product = session.merge(product) in a new SQLAlchemy Session?`add()` makes that same object persistent in the new Session without SQL, and fails with `InvalidRequestError` if the Session already holds another object for key 5. `merge()` looks up or loads the Session's own object for key 5, copies the detached object's loaded state onto it and returns it, leaving the original detached.
- A detached object cannot be read at all, not even its loaded attributes.
- DetachedInstanceError means the database row was deleted.
- expire_on_commit=False also lets detached objects lazy-load relationships.
- session.merge(product) reattaches the very object passed in.
- Reopening any Session makes detached objects load again automatically.