Passing python list oracle where clause cx_Oracle

Question

I'm frustrated trying to pass a Python list to an Oracle WHERE clause. I'm using cx_Oracle, here's my code:

# -*- coding: utf-8 -*-
from __future__ import unicode_literals
import cx_Oracle

con = cx_Oracle.connect(str('user/passwordr@server/orcl'))
cursor = con.cursor()
ids = [19 , 87 , 84]
cursor.execute(str("select  employee_id , first_name , last_name from employees  where employee_id in ('"+ids+"')" ))
people = cursor.fetchall()
print people

'''The following code works for me , but the problem is the string formater placeholer is not gonna be static is dynamic.'''
params = (198 , 199)
cursor.execute(str("select  employee_id , first_name , last_name from employees  where employee_id in ('%s' , '%s')" %(params)))

'''Also it would be valid if i can create dynamically the string formater placeholder depending on "length of something".
Sorry if this question was answered i spend hours searching the solution , but i do not found it.'''

Hustels · Accepted Answer · 2016-07-01 22:30:16Z

2

After hours trying to figure out how to do it , finally i get the solution. here is the code:

# -*- coding: utf-8 -*-
#from __future__ import unicode_literals
import cx_Oracle
con = cx_Oracle.connect(str('user/pass@server/orcl'))

cursor = con.cursor()

cursor.execute(str('select employee_id from employees where rownum < 3 '))

desc = [d[0] for d in cursor.description]
resutl = [dict(zip(desc,line)) for line in cursor]

ids = []
for i in range(len(resutl)):
    ids.append(resutl[i]['EMPLOYEE_ID'])

placeholders = ','.join(":x%d" % i for i,_ in enumerate(ids)) 

sql = """SELECT job_id
         FROM job_history
         WHERE employee_id IN (%s)""" % placeholders 

cursor.execute(sql,ids ) 
rs =  cursor.fetchall() 

print rs

answered Jul 1, 2016 at 22:30

Hustels

672 silver badges7 bronze badges

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

1 Comment

Christopher Jones Over a year ago

Various solutions, including those for very large numbers of placeholders, are discussed in the driver documentation Binding Multiple Values to a SQL WHERE IN Clause.

Collectives™ on Stack Overflow

Passing python list oracle where clause cx_Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related