How to Put Parameterized SQL Query into Variable and Then Execute in Python

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 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.

Python and Prepared SQL Statements using Variables

Ok I have resolved this issue now.
I had to update the sybase module in order to get it to work with None > NULL.

As posted in the updated question. the below is how I was running the queries.

cursor.execute("SELECT * FROM DB..table where app_name=@app_name", {"@app_name": app_name})
or
params = {"@appname": app.name. "@appver": app.version}
sql = "INSERT INTO DB..table (app_name, app_version) VALUES (@appname, @appversion)
cursor.execute(sql, params)

Passing SQL Variable Using IN Expression in Python SQL Query

This is almost a dupe of python list in sql query as parameter but there's a couple of things missing:

1. You have other parameters, not just for your IN clause, so you'll need some unpacking
2. A note about the fact that some SQL dialects (e.g. SQLite) will have limits on the number of parameters you can pass.

Corrected code:

cursor = conn.cursor()

membership_data = [1, 2] 
placeholders = ', '.join(['?' for item in membership_data])

query = """
        SELECT touchtonekey, COUNT(touchtonekey) AS touchedthismanytimes 
        FROM vw_callhandlertraffic
        WHERE callhandlername = ? AND createddatetime BETWEEN ? AND ? 
            AND touchtonekey IN ({}) 
        GROUP BY touchtonekey 
        ORDER BY touchtonekey
        """.format(placeholders) # Add the placeholders to your IN clause

data = cursor.execute(query,
                      ('My CallHandler',
                      '2019-10-09 13:00:00',
                      '2019-12-09 13:59:59',
                      *membership_data)) # Unpack the list

Note the use of *, the "splat"/"unpacking" operator to make a flat tuple of arguments to execute

SQL injection , use parameter

It is recommended to set the queries this way:

query = """Update employee set Salary = %s where id = %s"""
tuple1 = (8000, 5)
cursor.execute(query, tuple1)

More info here:
https://pynative.com/python-mysql-execute-parameterized-query-using-prepared-statement/

Execute multiple variable query in SQL Server using Python cursor

Consider actual SQL parameterization of the time variables and not string interpolation or concatenation with F-strings which generally is not safe or efficient for passing values from application layer to backend database. The library, pymssql, supports parameters. Python's datetime.datetime should translate to MSSQL's DATETIME.

# PREPARED STATEMENTS WITH %s PLACEHOLDERS
query = """DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10);  
           SET @begin_time = %s;  
           SET @end_time = %s; 
           SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);  
           SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time); 
           SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_users (@from_lsn, @to_lsn, 'all');
        """
print(query)

# EXECUTE QUERY WITH BINDED PARAMS
cur.execute(query, [start_date, end_date])

In fact, you can shorten the query since parameters do not need declaration:

# PREPARED STATEMENTS WITH %s PLACEHOLDERS
query = """DECLARE @from_lsn binary(10), @to_lsn binary(10);
           SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', %s);  
           SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', %s); 
           SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_users (@from_lsn, @to_lsn, 'all');
        """

By the way, please note pymmsql is no longer a maintained library. Consider pyodbc for most secure, updated DB-API for Python-SQL Server connections. But note the parameter placeholder for pyodbc is qmarks ? and not %s (unlike most Python DB-APIs).

MySQL parameterized queries

Beware of using string interpolation for SQL queries, since it won't escape the input parameters correctly and will leave your application open to SQL injection vulnerabilities. The difference might seem trivial, but in reality it's huge.

Incorrect (with security issues)

c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s" % (param1, param2))

Correct (with escaping)

c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s", (param1, param2))

It adds to the confusion that the modifiers used to bind parameters in a SQL statement varies between different DB API implementations and that the mysql client library uses printf style syntax instead of the more commonly accepted '?' marker (used by eg. python-sqlite).

---

Related Topics