skip to content

Unit-of-Work Session

The Session tracks loaded and pending objects, flushes changes and owns the transaction. Interviewers ask how long one should live, when it flushes and why attributes expire after commit.

on this pageshow

explore

questions

11

In SQLAlchemy 2.1, how does `with Session() as session, session.begin():` differ from `with Session() as session:` followed by `session.commit()`?

level: juniorimportance: must knowfreq 72%

answer

  1. two ways to frame one transaction
  2. who calls commit, and when
  3. autobegin on first use
  4. begin() after a query already ran

basics

~20 s

The begin-once form commits when its block succeeds, rolls back and re-raises on an error, then closes the Session. The commit-as-you-go form autobegins on first use, commits only at each session.commit(), and close() rolls back whatever came after.

solid answer

~40 s

Both start from a `sessionmaker(engine)` built once at module level. `with Session() as session, session.begin():` is **begin once**: the `SessionTransaction` that `session.begin()` returns commits when the block ends normally, rolls back and re-raises if it raises, and the outer context then calls `close()`. `with Session() as session:` alone is **commit as you go**: the Session **autobegins** a transaction at its first use, you call `session.commit()` or `session.rollback()` wherever a unit of work ends, the next use autobegins again, and the closing `close()` rolls back anything left uncommitted. One trap: calling `session.begin()` on a Session that has already autobegun, for example after a `select()`, raises `InvalidRequestError` ("A transaction is already begun on this Session."). `sessionmaker.begin()`, written `with Session.begin() as session:`, is shorthand for the begin-once form.

code

python · 41 lines
python
from sqlalchemy import create_engine, select, String, exc
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, sessionmaker


class Base(DeclarativeBase):
    pass


class Invoice(Base):
    __tablename__ = "invoice"
    id: Mapped[int] = mapped_column(primary_key=True)
    number: Mapped[str] = mapped_column(String(20), unique=True)
    status: Mapped[str] = mapped_column(String(10), default="open")


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
Session = sessionmaker(engine)  # configured once, at module level

# Begin once: commit on success, rollback on error, then close
with Session() as session, session.begin():
    session.add(Invoice(number="INV-1"))

# Commit as you go: autobegin on first use, commit when you choose
with Session() as session:
    invoice = session.scalars(select(Invoice).filter_by(number="INV-1")).one()
    invoice.status = "paid"
    session.commit()
    session.add(Invoice(number="INV-2"))  # never committed
# close() at the end of the block rolls INV-2 back

with Session() as session:
    print(session.scalars(select(Invoice.number)).all())  # ['INV-1']

# The trap: begin() after the Session has already autobegun
with Session() as session:
    session.scalars(select(Invoice)).all()
    try:
        session.begin()
    except exc.InvalidRequestError as err:
        print(err)  # A transaction is already begun on this Session.

go deeper

for a junior

Recall both shapes: with Session() as session, session.begin() commits or rolls back for you; with Session() as session plus session.commit() leaves committing to your code, and close() rolls back the rest.

for a middle

Explain autobegin: the transaction starts at first use, a connection is checked out only at the first SQL, and commit() or rollback() leaves the Session ready to autobegin again.

for a senior

Spot the InvalidRequestError raised when begin() follows a query, and pick begin once for all-or-nothing units and commit as you go for checkpointed batch loops.

for a principal

Weigh a team convention: begin once makes boundaries visible and impossible to forget, while commit as you go suits staged batch work; autobegin=False is the stricter option some teams enforce.

