如何将数据帧作为参数传递给Python中的SQL查询? [英] How to pass a data frame as parameter to a SQL query in Python?
本文介绍了如何将数据帧作为参数传递给Python中的SQL查询?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个由一列值组成的数据框,我想将其作为参数传递来执行以下sql查询:
I have a dataframe that consists of one column of values and I want to pass it as a parameter to execute the following sql query:
query = "SELECT ValueDate, Value"\
"FROM Table "\
"WHERE [ID] in ( ? ) "
因此,我尝试了很多其他事情:
So I tried (among so many other things) the following:
df = pd.read_sql_query(query, conn, params=[ df['ID'].values ])
df = pd.read_sql_query(query, conn, params=[ df['ID'].tolist ])
df = pd.read_sql_query(query, conn, params=[ list(df['ID'].values) ])
...
传递数据帧值的正确方法是什么?
What is the correct way to pass the dataframe values ?
注意:我使用的是Microsoft SQL Server,因此查询格式需要与我一样设置.
NB: I am using Microsoft SQL Server so the query needs to be formatted as I did.
推荐答案
这能满足您的需求吗?
Does this get you what you need?
import pandas as pd
your_column = pd.Series([1,2,3,4,5,6,7,8,9])
query = "SELECT ValueDate, Value"\
"FROM Table "\
"WHERE [ID] in {}".format(tuple(your_column))
print(query)
# 'SELECT ValueDate, ValueFROM Table WHERE [ID] in (1, 2, 3, 4, 5, 6, 7, 8, 9)'
然后,您应该可以在不使用其他参数的情况下进行查询.
Then you should be able to query without further parameters.
df = pd.read_sql_query(query, conn)
这篇关于如何将数据帧作为参数传递给Python中的SQL查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文