Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 37 ยท Engine and Connections

Connections and Transactions

Understand SQLAlchemy 2.0 connections, commit-as-you-go, engine.begin(), rollback on error, savepoints with begin_nested(), and how to inspect transaction state.

A transaction is a group of statements that either all succeed or all disappear. Transfer money between two accounts: subtract from one, add to the other. If the second step fails, you must undo the first. Databases provide this guarantee through transactions, and SQLAlchemy 2.0 makes them explicit and predictable.

You get a Connection from the engine with engine.connect(). In 2.0 style, the connection starts a transaction automatically on the first statement (this is called autobegin) and keeps it open until you call commit() or rollback(), or until the with block ends. Nothing is saved unless you commit. This "commit as you go" style replaced the old autocommit behaviour of 1.x.

Commit as you go

Python connect() and commit()

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.

The second INSERT was visible inside the same transaction but disappeared on rollback(). If you leave a with engine.connect() block without committing, SQLAlchemy rolls back automatically. That surprises people who forget commit(): the code runs without error but no data is stored.

engine.begin(): commit when the block succeeds

For write operations, engine.begin() is the most convenient form. It opens a connection and a transaction, commits at the end of the block, and rolls back if an exception escapes.

Python begin() commits or rolls back for you

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.

The failing transfer raised an IntegrityError on the first UPDATE, so the transaction rolled back and the second UPDATE never ran. Both balances are unchanged. That all-or-nothing behaviour is the whole point of a transaction.

Which to use

Pattern Use it when
with engine.begin() as conn: A unit of writes that should commit or fail as one.
with engine.connect() as conn: + conn.commit() You want several commits, or mostly reads.
with conn.begin(): You already hold a connection and want an explicit block.

You cannot call conn.begin() while an autobegun transaction is already in progress. Pick one style per connection and stay with it.

Savepoints: partial rollback

Inside a transaction you can create a savepoint with begin_nested(). If the nested block fails you roll back only to the savepoint, and the outer transaction goes on.

Python begin_nested()

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.

Only the duplicate insert was undone. The rest of the transaction committed normally. Savepoints are handy for "try this, fall back if it fails" logic, and the ORM uses the same mechanism for session.begin_nested() (chapter 6).

SQLite's Python driver has historically been quirky with transactions and savepoints. SQLAlchemy documents a workaround for strict control. For everyday use, the examples above behave as shown, and PostgreSQL has no such quirk.

Isolation levels and autocommit

Databases support different isolation levels, which control what concurrent transactions can see of each other (READ COMMITTED, REPEATABLE READ, SERIALIZABLE). You can set one for an engine (isolation_level= in create_engine) or per connection with conn.execution_options(isolation_level="SERIALIZABLE"). Some drivers also offer "AUTOCOMMIT", where every statement commits at once. It is useful for commands that cannot run inside a transaction (for example CREATE DATABASE or VACUUM on some servers).

stateDiagram-v2
    [*] --> Idle: connect()
    Idle --> InTransaction: first execute() (autobegin)
    InTransaction --> InTransaction: more statements
    InTransaction --> Idle: commit() or rollback()
    Idle --> [*]: close() / end of with
    InTransaction --> [*]: end of with (rollback)

A connection is either idle or inside a transaction. Leaving the with block while a transaction is open rolls it back.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You use with engine.connect() as conn, run an INSERT and leave the block without calling commit(). What happens?

    2.0 connections never autocommit. Leaving the block ends the transaction with a rollback.
  2. 2. Which form commits automatically when the block succeeds?

    engine.begin() wraps a connection and a transaction, committing on success and rolling back on an exception.
  3. 3. What does conn.begin_nested() create?

    A nested transaction is a SAVEPOINT. Failing inside it rolls back only that part.
  4. 4. Why did the failed transfer leave both balances unchanged?

    The first UPDATE ran, but the exception triggered a rollback that undid it.

Summary

  • SQLAlchemy 2.0 connections autobegin a transaction and require an explicit commit().
  • engine.begin() commits on success and rolls back on error; it is the idiom for writes.
  • begin_nested() creates a savepoint for partial rollback.
  • Isolation level and AUTOCOMMIT can be set per engine or per connection.

Next: Running Text SQL with Bound Parameters