skip to content

With SQLAlchemy's before_cursor_execute and after_cursor_execute events, how do you log every statement slower than a set threshold?

level: middleimportance: should knowfreq 32%

answer

  1. two hooks around cursor.execute()
  2. start time stashed on the connection
  3. a stack, not a single slot
  4. statement text, parameters left out

basics

~20 s

Register both events on the engine: before_cursor_execute pushes a start time onto conn.info, after_cursor_execute pops it and logs the SQL string when the elapsed time passes the threshold, leaving the bound parameters out of the log.

solid answer

~40 s

Attach two listeners to the application's `Engine`. `before_cursor_execute(conn, cursor, statement, parameters, context, executemany)` appends `time.perf_counter()` to a list in `conn.info`; `after_cursor_execute` pops it, computes the elapsed time and, above the threshold, logs `statement`, the exact SQL string the dialect hands the driver, with placeholders such as `?` or `%(name)s` instead of values. Leave `parameters` out by default, because bound values may hold personal data. The list is a stack because cursor executions can nest. Know the blind spots: the timing covers `cursor.execute()` only, not fetching rows afterwards, and when a statement raises, `after_cursor_execute` never runs while `handle_error` does. Among the engine's events, only `before_execute`, `before_cursor_execute` and `handle_error` accept `retval=True`.

code

python · 26 lines
python
import logging
import time

from sqlalchemy import create_engine, event, text

logging.basicConfig(level=logging.WARNING)
log = logging.getLogger("app.slow_sql")
SLOW_SECONDS = 0.0  # set to 0.5 in production; 0.0 here so the demo logs

engine = create_engine("sqlite://")


@event.listens_for(engine, "before_cursor_execute")
def start_timer(conn, cursor, statement, parameters, context, executemany):
    conn.info.setdefault("query_start", []).append(time.perf_counter())


@event.listens_for(engine, "after_cursor_execute")
def log_slow(conn, cursor, statement, parameters, context, executemany):
    elapsed = time.perf_counter() - conn.info["query_start"].pop()
    if elapsed >= SLOW_SECONDS:
        log.warning("slow statement %.3fs: %s", elapsed, statement)


with engine.connect() as conn:
    conn.execute(text("SELECT :x AS answer"), {"x": 42})

go deeper

for a junior

Recall the two event names, that both receive the SQL string and parameters, and that they are registered on the Engine.

for a middle

Explain the conn.info stack, which hook can use retval=True, what statement contains, and why the pair misses a failed statement.

for a senior

Build the listener cheaply, keep parameters out of logs, cover timeouts through handle_error, and know the timing stops at cursor.execute().

for a principal

Decide what statement telemetry a team keeps in-process versus in the database's own slow log, and who may read logged SQL.

