How do I pass multiple parameters via pd.read_sql, one singular and another in list format?

Question

I have raw data as:

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

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  
sparm['idnum'] = id_num  

and trying to use it as:

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

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

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

Answer 1

I have raw data as:

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

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 

and create a tuple for the where clause as:

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

and trying to use it as:

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

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

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