How can I format strings to query with mysqldb in Python?

Question

How do I do this correctly:

I want to do a query like this:

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

All works fine but the order by %s %s is not putting the strings in correctly. It is putting the two substitutions in with quotes around them.

So it ends up like:

ORDER BY 'somecol' 'DESC'

Which is wrong should be:

ORDER BY somecol DESC

Any help greatly appreciated!

Solution 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.

Solution 2

%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