Python SQL query string formatting

Question

I am trying to find the best way to format sql query string. When I debug my application I would like to register in order to write all sql query strings, and it is important that the string is correctly formed.

Option 1

def myquery(): sql = "select field1, field2, field3, field4 from table where condition1=1 and condition2=2" con = mymodule.get_connection() ... 

• This is useful for printing sql strings.
• This is not a good solution if the string is long and does not match 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() ... 

• The code is clear here, but when you print the sql query string, you get all these annoying spaces.

  u '\ nselect field1, field2, field3, field4 \ n_ ____ from the table \ n ____ , where condition1 = 1 \ n ____ _and condition2 = 2'

Note. I replaced the white spaces with the underscore _ because they were 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 do not like this parameter because it violates the clarity of the code with the table with the table.

Option 4

 def query(): sql = "select field1, field2, field3, field4 " \ "from table " \ "where condition1=1 " \ "and condition2=2 " con = mymodule.get_connection() ... 

• I do not like this parameter, because there is all additional input on each line and it is also difficult to edit the query.

For me, Option 2 would be the best solution, but I don't like the extra spaces when printing an sql string.

Do you know any other options?

Answer 1

Sorry to post in such an old thread, but as a person who also shares a passion for a “better” python, I decided to share my decision.

The solution is to create SQL statements using the python String Literal Concatenation ( http://docs.python.org/ ), which can be qualified somewhere between Option 2 and Option 4

Code example:

 sql = ('select field1, field2, field3, field4 ' 'from table ' 'where condition1=1 ' 'and condition2=2 ') 

Pros:

• It retains the python "well-chosen" format, but does not add extra space characters (which pollutes logging).
• This avoids omitting the continuation of the backslash of Option 4, which makes it difficult to add statements (not to mention the blindness of white space).
• And yet, it’s very easy to expand the statement in VIM (just place the cursor at the insertion point and press SHIFT-O to open a new line).

Answer 2

You have obviously considered many ways to write SQL so that it prints in order, but what about modifying the print statement that you use to debug logging instead of writing your own SQL in a way that you don't like? Using your favorite option above, what about the logging function, for example:

 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) 

It will also make it easier to add additional logic to split the recorded line into several lines if the line is longer than the required length.

Answer 3

The cleanest way I've 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 start the sentence should be right-aligned, with field names, etc. Must be left aligned. It looks very neat and easier to debug.

Answer 4

 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 condition value should be a string, you can do the following:

 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'" 

Answer 5

you can put the field names in the "fields" of the array, and then:

 sql = 'select %s from table where condition1=1 and condition2=2' % ( ', '.join(fields)) 

Answer 6

 sql = """\ select field1, field2, field3, field4 from table where condition1=1 and condition2=2 """ 

[edit in responese to comment]
The presence of an SQL string inside a method does NOT mean that you should "tab" it:

 >>> class Foo: ... def fubar(self): ... sql = """\ ... select * ... from frobozz ... where zorkmids > 10 ... ;""" ... print sql ... >>> Foo().fubar() select * from frobozz where zorkmids > 10 ; >>> 

Answer 7

I would advise sticking with option 2 (I always use it for queries more complex than SELECT * FROM table ), and if you want to print it in a beautiful way, you can always use a separate module .

Answer 8

For short queries that can fit in one or two lines, I use the string literal solution in the solution above. For longer queries, I split them into .sql files. Then I use the 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 the class, so I usually should not explicitly skip the cursor . I also usually use codecs.open() , but this gives a general idea. Then, SQL scripts are completely autonomous in their files with syntax highlighting.

Answer 9

In addition to @ user590028:

Using the format was useful for what I was working on:

 statement = (ins "(name,standard_price,list_price,mes_type,uom_id,uom_po_id,type,procure_method,cost_method_categ_id,supply_method,sale_ok) " "VALUE ('{0}','{1}','{2}'".format(row[1],str(row[2]),str(row[2])) + ",'fixed',1,1,'product','make_to_stock','standard',1,'buy',True) RETURNING id" ) 

and

 statement = ("INSERT INTO product_product " "(product_tmpl_id,default_code,active,valuation) " "VALUE " "('{0}','{1}',True,'manual_periodic')".format(str(row[0]), row[1]) ) 

Answer 10

I would suggest a very simple option. just put r before the line. You can use it as below:

 query=(r'SELECT f1,f2,f3 ' r'FROM table1 ' r'WHERE f4=cond1 ' r'AND f5=cond2 ') cursor.execute(str(query)) results=cursor.fetchall() cursor.close() 

Answer 11

To completely avoid formatting , I think a good solution is to use procedures .

A procedure call gives you the result of any request that you want to place in this procedure. You can handle multiple requests in one procedure. The call will simply return the last request 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;')