## The two hooks SQLAlchemy's **connection events** fire around every statement an `Engine` executes, whether it came from the ORM, from Core or from `text()`. Two of them bracket the driver call itself: - `before_cursor_execute(conn, cursor, statement, parameters, context, executemany)` runs just before SQLAlchemy calls the DBAPI cursor's `execute()` or `executemany()`. - `after_cursor_execute(...)` with the same arguments runs just after that call returns. `statement` is the final SQL string as the dialect prepared it, with the driver's placeholders (`?` for SQLite, `%(name)s` for drivers using the pyformat style) rather than literal values. `parameters` is the dictionary, tuple or list of values sent alongside. `executemany` says whether the call was an `executemany()`. ## The recipe The SQLAlchemy FAQ's timing recipe stores the start time on `conn.info`, a dictionary attached to the underlying DBAPI connection: ```python @event.listens_for(engine, "before_cursor_execute") def start_timer(conn, cursor, statement, parameters, context, executemany): conn.info.setdefault("query_start", []).append(time.perf_counter()) @event.listens_for(engine, "after_cursor_execute") def log_slow(conn, cursor, statement, parameters, context, executemany): elapsed = time.perf_counter() - conn.info["query_start"].pop() if elapsed >= SLOW_SECONDS: log.warning("slow statement %.3fs: %s", elapsed, statement) ``` Three details matter: 1. **A list, used as a stack.** The FAQ uses a stack for the occasional case where cursor execute events nest; each `after_cursor_execute` pops the start time pushed by its own `before_cursor_execute`. 2. **`conn.info` outlives the `Connection`.** It follows the DBAPI connection back into the pool and into the next checkout, so nothing stored there should assume a fresh dictionary. 3. **`time.perf_counter()`** is a monotonic clock; wall-clock `time.time()` can jump. Register on the engine instance, not the `Engine` class, so a second engine, a test engine for instance, does not inherit the logging. ## What the timing does and does not measure | Situation | What happens | |---|---| | Statement succeeds | both hooks run; elapsed time covers `cursor.execute()` | | Statement raises | `before_cursor_execute` runs, `after_cursor_execute` does not; the engine's `handle_error` event fires instead | | Large SELECT | rows not yet fetched are not timed; how much work happens inside `execute()` depends on the driver | | `executemany()` batch | one pair of events for the batch, with `executemany=True` | The failure row has a practical consequence: a start time stays on the stack for each failed statement. Because later statements pop their own entries first, their timings stay correct, but a `handle_error` listener can pop the orphan if the leak matters, and it is also the place to log slow statements that ended in a timeout. ## Logging safely - Log `statement`, which contains placeholders only. **Do not log `parameters` by default**: bound values can carry e-mail addresses, tokens or other personal data, and a slow-query log is often shipped to a wider audience than the database. - Use the logging module's lazy `%s` arguments so the message is built only when the record is emitted. - Keep the listeners cheap. They run for every statement the engine executes, so a network call or a lock inside them slows the whole application. ## retval=True: changing the statement By default the hooks only observe. `before_execute` and `before_cursor_execute` (and `handle_error`) accept `retval=True`; `before_cursor_execute` must then return a `(statement, parameters)` tuple, which SQLAlchemy sends instead. The documented use is appending a SQL comment, such as a request identifier, so the database's own slow log can be traced back to application code. Passing `retval=True` for `after_cursor_execute` raises `ArgumentError`. ## Why the engine, not the Session Connection events sit below the ORM, so every statement the engine runs passes through them: - statements the ORM emits during a flush, which the Session's `do_orm_execute` hook never sees; - lazy loads, eager loads and `session.get()` lookups; - Core statements on a `Connection` and `text()` calls. That completeness is the point of a slow-statement alarm: the slow query is often one nobody wrote by hand, such as a lazy load inside a loop or the UPDATE a flush generated. The price is that the listener has no ORM context. It knows the SQL string, not which entity or code path asked for it, which is another reason to append a request identifier as a comment. ## Related tools `create_engine(echo=True)` and the `sqlalchemy.engine` logger print every statement but do not filter by duration; the listener pair is what turns logging into a slow-statement alarm. With the asyncio extension, register the same two functions on `AsyncEngine.sync_engine`.

  • How would you add the request identifier to every SQL statement SQLAlchemy sends, so database-side logs can be traced?
    Register `before_cursor_execute` with `retval=True` and return `(statement + " -- request id", parameters)`; SQLAlchemy then sends the returned pair. Read the identifier from a context variable set by the request handler, and validate it, because anything appended here is literal SQL text, not a bound parameter.
  • Why can't a slow-statement listener in SQLAlchemy rely on after_cursor_execute to report statements that time out?
    When the driver raises, SQLAlchemy skips `after_cursor_execute` and routes the failure through the error path, where the engine's `handle_error` event fires. A statement killed by a database timeout is therefore invisible to the pair unless a `handle_error` listener also reads the start time and logs it.

  • after_cursor_execute also fires for a failed statement, carrying the exception.
  • Any connection event accepts retval=True to rewrite what it receives.
  • conn.info is a fresh dictionary for every Engine.connect() call.
  • The elapsed time includes fetching every row of a large SELECT.
  • Logging the parameters argument by default is harmless for a slow log.