Python3 + DB: "Select * from `table` where `field` IN (...) AND `field2` = '...'"

Question

Using Python3 and the

cursor.execute(sql, {key1: val1, key2: val2})

syntax, I want to execute a safe (SQL-injection-proof) query such as:

SELECT * FROM `table`

WHERE 

a = %(fieldA)s AND b IN (%(fieldB)s)

Basically, I am looking for the answer of this question but with the Python3 syntax and using multiple fields.

If I use @nosklo's answer:

format_strings = ','.join(['%s'] * len(list_of_ids))
cursor.execute("DELETE FROM foo.bar WHERE baz IN (%s)" % format_strings,
tuple(list_of_ids))

1) How do I implement this double formatting syntax (format_strings, tuple(list_of_ids))) using the dictionary syntax:

cursor.execute(sql, {'field': 'val'})

2) and how do I implement it in my case when I have multiple fields:

cursor.execute(sql, {'x': 'myList', 'y':myOtherVar'})

?

score 0 · Accepted Answer · answered Feb 11 '19 at 13:47

OK, I think I just found it out:

myList = ["a", "b", "c"]
myStr = "d"

sql = """SELECT * FROM `table`

WHERE 

a = %(myStr)s AND b IN %(myList)s"""

cursor.execute(sql, {
    myList: myList,
    myStr: myStr
})

Have been trying with IN (%(myStr)s) before instead of IN %(myStr)s.

I still don't understand why some people claim we must use tuples in that case, as the list worked just perfectly fine.

Python3 + DB: "Select * from `table` where `field` IN (...) AND `field2` = '...'"

1 Answers1