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 — roganjosh
– roganjosh, Commented Dec 18, 2019 at 19:20
Specifically, this 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
– roganjosh, Commented Dec 18, 2019 at 19:22
Oops, this answer, even. They basically say the same thing. But actually, they're missing unpacking — roganjosh
– roganjosh, Commented Dec 18, 2019 at 19:32

roganjosh · Accepted Answer · 2019-12-18 19:55:06Z

1

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

edited Dec 18, 2019 at 19:55

answered Dec 18, 2019 at 19:42

roganjosh

13.3k4 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Randy Stegner Sr. Over a year ago

This was a perfect solution. Thank you for the help @roganjosh.

roganjosh Over a year ago

@RandyStegnerSr. if you feel that this has answered your question, please see What should I do when someone answers my question? (which applies to your previous question also)

Randy Stegner Sr. Over a year ago

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. · Accepted Answer · 2019-12-18 19:26:16Z

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, 2019 at 19:26

answered Dec 18, 2019 at 19:22

Randy Stegner Sr.

878 bronze badges

5 Comments

roganjosh Over a year ago

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.

gold_cy Over a year ago

@roganjosh agreed, you should never have to include the keyword IN as part of your parameter substitution

Randy Stegner Sr. Over a year ago

@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.

roganjosh Over a year ago

I linked to it under your question, twice

Randy Stegner Sr. Over a year ago

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.

Collectives™ on Stack Overflow

Passing SQL Variable Using IN Expression in Python SQL Query

2 Answers 2

3 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related