TopolearnUnderstanding compilation helps you debug generated SQL, keep the compiled cache effective and write constructs that work across databases.
A select() is a tree of ClauseElement objects. Executing it compiles the tree with the engine's dialect: a SQLCompiler visits each node and emits SQL text with the right quoting, parameter style (?, %(name)s, $1) and syntax (LIMIT vs TOP, RETURNING support). stmt.compile(dialect=postgresql.dialect()) shows what a given database would receive, and compile_kwargs={"literal_binds": True} inlines values for debugging; never execute literal-bound SQL built from user input.
Compiling is expensive in Python, so SQLAlchemy keeps a compiled cache per engine. Each statement produces a cache key from its structure, with bound values left out, so the same query shape with different parameters reuses the compiled form. echo=True logs [cached since 12.3s ago] or [generated in 0.0003s] per statement, telling you whether caching works.
Things that defeat the cache: building SQL strings with values via text() f-strings, ever-changing IN lists rendered as literals, or custom constructs with inherit_cache not set. Third-party ClauseElement subclasses must declare inherit_cache = True (if safe) or they disable caching with a warning.
The dialect also handles type conversion: bind_processor and result_processor per type turn Python values into driver values and back. Extending compilation for your own construct is done with @compiles(MyElement, "postgresql").
Example
from sqlalchemy import Table, Column, Integer, String, MetaData, select
from sqlalchemy.dialects import postgresql, mssql
users = Table("users", MetaData(),
Column("id", Integer, primary_key=True), Column("name", String))
stmt = select(users.c.name).where(users.c.id > 5).limit(3)
print(stmt.compile(dialect=postgresql.dialect()))
# SELECT users.name
# FROM users
# WHERE users.id > %(id_1)s::INTEGER
# LIMIT %(param_1)s::INTEGER
print(stmt.compile(dialect=mssql.dialect()))
# SELECT TOP __[POSTCOMPILE_param_1] users.name ...
c = stmt.compile(compile_kwargs={"literal_binds": True})
print(" ".join(str(c).split())) # SELECT users.name FROM users WHERE users.id > 5 LIMIT 3
k1 = select(users).where(users.c.id == 1)._generate_cache_key()
k2 = select(users).where(users.c.id == 2)._generate_cache_key()
print(k1.key == k2.key) # True: same shape, different parametersRemember
- Statements are trees compiled per dialect into SQL text plus parameters
- The compiled cache keys on statement structure, not bound values
- echo=True shows whether each statement was cached or generated
- Custom constructs need @compiles and inherit_cache
Watch out for
- Formatting values into text() strings, which defeats both caching and injection safety.