Python3 + DB: “Select * from `table` where `field` IN (…) AND `field2` = '…'”

31
February 12, 2019, at 11:10 AM

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'})

?

Answer 1

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.

READ ALSO
How to export a database (.sql) file to remote database through wifi? [on hold]

How to export a database (.sql) file to remote database through wifi? [on hold]

I have external database where I have export thesql file to my local now I want to import the same database through terminal by using wifi to another

34
how can i structure posts, shared posts and pages table in mysql database?

how can i structure posts, shared posts and pages table in mysql database?

I am working on a social networking site and i need to structure my POSTS table, SHARED_POSTS table and PAGES table and i don't know the best way to go about thisi have my USERS table and each post being made by a user is stored in the POSTS table with the current...

56
Mysql order on calculation with variable from outside tables

Mysql order on calculation with variable from outside tables

Can I do a calculation like this in MySQL:

46
What type of join to use here?

What type of join to use here?

I have two tables: StorageTransactions and FutureStockUsageMaterials

62