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)

Does this answer your question? [python list in sql query as parameter](https://stackoverflow.com/questions/283645/python-list-in-sql-query-as-parameter) — roganjosh, Dec 18 '19 at 19:20
Specifically, [this](https://stackoverflow.com/a/4233213/4799172) answer, where you need to create placeholders. Note that some SQL dialects have limits to how many parameters you can have in an IN clause — roganjosh, Dec 18 '19 at 19:22
Oops, [this](https://stackoverflow.com/a/283801/4799172) answer, even. They basically say the same thing. But actually, they're missing unpacking — roganjosh, Dec 18 '19 at 19:32

roganjosh · Accepted Answer · 2019-12-18T19:55:06.067

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

You have other parameters, not just for your IN clause, so you'll need some unpacking
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

This was a perfect solution. Thank you for the help @roganjosh. — Randy Stegner Sr., Dec 19 '19 at 14:12
@RandyStegnerSr. if you feel that this has answered your question, please see [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers) (which applies to your previous question also) — roganjosh, Dec 19 '19 at 14:33
What would I do without you @roganjosh? I appreciate you helping me on more than just the problem I had! It's users like you that make Stack Overflow so awesome. — Randy Stegner Sr., Dec 19 '19 at 16:47

Randy Stegner Sr. · Answer 2 · 2019-12-18T19:26:16.840

0

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')")

edited Dec 18 '19 at 19:26

answered Dec 18 '19 at 19:22

Randy Stegner Sr.

67
7

1

Nope, this isn't figured out. This is hard-coded. Your initial idea was on the correct lines, you just didn't quite execute it correctly. – roganjosh Dec 18 '19 at 19:22
@roganjosh agreed, you should never have to include the keyword `IN` as part of your parameter substitution – gold_cy Dec 18 '19 at 19:25
@roganjosh, then what is the correct way to execute? I'm not a developer, I simply created a few scripts for some telecom reporting. Any help is appreciated. – Randy Stegner Sr. Dec 18 '19 at 19:28
I linked to it under your question, twice – roganjosh Dec 18 '19 at 19:30
I'm sorry about that as I totally missed it. I failed to refresh page. Duly noted for the future though. Thanks for helping a noob. – Randy Stegner Sr. Dec 19 '19 at 14:13

Passing SQL Variable Using IN Expression in Python SQL Query

2 Answers2