How to Use SQL Parameters with Python

How to use variables in SQL statement in Python?

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

Note that the parameters are passed as a tuple.

The database API does proper escaping and quoting of variables. Be careful not to use the string formatting operator (%), because

1. it does not do any escaping or quoting.
2. it is prone to Uncontrolled string format attacks e.g. SQL injection.

How do I use SQL parameters with python?

After creating a connection object db:

cursor = db.execute('SELECT * FROM Customer WHERE CustomerID = %s', [customer_id])

then use any of the fetch... methods of the resulting cursor object.

Don't be fooled by the %s part: this is NOT string formatting, it's parameter substitution (different DB API modules use different syntax for parameter substitution -- pymssql just happens to use the unfortunate %s!-).

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

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)

How to use SQL parameters with IN clause for a variable number of values with pyodbc?

You can use JSON to pass the list to SQL Server. EG

import numpy as np
import pandas as pd
import pyodbc
import json 

files = ['file1', 'file2', 'file3']  # this list can have a variable number of elements
json_files = json.dumps(files)
print(json_files)
conn = pyodbc.connect('Driver={Sql Server};'
                      'Server=localhost;'
                      'Database=tempdb;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()

cursor.execute("create table #sometable(id int, file_name varchar(255)); insert into #sometable(id,file_name) values (1,'file2')")
# What I'd like to do
result = cursor.execute('SELECT * FROM #sometable WHERE file_name IN (select value from openjson(?))', json_files)
rows = cursor.fetchall()
print(rows)

Passing parameters from Python to SQL Server

If I remember correctly, the placeholder is %s and not ?.

Regardless, you can use the format method / string formatting to get the job done:

conn = pyodbc.connect(<Connection Details>)
c = conn.cursor()
employee_id=(100,101)

query = "select * from employees where employee_id in {}"
c.execute(query.format(employee_id))

---

Related Topics