In SQLAlchemy 2.1, how does `with Session() as session, session.begin():` differ from `with Session() as session:` followed by `session.commit()`?
answer
- two ways to frame one transaction
- who calls commit, and when
- autobegin on first use
- begin() after a query already ran
basics
~20 sThe 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 sBoth 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 linesfrom 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
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.
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.
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.
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.