Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 37 ยท Sessions and Transactions

Sessions, the Identity Map and the Unit of Work

Understand what a SQLAlchemy Session really is: a unit of work with an identity map. Add, get, query, modify and delete objects and see exactly when SQL is sent.

Everything you do with ORM objects goes through a Session. Beginners treat it as a connection with a nicer API, but it is more: it is a workspace that remembers every object it has loaded or created, tracks what you change, and sends the right INSERT, UPDATE and DELETE statements in the right order when you ask it to. Understanding this model removes most of the surprises people have with the ORM.

Two ideas inside every session

The identity map. A session keeps at most one Python object per database row (per primary key). Load the same row twice in the same session and you get the same object, not two copies.

The unit of work. The session records changes (new objects, modified attributes, deletions) and writes them to the database together, in dependency order, when it flushes. Parents are inserted before their children, deletes happen in the reverse order, and you never write that ordering yourself.

flowchart LR
    Code["Your code<br/>add, modify, delete"] --> S["Session<br/>identity map + pending changes"]
    S -- "flush" --> DB[("Database<br/>INSERT / UPDATE / DELETE")]
    DB -- "commit" --> Done["Changes permanent"]

Your code changes objects in memory. The session sends SQL on flush, and the transaction becomes permanent on commit.

The identity map in action

Python One row, one object

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

session.get(User, 1) is special: if the object is already in the identity map it returns it without any SQL. A select() always queries the database, but the rows it returns are matched to objects already in the map, so identity is preserved.

Adding, changing and deleting

The four verbs you need:

Action Code When SQL is sent
Create session.add(obj) / add_all([...]) on flush
Read session.get(Cls, pk) or session.scalars(select(...)) immediately
Update assign to an attribute on flush (only changed columns)
Delete session.delete(obj) on flush

The session sends nothing until it has to. Flush happens automatically before a query (so the query sees your pending changes), on commit(), or when you call session.flush() yourself.

Python Watching SQL appear on flush

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Look at the UPDATE: it sets only done, because the session knows which attribute changed. Setting an attribute to the value it already has sends nothing at all.

The ORM does not tell you about changes in the database

The identity map is a cache of rows as of the time they were loaded. If another process (or raw SQL on the same connection) changes the row, your object does not notice. After commit() the session expires all objects, so the next attribute access reloads fresh data. That is the default expire_on_commit=True, and it is why reading user.name after a commit sends a SELECT.

Querying with the modern select() API

The 2.0 way to read is session.execute(select(...)) or the convenience session.scalars(select(...)):

Python
users = session.scalars(select(User).where(User.name.like("A%")).order_by(User.id)).all()
first = session.scalars(select(User)).first()          # first row or None
only = session.scalars(select(User).where(...)).one()  # exactly one, else error
maybe = session.scalar(select(User).where(...))        # first column of first row or None
pairs = session.execute(select(User.id, User.name)).all()   # rows of tuples

The difference: scalars() unwraps the first column (usually the entity), while execute() returns Row objects. Use one() when you expect exactly one row, one_or_none() when zero is fine, and first() when extra rows should be ignored.

Bulk helpers: update and delete statements

Changing thousands of rows by loading each object is slow. For those cases use SQL-level statements through the session. They run in one statement and do not touch the identity map unless you ask:

Python Set-based UPDATE and DELETE

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Set-based statements bypass cascades configured on relationships and do not fire per-object events. Use them for pure column changes. When you need relationship behavior, load the objects and use session.delete().

Always close the session

A session holds a connection while a transaction is open. Use with Session(engine) as session: so it is closed (and the transaction rolled back if you did not commit) even when an error happens. Closing releases the connection to the pool and detaches the objects, which brings us to object states in the next lesson.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the identity map guarantee?

    Loading the same primary key twice in one session returns the same object.
  2. 2. When does session.add(obj) send an INSERT?

    add() only registers the object as pending. The unit of work writes it on the next flush.
  3. 3. Which call returns an object without any SQL if it is already in the identity map?

    get() checks the identity map first and only queries when the object is absent.
  4. 4. Why use with Session(engine) as session?

    The context manager closes the session, releasing the connection and rolling back unfinished work.

Summary

  • A Session is an identity map plus a unit of work, not just a connection.
  • Changes are collected in memory and written in dependency order on flush.
  • Flush runs before queries (autoflush), on commit() and on explicit flush().
  • get() uses the identity map; select() always queries but maps rows onto existing objects.
  • After commit() objects are expired and reload on next access.
  • Use update() and delete() statements for set-based changes; use with Session(...) to always close.

Next: Object States, Flush and Expire