CODEWITHSUNDEEP
pythonsqlvariablesselectpyodbc
Parth
Parth

Reputation: 1266

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

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):

Upvotes: 23

Views: 70094

Answers (2)

Bryan
Bryan

Reputation: 17703

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

Upvotes: 59

Mehdi Abd
Mehdi Abd

Reputation: 1

I don't know if my problem is similar to yours or not but my problem was because I had written a query like WHERE date > ?" "OR date NOT LIKE '9%' and I'd forgotten to put a simple space (' ') either at the end of the 1st line or the end of the 2nd one. Finally I resolved it just with doing this. And the final code looks like:

WHERE date > ? "
            "OR date NOT LIKE '9%'

note: pay attention to the final ' ' at the end of the 1st line.

Upvotes: -1

Related Questions