SQLAlchemy print the actual query

Question

I d really like to be able to print out valid SQL for my application  including values  rather than bind parameters  but it s not obvious how to do this in SQLAlchemy  by design  I m fairly sure     Has anyone solved this problem in a general way

User · Answer

We can use compile method for this purpose. From the docs:

from sqlalchemy.sql import text
from sqlalchemy.dialects import postgresql

stmt = text("SELECT * FROM users WHERE users.name BETWEEN :x AND :y")
stmt = stmt.bindparams(x="m", y="z")

print(stmt.compile(dialect=postgresql.dialect(),compile_kwargs={"literal_binds": True}))

Result:

SELECT * FROM users WHERE users.name BETWEEN 'm' AND 'z'

Warning from docs:

Never use this technique with string content received from untrusted input, such as from web forms or other user-input applications. SQLAlchemy’s facilities to coerce Python values into direct SQL string values are not secure against untrusted input and do not validate the type of data being passed. Always use bound parameters when programmatically invoking non-DDL SQL statements against a relational database.

User · Answer

I would like to point out that the solutions given above do not  just work  with non-trivial queries  One issue I came across were more complicated types  such as pgsql ARRAYs causing issues  I did find a solution that for me  did just work even with pgsql ARRAYs   borrowed from  https   gist github com gsakkis 4572159  The linked code seems to be based on an older version of SQLAlchemy  You ll get an error saying that the attribute  mapper zero or none doesn t exist  Here s an updated version that will work with a newer version  you simply replace  mapper zero or none with bind  Additionally  this has support for pgsql arrays     adapted from    https   gist github com gsakkis 4572159 from datetime import date  timedelta from datetime import datetime  from sqlalchemy orm import Query   try      basestring except NameError      basestring   str   def render query statement  dialect None               Generate an SQL expression string with bound parameters rendered inline     for the given SQLAlchemy statement      WARNING  This method of escaping is insecure  incomplete  and for debugging     purposes only  Executing SQL statements with inline-rendered user values is     extremely insecure      Based on http   stackoverflow com questions 5631078 sqlalchemy-print-the-actual-query             if isinstance statement  Query           if dialect is None              dialect   statement session bind dialect         statement   statement statement     elif dialect is None          dialect   statement bind dialect      class LiteralCompiler dialect statement compiler            def visit bindparam self  bindparam  within columns clause False                              literal binds False    kwargs               return self render literal value bindparam value  bindparam type           def render array value self  val  item type               if isinstance val  list                   return    s         join  self render array value x  item type  for x in val               return self render literal value val  item type           def render literal value self  value  type                if isinstance value  long                   return str value              elif isinstance value   basestring  date  datetime  timedelta                    return    s     str value  replace                        elif isinstance value  list                   return     s           join  self render array value x  type  item type  for x in value                return super LiteralCompiler  self  render literal value value  type        return LiteralCompiler dialect  statement  process statement    Tested to two levels of nested arrays

User · Answer

This works in python 2 and 3 and is a bit cleaner than before  but requires SA  1 0   from sqlalchemy engine default import DefaultDialect from sqlalchemy sql sqltypes import String  DateTime  NullType    python2 3 compatible  PY3   str is not bytes text   str if PY3 else unicode int type   int if PY3 else  int  long  str type   str if PY3 else  str  unicode    class StringLiteral String          Teach SA how to literalize various things         def literal processor self  dialect           super processor   super StringLiteral  self  literal processor dialect           def process value               if isinstance value  int type                   return text value              if not isinstance value  str type                   value   text value              result   super processor value              if isinstance result  bytes                   result   result decode dialect encoding              return result         return process   class LiteralDialect DefaultDialect       colspecs               prevent various encoding explosions         String  StringLiteral            teach SA about how to literalize a datetime         DateTime  StringLiteral            don t format py2 long integers to NULL         NullType  StringLiteral          def literalquery statement          NOTE  This is entirely insecure  DO NOT execute the resulting strings         import sqlalchemy orm     if isinstance statement  sqlalchemy orm Query           statement   statement statement     return statement compile          dialect LiteralDialect            compile kwargs   literal binds   True         string   Demo     coding  UTF-8 from datetime import datetime from decimal import Decimal  from literalquery import literalquery   def test        from sqlalchemy sql import table  column  select      mytable   table  mytable   column  mycol        values             5          u snowman              b UTF-8 snowman   xe2 x98 x83           datetime now            Decimal  3 14159            10    20     a long integer            statement   select  mytable   where mytable c mycol in  values   limit 1      print literalquery statement     if   name         main         test     Gives this output   tested in python 2 7 and 3 4   SELECT mytable mycol FROM mytable WHERE mytable mycol IN  5   snowman       UTF-8 snowman             2015-06-24 18 09 29 042517   3 14159  100000000000000000000   LIMIT 1

