python mysqldb string formatting

Answer 1

paramstyle
Parameter placeholders can only be used to insert column values. They can not be used for other parts of SQL, such as table names, statements, etc.

Answer 2

You could substitute your variables before passing the query string to execute():

query = """SELECT * FROM sometable 
                    order by %s %s 
                    limit %s, %s;"""
conn = app_globals.pool.connection()
cur = conn.cursor()
# This will pass a copy of query with all %s's substituted
cur.execute(query % (sortname, sortorder, limit1, limit2) )
results = cur.fetchall()

Answer 3

Not all parts of an SQL query can be parametrized. The DESC keyword for example is not a parameter. Try

query = """SELECT * FROM sometable 
                    order by %s """ + sortorder + """
                    limit %s, %s"""

cur.execute(query, (sortname, limit1, limit2) ) 

Answer 4

You could try this alternatively...

query = """SELECT * FROM sometable 
                    order by {0} {1} 
                    limit {2}, {3};"""

sortname = 'somecol'
sortorder = 'DESC'
limit1 = 'limit1'
limit2 = 'limit2'

print(query.format(sortname, sortorder, limit1, limit2))

Answer 5

%s placeholders inside query string are reserved for parameters. %s in 'order by %s %s' are not parameters. You should make query string in 2 steps:

query = """SELECT * FROM sometable order by %s %s limit %%s, %%s;"""
query = query % ('somecol', 'DESC')
conn = app_globals.pool.connection()
cur = conn.cursor()
cur.execute(query, (limit1, limit2) ) 
results = cur.fetchall()

DO NOT FORGET to filter first substitution to prevent SQL-injection possibilities