python - SQLAlchemy: print the actual query -

i'd able print out valid sql application, including values, rather bind parameters, it's not obvious how in sqlalchemy (by design, i'm sure).

has solved problem in general way?

in vast majority of cases, "stringification" of sqlalchemy statement or query simple as:

print str(statement) 

this applies both orm query select() or other statement.

note: following detailed answer being maintained on sqlalchemy documentation.

to statement compiled specific dialect or engine, if statement not bound 1 can pass in compile():

print statement.compile(someengine) 

or without engine:

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

when given orm query object, in order @ compile() method need access .statement accessor first:

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

with regards original stipulation bound parameters "inlined" final string, challenge here sqlalchemy not tasked this, handled appropriately python dbapi, not mention bypassing bound parameters exploited security holes in modern web applications. sqlalchemy has limited ability stringification in circumstances such of emitting ddl. in order access functionality 1 can use 'literal_binds' flag, passed 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 caveats supported basic types, such ints , strings, , furthermore if bindparam without pre-set value used directly, won't able stringify either.

to support inline literal rendering types not supported, implement typedecorator target type includes 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  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 mytable mytable.x > my_fancy_formatting(5)