skip to content

In SQLAlchemy, how do you pass values into a text() statement, and what does SQLAlchemy do with the :name placeholders?

level: juniorimportance: must knowfreq 55%

answer

  1. colon-prefixed names
  2. second argument to execute
  3. dialect rewrites the placeholder
  4. list of dicts versus one dict
  5. expanding for IN lists

basics

~20 s

SQLAlchemy's text() marks values as :name placeholders, and Connection.execute() takes their values as a dict, or a list of dicts for executemany. SQLAlchemy rewrites each placeholder into the driver's parameter style and sends values separately from the SQL.

solid answer

~40 s

Inside `text()` you write bound parameters as `:name`, for example `text("SELECT id, total FROM orders WHERE status = :status")`, and pass `{"status": "paid"}` as the second argument to `Connection.execute()`. SQLAlchemy compiles the placeholder into whatever the DBAPI driver expects (`?` for SQLite, `%(status)s` for psycopg) and hands the values to the driver separately, so they are never spliced into the SQL string. A list of dicts runs the statement as an executemany, once per dict. A missing value raises `StatementError` naming the bind parameter. Values can be fixed on the statement with `.bindparams()`, and a Python list for `IN` needs `bindparam("ids", expanding=True)`. Never build the SQL with an f-string; in 2.1 on Python 3.14 the `tstring()` construct turns interpolated values into bound parameters instead.

code

python · 25 lines
python
from sqlalchemy import bindparam, create_engine, text
from sqlalchemy.dialects import postgresql

engine = create_engine("sqlite://")
with engine.begin() as conn:
    conn.execute(text("CREATE TABLE orders (id INTEGER PRIMARY KEY, status TEXT, total INTEGER)"))
    conn.execute(  # a list of dicts runs as executemany
        text("INSERT INTO orders (id, status, total) VALUES (:id, :status, :total)"),
        [
            {"id": 1, "status": "paid", "total": 40},
            {"id": 2, "status": "open", "total": 15},
            {"id": 3, "status": "paid", "total": 70},
        ],
    )

by_status = text("SELECT id, total FROM orders WHERE status = :status")
print(by_status.compile(dialect=postgresql.dialect()))  # ... status = %(status)s

by_ids = text("SELECT id FROM orders WHERE id IN :ids").bindparams(
    bindparam("ids", expanding=True)
)

with engine.connect() as conn:
    print(conn.execute(by_status, {"status": "paid"}).all())  # [(1, 40), (3, 70)]
    print(conn.execute(by_ids, {"ids": [1, 3]}).scalars().all())  # [1, 3]

go deeper

for a junior

Recall the :name syntax and that values go in a dict as the second argument to execute(), never into the SQL string.

for a middle

Explain how the dialect compiles :name into the driver's paramstyle, how a list of dicts becomes an executemany, and the expanding parameter for IN lists.

for a senior

Handle the edge cases a code review catches: literal colons, PostgreSQL :: casts, identifiers that cannot be bound, and missing-parameter errors.

for a principal

Decide where textual SQL is allowed at all in a code base, and how reviews keep string formatting out of it, with Core constructs or tstring() as the alternatives.

