在Python SQL查询中使用IN表达式传递SQL变量

Question

在Python中传递SQL查询的最佳实践是使用（？）占位符。我遇到了一个问题，我的SQL查询有一个IN表达式，无法确定如何将变量参数传递给占位符。编辑：这不同于其他答案（如评论中指出的），因为其他答案不包括解包。我的工作代码是：

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

但当我尝试用以下代码删除IN参数时：

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

我得到：

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

如果我去掉括号中的引号，我会得到：

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

Answer 1

这几乎是一个python list in sql query as parameter的复制品，但缺少一些东西：

1. 您还有其他参数，而不仅仅是IN子句的参数，因此需要一些解包
2. 关于某些SQL方言（例如SQLite）对number of parameters you can pass有限制这一事实的说明。你知道吗

更正代码：

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

注意使用*，the "splat"/"unpacking" operator来生成execute的参数的平面元组

Answer 2

当我把问题打出来的时候，我想出来了。因为我在堆栈溢出上没有看到答案（我可能就是找不到答案），所以我想我应该发布这个问题，然后再回答它，以防它对其他人有帮助。关键是始终使用=？语法，然后在参数中包含IN关键字，如下所示：

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