How to use variables to execute SQL statements in Python

Question

I have the following Python code:

cursor.execute("INSERT INTO table VALUES var1, var2, var3,")

Where var1 is an integer, var2 and var3 are strings.

How do I write variable names without having Python make them part of the query text?

Answer 1

Different Python DB-API implementations allow different placeholders, so you need to find out which one you are using -- for example (using MySQLdb):

cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", (var1, var2, var3))

Or (using sqlite3 from the Python standard library):

cursor.execute("INSERT INTO table VALUES (?, ?, ?)", (var1, var2, var3))

or other methods (after VALUES, you can use (:1, :2, :3), or use "named styles" (:fee, :fie, :fo) or (%(fee)s, %(fie)s, %(fo)s), in which case you need to use a dictionary as the second parameters are passed to the execute function). Check the paramstyle string constants in the DB API module you are using, and look at paramstyle at http://www.python.org/dev/peps/pep-0249/ , understand all the parameter passing methods!

Answer 2

cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", (var1, var2, var3))

Please note that the parameters are passed as a tuple, (a, b, c). If you pass only one parameter, the tuple needs to end with a comma, (a,).

The database API will properly escape and quote variables. Please be careful not to use the string formatting operator (%) because

1. It does not do any escaping or quoting.
2. It is vulnerable to uncontrollable string formatting attacks, such as SQL injection.