DB Programming: Bind Variables Inside IN Clause
Not using bind variables for your DB queries is a sin. But how do you do it if you want the stuff inside the IN clause to be bind variables? Let’s say we are getting a list of strings
str_list from an HTML form and we want this to be the list inside the IN clause.
The following does not work (I’ll use Python and MySQL):
mycursor.execute("""SELECT * FROM mytable WHERE my_column IN (%s)""", str_list)
str_list contains more than one element, the statement will fail.
The solution is to have a varying number of bind variables depending on the number of elements in
str_list. So for example we have three elements in
str_list, we want
"%s, %s, %s" inside the IN clause. Here’s how to do it:
bind_vars = ", ".join(["%s"] * len(str_list)) mycursor.execute("""SELECT * FROM mytable WHERE my_column IN (%s)""" % bind_vars, str_list)