skip to content

In a SQLAlchemy test, how do you count the SQL statements a code path sends with a before_cursor_execute listener, and what inflates the count?

level: middleimportance: should knowfreq 25%

answer

  1. an engine event, not the log
  2. listen, run, remove
  3. one call per cursor execution
  4. savepoints are statements too

basics

~10 s

Register a before_cursor_execute listener on the test's Connection with event.listen, append each statement, run the code, then event.remove it. SAVEPOINTs from the test recipe, refresh SELECTs after commit, and per-batch INSERTs all count.

solid answer

~40 s

`event.listen(connection, "before_cursor_execute", fn)` registers a function called as `fn(conn, cursor, statement, parameters, context, executemany)` before each DBAPI cursor execution; append `statement` to a list, run the code, and remove the listener with `event.remove()` with the same arguments in a `finally`. Assert on the list, not just its length, so a failure prints the SQL. The count includes `SAVEPOINT` and `RELEASE SAVEPOINT` (they are executed through a cursor), each insertmanyvalues batch, and any lazy loads or refreshes of objects expired by the last commit. `BEGIN`, `COMMIT` and `ROLLBACK` go through DBAPI connection methods and are not counted, though `echo` logs them. Call `session.connection()` before the measured block so the Session's opening `SAVEPOINT` falls outside it.

code

python · 72 lines
python
from contextlib import contextmanager

from sqlalchemy import ForeignKey, String, create_engine, event, select
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    Session,
    mapped_column,
    relationship,
    selectinload,
)
from sqlalchemy.pool import StaticPool


class Base(DeclarativeBase):
    pass


class Customer(Base):
    __tablename__ = "customer"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    orders: Mapped[list["Order"]] = relationship(back_populates="customer")


class Order(Base):
    __tablename__ = "order_"
    id: Mapped[int] = mapped_column(primary_key=True)
    customer_id: Mapped[int] = mapped_column(ForeignKey("customer.id"))
    customer: Mapped[Customer] = relationship(back_populates="orders")


@contextmanager
def count_statements(connection):
    statements = []

    def before_cursor_execute(conn, cursor, statement, parameters, context, executemany):
        statements.append(statement)

    event.listen(connection, "before_cursor_execute", before_cursor_execute)
    try:
        yield statements
    finally:
        event.remove(connection, "before_cursor_execute", before_cursor_execute)


engine = create_engine(
    "sqlite://",
    poolclass=StaticPool,
    connect_args={"check_same_thread": False, "autocommit": False},
)
Base.metadata.create_all(engine)

connection = engine.connect()
trans = connection.begin()
session = Session(bind=connection, join_transaction_mode="create_savepoint")
session.add_all([Customer(name=f"c{i}", orders=[Order(), Order()]) for i in range(3)])
session.commit()

session.connection()  # start the Session's SAVEPOINT before counting
with count_statements(connection) as statements:
    customers = session.scalars(
        select(Customer).options(selectinload(Customer.orders))
    ).all()
    total_orders = sum(len(c.orders) for c in customers)

print(total_orders, len(statements))  # 6 2
assert len(statements) <= 2, statements

session.close()
trans.rollback()
connection.close()

go deeper

for a junior

Know that SQLAlchemy has engine events and that before_cursor_execute runs once per DBAPI cursor execution; register with event.listen, unregister with event.remove.

for a middle

Explain which statements fire the event and which do not, and why savepoints, expiry refreshes and autoflush change the number.

for a senior

Build a counter that is stable across runs: bounded to one connection, opened after the savepoint, asserting on the SQL list, and run on the production dialect.

for a principal

Decide which code paths carry a statement budget, who owns the counter helper, and how budgets measured in tests are kept honest against the production database.

