How to put parameterized sql query into variable and then execute in Python?

Question

I understand that the correct way to format a sql query in Python is like this:

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

so that it prevents sql injection. My question is if there is a way to put the query in a variable and then execute it? I have tried the example below but receive an error. Is it possible to do this?

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

Answer 1

You might try this:

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

But I'm not sure if that's what you're looking for. It depends on whether you want the values as part of the variable or not. If you do, Ants Aasma's answer is probably correct.

Answer 2

You're pretty close.

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

The asterisk means that the variable isn't to be considered as one parameter but instead unpacked into many parameters.

Answer 3

Here is the call signature for cursor.execute:

Definition: cursor.execute(self, query, args=None)

    query -- string, query to execute on server
    args -- optional sequence or mapping, parameters to use with query.

So execute expects at most 3 arguments (args is optional). If args is given, it is expected to be a sequence. so

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

is not going to work, because

cursor.execute(*sql_and_params)

expands the tuple sql_and_params into 4 arguments (and again, execute only expects 3).

If you really must use

sql_and_params = "INSERT INTO table VALUES (%s, %s, %s)", var1, var2, var3

then you'll have to break it apart whe feeding it to cursor.execute:

cursor.execute(sql_and_params[0],sql_and_params[1:])

But I think it feels much more pleasant to just use two variables:

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