Release: 1.2.1 current release | Release Date: January 15, 2018

SQLAlchemy 1.2 Documentation

SQL Expressions

How do I render SQL expressions as strings, possibly with bound parameters inlined?

The “stringification” of a SQLAlchemy statement or Query in the vast majority of cases is as simple as:

print(str(statement))

this applies both to an ORM Query as well as any select() or other statement. Additionally, to get the statement as compiled to a specific dialect or engine, if the statement itself is not already bound to one you can pass this in to ClauseElement.compile():

print(statement.compile(someengine))

or without an Engine:

from sqlalchemy.dialects import postgresql
print(statement.compile(dialect=postgresql.dialect()))

When given an ORM Query object, in order to get at the ClauseElement.compile() method we only need access the statement accessor first:

statement = query.statement
print(statement.compile(someengine))

The above forms will render the SQL statement as it is passed to the Python DBAPI, which includes that bound parameters are not rendered inline. SQLAlchemy normally does not stringify bound parameters, as this is handled appropriately by the Python DBAPI, not to mention bypassing bound parameters is probably the most widely exploited security hole in modern web applications. SQLAlchemy has limited ability to do this stringification in certain circumstances such as that of emitting DDL. In order to access this functionality one can use the literal_binds flag, passed to compile_kwargs:

from sqlalchemy.sql import table, column, select

t = table('t', column('x'))

s = select([t]).where(t.c.x == 5)

print(s.compile(compile_kwargs={"literal_binds": True}))

the above approach has the caveats that it is only supported for basic types, such as ints and strings, and furthermore if a bindparam() without a pre-set value is used directly, it won’t be able to stringify that either.

To support inline literal rendering for types not supported, implement a TypeDecorator for the target type which includes a TypeDecorator.process_literal_param() method:

from sqlalchemy import TypeDecorator, Integer


class MyFancyType(TypeDecorator):
    impl = Integer

    def process_literal_param(self, value, dialect):
        return "my_fancy_formatting(%s)" % value

from sqlalchemy import Table, Column, MetaData

tab = Table('mytable', MetaData(), Column('x', MyFancyType()))

print(
    tab.select().where(tab.c.x > 5).compile(
        compile_kwargs={"literal_binds": True})
)

producing output like:

SELECT mytable.x
FROM mytable
WHERE mytable.x > my_fancy_formatting(5)
Previous: MetaData / Schema Next: ORM Configuration