Introduction

SQLAlchemy builds SQL expressions programmatically, which is excellent for safety and composition, but sometimes you need to inspect the actual SQL text. That usually happens during debugging, query-plan analysis, or when you want to understand what a complex ORM expression really generated.

The key is to distinguish between two outputs: SQL with placeholders, and SQL with literal values rendered into the string. Both are useful, but they serve different purposes.

Compile a Statement Normally

Any SQLAlchemy statement can be compiled against a dialect.

1from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, select
2
3engine = create_engine("sqlite://")
4metadata = MetaData()
5
6users = Table(
7    "users",
8    metadata,
9    Column("id", Integer, primary_key=True),
10    Column("email", String),
11)
12
13stmt = select(users.c.id, users.c.email).where(users.c.email.like("%@example.com"))
14compiled = stmt.compile(dialect=engine.dialect)
15
16print(str(compiled))
17print(compiled.params)

str(compiled) gives you the SQL text with placeholders. compiled.params gives you the bound parameter values separately.

That is usually the safest debug view because it preserves the parameterized structure of the query.

Render Literal Values for Debugging

If you want a single string with values embedded, enable literal_binds.

1compiled_literal = stmt.compile(
2    dialect=engine.dialect,
3    compile_kwargs={"literal_binds": True},
4)
5
6print(str(compiled_literal))

This is useful for copy-pasting into a database console or reviewing the exact final text. It should be treated as a debugging tool, not as the normal way to execute queries.

ORM Statements Use the Same Mechanism

The same approach works with ORM-style select statements.

1from sqlalchemy import create_engine, select
2from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
3
4engine = create_engine("sqlite://", echo=False)
5
6class Base(DeclarativeBase):
7    pass
8
9class User(Base):
10    __tablename__ = "users"
11    id: Mapped[int] = mapped_column(primary_key=True)
12    email: Mapped[str]
13
14Base.metadata.create_all(engine)
15
16with Session(engine) as session:
17    session.add_all([User(email="[email protected]"), User(email="[email protected]")])
18    session.commit()
19
20    stmt = select(User).where(User.email.endswith("@example.com"))
21    print(stmt.compile(dialect=engine.dialect, compile_kwargs={"literal_binds": True}))

SQLAlchemy 2-style ORM code still compiles the same way because the underlying object is still a SQL expression.

Dialect Matters

SQL text varies by backend. A query compiled for SQLite may not look the same when compiled for PostgreSQL or MySQL.

1from sqlalchemy.dialects import postgresql
2
3pg_sql = stmt.compile(
4    dialect=postgresql.dialect(),
5    compile_kwargs={"literal_binds": True},
6)
7
8print(pg_sql)

If you care about the exact SQL syntax, compile against the same dialect as the database you actually target.

Query Logging Is a Different Tool

Sometimes you do not need manual compilation at all. If your real goal is to see what SQLAlchemy executes during a run, engine logging may be enough.

engine = create_engine("sqlite:///app.db", echo=True)

That shows executed SQL and parameters automatically. Use compilation when you want explicit control over one statement, and logging when you want to observe actual runtime behavior across many statements.

Common Pitfalls

A common mistake is compiling without the target dialect and then assuming the SQL is database-accurate. SQLAlchemy can only render dialect-specific syntax if you tell it which dialect to use.

Another issue is confusing placeholder SQL with literal SQL. str(compiled) and literal_binds are related but not the same output.

Developers also sometimes paste literal-rendered SQL into production execution code. That defeats the safety and portability benefits of bound parameters.

Finally, be careful with sensitive data. Literal rendering can put secrets or personal data directly into logs.

If you build helper utilities around compilation, keep them clearly marked as debugging helpers so they do not drift into normal production execution paths by accident.

Summary

• Use statement.compile(...) to inspect SQLAlchemy-generated SQL.
• 'str(compiled) gives SQL with placeholders, while compiled.params holds the values.'
• Use literal_binds only when you need a debug string with values embedded.
• Compile against the correct dialect for accurate backend-specific SQL.
• Prefer parameterized execution in real code even if literal SQL is convenient for debugging.