Python 将列表绑定到 Pandas read_sql_query 中的参数与其他参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36840438/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Binding list to params in Pandas read_sql_query with other params
提问by Scratch'N'Purr
I've been trying to test various methods for making my code to run. To begin with, I have this list:
我一直在尝试测试使我的代码运行的各种方法。首先，我有这个列表：
member_list = [111,222,333,444,555,...]
member_list = [111,222,333,444,555,...]
I tried to pass it into this query:
我试图将它传递给这个查询：
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])
However, I get an error that says:
但是，我收到一条错误消息：
'Invalid parameter type. param-index=2 param-type=list', 'HY105'
'无效的参数类型。参数索引=2 参数类型=列表', 'HY105'
So I looked around and tried using formatted strings:
所以我环顾四周并尝试使用格式化的字符串：
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)])
Now, I get the error:
现在，我收到错误：
'The SQL contains 18622 parameter markers, but 3 parameters were supplied', 'HY000'
'SQL 包含 18622 个参数标记，但提供了 3 个参数', 'HY000'
because it's looking to fill in all the ?placeholders in the formatted string.
因为它希望填充?格式化字符串中的所有占位符。
So, ultimately, is there a way to somehow evaluate the list and pass each individual element to bind to the ?or is there another method I could use to get this to work?
那么，最终，有没有办法以某种方式评估列表并传递每个单独的元素以绑定到?或者是否有另一种方法可以使它起作用？
Btw, I'm using pyodbcas my connector.
顺便说一句，我pyodbc用作我的连接器。
Thanks in advance!
提前致谢！
回答by Bryan
Break this up into three parts to help isolate the problem and improve readability:
将其分为三个部分以帮助隔离问题并提高可读性：
- Build the SQL string
- Set parameter values
- Execute pandas.read_sql_query
- 构建 SQL 字符串
- 设置参数值
- 执行pandas.read_sql_query
Build SQL
构建 SQL
First ensure ?placeholders are being set correctly. Use str.formatwith str.joinand lento dynamically fill in ?s based on member_listlength. Below examples assume 3 member_listelements.
首先确保?正确设置占位符。使用str.format与str.join和len?根据member_list长度动态填充s 。下面的示例假设有 3 个member_list元素。
Example
例子
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)
Returns
退货
select member_id, yearmonth
from queried_table
where yearmonth between ? and ?
and member_id in (?,?,?)
Set Parameter Values
设置参数值
Now ensure parameter values are organized into a flattuple
现在确保参数值被组织成一个平面元组
Example
例子
# 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)
Returns
退货
(201601, 201603, 1, 2, 3)
Execute
执行
Finally bring the sqland paramsvalues together in the read_sql_querycall
最后在调用中将sql和params值放在一起read_sql_query
query = pd.read_sql_query(sql, db2conn, params)
回答by Bluu
If you're using python 3.6+ you could also use a formatted string litteral for your query (cf https://docs.python.org/3/whatsnew/3.6.html#whatsnew36-pep498)
如果您使用的是 python 3.6+，您还可以为您的查询使用格式化的字符串（参见https://docs.python.org/3/whatsnew/3.6.html#whatsnew36-pep498）
start, end = 201601, 201603
selected_members = (111, 222, 333, 444, 555) # requires to be a tuple
query = f"""
SELECT member_id, yearmonth FROM queried_table
WHERE yearmonth BETWEEN {start} AND {end}
AND member_id IN {selected_members}
"""
df = pd.read_sql_query(query, db2conn)
