What is the correct way to form MySQL queries in python?

Question

I am new to python, I come here from the land of PHP. I constructed a SQL query like this in python based on my PHP knowledge and I get warnings and errors

cursor_.execute("update posts set comment_count = comment_count + "+str(cursor_.rowcount)+" where ID = " + str(postid))
# rowcount here is int

What is the right way to form queries?

Also, how do I escape strings to form SQL safe ones? like if I want to escape -, ', " etc, I used to use addslashes. How do we do it in python?

Thanks

Answer 1

First of all, it's high time to learn to pass variables to the queries safely, using the method Matus expressed. Clearer,

tuple = (foovar, barvar)
cursor.execute("QUERY WHERE foo = ? AND bar = ?", tuple)

If you only need to pass one variable, you must still make it a tuple: insert comma at the end to tell Python to treat it as a one-tuple: tuple = (onevar,)

Your example would be of form:

cursor_.execute("update posts set comment_count = comment_count + ? where id = ?",
 (cursor_.rowcount, postid))

You can also use named parameters like this:

cursor_.execute("update posts set comment_count = comment_count + :count where id = :id",
 {"count": cursor_.rowcount, "id": postid})

This time the parameters aren't a tuple, but a dictionary that is formed in pairs of "key": value.

• arbithero: Can I use something like this? cursor.execute("update posts set comment_count = %s where postid = %d",cursor_.rowcount,postid)
• mike3996: @arbithero: y-yes, I think you can. From wiki.python.org/moin/DbApiFaq I interpret that as a safe method as well. Where did you get that example from? I can't find %d anywhere.
• mike3996: @arbithero: added some examples about named queries if that looks better.

Answer 2

from python manual:

t = (symbol,)
c.execute( 'select * from stocks where symbol=?', t )

this way you prevent SQL injection ( suppose this is the SQL safe you refer to ) and also have formatting solved