SQLAlchemy 2.0 #2 Engines and Transactions: Connection Pools and the Two Commit Patterns

Programming Language Python SQLAlchemy Database
5 min read

In part 1 we created an engine and ran queries with text(). This part is about what happens underneath. Knowing exactly how the connection pool works and when transactions begin and commit is what keeps you steady once you climb up to the ORM. The answers to the production classics — “connection pool exhausted,” “I committed but nothing changed” — all live in this layer.

The connection pool: what the engine actually does #

Database connections are expensive. TCP setup, authentication, and session initialization cost milliseconds, and the server caps how many concurrent connections it will take. So instead of opening a fresh connection every time, the engine keeps them in a connection pool and reuses them.

main.py 
from sqlalchemy import create_engine

engine = create_engine(
    "postgresql+psycopg2://user:pw@localhost/mydb",
    pool_size=5,        # connections kept in the pool (default 5)
    max_overflow=10,    # extra connections opened when the pool is empty (default 10)
    pool_timeout=30,    # seconds to wait when nothing is available (default 30)
    pool_pre_ping=True, # check liveness before lending a connection
)

It works like a lending library. engine.connect() borrows a connection from the pool, and when the with block ends the connection is returned, not closed. Three values are worth knowing.

  • pool_size + max_overflow is the effective ceiling. With defaults, a process can hold at most 15 concurrent connections; beyond that, requests wait pool_timeout seconds and then raise TimeoutError. The usual culprit behind “pool exhausted” errors is connections that never got returned — borrowed without with and never closed — eating the pool.
  • Multiply by the number of processes. Four gunicorn workers mean four pools, and up to 60 connections for the database to absorb. Budget this against the database’s own max_connections.
  • pool_pre_ping filters out connections that the server silently dropped while they sat idle. If you meet “MySQL server has gone away”-type errors, check this option first.

SQLite file databases have almost no connection cost, so pool settings rarely matter there. Pools become a real concern with PostgreSQL and MySQL across a network.

Transactions: there is no autocommit #

The governing rule in 2.0 is that transactions are always explicit. DBAPI drivers are not autocommit by nature, and SQLAlchemy does not paper over that. If you do not commit, the block ends in a rollback.

main.py 
from sqlalchemy import text

# This INSERT disappears — it was never committed.
with engine.connect() as conn:
    conn.execute(text("INSERT INTO memo (body) VALUES ('temp')"))
# block ends → rollback

There are two patterns for committing.

main.py 
# Pattern 1: commit-as-you-go — commit exactly where you choose
with engine.connect() as conn:
    conn.execute(text("INSERT INTO memo (body) VALUES ('first')"))
    conn.commit()  # everything up to here is final
    conn.execute(text("INSERT INTO memo (body) VALUES ('second')"))
    conn.commit()  # second transaction finalized

# Pattern 2: begin-once — the whole block is one transaction
with engine.begin() as conn:
    conn.execute(text("INSERT INTO memo (body) VALUES ('third')"))
    conn.execute(text("INSERT INTO memo (body) VALUES ('fourth')"))
# commits if the block exits normally, rolls back on exception

The production default is pattern 2 (engine.begin()). It matches the whole point of a transaction — all-or-nothing — and makes forgetting to commit impossible. Pattern 1 is for cases like long batch jobs where one connection needs several transaction boundaries as intermediate save points.

Defining tables: MetaData and Table #

In Core, a table is a Table object, and the catalog of tables lives in a MetaData.

main.py 
from sqlalchemy import MetaData, Table, Column, Integer, String, ForeignKey

metadata = MetaData()

user_table = Table(
    "user_account",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(30), nullable=False),
    Column("email", String(100), nullable=False, unique=True),
)

address_table = Table(
    "address",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("user_id", ForeignKey("user_account.id"), nullable=False),
    Column("email_address", String(100), nullable=False),
)

metadata.create_all(engine)  # CREATE TABLE only for tables that do not exist

create_all() is convenient for learning and prototypes, but it never alters existing tables. Schema changes like adding a column belong to Alembic, which part 7 covers.

CRUD with Core: SQL as Python expressions #

Unlike text(), Core expressions compose SQL from Python objects. Typos no longer hide inside strings, and parameter binding is automatic.

main.py 
from sqlalchemy import insert, select, update, delete

# INSERT
with engine.begin() as conn:
    conn.execute(
        insert(user_table),
        [
            {"name": "Alice", "email": "alice@example.com"},
            {"name": "Bob", "email": "bob@example.com"},
        ],
    )

# SELECT
with engine.connect() as conn:
    stmt = select(user_table).where(user_table.c.name == "Alice")
    for row in conn.execute(stmt):
        print(row.id, row.name, row.email)

# UPDATE and DELETE
with engine.begin() as conn:
    conn.execute(
        update(user_table)
        .where(user_table.c.email == "bob@example.com")
        .values(name="Robert")
    )
    conn.execute(delete(user_table).where(user_table.c.id == 99))
  • Columns are accessed as table.c.column_name. user_table.c.name == "Alice" does not produce a boolean — it produces a SQL condition object. The Python operators are overloaded.
  • Passing a list of dictionaries to insert() becomes an executemany that inserts multiple rows at once — the basic form for bulk loading.
  • With echo=True on, you can see what SQL each expression compiles to, and confirm that every where() condition becomes a bound parameter (? or %(name)s).

This select() syntax is exactly what the ORM uses from the next part on. The only change is that user_table.c.name becomes User.name. This is the payoff of learning Core first in 2.0.

Summary #

  • An engine is, in substance, a connection pool. pool_size + max_overflow is the per-process concurrency ceiling; multiply by worker count and reconcile with the database’s max_connections.
  • The main cause of pool exhaustion is unreturned connections. Always borrow with with.
  • There is no autocommit. Default to engine.begin() wrapping the block in one transaction, and use connect() + commit() only when you need mid-stream commits.
  • Tables are defined with Table and MetaData, and manipulated with the insert, select, update, and delete expressions. Conditions are written with Python operators but compile to bound parameters.
  • Next up: declaring the same tables as ORM classes with Mapped and mapped_column.
X