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)

If 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)

  1. techieyuckyboy posted this
Short URL for this post: http://tmblr.co/ZT-mIxAkuO2H