Passing SQL Variable Using IN Expression in Python SQL Query

Question

Best practice for passing a SQL query in Python is to use (?) placeholders. I'm running into an issue where I have an IN expression for my SQL query and cannot determine how to pass the variable arguments to the placeholder. Edit: This differs from other answers (as pointed out in comments) in that other answers don't include unpacking. My working code is:

cursor = conn.cursor()
query = "Select touchtonekey, COUNT(touchtonekey) as touchedthismanytimes from vw_callhandlertraffic\
            where callhandlername = ? and createddatetime between ?\
            and ? and touchtonekey IN ('1','2') Group By touchtonekey Order by touchtonekey"

data = cursor.execute(query,'My CallHandler','2019-10-09 13:00:00',
                      '2019-12-09 13:59:59')

But when I try to remove the IN arguments with this code:

query = "Select touchtonekey, COUNT(touchtonekey) as touchedthismanytimes from vw_callhandlertraffic\
            where callhandlername = ? and createddatetime between ?\
            and ? and touchtonekey IN ? Group By touchtonekey Order by touchtonekey"

data = cursor.execute(query,'My CallHandler','2019-10-09 13:00:00',
                      '2019-12-09 13:59:59', "('1','2')")

I get:

Right hand side of IN expression must be a COLLECTION type.

And If I remove the quotes from the parenthesis, I get:

Invalid application buffer type. (-11116) (SQLBindParameter)

Answer 1

This is almost a dupe of python list in sql query as parameter but there's a couple of things missing:

1. You have other parameters, not just for your IN clause, so you'll need some unpacking
2. A note about the fact that some SQL dialects (e.g. SQLite) will have limits on the number of parameters you can pass.

Corrected code:

cursor = conn.cursor()

membership_data = [1, 2] 
placeholders = ', '.join(['?' for item in membership_data])

query = """
        SELECT touchtonekey, COUNT(touchtonekey) AS touchedthismanytimes 
        FROM vw_callhandlertraffic
        WHERE callhandlername = ? AND createddatetime BETWEEN ? AND ? 
            AND touchtonekey IN ({}) 
        GROUP BY touchtonekey 
        ORDER BY touchtonekey
        """.format(placeholders) # Add the placeholders to your IN clause

data = cursor.execute(query,
                      ('My CallHandler',
                      '2019-10-09 13:00:00',
                      '2019-12-09 13:59:59',
                      *membership_data)) # Unpack the list

Note the use of *, the "splat"/"unpacking" operator to make a flat tuple of arguments to execute

Answer 2

While I was typing up my question I figured it out. Since I didn't see an answer on Stack Overflow (I may have just not been able to find one) I figured I would post the question and then answer it in case it helps someone else. The key is to always use the = ? syntax and then include the IN keyword in the parameter like so:

cursor = conn.cursor()

    query = "Select touchtonekey, COUNT(touchtonekey) as 
             touchedthismanytimes from vw_callhandlertraffic\
             where callhandlername = ? and createddatetime between ?\
             and ? and touchtonekey = ? Group By touchtonekey Order by\ 
             touchtonekey"

    data = cursor.execute(query,'My CallHandler','2019-10-09 13:00:00',
                                 '2019-12-09 13:59:59',"IN ('1','2')")