SQLAlchemy 2.0 #4 The Session: Change Tracking, flush vs commit, and the Four Object States

In Core, we pushed SQL directly with conn.execute(). The ORM works differently. You create, modify, and delete objects, and the Session remembers those changes and converts them to SQL at the right moment. That “remember, then emit in one go” is the Unit of Work pattern, and understanding the Session means understanding that timing.

Creating sessions: sessionmaker #

A session is a short-lived workspace that borrows connections from the engine. You could build one each time with Session(engine), but the convention is a factory with the configuration baked in.

from sqlalchemy.orm import Session, sessionmaker

SessionLocal = sessionmaker(engine)

# default pattern: the whole block is one transaction
with SessionLocal.begin() as session:
    session.add(User(name="Alice", email="alice@example.com"))
# normal exit → commit, exception → rollback

# when you need finer control
with SessionLocal() as session:
    user = User(name="Bob", email="bob@example.com")
    session.add(user)
    session.commit()

sessionmaker.begin() is the ORM counterpart of engine.begin() from part 2. For short scripts it is the default form; use the second form when you need to choose commit points yourself.

Changes do not become SQL immediately: flush and commit #

The Session’s core behavior, verified in code:

with SessionLocal() as session:
    user = User(name="Carol", email="carol@example.com")
    session.add(user)      # no SQL yet — the session just notes "new object"
    print(user.id)         # None: no primary key before INSERT

    session.flush()        # INSERT executes here (transaction still open)
    print(user.id)         # 1: the database-issued key is filled in

    session.commit()       # transaction finalized

• flush: converts the session’s accumulated changes (INSERT, UPDATE, DELETE) to SQL and sends them to the database — inside the transaction, not yet final.
• commit: performs one more flush, then finalizes the transaction. That is why most code never calls flush directly.

The classic reason to call flush yourself is the one above: you need the database-issued primary key before commit. The session also flushes automatically right before executing a query (autoflush) — that is why an object you just added shows up in the very next select.

Updates and deletes work the same way. Changing an attribute on a loaded object is by itself enough to mark it for UPDATE. There is no save call.

with SessionLocal.begin() as session:
    user = session.get(User, 1)   # primary-key lookup
    user.name = "Caroline"        # the session detects the change (dirty)
    session.delete(session.get(User, 2))  # DELETE scheduled
# on block exit, the UPDATE and DELETE flush and commit together

The four object states #

The relationship between a session and an object comes down to four states. The terms appear verbatim in error messages, so knowing them speeds up debugging.

The one that bites in practice is detached. Touch an unloaded attribute (especially the relationship attributes of the next part) after the session has closed, and you get DetachedInstanceError. Keep the principle in mind — an object outside its session can no longer talk to the database — and the cause is always obvious.

The identity map: same row, same object #

Query the same primary key twice within one session, and the second lookup returns the very object the session already holds.

with SessionLocal() as session:
    a = session.get(User, 1)
    b = session.get(User, 1)
    print(a is b)  # True — the same Python object

That is the identity map. The session maintains a “primary key → object” dictionary, which structurally prevents the accident of two objects representing the same row with diverging values inside one transaction. The flip side: different sessions mean different objects even for the same row. A change made in session A is not automatically reflected in session B’s object.

Why the first access after commit queries again: expire #

Under the default (expire_on_commit=True), commit marks every object in the session expired. The first attribute access after commit makes the session run a SELECT to fetch fresh values. Another transaction may have changed the row between commits; this is the safety mechanism against holding stale values.

Two practical consequences follow.

• Reading object attributes one by one in a loop after commit can emit multiple SELECTs. Turn on echo=True and it is immediately visible.
• Access an attribute after committing and closing the session, and there is no session left to reload the expired attribute — the DetachedInstanceError from above. Before returning objects in an API response, read what you need first, or finish converting to response data (a Pydantic model, say) inside the session. That is the standard practice.

Session scope: one session per request #

Where sessions are created and closed is an architecture question with one principle: one session per unit of work. In a web application, one HTTP request is the unit of work.

• Never keep one global session forever. Sessions are not thread-safe, and once an error leaves one needing a rollback, every subsequent request is poisoned.
• In FastAPI, create a session per request via dependency injection and close it after the response. The concrete pattern is in Modern Python in Practice #3.
• In batch jobs, open and close a session per logical unit (one file, one chunk). Processing hundreds of thousands of rows in a single session piles up tracked objects, and memory and flush time grow together.

Summary #

• The Session is a unit-of-work manager that batches changes and emits SQL at flush time. add and attribute changes do not become SQL immediately.
• flush sends the SQL; commit is flush plus finalize. Call flush directly only when you need a database-issued key early.
• Objects move through transient, pending, persistent, and detached. Touching unloaded attributes outside a session raises DetachedInstanceError.
• Within a session, the same row is always the same object (identity map). Commit expires attributes so the next access re-reads them.
• The scoping rule is one session per unit of work. No global sessions.
• Next part: relationship — declaring 1:N and N:M, and the ORM’s biggest trap, the N+1 problem.