In SQLAlchemy 2.1, what does Session.get(Product, 5) do when called twice in one Session, and how does a select() differ?
answer
- one object per primary key
- look in the map before SQL
- a query still runs its SELECT
- loaded attributes are not overwritten
- populate_existing forces the reload
basics
~20 sSession.get() checks the Session's identity map before sending SQL, so a second get(Product, 5) returns the same object with no SELECT. A select() always queries, but returns that same object and leaves its loaded attributes as they were.
solid answer
~40 sEvery `Session` keeps an **identity map**: one Python object per primary key, keyed by an identity key such as `(Product, (5,), None)`. `session.get(Product, 5)` looks there first; on a hit it returns the object with no SQL, on a miss it sends `SELECT ... WHERE product.id = ?` and returns `None` if no row comes back. It also queries when the object it finds is fully expired, as every object is after `commit()` by default. A `select(Product).where(Product.id == 5)` run through `session.scalars()` always sends its SELECT, yet the row is matched to the object already in the map and its loaded attributes are **not** overwritten. To force fresh values, pass `populate_existing=True` to `get()`, or set it as an execution option on the `select()`.
code
python · 35 linesfrom sqlalchemy import String, create_engine, event, select, text
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
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]
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as session, session.begin():
session.add(Product(id=5, sku="A-5", stock=10))
statements = []
event.listen(engine, "before_cursor_execute",
lambda conn, cursor, stmt, *rest: statements.append(stmt))
with Session(engine) as session:
first = session.get(Product, 5) # SELECT ... WHERE product.id = ?
second = session.get(Product, 5) # identity map hit: no SQL
print(first is second, len(statements)) # True 1
session.execute(text("UPDATE product SET stock = 0 WHERE id = 5"))
again = session.scalars(select(Product).where(Product.id == 5)).one()
print(again is first, again.stock) # True 10 (not overwritten)
fresh = session.get(Product, 5, populate_existing=True)
print(fresh is first, fresh.stock) # True 0go deeper
Recall that session.get(Product, 5) returns the same object on every call in one Session, sends at most one SELECT while the object stays loaded, and returns None when the row is missing.
Explain the identity key, why a second select() returns the same object without overwriting loaded attributes, and when get() still has to query: a miss, a fully expired object, populate_existing.
Diagnose stale reads after untracked SQL in the same transaction and choose between populate_existing, refresh() and expire(), knowing each discards unflushed edits on the objects it reloads.
Be ready to explain to a team why the identity map is a per-Session consistency guarantee, not a cache, and why short Sessions make most stale-read questions disappear.
## The identity map inside a Session A SQLAlchemy `Session` tracks the objects it has loaded or flushed in its **identity map**, exposed as `session.identity_map`. The map holds clean objects by weak reference, so an object your code no longer references can drop out of it; objects with unflushed changes are held until the flush. The map is keyed by an **identity key**: a tuple of the mapped class, the primary-key values and an identity token. You can read it with `inspect(product).key`, and for a `Product` with primary key 5 it looks like `(Product, (5,), None)`. The map belongs to one `Session`; two Sessions that load row 5 hold two different Python objects. The map gives a Session two guarantees: - **One object per row.** Inside one Session, every load of row 5 hands back the same Python object, so `first is second` is true however the row was reached. - **No silent overwrite.** Once an attribute is loaded, a later query for the same row does not replace it, so an unflushed edit is not clobbered by the row the database sends back. ## What Session.get() does, step by step `Session.get(entity, ident, *, options=None, populate_existing=None, with_for_update=None, ...)` is the primary-key lookup of the 2.0-style API that 2.1 keeps; the legacy `session.query(Product).get(5)` was superseded by it in 2.0. For `session.get(Product, 5)`: 1. It builds the identity key from the class and the primary-key value. A composite key is passed as a tuple, `(5, 10)`, or as a dict keyed by attribute name. 2. Unless `populate_existing` or `with_for_update` is passed, it looks the key up in the identity map. A hit returns the object at once: no SQL and no autoflush. 3. If the object it finds is **fully expired**, as every object is after `commit()` under the default `expire_on_commit=True` or after `expire_all()`, it sends a SELECT to reload it. 4. On a miss it autoflushes, sends `SELECT ... FROM product WHERE product.id = ?`, puts the result in the map and returns it, or returns `None` when no row exists. So two calls in a row cost one SELECT; the second is a dictionary lookup. ## How a select() differs | | `session.get(Product, 5)` | `session.scalars(select(Product).where(Product.id == 5)).one()` | |---|---|---| | SQL when the object is already in the map | none | one SELECT, every time | | Object returned | the one in the map | the one in the map | | Attributes already loaded | untouched | untouched; only expired or unloaded ones are filled | | Row missing | returns `None` | `one()` raises `NoResultFound` | | Autoflush before it | only when it has to query | yes, before every execution | The third row surprises people. The query runs, the database returns the current row, and SQLAlchemy matches it to the object it already holds by primary key and **skips repopulating** the attributes that are already loaded. The Session behaves as though the transaction were perfectly isolated: it does not overwrite what it has already read unless it is told to. ## Forcing fresh values with populate_existing When a statement the ORM did not track, such as `session.execute(text("UPDATE product SET stock = 0 WHERE id = 5"))`, changes a loaded row in the same transaction, both calls above keep returning the old value. Two ways to reload through a query: - `session.get(Product, 5, populate_existing=True)` skips the identity-map shortcut, sends the SELECT and overwrites the object's loaded attributes. - `select(Product).execution_options(populate_existing=True)` does the same for every object that query returns, and lets loader options refresh related collections in the same pass. Both erase unflushed changes on the objects they reload. With autoflush on, the Session normally flushes those changes before the SELECT, so they survive by way of the database; inside a `no_autoflush` block they are lost. `Session.refresh()` and `Session.expire()` are the per-object alternatives. ## Pitfalls interviewers probe - **Treating the map as a cache.** It lives and dies with one Session. It is not shared across Sessions, threads or requests, and it does not save queries from one transaction to the next, because `commit()` expires every object under the default settings. - **Expecting a query to refresh.** Re-running a `select()` does not pick up another statement's change to an already-loaded row. - **Assuming get() never queries.** It queries on a miss, on a fully expired object, and whenever `populate_existing` or `with_for_update` is passed. - **Assuming get() raises on a missing row.** It returns `None`, so the caller must check for it; `scalars(...).one()` is the form that raises. - **Reaching for the legacy form.** `session.query(Product).get(5)` still runs in 2.1 but emits a `LegacyAPIWarning`; new code calls `session.get(Product, 5)`.
- In SQLAlchemy, why does session.get(Product, 5) send a SELECT right after session.commit(), although the object was loaded before?With the default `expire_on_commit=True`, `commit()` expires every attribute of every object in the Session. `get()` still finds the object in the identity map, but because it is fully expired it sends a SELECT to reload it and to confirm the row still exists. The object returned is the same Python object as before the commit.
- How does Session.get() report a primary key that has no row, compared with scalars(select(...)).one()?`session.get(Product, 99)` returns `None`, so the caller decides what a missing row means. `session.scalars(select(Product).where(Product.id == 99)).one()` raises `NoResultFound` with "No row was found when one was required"; `one_or_none()` is the query form that returns `None` instead.
- Session.get() always sends a SELECT, exactly like a select() by primary key.
- Running a select() again overwrites the loaded object's attributes with the current row.
- Two session.get() calls for the same key return equal but distinct objects.
- The identity map is a cache shared by every Session in the process.
- session.get() raises an exception when no row has that primary key.