How to use variables in 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 in Python without them being included in the query text?

Answer 1

Different implementations of the Python DB-API 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 something else (after VALUES you can have (:1, :2, :3), or "named style" (:fee, :fie , :fo) or (%(fee)s, %(fie)s, %(fo)s), pass one in the second parameter of execute dictionary rather than a map). Check the paramstyle string constants in the DB API module you are using and look for paramstyle at http://www.python.org/dev/peps/pep-0249/ , to learn about all parameter passing styles!

Answer 2

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

Please note that the parameter is 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.