## The factory: `sessionmaker` configured once A SQLAlchemy **`Session`** is the ORM object that tracks your mapped objects and talks to the database inside one transaction at a time. You rarely construct it by hand. Instead an application builds a **`sessionmaker`** once, next to its `Engine`, and calls it whenever a unit of work starts: ```python engine = create_engine("sqlite:///billing.db") Session = sessionmaker(engine) # a factory with fixed configuration ``` The `sessionmaker` holds the configuration (`bind`, `autoflush`, `expire_on_commit`, `autobegin` and so on); each call such as `Session()` returns a fresh `Session` with those settings, and keyword arguments passed to the call override them for that one Session. Both the engine and the factory are safe to share; the Sessions they produce are not. ## Begin once ```python with Session() as session, session.begin(): session.add(Invoice(number="INV-1")) ``` Two context managers are stacked here: - `Session()` as a context manager calls **`Session.close()`** when the block exits, whatever happened. - `session.begin()` returns a **`SessionTransaction`**. Its exit **commits** if the block finished normally; if an exception escaped, it **rolls back** and lets the exception continue upward. It never swallows the error. The whole block is therefore one transaction with a guaranteed outcome. `sessionmaker.begin()` packs the same thing into one call: `with Session.begin() as session:` creates a Session, begins, commits or rolls back, and closes. ## Commit as you go, and autobegin ```python with Session() as session: invoice = session.scalars(select(Invoice).filter_by(number="INV-1")).one() invoice.status = "paid" session.commit() session.add(Invoice(number="INV-2")) # never committed ``` The Session has **autobegin**: a new Session holds no transaction, and the first operation that needs one (an `execute()`, a `scalars()`, even an `add()`) begins it. A database connection is checked out from the engine's pool only when SQL is actually emitted. After `commit()` or `rollback()` the Session has no transaction again, and the next use autobegins a new one. When the `with` block ends, `close()` rolls back whatever is still open, so `INV-2` above is never written. ## Side by side | | begin once | commit as you go | |---|---|---| | Who commits | the `session.begin()` context, on normal exit | your code, at each `session.commit()` | | On an exception | rollback, then re-raise | nothing automatic until `close()` rolls back | | Transactions per block | exactly one | as many as you commit | | Typical use | one unit of work, all or nothing | a loop or script that commits in stages | ## The trap: `begin()` after autobegin `Session.begin()` starts the **outermost** transaction, and the Session allows only one. If the Session has already autobegun, because a query ran or an object was added, `session.begin()` raises `InvalidRequestError` with the message "A transaction is already begun on this Session." The usual cause is code that queries first and wraps the writes in `with session.begin():` afterwards. The fixes: 1. Open the transaction first: `with Session() as session, session.begin():` around the reads and the writes. 2. Or stay with commit as you go and call `session.commit()` at the end. 3. For a nested scope inside a running transaction, use `session.begin_nested()`, which is a SAVEPOINT, not a second outer transaction. A Session built with `autobegin=False` reverses the default: it never begins by itself, and any use before an explicit `session.begin()` raises `InvalidRequestError`. ## What happens, in order, when a begin-once block fails 1. The exception leaves the body of the block. 2. The `SessionTransaction` exit sees it and calls rollback: a `ROLLBACK` is emitted if a connection was in use, pending objects are expunged and the rest are expired. 3. The exception continues outward; the transaction context does not suppress it. 4. The outer Session context runs `close()`, which releases the connection to the pool. The caller therefore sees the original error, and the database sees none of the block's writes. ## Choosing between them in a billing job A command-line billing job that settles one customer's invoices as a single all-or-nothing unit reads best as begin once: the code cannot forget to commit, and a failure cannot leave half the invoices written. A job that walks thousands of invoices and wants to keep progress every hundred rows suits commit as you go, with `session.commit()` at each checkpoint. Either way: - build the `sessionmaker` once and open a new `Session` per unit of work; - keep the `with Session() as session:` block, so `close()` always runs and the connection always returns to the pool; - never rely on garbage collection to end a transaction.

  • Does constructing a SQLAlchemy Session open a database connection?
    No. A new Session holds no transaction and no connection. Its transaction autobegins at the first operation that needs one, even `session.add()`, but a connection is checked out from the engine's pool only when SQL is actually emitted, for example at a query or a flush. Creating Sessions is cheap; holding one open across a slow step is what ties up a pooled connection.
  • What does `autobegin=False` change on a SQLAlchemy Session?
    With `Session(engine, autobegin=False)`, or the same flag on `sessionmaker`, the Session never begins a transaction by itself. After construction and after every `commit()`, `rollback()` or `close()`, code must call `session.begin()` before using it again; otherwise `InvalidRequestError` reports that autobegin is disabled. It makes every transaction boundary explicit, at the cost of more ceremony.

  • Leaving a with Session() block commits pending changes automatically.
  • session.begin() simply starts another transaction even after a query already ran.
  • A Session opens a database connection as soon as it is constructed.
  • In SQLAlchemy 2.x you must call session.begin() before every query.
  • The session.begin() context manager swallows the exception after rolling back.
