Python SQL query string formatting

Question

I m trying to find the best way to format an sql query string  When I m debugging  my application I d like to log to file all the sql query strings  and it is important that the string is properly formated   Option 1  def myquery        sql    select field1  field2  field3  field4 from table where condition1 1 and condition2 2      con   mymodule get connection              This is good for printing the sql string  It is not a good solution if the string is long and not fits the standard width  of 80 characters    Option 2  def query        sql               select field1  field2  field3  field4         from table         where condition1 1         and condition2 2        con   mymodule get connection              Here the code is clear but when you print the sql query string you get all these annoying white spaces      u  nselect field1  field2  field3  field4 n     from table n    where condition1 1  n     and condition2 2     Note  I have replaced white spaces with underscore    because they are trimmed by the editor  Option 3  def query        sql      select field1  field2  field3  field4 from table where condition1 1 and condition2 2        con   mymodule get connection              I don t like this option because it breaks the clearness of the well tabulated code    Option 4  def query        sql    select field1  field2  field3  field4                from table                where condition1 1                and condition2 2       con   mymodule get connection                  I don t like this option because all the extra typing in each line and is difficult to edit the query also    For me the best solution would be Option 2 but I don t like the extra whitespaces when I print the sql string   Do you know of any other options

User · Answer

To avoid formatting entirely  I think a great solution is to use procedures   Calling a procedure gives you the result of whatever query you want to put in this procedure  You can actually process multiple queries within a procedure  The call will just return the last query that was called   MYSQL  DROP PROCEDURE IF EXISTS example   DELIMITER     CREATE PROCEDURE example      BEGIN    SELECT 2 222 2222 222 222 2222 2222 AS this is a really long string test     END     DELIMITER    calling the procedure gives you the result of whatever query you want to put in this procedure  You can actually process multiple queries within a procedure  The call just returns the last query result  call example    Python  sql    call example

User · Answer

Sorry for posting to such an old thread -- but as someone who also shares a passion for pythonic  best   I thought I d share our solution   The solution is to build SQL statements using python s String Literal Concatenation  http   docs python org    which could be qualified a somewhere between Option 2 and Option 4  Code Sample   sql     SELECT field1  field2  field3  field4           FROM table           WHERE condition1 1           AND condition2 2      Works as well with f-strings   fields    field1  field2  field3  field4  table    table  conditions    condition1 1 AND condition2 2   sql    f SELECT  fields           f FROM  table           f WHERE  conditions       Pros    It retains the pythonic  well tabulated  format  but does not add extraneous space characters  which pollutes logging    It avoids the backslash continuation ugliness of Option 4  which makes it difficult to add statements  not to mention white-space blindness   And further  it s really simple to expand the statement in VIM  just position the cursor to the insert point  and press SHIFT-O to open a new line

User · Answer

You can use inspect cleandoc to nicely format your printed SQL statement  This works very well with your option 2  Note  the print  quot - quot  40  is only to demonstrate the superflous blank lines if you do not use cleandoc  from inspect import cleandoc def query        sql    quot  quot  quot          select field1  field2  field3  field4         from table         where condition1 1         and condition2 2      quot  quot  quot       print  quot - quot  40      print sql      print  quot - quot  40      print cleandoc sql       print  quot - quot  40   query    Output  ----------------------------------------          select field1  field2  field3  field4         from table         where condition1 1         and condition2 2  ---------------------------------------- select field1  field2  field3  field4 from table where condition1 1 and condition2 2 ----------------------------------------  From the docs   inspect cleandoc doc  Clean up indentation from docstrings that are indented to line up with blocks of code  All leading whitespace is removed from the first line  Any leading whitespace that can be uniformly removed from the second line onwards is removed  Empty lines at the beginning and end are subsequently removed  Also  all tabs are expanded to spaces

User · Answer

For short queries that can fit on one or two lines  I use the string literal solution in the top-voted solution above  For longer queries  I break them out to  sql files  I then use a wrapper function to load the file and execute the script  something like   script cache      def execute script cursor script  args   kwargs       if not script in script cache          with open script  r   as s              script cache script    s     return cursor execute script cache script   args   kwargs    Of course this often lives inside a class so I don t usually have to pass cursor explicitly  I also generally use codecs open    but this gets the general idea across  Then SQL scripts are completely self-contained in their own files with their own syntax highlighting

User · Answer

Cleanest way I have come across is inspired by the sql style guide   sql           SELECT field1  field2  field3  field4       FROM table      WHERE condition1   1        AND condition2   2        Essentially  the keywords that begin a clause should be right-aligned and the field names etc  should be left aligned  This looks very neat and is easier to debug as well

User · Answer

Using  sqlparse  library we can format the sqls   gt  gt  gt  import sqlparse  gt  gt  gt  raw    select   from foo  select   from bar    gt  gt  gt  print sqlparse format raw  reindent True  keyword case  upper    SELECT   FROM foo   SELECT   FROM bar   Ref  https   pypi org project sqlparse

User · Answer

This is slightly modified version of  aandis answer  When it comes to raw string  prefix  r  character before the string  For example  sql   r quot  quot  quot      SELECT field1  field2  field3  field4       FROM table      WHERE condition1   1        AND condition2   2   quot  quot  quot   This is recommended when your query has any special character  like     which requires escaping and lint tools like flake8 reports it as error

User · Answer

you could put the field names into an array  fields   and then    sql    select  s from table where condition1 1 and condition2 2            join fields

User · Answer

sql     select field1  field2  field3  field4           from table           where condition1              and condition2      format 1  2   Output   select field1  field2  field3  field4 from table           where condition1 1 and condition2 2    if the value of condition should be a string  you can do like this   sql     select field1  field2  field3  field4           from table           where condition1   0             and condition2   1     format  2016-10-12    2017-10-12    Output   select field1  field2  field3  field4 from table where          condition1  2016-10-12  and condition2  2017-10-12

User · Answer

You ve obviously considered lots of ways to write the SQL such that it prints out okay  but how about changing the  print  statement you use for debug logging  rather than writing your SQL in ways you don t like   Using your favourite option above  how about a logging function such as this   def debugLogSQL sql        print     join  line strip   for line in sql splitlines     strip    sql           select field1  field2  field3  field4     from table    if debug      debugLogSQL sql    This would also make it trivial to add additional logic to split the logged string across multiple lines if the line is longer than your desired length

User · Answer

sql        select field1  field2  field3  field4 from table where condition1 1 and condition2 2        edit in responese to comment  Having an SQL string inside a method does NOT mean that you have to  tabulate  it    gt  gt  gt  class Foo          def fubar self               sql            select       from frobozz     where zorkmids  gt  10                      print sql      gt  gt  gt  Foo   fubar   select   from frobozz where zorkmids  gt  10    gt  gt  gt

User · Answer

I would suggest sticking to option 2  I m always using it for queries any more complex than SELECT   FROM table  and if you want to print it in a nice way you may always use a separate module

[python] Python SQL query string formatting

MYSQL

Python

Examples related to python

Examples related to sql

Examples related to string-formatting