skip to content

Engine Layer

An Engine pairs a dialect and a DBAPI driver with a connection pool and hands out connections. Interviewers ask how a URL picks the driver and why the pool, not your code, owns connections.

on this pageshow

explore

questions

11

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.

open as a page

In SQLAlchemy 2.1, what does a create_engine() URL encode, and how do you pass a password containing @ or / safely?

level: juniorimportance: must knowfreq 60%

basics

~10 s

A SQLAlchemy URL reads dialect+driver://user:password@host:port/database, and create_engine() turns it into a dialect, a DBAPI driver and a pool. Percent-encode special password characters, or build the URL with URL.create() and pass them raw.

open as a page

In SQLAlchemy 2.x, how does engine.connect() with Connection.commit() differ from engine.begin(), and what happens to uncommitted work?

level: middleimportance: must knowfreq 66%

basics

~20 s

engine.connect() gives a Connection that autobegins a transaction and commits only when you call commit(); closing it rolls back anything uncommitted. engine.begin() wraps the block in one transaction that commits on normal exit and rolls back on an exception.

open as a page

In SQLAlchemy 2.1, what do create_engine's pool_size, max_overflow and pool_timeout control, and what happens when every pooled connection is checked out?

level: middleimportance: must knowfreq 48%

basics

~20 s

pool_size (default 5) is how many connections the QueuePool keeps open when idle, max_overflow (default 10) how many extra it may open, and pool_timeout (default 30 s) how long a checkout waits at that ceiling before raising TimeoutError.

open as a page

In SQLAlchemy, how do Result.one(), first(), one_or_none(), scalar_one(), scalars() and mappings() differ, and when do they raise?

level: middleimportance: should knowfreq 48%

basics

~20 s

SQLAlchemy's one() demands exactly one row, raising NoResultFound or MultipleResultsFound; one_or_none() allows zero; first() returns the first row or None and silently discards the rest. scalar_one() and scalars() return column values instead of rows; mappings() returns dict-like rows.

open as a page

In SQLAlchemy, when would you pass poolclass=NullPool to create_engine instead of keeping the default QueuePool, and what does that cost?

level: middleimportance: should knowfreq 28%

basics

~20 s

NullPool opens a new DBAPI connection for every checkout and closes it on return. Use it behind an external connection pooler, for short batch jobs or around forking; the cost is a handshake per checkout and no connection ceiling.

open as a page

In SQLAlchemy, a firewall silently drops database connections idle for 30 minutes; how do pool_pre_ping and pool_recycle each stop stale pooled connections from failing requests?

level: middleimportance: should knowfreq 42%

basics

~10 s

pool_pre_ping=True pings each connection at checkout and replaces a dead one; pool_recycle=N replaces any connection older than N seconds at checkout. Set recycle below 1800 seconds, enable pre-ping, or both.

open as a page

A nightly SQLAlchemy script inserts daily totals without error, but the rows never appear, and echo=True logs BEGIN (implicit), the INSERT, then ROLLBACK; what happened and how do you fix it?

level: seniorimportance: should knowfreq 34%

basics

~10 s

The INSERT ran inside a SQLAlchemy Connection whose block ended without commit(), so Connection.close() rolled it back; the ROLLBACK line proves it. Wrap the write in engine.begin(), or call conn.commit() before the block ends.

open as a page

In SQLAlchemy, an Engine created before a pre-forking server starts four workers begins returning protocol errors and mixed-up results; why, and how does engine.dispose(close=False) fix it?

level: seniorimportance: should knowfreq 35%

basics

~20 s

Pooled connections are sockets, and fork copies them into every worker, so several processes talk over one connection. Calling engine.dispose(close=False) in each child replaces its pool with an empty one without closing the parent's connections.

open as a page

How would you set SQLAlchemy's pool_size and max_overflow for an API of four worker processes plus a nightly batch job against a PostgreSQL server allowing 100 connections?

level: principalimportance: should knowfreq 30%

basics

~20 s

Each process has its own pool, so the database sees processes x (pool_size + max_overflow) per engine, plus the batch job, deploy overlap and admin connections. Size pool_size to steady concurrency, use max_overflow as headroom, keep the sum under 100.

open as a page

In SQLAlchemy, what does the connection pool do to a connection when it is returned, and when is pool_reset_on_return=None appropriate?

level: middleimportance: nice to knowfreq 18%

basics

~20 s

By default the pool calls the DBAPI rollback() on every returned connection, unless the Connection already rolled back, so no transaction, locks or pending writes leak to the next borrower. None fits pure autocommit use or a custom reset handler.

open as a page