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?
answer
- an engine event, not the log
- listen, run, remove
- one call per cursor execution
- savepoints are statements too
basics
~10 sRegister 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 linesfrom 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
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.
Explain which statements fire the event and which do not, and why savepoints, expiry refreshes and autoflush change the number.
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.
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