How to Safely Parameterize SQL SELECT Statements in Python with pyodbc?

Parameterized Select Statements in Python Using pyodbc

This question discusses how to execute a SQL SELECT statement using a variable as a parameter in a loop over a table called "Throughput." The specific parameter used varies depending on the row's "DeviceName" attribute.

Initial Attempts

Initially, the user attempted the following code without success:

for row in cursor.execute("SELECT * FROM Throughput WHERE DeviceName=%s", %(data['DeviceName'])):

Another unsuccessful attempt was:

for row in cursor.execute("SELECT * FROM Throughput WHERE(DeviceName), values(?)", (data['DeviceName']) ):

Final Working Code

Ultimately, the user successfully resolved the issue with the following snippet:

query = "SELECT * FROM Throughput WHERE DeviceName = '%s'" % data['Device Name']
try:
    for row in cursor.execute(query):

Parameterized Statements

The recommended approach is to use parameterized statements:

cursor.execute("SELECT * FROM Throughput WHERE DeviceName = ?", data['DeviceName'])

This approach offers several advantages over the previous attempts:

• Protection against SQL injection
• No need to escape where clause values with single quotes
• SQL preparation only occurs once, improving performance for subsequent executions

The above is the detailed content of How to Safely Parameterize SQL SELECT Statements in Python with pyodbc?. For more information, please follow other related articles on the PHP Chinese website!