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

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!

Answers


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.


%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


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) ) 

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))

Need Your Help

How do I vertically centering text inside a responsive box that has a (fluid height)

css responsive-design vertical-alignment

I am building a responsive website and am using a grid system. The grid boxes of course do not have a set height. they are fluid. The image that is placed within them will define the height ad then...

Is Validation a SemiGroup/Monoid : using |+| does not work

scalaz

I was uder the impression that Validation could be used as a Monoid/SemiGroup