python - pyodbc - How to perform a select statement using a variable for a parameter

Question

I'm trying to iterate through all the rows in a table named Throughput, but for a specific DeviceName (which I have stored in data['DeviceName']. I've tried the following, but it doesn't work:

for row in cursor.execute("select * from Throughput where DeviceName=%s"), %(data['DeviceName']):

EDIT: also tried this but it doesn't work:

for row in cursor.execute("select * from Throughput where(DeviceName), values(?)", (data['DeviceName']) ):

EDIT2: A snippet of my final working code:

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

"Doesn't work" how? What error or unexpected behavior do you receive? — Andy♦

Bryan Bryan · Accepted Answer · 2012-03-01T17:55:37

You are also able to parameterize statements:

...
cursor.execute("select * from Throughput where DeviceName = ?", data['DeviceName'])
...

This a better approach for the following reasons:

Protection against SQL injection (you should always validate user input regardless of whether parameterized or dynamic SQL is used)
You don't have to worry about escaping where clause values with single quotes since parameters are passed to the database separately
SQL is prepared once, subsequent executions of the query use the prepared statement instead of recompiling

python - pyodbc - How to perform a select statement using a variable for a parameter

2 Answers