open as a page

In SQLAlchemy 2.1, when does a Session autoflush, and why can an IntegrityError surface on a statement that only reads?

level: middleimportance: must knowfreq 50%

basics

~20 s

With autoflush on, the default, a SQLAlchemy 2.1 Session flushes its pending INSERTs, UPDATEs and DELETEs before every statement it executes, text() included. A constraint those writes break therefore fails on the read that triggered the flush.

open as a page

What does SQLAlchemy's `expire_on_commit=True` default do to a Session's objects after commit, and what does it cost the code that reads them?

level: middleimportance: must knowfreq 62%

basics

~20 s

Session.commit() expires every object it holds, erasing the loaded values. The next attribute read autobegins a transaction and SELECTs that row again, so reads after commit cost extra queries, and objects from a closed Session cannot reload.

open as a page

A SQLAlchemy Product returned after its Session closed raises DetachedInstanceError when a report reads product.stock; why, and how do you fix it?

level: seniorimportance: must knowfreq 45%

basics

~20 s

A 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.

open as a page

In SQLAlchemy 2.1, what does Session.get(Product, 5) do when called twice in one Session, and how does a select() differ?

level: juniorimportance: should knowfreq 50%

basics

~20 s

Session.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.

open as a page

In SQLAlchemy 2.1, how do Session.expire(), Session.refresh() and populate_existing differ, and when does a loaded Product need one?

level: middleimportance: should knowfreq 40%

basics

~20 s

Session.expire() erases an object's loaded attributes so the next access reloads them; Session.refresh() does that and sends the SELECT at once; populate_existing makes a query overwrite every object it returns. All three discard unflushed edits on those objects.

open as a page

With a SQLAlchemy Session, how do you read an object's state with inspect(), and which calls move it between the five states?

level: middleimportance: should knowfreq 35%

basics

~20 s

sqlalchemy.inspect(obj) returns the object's InstanceState, whose transient, pending, persistent, deleted and detached flags name its state. add() makes it pending, a flush makes it persistent, delete() plus a flush makes it deleted, and closing the Session or committing the deletion detaches it.

open as a page

In SQLAlchemy 2.1, what do `Session.commit()`, `Session.rollback()` and `Session.close()` each do to the connection and to the objects the Session holds?

level: middleimportance: should knowfreq 48%

basics

~20 s

All three end the transaction and release the connection to the pool. commit() flushes, commits and expires objects; rollback() expunges the transaction's new objects and expires the rest; close() rolls back and detaches every object.

open as a page

Why does SQLAlchemy provide `scoped_session`, and what must a thread-pool worker call on it when each task finishes?

level: middleimportance: should knowfreq 45%

basics

~20 s

A Session must not be shared across threads; scoped_session is a registry that gives each thread its own Session behind one global object. A pool worker calls remove() after each task to close and discard that Session.

open as a page

A SQLAlchemy consumer calls session.merge() on a Product rebuilt from a queue message, then edits that object; why are the edits never written?

level: seniorimportance: should knowfreq 35%

basics

~20 s

Session.merge() copies the incoming object's state onto the Session's own persistent Product, loaded by primary key, and returns that copy. The object passed in never joins the Session, so later edits to it are not tracked; edit the returned object.

open as a page

A SQLAlchemy billing job inserts 500 invoices in one transaction and one duplicate number raises IntegrityError; how does `Session.begin_nested()` let it keep the other 499?

level: seniorimportance: should knowfreq 40%

basics

~20 s

A failed flush rolls back the whole transaction and the Session refuses SQL until rollback(). Wrapping each insert in with session.begin_nested() puts it behind a SAVEPOINT, so only the duplicate is rolled back and the rest commit.

open as a page