Python MySQL executemany in WHERE clause

Question

I want to select values from MySQL as follows

do_not_select = [1,2,3]

cursor = database.cursor() 
 cursor.executemany("""SELECT * FROM table_a WHERE id != %s""",(do_not_select))
data = cursor.fetchall()

The query return all the values in the db apart form the first id (1). I don't want it to select id 1,2 or 3 however.

Is this possible using the executemany command..?

Nick Burns · Accepted Answer · 2013-08-18T00:19:21.837

2

Give NOT IN a go:

do_not_select = [1, 2, 3]

cursor.execute("""SELECT * FROM table_a
                    WHERE id NOT IN ({}, {}, {})""".format(do_not_select[0],
                                                           do_not_select[1],
                                                           do_not_select[2]))
data.cursor.fetchall()

I suspect (though I haven't tested this) that this would work better id do_not_select was a tuple, then I think you could just fire it straight into your query:

do_not_select = (1, 2, 3)
cursor.execute("""SELECT * FROM table_a
                    WHERE id NOT IN {}""".format(do_not_select))
data.cursor.fetchall()

I'd be interested to know if this works - if you try it please let me know :)

edited Aug 18 '13 at 00:19

answered Aug 17 '13 at 23:30

Nick Burns

973
6
4

This works if I type them but not if I feed them in using my do_not_select list....? – Jim Aug 17 '13 at 23:58
you would need to unpack do_not_select into the conditional, I will update above – Nick Burns Aug 18 '13 at 00:07
Really good that! I always used %s with some parameters instead of *.format()*. I'm gonna do that from now on. – Matheus Douglas Jul 23 '19 at 09:24

Python MySQL executemany in WHERE clause

1 Answers1

Linked