I have a list of ids in python. For example:
x = [1,2,3,4,5,6]
And i want to select a list of records in my (mysql ) data-base under the condition that the ids of these records are in x. something like below:
SELECT * FROM mytable WHERE id IN x
but I don't know who I can do this in python. I have seen some examples using %s in their sql string. However this does not work when the variable is a list. does anyone know how I can do this? Thanks
Try something like this:
'(%s)' % ','.join(map(str,x))
This will give you a string that you could use to send to MySql as a valid IN clause:
(1,2,3,4,5,6)
Well, if all of those are known to be numbers of good standing, then you can simply call
"SELECT * FROM mytable WHERE ID IN ({0})".format(','.join(x))
If you know that they are numbers but any of them might have been from the user, then I might use:
"SELECT * FROM mytable WHERE ID IN ({0})".format(','.join(list(map(int,x))))
format will perform the replacement at the appropriate index. join is used so that you don't have the []. list converts everything to a list, map applies a function to a list/tuple/iterable. In Python 3, however, map returns a generator, which isn't what you need. You need a list. So, list(map(x,y)) will return the list form of map(x,y).