## Why count in a test A statement count turns a performance property, such as "this listing issues two queries, not one per row", into an assertion that fails the build. SQLAlchemy's public API offers no ready-made counter for application tests; you build one from an **engine event**. Counting log lines is not a substitute: `echo` prints transaction markers and parameter lines beside each statement. ## The listener **`before_cursor_execute`** is a `ConnectionEvents` hook. It can be registered on the `Engine` class, an `Engine`, or a single `Connection`, and receives: - `conn` — the SQLAlchemy `Connection` - `cursor` — the DBAPI cursor about to run - `statement` — the exact SQL string prepared for the driver - `parameters` — the parameters for it - `context` — the execution context - `executemany` — `True` when a list of parameter sets goes in one call In the outer-transaction test recipe, register it on the **test's connection** so only that test's statements are counted, and remove it with **`event.remove()`** using the same three arguments: ```python from contextlib import contextmanager from sqlalchemy import event @contextmanager def count_statements(connection): statements = [] def before_cursor_execute(conn, cursor, statement, parameters, context, executemany): statements.append(statement) event.listen(connection, "before_cursor_execute", before_cursor_execute) try: yield statements finally: event.remove(connection, "before_cursor_execute", before_cursor_execute) ``` ## What fires it and what does not | Activity | Fires `before_cursor_execute`? | |---|---| | a SELECT, INSERT, UPDATE or DELETE | yes, once | | `SAVEPOINT`, `RELEASE SAVEPOINT`, `ROLLBACK TO SAVEPOINT` | yes, each one | | an `executemany()` call with a list of parameter sets | once for the whole list | | each batch of an insertmanyvalues INSERT ... RETURNING | once per batch | | `BEGIN` started by the driver | no | | `COMMIT` and `ROLLBACK` | no; they are DBAPI connection methods | ## Things that inflate the count - **The recipe's own savepoints.** After `session.commit()`, the next statement makes a `create_savepoint` Session open a new `SAVEPOINT`. If counting starts first, it counts one statement the code under test never wrote. - **Expiry on commit.** With `expire_on_commit=True` (the default), touching an object loaded before the last commit refreshes it with a SELECT. - **Autoflush.** Pending changes are flushed before a query runs, and the INSERTs or UPDATEs land in the measured block. - **Assertions inside the block.** Test code that touches an unloaded relationship while checking results adds its own lazy-load SELECTs; read what you assert on after the block closes. - **Dialect batching.** A flush that needs primary keys back sends one INSERT per row on SQLite (the log notes `batch not supported`), while PostgreSQL's dialect can batch the same rows. A flush budget tuned on one database may not hold on the other. ## Making the assertion stable 1. Finish setup, then call **`session.connection()`** so the Session's savepoint is already open, or filter out statements that start with `SAVEPOINT`, `RELEASE` or `ROLLBACK TO`. 2. Load test data with a commit before measuring, and re-query what the code needs inside the block rather than reusing expired objects. 3. Measure exactly one call of the code under test inside the context manager. 4. Assert `len(statements) <= budget` with the list in the failure message, so a regression shows the extra SQL. 5. Run the budget on the database production uses; a SQLite count describes SQLite. ## Engine or Connection scope Where the listener is registered decides what it sees: - **On the test's `Connection`** — only statements on that connection. In the outer-transaction recipe this is exactly the code under test, which makes it the usual choice. - **On the `Engine`** — every connection the engine hands out, including ones a background thread or another fixture checks out. Simple when tests run strictly one at a time, noisy otherwise. - **On the `Engine` class** — every engine in the process. Useful for a global debugging hook, wrong for a per-test budget. Whichever scope you pick, keep the list of statements and not just a number: a failing budget that prints the SQL tells the reader which relationship loaded lazily or which flush split into per-row INSERTs, while a bare number only says that something changed. ## Where this stops This is the SQLAlchemy mechanism for counting. When to set a budget, how strict to make it and how to watch query counts in production are general data-access questions, the same for any ORM.

  • Why does the counter report a SAVEPOINT the code under test never wrote?
    Under `join_transaction_mode="create_savepoint"`, a Session that has committed opens a new `SAVEPOINT` on its next statement, and `SAVEPOINT` is executed through a cursor, so it fires `before_cursor_execute`. Begin the Session with `session.connection()` before measuring, or filter savepoint statements out.
  • Does a bulk INSERT of 100 rows count as one statement?
    It depends on how it executes. A Core `insert()` with a list and no RETURNING goes through one `executemany()` and fires once. With RETURNING, insertmanyvalues fires once per batch; when the ORM needs keys back in order, SQLite sends one INSERT per row.
  • Why not count lines from echo or the sqlalchemy.engine logger?
    The log interleaves `BEGIN (implicit)`, `COMMIT` and `ROLLBACK` markers and a parameter line per statement, and its level is only read when a connection is checked out. A cursor event counts exactly what the driver executes.

  • Counting echo output lines and calling the number a statement count
  • Leaving the listener attached so later tests add to the count
  • Expecting COMMIT and ROLLBACK to appear in before_cursor_execute
  • Starting the count before the Session has opened its SAVEPOINT
  • Assuming a flush's INSERT count is the same on SQLite and PostgreSQL