I'd like to use the IN clause with a prepared Oracle statement using cx_Oracle in Python.

E.g. query - select name from employee where id in ('101', '102', '103')

On python side, I have a list [101, 102, 103] which I converted to a string like this ('101', '102', '103') and used the following code in python -

import cx_Oracle

ids = [101, 102, 103]

ALL_IDS = "('{0}')".format("','".join(map(str, ids)))

conn = cx_Oracle.connect('username', 'pass', 'schema')

cursor = conn.cursor()

results = cursor.execute('select name from employee where id in :id_list', id_list=ALL_IDS)

names = [x[0] for x in cursor.description]

rows = results.fetchall()

This doesn't work. Am I doing something wrong?

解决方案

This concept is not supported by Oracle -- and you are definitely not the first person to try this approach either! You must either:

create separate bind variables for each in value -- something that is fairly easy and straightforward to do in Python

create a subquery using the cast operator on Oracle types as is shown in this post: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:210612357425

use a stored procedure to accept the array and perform multiple queries directly within PL/SQL

or do something else entirely!