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!

SilentGhost · Accepted Answer · 2010-03-29 13:55:11Z

9

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.

answered Mar 29, 2010 at 13:55

SilentGhost

322k67 gold badges312 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yaroslav · Accepted Answer · 2010-03-29 14:10:28Z

6

%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

edited Mar 29, 2010 at 14:10

answered Mar 29, 2010 at 14:00

Yaroslav

2,73617 silver badges16 bronze badges

Comments

unutbu · Accepted Answer · 2010-03-29 13:57:20Z

0

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

answered Mar 29, 2010 at 13:57

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

Comments

naivnomore · Accepted Answer · 2010-03-29 13:57:28Z

-1

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

answered Mar 29, 2010 at 13:57

naivnomore

1,3098 silver badges14 bronze badges

2 Comments

Heals Over a year ago

You should note that this will only work in >= python 2.6 -- which not everyone is using by now.

alexgolec Over a year ago

Also note that this is an incorrect solution for when the values are not controlled, due to the possibility of SQL injection attacks.

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related