In SQLAlchemy 2.1, how do you see the SQL an engine sends during a test run, and how does echo=True differ from the 'sqlalchemy.engine' logger?
answer
- one flag, one logger name
- INFO for statements, DEBUG for rows
- the namespace starts at WARNING
- two handlers print each line twice
basics
~20 sPass echo=True to create_engine to print each statement and its parameters to standard output, or set the 'sqlalchemy.engine' logger to INFO (DEBUG adds rows). The logger joins your logging setup; echo adds its own handler and can duplicate lines.
solid answer
~40 s`create_engine(url, echo=True)` attaches SQLAlchemy's own handler, writing to standard output, and logs every statement, its parameters and the `BEGIN (implicit)` / `COMMIT` / `ROLLBACK` markers; `echo="debug"` adds result rows. The same records come from `logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO)` (or `DEBUG`), which sends them through whatever handlers the application or the test runner already configured, so pytest's log capture sees them. The whole `sqlalchemy` namespace starts at `WARNING`, so nothing appears until you raise a level. Pick one: `echo` on top of configured logging prints every line twice. In a suite, prefer the logger, and set `hide_parameters=True` when the logged parameters could hold personal data or secrets.
code
python · 13 linesimport logging
import sys
from sqlalchemy import create_engine, text
logging.basicConfig(stream=sys.stdout, format="%(levelname)s %(name)s %(message)s")
logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO)
# no echo=True: the records go through the handler configured above, once
engine = create_engine("sqlite://", hide_parameters=True)
with engine.connect() as conn:
conn.execute(text("SELECT :email AS email"), {"email": "[email protected]"})go deeper
Know both switches: echo=True on create_engine, and the sqlalchemy.engine logger raised to INFO. Remember DEBUG, or echo='debug', also prints result rows.
Explain why echo duplicates lines under configured logging, why the namespace defaults to WARNING, and why a level change appears only after the next connection checkout.
Show how you would log SQL in a suite: the logger through the runner's capture, logging_token to tag tests, hide_parameters wherever bound values can be sensitive.
Set the team's default: no echo in shared code, logging configured in one place, parameters hidden outside local development, and a statement counter rather than log scraping for budgets.
## Two switches over one logger SQLAlchemy logs through Python's standard **`logging`** module. Every statement an `Engine` sends goes to a logger in the **`sqlalchemy.engine`** namespace (the record's name is `sqlalchemy.engine.Engine`). There are two ways to make those records visible: - **`create_engine(url, echo=True)`** — a development shortcut. It makes the engine log at INFO and attaches SQLAlchemy's own `StreamHandler` writing to standard output, independent of any logging configuration you have. - **`logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO)`** — the standard route. The records flow to whatever handlers your application, or your test runner, has configured. By default SQLAlchemy sets the whole `sqlalchemy` namespace to **`WARNING`**, so an application that turns on logging for itself still sees no SQL until it raises this logger's level. ## Levels and what each one shows | Setting | Equivalent logger level | What is logged | |---|---|---| | `echo=True` | `sqlalchemy.engine` at `INFO` | statements, parameters, transaction markers | | `echo="debug"` | `sqlalchemy.engine` at `DEBUG` | the above plus column names and every result row | | `echo_pool=True` | `sqlalchemy.pool` at `INFO` | connection invalidation and recycling | | `echo_pool="debug"` | `sqlalchemy.pool` at `DEBUG` | the above plus every checkout and checkin | A typical INFO record set for one statement looks like this: ```plaintext INFO sqlalchemy.engine.Engine BEGIN (implicit) INFO sqlalchemy.engine.Engine SELECT ? AS email INFO sqlalchemy.engine.Engine [generated in 0.00012s] [SQL parameters hidden due to hide_parameters=True] INFO sqlalchemy.engine.Engine ROLLBACK ``` `BEGIN (implicit)` records that SQLAlchemy has started a transaction and left the actual `BEGIN` to the **driver**; SQLAlchemy sends no `BEGIN` statement there. The bracketed line carries the parameters and a note on the compiled-statement cache. ## Reading the bracketed line The line after each statement is worth reading, not skipping: - **`[generated in 0.00012s]`** — the statement was compiled for the first time on this engine; the time is the compile cost. - **`[cached since 0.0012s ago]`** — the compiled form came from the statement cache, so no compile cost was paid. - **`[no key ...]`** — the statement has no cache key and is not cached, as with the `SAVEPOINT` and DDL lines in a test log. - **`[insertmanyvalues 2/3 (ordered; batch not supported)]`** — one batch of a multi-row INSERT; the counter shows how many round trips the INSERT took. - **`[raw sql]`** — the statement was sent as a plain string, for example through `exec_driver_sql()`. In a test log, many `insertmanyvalues` batches where you expected one INSERT is a quick sign that the dialect could not batch those rows. ## Why a test suite should prefer the logger 1. **Capture.** A test runner that captures log records (pytest's log capture does) attaches its handler to the root logger. Records from `sqlalchemy.engine` reach it once you raise the level; with `echo` the same lines are also written a second time by SQLAlchemy's own handler. 2. **No duplicates.** `echo` adds its own handler on top of any existing configuration. With `logging.basicConfig()` plus the logger at INFO plus `echo=True`, every line prints twice. SQLAlchemy's guidance is to leave every `echo` flag at `False` once logging is configured explicitly. 3. **Per-test control.** Raise the level in the tests that need SQL and leave it at `WARNING` elsewhere, instead of rebuilding an engine with a different flag. ## Gotchas worth knowing - **The level is read at connection checkout.** To save function calls, the engine checks whether INFO or DEBUG is enabled when a `Connection` is procured from the pool. Raising the level while a `Session` is in the middle of a transaction logs nothing until that transaction ends and a new connection is checked out. - **The handler outlives the engine.** `echo=True` attaches its handler to the shared `sqlalchemy.engine.Engine` logger, so engines created later without `echo` can print through it too. `logging_name="..."` gives an engine its own logger name (`sqlalchemy.engine.Engine.<name>`) when several engines must be told apart. - **Echo lines are not a statement count.** Transaction markers and parameter lines appear beside each statement, so counting log lines over-counts; count cursor executions with an engine event instead. ## Keeping secrets out of the log Logged parameters are values your code bound: e-mail addresses, tokens, password hashes. Two controls exist: - **`hide_parameters=True`** on `create_engine` replaces every parameter list with `[SQL parameters hidden due to hide_parameters=True]`, in log records and in exception messages. - **`execution_options(logging_token="...")`** on a connection or engine prefixes each record with a token such as a test name or request id, which makes interleaved logs readable without logging more data. Binding values as parameters, rather than formatting them into the SQL string, is what keeps them in that hideable list in the first place.
- Why can raising the SQLAlchemy logger level mid-test show nothing for a while?The `Engine` checks whether INFO or DEBUG is enabled only when a `Connection` is checked out of the pool. A `Session` already inside a transaction keeps its connection, so its statements stay unlogged until that transaction ends and the next one checks out a connection.
- How do you stop SQLAlchemy from writing bound values such as e-mail addresses to the log?Create the engine with `hide_parameters=True`. Statements are still logged, but each parameter list is replaced by `[SQL parameters hidden due to hide_parameters=True]`, and the same masking applies to the parameters shown in exception messages.
- Turning on echo=True on top of configured logging and blaming the duplicate lines on the code
- Believing echo is the only way to see SQLAlchemy's SQL
- Expecting result rows at INFO; rows appear only at DEBUG or echo='debug'
- Reading BEGIN (implicit) as a BEGIN statement SQLAlchemy sent
- Counting echo lines to measure how many statements ran
- Logging production SQL with parameters that hold personal data and no hide_parameters