How to Execute a Parameterized SQL Query Stored in a Variable in Python?

Execute parameterized SQL queries stored in variables in Python

When dealing with SQL queries in Python, it is crucial to use parameterized queries to prevent SQL injection. This involves replacing parameter placeholders (for example, '%s') with variables containing actual values.

A common question is whether it is possible to store a parameterized SQL query in a variable and then execute it. Let's explore the syntax and how to achieve this.

Question

Consider the following Python code:

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

Unfortunately, this code throws an error because cursor.execute() accepts up to three parameters: query string and optional parameters. In this example, we have four parameters, represented by the tuple (sql, var1, var2, var3).

Solution

There are two ways to execute parameterized SQL queries from variables:

Method 1: Expand parameters

We can expand the parameters of the tuple containing the query and parameters into cursor.execute():

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

This expands the tuple to four parameters, but this may seem clumsy.

Method 2: Separate query and parameters

For clarity, it is best to separate the SQL query and parameters into two variables:

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

This approach is more concise and easier to read and maintain.

Conclusion

In summary, when executing a parameterized SQL query from variables in Python, you need to expand the parameters or separate the query and parameters into different variables. The second method is usually recommended because it is simpler and more straightforward.

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