11

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)
Will
  • 113
  • 1
  • 1
  • 4

4 Answers4

25

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 when 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)
unutbu
  • 842,883
  • 184
  • 1,785
  • 1,677
6

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.

Ants Aasma
  • 53,288
  • 15
  • 90
  • 97
  • why did you use `,` instead of `%`? For example to do this: `sql_and_params = "INSERT INTO table VALUES (%s, %s, %s)" % (var1, var2, var3)` – bzupnick Apr 04 '16 at 10:55
  • 2
    @bzupnick using simple string formatting (the `%` operator), or substitution, would potentially allow SQL injection, not to mention failing to add quotation marks to the literals if needed. Parameterized queries are highly superior to formatted/substituted queries. – Eric Hughes May 24 '16 at 19:46
1

This worked for me. Querying Microsoft SQL Server using pyodbc.

cusotmer_list = ['ABC', '123']

# parameterized query placeholders
placeholders = ",".join("?" * len(customer_list))

# query table
query = 
"""
SELECT
[ID],
[Customer]
FROM xyz.dbo.abc
WHERE [Customer] IN (%s)
""" % placeholders

# read query results in pandas dataframe
df = pd.read_sql(sql=query, con=cnxn, params=customer_list)
Joe Rivera
  • 307
  • 2
  • 11
1

the best way for pass parameters to SQL query in Python is:

"INSERT INTO table VALUES (:1, :2, :3)  ", [val1, val2, val3]

or another example:

"UPDATE table T SET T.column2 = :1 where T.column1= :2 ", [val1,val2]
Nolequen
  • 3,032
  • 6
  • 36
  • 55
Morteza Parvaresh
  • 11
  • 3