0

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.

flying_fluid_four
  • 704
  • 1
  • 6
  • 14
  • Ok, I changed the sparm['idnum'] = id_num to sparm['idnum'] = list(id_num.split(",")) to convert it into a list. Now, I get an Oracle ERROR: ORA-01484: arrays can only be bound to PL/SQL statement. – flying_fluid_four Aug 07 '19 at 18:13
  • Possible duplicate of [How do I pass a list as a parameter in a stored procedure?](https://stackoverflow.com/questions/42448596/how-do-i-pass-a-list-as-a-parameter-in-a-stored-procedure) – Trenton McKinney Aug 07 '19 at 18:29

1 Answers1

0

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.

flying_fluid_four
  • 704
  • 1
  • 6
  • 14