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

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

valerybodak · Accepted Answer · 2023-02-17 10:40:18Z

60

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

edited Feb 17, 2023 at 10:40

valerybodak

4,4334 gold badges44 silver badges56 bronze badges

answered Mar 1, 2012 at 17:55

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

M. Barbieri Over a year ago

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 stackoverflow.com/questions/37861319/… I would really appreciate it.

DrDamnit Over a year ago

@M.Barbieri, enable logging in MySQL to see what statement was actually executed.

Auspex Over a year ago

"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!

Mehdi Abd · Accepted Answer · 2021-05-10 05:46:26Z

-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.

answered May 10, 2021 at 5:46

Mehdi Abd

1

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related