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};")
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.
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.
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'"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With