In SQLAlchemy 2.x, how does engine.connect() with Connection.commit() differ from engine.begin(), and what happens to uncommitted work?
answer
- two transaction styles
- autobegin on first execute
- context exit decides the outcome
- close means rollback
basics
~20 sengine.connect() gives a Connection that autobegins a transaction and commits only when you call commit(); closing it rolls back anything uncommitted. engine.begin() wraps the block in one transaction that commits on normal exit and rolls back on an exception.
solid answer
~40 s`engine.connect()` returns a `Connection` in **commit-as-you-go** style: the first `execute()` autobegins a transaction, `conn.commit()` or `conn.rollback()` ends it, and the next `execute()` starts another. When the `with` block ends, `Connection.close()` rolls back whatever was not committed, so a forgotten `commit()` loses the writes without any error. `engine.begin()` is **begin once**: it checks out a connection and frames the whole block as a single transaction that commits when the block exits normally and rolls back and re-raises on an exception. Prefer `engine.begin()` for a unit of work with one outcome, and `connect()` when one connection must commit several separate batches. Calling `conn.begin()` after a statement already autobegan raises `InvalidRequestError`. SQLAlchemy 2.0 introduced this model and removed library-level autocommit.
code
python · 26 linesfrom sqlalchemy import create_engine, text
engine = create_engine("sqlite:///orders.db", echo=True)
with engine.begin() as conn: # begin once: COMMIT on exit
conn.execute(text("CREATE TABLE IF NOT EXISTS daily_totals (day TEXT, total INTEGER)"))
with engine.connect() as conn: # commit as you go
conn.execute(
text("INSERT INTO daily_totals (day, total) VALUES (:day, :total)"),
{"day": "2026-10-09", "total": 1250},
)
conn.commit() # without this line the block ends in ROLLBACK
try:
with engine.begin() as conn:
conn.execute(
text("INSERT INTO daily_totals (day, total) VALUES (:day, :total)"),
{"day": "2026-10-10", "total": 99},
)
raise RuntimeError("report failed halfway")
except RuntimeError:
pass # engine.begin() emitted ROLLBACK, so the 2026-10-10 row is gone
with engine.connect() as conn:
print(conn.execute(text("SELECT day, total FROM daily_totals")).all())go deeper
Know that engine.begin() commits at the end of its block, and that a connect() block needs an explicit conn.commit() or its writes vanish.
Explain autobegin, why Connection.close() rolls back, the InvalidRequestError when begin() follows an autobegun statement, and when commit-as-you-go beats begin-once.
Recognise the BEGIN (implicit) then ROLLBACK pattern in a log as a missing commit, and catch 1.x code ported to 2.x that relied on autocommit.
Set a team default, such as engine.begin() for every unit of work and connect() only where batches commit separately, and make reviews look for connect() blocks that write.
## The Connection always works inside a transaction A SQLAlchemy **Connection** wraps one DBAPI connection checked out of the engine's pool. In SQLAlchemy 2.x it never runs a statement outside a transaction unless you ask for driver-level autocommit. The first time `Connection.execute()` runs, the connection **autobegins**: SQLAlchemy marks a transaction as started (the echo log shows `BEGIN (implicit)`), and the DBAPI driver begins the real transaction when it sends the statement. That transaction stays open until something ends it. There are two styles for ending it. ## Commit as you go: engine.connect() `with engine.connect() as conn:` hands you a Connection and leaves the transaction boundaries to you. - `conn.commit()` commits everything since the transaction began. - `conn.rollback()` discards it. - The next `conn.execute()` after either one autobegins a fresh transaction. - When the `with` block exits, `Connection.close()` returns the DBAPI connection to the pool and **unconditionally rolls back** any transaction still open. That last point is the trap: a block that executes an INSERT and never calls `commit()` raises no error. The echo log shows `BEGIN (implicit)`, the INSERT, then `ROLLBACK`, and the row is gone. Commit as you go suits code that needs several commits on one connection, for example a nightly report that commits each day's totals separately so one bad day does not undo the others. ## Begin once: engine.begin() and Connection.begin() `with engine.begin() as conn:` checks out a connection **and** opens a transaction in one step. On exit: 1. if the block finished normally, it emits `COMMIT`; 2. if an exception escaped, it emits `ROLLBACK` and lets the exception propagate; 3. either way, the connection goes back to the pool. The same framing is available on an existing connection as `with conn.begin():`, which returns a `Transaction` object that is also a context manager. The documentation recommends begin once for most code, because the block states its intent up front and cannot forget to commit. ## Rules where the styles meet | Situation | Result | |---|---| | `conn.begin()` before any statement on a fresh `connect()` block | starts an explicit transaction | | `conn.begin()` after a statement already autobegan | raises `InvalidRequestError` until `commit()` or `rollback()` is called | | `conn.commit()` inside `engine.begin()` | commits early; any further SQL in that block raises `InvalidRequestError` | | `connect()` block exits without `commit()` | `ROLLBACK`; uncommitted work discarded | | exception inside `engine.begin()` | `ROLLBACK`, exception re-raised | The styles can be mixed inside one `connect()` block, provided each `conn.begin()` comes before any statement or straight after a commit or rollback. ## What 2.0 changed Before SQLAlchemy 2.0 an engine could run statements directly with `Engine.execute()`, and the library committed INSERT, UPDATE and DELETE on its own (library-level autocommit). Both were removed in 2.0, which made commit as you go the standard style (1.4 offered it only in a transitional mode): every statement now runs on a `Connection`, and nothing is committed unless code commits it or `engine.begin()` does. Code ported from 1.x that simply swapped `engine.execute()` for `with engine.connect() as conn: conn.execute()` therefore loses its writes. When a statement genuinely must run outside a transaction, the supported switch is driver-level autocommit: `engine.execution_options(isolation_level="AUTOCOMMIT")` or the same option on a `Connection`. ## Choosing between them - **One unit of work, one outcome** (write an order and its lines): `engine.begin()`. - **Several independent batches on one connection** (commit per day, per file): `engine.connect()` with `conn.commit()` after each batch. - **Read-only work**: either; the closing `ROLLBACK` costs little and releases any locks or snapshot the read held. - **Never** rely on block exit to commit a `connect()` block. ## Mistakes that show up in review - **An early `return` before `commit()`** in a `connect()` block: that path skips the commit and the block ends in `ROLLBACK`, with no error. - **Catching an exception inside `engine.begin()` and carrying on**: the block then exits normally and attempts a `COMMIT` instead of the automatic rollback, so the all-or-nothing promise holds only when exceptions are allowed to escape the block. - **One long `connect()` block for a whole job**: the autobegun transaction stays open until the next commit, keeping its locks and snapshot for the entire run. Commit per batch instead. The ORM `Session` has its own `commit()` and `begin()` with parallel rules; those are covered with the Session's transaction scope.
- In SQLAlchemy, what happens if you call conn.commit() inside a with engine.begin() block and then execute another statement?The early `commit()` ends the transaction the block was framing. Any further SQL on that connection inside the block raises `InvalidRequestError` ("Can't operate on closed transaction inside context manager") until the block exits. Use `engine.connect()` if you need several commits.
- How do you run a statement that must not be inside a transaction on a SQLAlchemy Connection?Switch the connection, or a copy of the engine, to driver-level autocommit with `execution_options(isolation_level="AUTOCOMMIT")`. SQLAlchemy treats autocommit as an isolation level: each statement is committed by the database as it runs, so you also lose atomicity across statements.
- Why does a SQLAlchemy connect() block that only reads still log ROLLBACK at the end?Every Connection runs inside an autobegun transaction, and `Connection.close()` ends any open one with a rollback. For a read this is harmless and releases whatever snapshot or locks the read held; it does not mean an error happened.
engine.begin() is an editor that saves your document when you close it normally and discards it if the program crashes; engine.connect() is an editor where you press save yourself, and closing without saving throws the changes away.
- Leaving a with engine.connect() block commits the work automatically.
- engine.begin() commits the statements that succeeded even when the block raises.
- Calling conn.begin() after an execute() opens a nested savepoint.
- Engine.execute() with autocommit is still the shortest way to run an INSERT.
- A ROLLBACK in the log of a read-only block means a query failed.
Assumes SQLAlchemy 2.x; 2.0 made commit-as-you-go the Connection's standard style and removed Engine.execute() and library-level autocommit, so 1.x code that relied on them now rolls back.