Python CX_Oracle: How to use returned tuples in another query

Question

I am using cx_Oracle to query my databse with Python. I have a query that returns multiple results. In cx_Oracle using the .fetchall() command on a query that returns multiple results puts each row into a tuple, and then makes a list of tuples (1 for every returned row). After fetching the results of my query it is formatted like this:

[('R100',), ('R200',)]

I now want to use those results in another query. The next query is as follows:

base_query = "select MODEL from USER.TABLE where SERIES in :series"

where :series is a special parameter marker that can be substituted when you execute the query like this:

cursor.execute(base_query, series=[('R100',), ('R200',)])

When I attempt to set series to my list of tuples I get this error:

cx_Oracle.NotSupportedError: element 0 value is unsupported

I understand that this is probably a syntax issue since in raw SQL what I am trying to to is probably creating a query that reads like this:

base_query = "select MODEL from USER.TABLE where SERIES in [('R100',), ('R200',)]"

when what I actually want is this:

base_query = "select MODEL from USER.TABLE where SERIES in ('R100','R200')

I am having trouble making the parsed raw query look like the second example though because I am unsure as to how python data types are being interpreted (I am guessing my 1st example isn't even a correct interpretation of what the raw SQL looks like)

UPDATE: So I think you are supposed to be able to do this using by doing: cursor.executemany(base_query, [('R100',), ('R200',)]) But I am getting an error: cx_Oracle.DatabaseError: DPI-1013: not supported I am on cx_oracle Ver 7.0.0 trying to figure out what version my DB is now

Dhanya SJ · Accepted Answer · 2018-10-17 15:12:06Z

2

You can use this function to convert the format and then use it in your query.

    def to_tuple(first_output):
        series = []
        for py_tuple in first_output:
            series.append(py_tuple[0])
        return tuple(series)

    series = to_tuple(first_output) # Output : ('R100', 'R200')
    query = "select MODEL from USER.TABLE where SERIES in {}".format(series) 
    #Output:"select MODEL from USER.TABLE where SERIES in ('R100', 'R200')"

answered Oct 17, 2018 at 15:12

Dhanya SJ

976 bronze badges

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

4 Comments

Rosey Over a year ago

I thought that was going to work but apparently even though things are returned as tuples cx_Oracle doesn't want you to use those in a query. I tried using the output of that method: ('R100', 'R200') but got this error: Python value of type tuple not supported. setting the tuple to a string str(series) also did not work even though str(series) outputs as the exact string I thought I needed >>('R100', 'R200') I have no idea why this doesn't work because hard coding the entire query does: select MODEL from USER.TABLE where SERIES in ('R100', 'R200')

Rosey Over a year ago

Oh its probably because cx_Oracle is interpreting it as "('R100', 'R200')" not ("R100", "R200")

Dhanya SJ Over a year ago

I believe its better to modify your query to nested query. query = "select MODEL from USER.TABLE where SERIES IN ("Write your first query here that returns a series"). That way you can get rid of the extra formatting.

Dhanya SJ Over a year ago

As per the current idea, maybe Oracle is looking for something like this.. sorry I cannot run it as I dont have Oracle database. query = "select MODEL from USER.TABLE where SERIES in ('{}','{}')".format(series[0],series[1]). Not a good approach as the series can be of any length

Rosey · Accepted Answer · 2018-10-18 13:44:04Z

Got it. Posting here for anyone else doing this. Basically you have to dynamically generate n number of query params and convert your list of tuples to a list of strings.

# var series is what you will get from a query that returns multiple results
print(series)  # >> [('R100',), ('R200',)]
tuple_list_to_str_list = [str(i[0]) for i in results['series']]
print(tuple_list_to_str_list)  # >> ['R100', 'R200']
vars_list = ','.join(':%d' % i for i in range(len(results['series'])))
print(vars_list)  # Makes n number of query params >> :0,:1
base_query = "select MODEL from USER.TABLE where SERIES in (%s)" % vars_list  # Base query
print(base_query)  # select MODEL from USER.TABLE where SERIES in (:0,:1)
cursor.execute(base_query, tuple_list_to_str_list)

Collectives™ on Stack Overflow

Python CX_Oracle: How to use returned tuples in another query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related