User · Answer

So building on  zzzeek s comments on  bukzor s code I came up with this to easily get a  pretty-printable  query   def prettyprintable statement  dialect None  reindent True          Generate an SQL expression string with bound parameters rendered inline     for the given SQLAlchemy statement  The function can also receive a      sqlalchemy orm Query  object instead of statement      can       WARNING  Should only be used for debugging  Inlining parameters is not              safe when handling user created data              import sqlparse     import sqlalchemy orm     if isinstance statement  sqlalchemy orm Query           if dialect is None              dialect   statement session get bind   dialect         statement   statement statement     compiled   statement compile dialect dialect                                   compile kwargs   literal binds   True       return sqlparse format str compiled   reindent reindent    I personally have a hard time reading code which is not indented so I ve used sqlparse to reindent the SQL  It can be installed with pip install sqlparse

User · Answer

This code is based on brilliant existing answer from  bukzor  I just added custom render for datetime datetime type into Oracle s TO DATE      Feel free to update code to suit your database   import decimal import datetime  def printquery statement  bind None               print a query  with values filled in     for debugging purposes  only      for security  you should always separate queries from their values     please also note that this function is quite slow             import sqlalchemy orm     if isinstance statement  sqlalchemy orm Query           if bind is None              bind   statement session get bind                      statement  mapper zero or none                         statement   statement statement     elif bind is None          bind   statement bind       dialect   bind dialect     compiler   statement  compiler dialect      class LiteralCompiler compiler   class             def visit bindparam                  self  bindparam  within columns clause False                   literal binds False    kwargs                        return super LiteralCompiler  self  render literal bindparam                      bindparam  within columns clause within columns clause                      literal binds literal binds    kwargs                       def render literal value self  value  type                   Render the value of a bind parameter as a quoted literal               This is used for statement sections that do not accept bind paramters             on the target driver database               This should be implemented by subclasses using the quoting services             of the DBAPI                               if isinstance value  basestring                   value   value replace                            return    s     value             elif value is None                  return  NULL              elif isinstance value   float  int  long                    return repr value              elif isinstance value  decimal Decimal                   return str value              elif isinstance value  datetime datetime                   return  TO DATE   s   YYYY-MM-DD HH24 MI SS      value strftime   Y- m- d  H  M  S                else                  raise NotImplementedError                               Don t know how to literal-quote value  r    value                   compiler   LiteralCompiler dialect  statement      print compiler process statement

User · Answer

In the vast majority of cases  the  quot stringification quot  of a SQLAlchemy statement or query is as simple as  print str statement    This applies both to an ORM Query as well as any select   or other statement  Note  the following detailed answer is being maintained on the sqlalchemy documentation  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 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 compile   method we only need access the  statement accessor first  statement   query statement print statement compile someengine    with regards to the original stipulation that bound parameters are to be  quot inlined quot  into the final string  the challenge here is that SQLAlchemy normally is not tasked with this  as this is handled appropriately by the Python DBAPI  not to mention bypassing bound parameters is probably the most widely exploited security holes 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   quot literal binds quot   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  quot my fancy formatting  s  quot    value  from sqlalchemy import Table  Column  MetaData  tab   Table  mytable   MetaData    Column  x   MyFancyType      print      tab select   where tab c x  gt  5  compile          compile kwargs   quot literal binds quot   True      producing output like  SELECT mytable x FROM mytable WHERE mytable x  gt  my fancy formatting 5

User · Answer

Given that what you want makes sense only when debugging  you could start SQLAlchemy with echo True  to log all SQL queries  For example  engine   create engine       quot mysql   scott tiger hostname dbname quot       encoding  quot latin1 quot       echo True     This can also be modified for just a single request   echo False     if True  the Engine will log all statements as well as a repr   of their parameter lists to the engines logger  which defaults to sys stdout  The echo attribute of Engine can be modified at any time to turn logging on and off  If set to the string  quot debug quot   result rows will be printed to the standard output as well  This flag ultimately controls a Python logger  see Configuring Logging for information on how to configure logging directly  Source  SQLAlchemy Engine Configuration  If used with Flask  you can simply set app config  quot SQLALCHEMY ECHO quot     True  to get the same behaviour

[python] SQLAlchemy: print the actual query

Examples related to python

Examples related to sqlalchemy