如何通过pd.read_sql传递多个参数，一个以单数形式传递，另一个以列表格式传递? [英] How do I pass multiple parameters via pd.read_sql, one singular and another in list format?

问题描述

我的原始数据如下:

id = 2345  
id_num = 3,6,343,32  

我需要通过cx_Oracle连接将以上两个参数作为ORACLE SQL查询中的参数进行传递:

I need to pass both the above as parameters in an ORACLE SQL query via a cx_Oracle connection as:

query = """  
        select * from mytable where pid = 2345 and id_num in (3,6,343,32)  
        """  

我创建的字典为:

sparm = {}  
sparm['pid'] = id  
sparm['idnum'] = id_num  

并尝试将其用作:

query = """  
        select * from mytable where pid = :pid and id_num in :idnum  
        """  

df = pd.read_sql(query, con=conct, params=sparm)  

没有成功.
:pid有效，但:idnum无效.任何建议将不胜感激.

without success.
The :pid works but the :idnum doesn’t. Any suggestions would be much appreciated.

推荐答案

我的原始数据为:

id = 2345  
id_num = 3,6,343,32  

我需要通过cx_Oracle连接将以上两个参数作为ORACLE SQL查询中的参数进行传递:

I need to pass both the above as parameters in an ORACLE SQL query via a cx_Oracle connection as:

query = """  
        select * from mytable where pid = 2345 and id_num in (3,6,343,32)  
        """  

我正在创建一个字典，为:

I am creating a dictionary as:

sparm = {}  
sparm['pid'] = id 

并为where子句创建一个元组为:

and create a tuple for the where clause as:

where=tuple(list(id_num.split(","))) 

并尝试将其用作:

query = """  
        select * from mytable where pid = :pid and id_num in {}  
        """.format(where)

df = pd.read_sql(query, con=conct, params=sparm)  

成功.:pid与dict输入配合使用，:idnum与元组输入配合使用.

with success. The :pid works with a dict input and the :idnum works as a tuple input.

这篇关于如何通过pd.read_sql传递多个参数，一个以单数形式传递，另一个以列表格式传递?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！