skip to content

In SQLAlchemy Core, how does a select() send Python values to the database, and how do you see the SQL it will emit?

level: middleimportance: must knowfreq 58%

answer

  1. values never spliced into text
  2. placeholders such as :region_1
  3. generic dialect versus the target
  4. compile(dialect=...) and .params
  5. literal_binds for humans only

basics

~20 s

Python values compared in an expression become bound parameters: a placeholder in the SQL, with the value sent to the driver separately. print(stmt) shows generic SQL; stmt.compile(dialect=...) shows the target's SQL and compiled.params the values.

solid answer

~40 s

By default SQLAlchemy does not splice a Python value into SQL text: `customers.c.region == "EU"` creates an anonymous `BindParameter` rendered as `:region_1`, and at execution the compiled string and a parameter set reach the driver separately. Quotes in user input therefore stay data, and one compiled form is cached and reused for every value. `bindparam("cid")` names a parameter whose value arrives at execute time or through `stmt.params(cid=7)`. `print(stmt)` uses a generic dialect, so `ilike()` shows as `lower(...) LIKE lower(...)` and `in_()` as `IN (__[POSTCOMPILE_region_1])`; for the real thing call `stmt.compile(dialect=postgresql.dialect())` or `stmt.compile(engine)` and read `compiled.params`. `compile_kwargs={"literal_binds": True}` inlines values for a log line or a bug report only; the documentation warns never to use it with untrusted input.

code

python · 25 lines
python
from sqlalchemy import Column, Integer, MetaData, String, Table, select
from sqlalchemy.dialects import postgresql, sqlite

metadata = MetaData()
customers = Table(
    "customers", metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(100), nullable=False),
    Column("region", String(20)),
)

search = "o'brien%"  # untrusted input stays a bound value
stmt = select(customers.c.id).where(
    customers.c.name.ilike(search), customers.c.region.in_(["EU", "UK"])
)

print(stmt)                                     # generic dialect
compiled = stmt.compile(dialect=postgresql.dialect())
print(compiled)                                 # the target's real SQL
print(compiled.params)                          # values travel separately
print(stmt.compile(dialect=sqlite.dialect(),
                   compile_kwargs={"render_postcompile": True}))
# debugging only: inline literals, never execute this string
print(stmt.compile(dialect=postgresql.dialect(),
                   compile_kwargs={"literal_binds": True}))

go deeper

for a junior

Recall that values in where() become placeholders such as :region_1 and that print(stmt) shows the SQL with those placeholders.

for a middle

Explain BindParameter, anonymous versus bindparam() names, compile(dialect=...) with compiled.params, and why IN prints a POSTCOMPILE placeholder.

for a senior

Show how you debug a wrong query: compile against the target dialect, read compiled.params, and keep literal_binds output out of execution paths.

for a principal

Set the rule that statements are built from expressions with bound values and that rendered literal SQL appears only in logs and bug reports.

