python - 将列表绑定(bind)到 Pandas read_sql_query 中的参数和其他参数

Question

我一直在尝试测试使我的代码运行的各种方法。首先，我有这个列表:

member_list = [111,222,333,444,555,...]

我试图将它传递到这个查询中:

query = pd.read_sql_query(
"""
select member id
    ,yearmonth
from queried_table
where yearmonth between ? and ?
    and member_id in ?
""", db2conn, params = [201601, 201603, member_list])

但是，我收到一条错误消息:

'Invalid parameter type. param-index=2 param-type=list', 'HY105'

所以我环顾四周并尝试使用格式化字符串:

query = pd.read_sql_query(
"""
select member id
    ,yearmonth
from queried_table
where yearmonth between ? and ?
    and member_id in (%s)
""" % ','.join(['?']*len(member_list), db2conn, params = [201601, 201603, tuple(member_list)])

现在，我得到了错误:

'The SQL contains 18622 parameter markers, but 3 parameters were supplied', 'HY000'

因为它要填写格式化字符串中的所有 ? 占位符。

那么，最终，有没有办法以某种方式评估列表并将每个单独的元素传递给 ？ 或者我可以使用另一种方法来让它工作吗？

顺便说一句，我正在使用 pyodbc 作为我的连接器。

提前致谢!

Answer 1

将其分为三个部分以帮助隔离问题并提高可读性:

1. 构建 SQL 字符串
2. 设置参数值
3. 执行pandas.read_sql_query

---

构建SQL

首先确保 ? 占位符设置正确。使用 str.format与 str.join和 len根据 member_list 长度动态填充 ?。下面的示例假设有 3 个 member_list 元素。

例子

member_list = (1,2,3)
sql = """select member_id, yearmonth
         from queried_table
         where yearmonth between {0} and {0}
         and member_id in ({1})"""
sql = sql.format('?', ','.join('?' * len(member_list)))
print(sql)

返回

select member_id, yearmonth
from queried_table
where yearmonth between ? and ?
and member_id in (?,?,?)

---

设置参数值

现在确保参数值被组织成一个扁平元组

例子

# generator to flatten values of irregular nested sequences,
# modified from answers http://stackoverflow.com/questions/952914/making-a-flat-list-out-of-list-of-lists-in-python
def flatten(l):
    for el in l:
        try:
            yield from flatten(el)
        except TypeError:
            yield el

params = tuple(flatten((201601, 201603, member_list)))
print(params)

返回

(201601, 201603, 1, 2, 3)

---

执行

最后将 sql 和 params 值一起放在 read_sql_query 调用中

query = pd.read_sql_query(sql, db2conn, params)