22

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):
SoMZeY
  • 7
  • 5
Parth
  • 1,226
  • 7
  • 28
  • 49

2 Answers2

57

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
valerybodak
  • 4,195
  • 2
  • 42
  • 53
Bryan
  • 17,112
  • 7
  • 57
  • 80
  • When doing this, is there any way to print out the whole query, because I am having a date/time conversion error, but I don't know where, however, when I print out the query string, it shows the question marks rather than the actual values that are supposed to replace those question marks. If you could take a look at my problem on http://stackoverflow.com/questions/37861319/pyodbc-cursor-execute-wont-insert-parameters-into-sql-string?noredirect=1#comment63185389_37861319 I would really appreciate it. – M. Barbieri Jun 16 '16 at 16:53
  • @M.Barbieri, enable logging in MySQL to see what statement was actually executed. – DrDamnit Jun 19 '17 at 19:15
  • "_always validate user input_" seems much too strong regarding parameterized queries. If my parameter requires an integer, and the authorized methods to invoked the code only permit integers, I don't see the benefit. I want all improper methods to fail badly! – Auspex Dec 02 '20 at 14:35
-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.

Mehdi Abd
  • 1