## Values become bound parameters When you write `customers.c.region == "EU"`, SQLAlchemy does not produce the text `region = 'EU'`. It builds a `BinaryExpression` whose right side is a **`BindParameter`**: an object holding the value `"EU"`, the column's type, and an anonymous name derived from the column key, such as `region_1`. When the statement is compiled, the parameter becomes a placeholder; when it is executed, the SQL string and a separate collection of values go to the driver. - **Anonymous parameters** come from any Python value in a comparison, in `in_()`, in `limit()` and `offset()`, and in function arguments. Names come from the column key where there is one and are numbered per statement: `:id_1`, `:id_2`, or `:param_1` for a limit. - **Named parameters** come from `bindparam("cid")`. A value can be given at execution, as in `conn.execute(stmt, {"cid": 7})`, or attached with `stmt.params(cid=7)`. Executing a statement whose `bindparam()` has no value raises a `StatementError` wrapping `A value is required for bind parameter 'cid'`. - **`None`** compared with `==` is not bound at all: it renders `IS NULL`. ## Why binding matters Binding does two jobs at once: 1. **Safety.** A value such as `o'brien%` travels as data, so its quote cannot end a string literal or change the statement's structure. 2. **Caching.** The SQL string depends only on the statement's shape, not on its values. SQLAlchemy caches the compiled form, so the engine's SQL log shows `[generated in ...]` for the first execution and `[cached since ...]` for the next one with a different value. Text built by formatting values in is a new string for every distinct value and gets no reuse. Each dialect renders placeholders in its driver's style: | Target | Placeholder in compiled SQL | |---|---| | generic `print(stmt)` | `:region_1` | | SQLite | `?` | | PostgreSQL (default driver) | `%(region_1)s`, with a type cast such as `::VARCHAR` | | MySQL | `%s` | ## Seeing the SQL Work from coarse to exact: 1. `print(stmt)` or `str(stmt)` compiles against a generic default dialect. Use it to check structure: joins, WHERE grouping, ORDER BY. 2. `stmt.compile(dialect=postgresql.dialect())`, or `stmt.compile(engine)`, renders what that database will receive. The `ilike()` operator, for example, prints as `lower(customers.name) LIKE lower(:name_1)` generically but as `ILIKE` against PostgreSQL. Some constructs that only one database supports raise `UnsupportedCompilationError` under the generic dialect and need the target's dialect to render. 3. `compiled.params` on the result is a dict of the values each placeholder will receive. 4. `compile_kwargs={"render_postcompile": True}` expands the **expanding parameters** behind `in_()`. Printed normally they show as `IN (__[POSTCOMPILE_region_1])`, because the list length is known only at execution; keeping one placeholder lets the cached SQL serve lists of any length. 5. `compile_kwargs={"literal_binds": True}` inlines the values themselves, which implies `render_postcompile`. ## Debugging a select() that returns the wrong rows 1. `print(stmt)` and read the structure: are all the joins there, is every WHERE criterion present, are the OR groups parenthesised as intended? 2. Compile against the engine you run on and compare: operators such as `ilike()`, casts and the placeholder style now match what the database receives. 3. Read `compiled.params` and check each value and its Python type, for example a string where the column expects an integer, or a list where a scalar was meant. 4. Only then render with `literal_binds` if you need a copy-paste version for a colleague or a ticket. ## `literal_binds` is a debugging aid The documentation presents `literal_binds` mainly for logging and debugging, and its warning is blunt: never use it with string content from untrusted input, because the conversion of Python values into SQL literals is not secure against such input and does not validate types. In practice: - The string is for a human. Against PostgreSQL it even doubles `%` signs for the driver's format, so pasting it into another tool can mislead you. - Only basic types such as integers and strings are promised to render; the documentation gives recipes for the others. - A `bindparam()` with no value renders as `NULL`, with a warning, so the printed SQL can differ from what will run. Execution should always go through the statement object with its bound values, never through a rendered string. When a running service needs to show its SQL, the engine's logging, covered with engine configuration, prints the statement and the parameter tuple side by side.

  • Why does `print(stmt)` show `IN (__[POSTCOMPILE_region_1])` for `in_(["EU", "UK"])`?
    `in_()` uses an expanding bound parameter. The compiled SQL keeps one placeholder and expands it into one parameter per list element at execution, so the cached string fits lists of any length. Pass `compile_kwargs={"render_postcompile": True}` to see the expanded form, such as `IN (?, ?)` against SQLite.
  • When does executing a select() raise `A value is required for bind parameter 'cid'`?
    When the statement contains `bindparam("cid")` and neither the execute call nor `stmt.params()` supplies a value. The error surfaces as a `StatementError` wrapping `InvalidRequestError`, with the rendered SQL attached. Pass a dict as the second argument of `execute()` or attach the value with `stmt.params(cid=7)`.
  • Why compile against an engine or dialect instead of trusting `str(stmt)`?
    `str()` uses a generic dialect that knows nothing about the target, so operators and placeholders may render differently, and dialect-only constructs may not render at all. `stmt.compile(engine)` uses the engine's dialect and shows the database's own spelling, such as `ILIKE` on PostgreSQL.

  • SQLAlchemy escapes quotes in values and pastes them into the SQL text
  • str(stmt) shows exactly what PostgreSQL will receive
  • literal_binds output is safe to execute when a driver is awkward
  • Inlining values helps the statement cache hit more often
  • An IN list must be wrapped in bindparam() before it can be bound