Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 37 ยท Engine and Connections

Connection Pooling and Events

How SQLAlchemy pools connections, what pool_size, max_overflow, pool_recycle and pool_pre_ping do, and how to hook into connections and statements with events.

Opening a database connection is slow compared with running a query: the server authenticates you, allocates memory, sets up a session. A web application that opened a new connection for every request would waste most of its time. A connection pool solves that by keeping a few connections open and handing them out as needed. SQLAlchemy builds one into every engine.

When you leave a with engine.connect() block, the connection is not closed. It is returned to the pool, ready for the next caller. Knowing this explains many surprises: why a transaction must be finished before the block ends, and why a "closed" connection still shows up on the database server.

The default pools

Which pool class you get depends on the database:

Pool class Behaviour Default for
QueuePool Fixed-size pool plus temporary overflow, thread-safe server databases, SQLite files
SingletonThreadPool One connection per thread sqlite:///:memory:
NullPool No pooling, connect every time you ask for it
StaticPool Exactly one connection shared by everyone tests with in-memory SQLite
AsyncAdaptedQueuePool QueuePool for async engines async engines
Python Inspecting the pool

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 status line reports how many connections are in the pool, checked out and in overflow. With pool_size=2 and max_overflow=1, a fourth simultaneous request waits for pool_timeout seconds and then raises TimeoutError.

Note that an in-memory SQLite database exists per connection, which is why tests that need several connections to see one database use StaticPool.

Settings for server databases

These options matter when you connect to PostgreSQL or MySQL (shown read-only here, since no server runs in the browser):

Python
engine = create_engine(
    "postgresql+psycopg://app:secret@db/shop",
    pool_size=10,         # connections kept open
    max_overflow=20,      # extra connections allowed during bursts
    pool_timeout=30,      # seconds to wait for a free connection
    pool_recycle=1800,    # replace connections older than 30 minutes
    pool_pre_ping=True,   # test a connection before using it
)
  • pool_pre_ping sends a cheap check (SELECT 1) when a connection is borrowed. If the database restarted or a firewall dropped an idle connection, SQLAlchemy quietly reconnects instead of failing your request.
  • pool_recycle avoids "server has gone away" errors with servers or proxies that close idle connections after a fixed time.
  • Size the pool for your concurrency, not for your traffic. Total connections across all app processes must stay under the server's connection limit.
  • Call engine.dispose() after forking worker processes so that children do not share the parent's connections.

Events

SQLAlchemy has an event system: you register a function that runs when something happens. Typical uses are setting database options when a connection is created, timing slow queries, and adding tracing.

The most useful pool event is connect. SQLite, for example, ignores foreign keys unless you turn them on per connection, and the connect event is the right place to do it.

Python PRAGMA foreign_keys with a connect event

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.

Remove the listener and run it again: SQLite will accept the bad row, because enforcement is off by default.

Timing statements

Cursor-level events fire around each statement. Two of them give you a simple query profiler.

Python before_cursor_execute and after_cursor_execute

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.

conn.info is a dictionary that lives as long as the connection, which makes it a safe place to stash a start time. Chapter 8 returns to events for profiling and auditing.

flowchart LR
    A["engine.connect()"] --> B{"free connection<br/>in pool?"}
    B -- yes --> C["check out"]
    B -- "no, below max" --> D["open new connection<br/>(connect event)"]
    B -- "no, at max" --> E["wait pool_timeout<br/>then TimeoutError"]
    D --> C
    C --> F["run statements"]
    F --> G["close(): return to pool<br/>(rollback if needed)"]

The pool is why closing a connection is cheap: it goes back to the pool and stays open.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does closing a connection obtained from engine.connect() usually do?

    The pool keeps the underlying DBAPI connection open and reuses it.
  2. 2. Which option tests a pooled connection before it is handed out?

    pool_pre_ping checks the connection and reconnects transparently if it is dead.
  3. 3. Where do you run PRAGMA foreign_keys=ON so that it applies to every SQLite connection?

    The pragma is per connection. The connect event runs whenever the pool opens a new DBAPI connection.
  4. 4. With pool_size=5 and max_overflow=10, how many connections can be open at once?

    The pool keeps 5 and may open up to 10 more temporarily, for a maximum of 15.

Summary

  • Every engine has a connection pool; closing a connection returns it to the pool.
  • Tune pool_size, max_overflow, pool_timeout, pool_recycle and pool_pre_ping for server databases; use StaticPool for shared in-memory test databases.
  • Events (connect, before_cursor_execute, after_cursor_execute) let you configure connections and observe statements.

Next: MetaData and Table Definitions