Passing a list of values to Oracle using Python

Question

I'm trying to define a list of values (id numbers) and pass them to SQL that's querying Oracle tables.

This code works as long as I only include one id.

named_params = {'ids':'123456548'}
query = """
select PEBEMPL_ECLS_CODE 
from PEBEMPL
inner join SPRIDEN on spriden_pidm = pebempl_pidm 
where SPRIDEN_CHANGE_IND is null
and SPRIDEN_ID in :ids
"""
df = pd.read_sql(query, connection, params=named_params)

What's the correct syntax for doing the same but passing a list of ids?

This code, for example, doesn't work:

idlist = ['123456548','546465464']
named_params = {'ids':idlist}
query = """
select PEBEMPL_ECLS_CODE 
from PEBEMPL
inner join SPRIDEN on spriden_pidm = pebempl_pidm 
where SPRIDEN_CHANGE_IND is null
and SPRIDEN_ID in :ids
"""
df = pd.read_sql(query, connection, params=named_params)

Error Returned:

': ORA-01484: arrays can only be bound to PL/SQL statements

Will Keeling · Accepted Answer · 2018-05-26 08:11:40Z

9

You will need to create a separate bind parameter for each item in the list, and then pass the list of ids into read_sql():

idlist = ['123456548','546465464']

in_vars = ','.join(':%d' % i for i in range(len(idlist)))

query = """
select PEBEMPL_ECLS_CODE 
from PEBEMPL
inner join SPRIDEN on spriden_pidm = pebempl_pidm 
where SPRIDEN_CHANGE_IND is null
and SPRIDEN_ID in (%s)
""" % in_vars
df = pd.read_sql(query, connection, params=idlist)

To avoid any confusion, the above example talks to Oracle using Pandas read_sql() function. For people working with a cursor object directly, then the syntax is:

cursor.execute(query, params)

edited May 26, 2018 at 8:11

answered May 24, 2018 at 17:00

Will Keeling

23.1k4 gold badges58 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dread Over a year ago

I get the following error with this code: "ORA-01008: not all variables bound". Any suggestion?

Will Keeling Over a year ago

Sorry my mistake - the idlist should be passed as a keyword argument - as params=idlist. I have updated the answer.

Stef Over a year ago

just for completeness' sake: link to the relevant cx_oracle documenation

Bharath Bharath · Accepted Answer · 2018-05-24 14:33:40Z

0

I believe you will have to pass the parameters as string. May be join all the ID's you want to query with a comma and send it as a string to SPRIDEN_ID.

answered May 24, 2018 at 14:33

Bharath Bharath

451 gold badge1 silver badge10 bronze badges

Collectives™ on Stack Overflow

Passing a list of values to Oracle using Python

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related