Using 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 to write variable names (as part of query text) without using Python?

Answer 1

Different implementations of the Python DB-API allow different placeholders, so you need to find out which one you are using - probably (e.g. using MySQLdb):

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

Or (for example, using sqlite3 from the Python standard library):

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

or other (after VALUES you can have (:1, :2, :3) or "named style" (:fee, : fie, :fo) or (%(fee)s, %(fie)s, %(fo)s) where you pass a dictionary instead of a map as the second argument to implement). Check the paramstyle string constants in the DB API module you are using and see all parameters at http://www.python.org/dev/peps/pep-0249/ What a delivery style!

Answer 2

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

Please note that parameters are passed in tuples, (a, b, c). If you pass a single argument, the tuple needs to end with a comma, (a,).

Database API properly escapes and quotes variables. Be careful not to use the string formatting operator (%) because

1. It does not do any escaping or quoting.
2. is vulnerable to uncontrolled string formatting attacks such as SQL_injection.