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

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

Answer 2

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.