## What text() is `text()` wraps a literal SQL string in a **TextClause**, a statement object that `Connection.execute()` accepts like any other SQLAlchemy statement. Compared with a plain string it adds **backend-neutral bound parameters**, per-statement execution options, and optional typing of parameters and result columns. `Connection.execute()` refuses a plain string altogether (it raises `ObjectNotExecutableError`), so textual SQL always goes through `text()`. A **bound parameter** is a placeholder in the SQL whose value travels to the database separately from the statement text. The driver passes it as data, so a value can never change the statement's structure, and the driver handles quoting and type conversion for the backend. ## Writing and supplying parameters In `text()` each parameter is a colon followed by a name: `:status`, `:day`, `:total`. Values are supplied at execution time: - **one dict** for a single execution: `conn.execute(stmt, {"status": "paid"})`; - **a list of dicts** for an **executemany**: the statement runs once per dict, which the driver can optimise. An executemany does not return result rows; - **`.bindparams(order_id=3)`** to attach a value to the statement itself, useful when a statement object is built once and passed around. `Connection.execute()` does not take parameters as keyword arguments; `conn.execute(stmt, status="paid")` raises `TypeError`. If a placeholder has no value, SQLAlchemy raises `StatementError` wrapping `InvalidRequestError`: "A value is required for bind parameter 'status'". ## What SQLAlchemy does with :name DBAPI drivers disagree on parameter syntax (the `paramstyle` each declares). SQLAlchemy hides that: you always write the **named colon format**, and the dialect compiles it into the driver's own style. | Driver | The placeholder sent to the driver | |---|---| | sqlite3 (SQLite) | `?` | | psycopg (PostgreSQL, 2.1 default) | `%(status)s` | | PyMySQL (MySQL) | `%s` | You can see this with `stmt.compile(dialect=postgresql.dialect())`, and the echo log shows the compiled SQL with the parameter tuple on the next line. ## Edge cases worth knowing 1. **IN lists.** A plain list bound to `IN (:ids)` is sent as one single value, and the statement fails. Declare the parameter as expanding: `text("... WHERE id IN :ids").bindparams(bindparam("ids", expanding=True))`. SQLAlchemy renders one placeholder per element at execution time, so the statement adapts to the list's length. 2. **Literal colons.** Any `:word` is read as a parameter, even inside a quoted string, so `'note :draft'` demands a value for `draft`. Escape it as `\:draft` in a raw string. A name immediately followed by another colon, as in PostgreSQL's `:value::int` cast, is not read as a parameter; write `:value ::int` or use `CAST(:value AS INTEGER)`. 3. **Identifiers cannot be bound.** Table names, column names and sort directions are SQL structure, not values; a placeholder cannot stand in for them. Choosing them safely from user input is a security subject of its own. 4. **Types.** `bindparam("day", type_=Date)` or `.columns(...)` let SQLAlchemy apply its type processing to values going in and results coming out. 5. **Reuse.** Build a `text()` statement once, for example at module level, and execute it with different parameter dicts. `.bindparams()` returns a new statement rather than changing the original, so a shared statement is safe to reuse, and the echo log marks repeat runs as `[cached since ...]` because the compiled form came from SQLAlchemy's statement cache. ## Why not format the string yourself An f-string or `%` formatting puts the value inside the SQL text. That breaks on quotes in the data, gives every distinct value a distinct statement string, and lets data change the statement. SQLAlchemy's tutorial is explicit: even integers and dates should never be stringified into textual SQL; a parameter should always be used. SQLAlchemy 2.1 adds **`tstring()`** for Python 3.14 template strings: `tstring(t"SELECT * FROM orders WHERE id = {order_id}")` turns each interpolated Python value into a bound parameter, while SQLAlchemy expressions such as `column()` are embedded as SQL. It needs Python 3.14; `text()` with `:name` works everywhere. ## In the reserved scenario The nightly report reads `text("SELECT day, total FROM daily_totals WHERE day >= :since")` with `{"since": start_day}`, and the same statement runs against the PostgreSQL URL in production and the SQLite file locally, because only the compiled placeholder differs.

  • Why does SQLAlchemy raise "A value is required for bind parameter 'draft'" for text("SELECT 'note :draft'")?
    `text()` scans the whole string for `:name`, quoted literals included, so `:draft` is taken as a parameter. Escape the colon with a backslash in a raw string, `text(r"SELECT 'note \:draft'")`, or pass the literal itself as a bound value.
  • How do you bind a Python list to an IN clause in a SQLAlchemy text() statement?
    Declare the parameter as expanding with `.bindparams(bindparam("ids", expanding=True))` and write `IN :ids` without parentheses. At execution SQLAlchemy renders one placeholder per element. A plain list bound to an ordinary parameter is sent as one single value, and the statement fails.
  • What does SQLAlchemy 2.1's tstring() do with a value interpolated into a Python 3.14 t-string?
    `tstring(t"... WHERE id = {order_id}")` wraps each plain Python value in `literal()`, so it is sent as a bound parameter rather than pasted into the SQL. SQLAlchemy expressions such as `column()` or `table()` are embedded as SQL. It requires Python 3.14; on older Pythons use `text()` with `:name` parameters.

  • text() quotes values safely, so an f-string inside it is fine.
  • Connection.execute(stmt, status="paid") supplies the :status value.
  • Bound parameters let you choose the table or column name at runtime.
  • Passing a list to IN (:ids) expands it automatically.
  • You must write ? for SQLite and %s for PostgreSQL in text().

Assumes SQLAlchemy 2.1; the :name syntax is shared by every 2.x release, while tstring() for Python 3.14 t-strings is new in 2.1.