Python SQL query string formatting

Python SQL query string formatting

Sorry for posting to such an old thread — but as someone who also shares a passion for pythonic best, I thought Id share our solution.

The solution is to build SQL statements using pythons 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 = (fSELECT {fields} 
       fFROM {table} 
       fWHERE {conditions};)

Pros:

  1. It retains the pythonic well tabulated format, but does not add extraneous space characters (which pollutes logging).
  2. It avoids the backslash continuation ugliness of Option 4, which makes it difficult to add statements (not to mention white-space blindness).
  3. And further, its 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).

Youve 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 dont 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.

Python SQL query string formatting

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.

Leave a Reply

Your email address will not be published.