python - Passing a list of values to Oracle using Python
问题描述
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
解决方案
您需要为列表中的每个项目创建一个单独的绑定参数,然后将 id 列表传递给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)
为避免混淆,上面的示例使用 Pandasread_sql()
函数与 Oracle 对话。对于cursor
直接使用对象的人来说,语法是:
cursor.execute(query, params)