0

I am trying to pass a string though an MySQL query in Python like so - 

cur.execute("""SELECT * FROM abcDB.zyzTable WHERE ordId IN %s """ %(orderString1))

and orderString1 looks like (534,523,544) the string has the brackets

I get the following error.

ValueError: unsupported format character 'T' (0x54) at index 2195

How do I fix this?

DYZ
  • 55,249
  • 10
  • 64
  • 93
Sankar Muthuswamy
  • 9
  • 3
  • 2
    It is almost certain this is not where your error is. Re-check your exception for correct location, or re-check that you gave us correct string and data. This error would happen if you had `%T` or `%5T` or similar in your format string, which you don't in your example (not to mention that your example format string doesn't have 2195 characters). (I'm almost certain you didn't give us the relevant string, since I doubt your table is really called `abcDB.zyzTable`.) – Amadan Feb 06 '18 at 01:35
  • The `...` is interesting (or rather, useless, and what is left out in its stead is interesting :p ). Find if there's any `%` in there that is not supposed to be processed by the format operator, like `name LIKE '%Titanic%'`. Hint: it will be around 2195th character of the format string. ;) Also, if you want to post your query, edit your original question, since comments are too limited for large chunks of code. – Amadan Feb 06 '18 at 01:45

1 Answers1

2

I would recommend a different approach to parameterizing your IN query. Instead of making a string like "(534,523,544)" with all of the values you want to check, pass each value as a parameter.

For example:

order_ids = [534, 523, 544]
query = "SELECT * FROM abcDB.zyzTable WHERE ordId IN ("
query += ",".join(["%s"]*len(order_ids)) + ")"
cur.execute(query, order_ids)

Which would effectively execute this query:

"SELECT * FROM abcDB.zyzTable WHERE ordId IN (534,523,544)"

The key here is that I used ",".join(["%s"]*len(order_ids)) to dynamically create the appropriate number of placeholders for my query.

Also note that the second argument to cur.execute() is the list of order IDs. I did not format the string, as you were doing.

Don't use %, or you're susceptible to sql-injection. This way is safer, and more reliable. More reading on this post:

Python best practice and securest to connect to MySQL and execute queries.

pault
  • 41,343
  • 15
  • 107
  • 149