Can I Execute Parameterized SQL Queries Stored in a Variable in Python?

Execute parameterized SQL queries from variables in Python

To prevent SQL injection, when executing parameterized SQL queries in Python, it is generally recommended to use the following format:

<code class="language-python">cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", var1, var2, var3)</code>

However, it is worth exploring the question of whether it is possible to store the query in a variable and execute it later.

execute() Method signature

To understand why this might not work, we need to check the signature of the execute() method:

<code class="language-python">cursor.execute(self, query, args=None)</code>

This method requires up to three parameters: a query and an optional parameter sequence or map.

Try to execute query from variable

If we try to execute a query stored in variable sql, for example:

<code class="language-python">sql = "INSERT INTO table VALUES (%s, %s, %s)"
cursor.execute(sql)</code>

We will get an error because sql contains four parameters, including the variable itself.

Separate queries and parameters

To execute a query from a variable, we can separate the query and parameters:

<code class="language-python">sql = "INSERT INTO table VALUES (%s, %s, %s)"
args = var1, var2, var3
cursor.execute(sql, args)</code>

In this case, sql contains the query and args contains the parameters. By specifying execute(sql, args), we can successfully execute the parameterized query.

Another way

Alternatively, we can use the more intuitive two-variable approach:

<code class="language-python">sql = "INSERT INTO table VALUES (%s, %s, %s)"
cursor.execute(sql, (var1, var2, var3))</code>

This approach eliminates the need to create separate variables for queries and parameters.

The above is the detailed content of Can I Execute Parameterized SQL Queries Stored in a Variable in Python?. For more information, please follow other related articles on the PHP Chinese website!