Python Cx_Oracle select query binding a tupled list with WHERE IN clause

Due to Oracle’s 1000 “IN” clause limit, one workaround is to use list tuples. I am attempting this in Python but cant seem to bind the list tuple correctly.

My code:

mylist = [(1, item1), (1, item2), (1, item3) ... (1, item1500)]
format_strings = ','.join(':%d' % i for i in range(len(mylist)))
query = '''
        select x, y, z from table where (1, column_name) IN (%s)
        '''%format_strings
with myconnection as connection:
    cursor = connection.cursor()
    cursor.execute(query, mylist)
    result = cursor.fetchall()
    ...

This gives me error: cx_Oracle.NotSupportedError: Python value of type tuple not supported.

Whats the possible workaround for this error? Thanks

Answer

Your python is generating the following query string…

select x, y, z from table where (1, column_name) IN (:0,:1,:2,...,:n)

Then you’re trying to supply a list of tuples as the parameters.

Perhaps instead generate this string, and supply a simple list for the parameters…

select x, y, z from table where (1, column_name) IN ((1,:0),(1,:1),(1,:2),...,(1,:n))

For example…

mylist = [item1, item2, item3, ..., item1500]
format_strings = ','.join('(1,:%d)' % i for i in range(len(mylist)))
query = '''
        select x, y, z from table where (1, column_name) IN (%s)
        '''%format_strings
with myconnection as connection:
    cursor = connection.cursor()
    cursor.execute(query, mylist)
    result = cursor.fetchall()
    ...