cx_Oracle Python List Querying Question

[DPolis]

Hey Everyone,

So, I'm trying to query a very large table and would like to limit the results using a python list of field values, which I can successfully execute using the cx_Oracle cursor.execute function, like so

import cx_Oracle
con = cx_Oracle('authentication string')
cur = con.cursor()

IDs = ('A1', 'A2', 'A3', 'A4')

query1 = """
SELECT * 
FROM table
WHERE ID IN """+str(IDs)

Q = cur.execute(query1)

However, if I attempt to join a table in the SQL statement I get the following error: ORA-00907: missing right parenthesis, like so:

query2 = """
SELECT * 
FROM (
SELECT t1.ID, t2.*
FROM table1 as t1, table2 as t2
WHERE t1.ID = t2.ID)
WHERE ID IN """+str(IDs)

Q = cur.execute(query2)

Plus, when I attempt to run the query without the nested subquery I get the following error: ORA-00933: SQL command not properly ended, like so

query3 = """
SELECT t1.new_ID as new_ID, t2.*
FROM table1 as t1, table2 as t2
WHERE t1.ID = t2.ID
AND t1.new_ID IN """ +str(IDs)

Q = cur.execute(query3)

I would appreciate any advice in this matter. I am new to using the cx_Oracle module. I assume that this is a result of user error, ie I'm coding something wrong. Thanks!

[JamesCrandall]

Can you wrap the code snippets into

 brackets?  Use the "#" tool in the posting toolset above.

import cx_Oracle

con = cx_Oracle('authentication string')
cur = con.cursor()
 
IDs = ('A1', 'A2', 'A3', 'A4')
 
query1 = """SELECT * FROM table WHERE ID IN """ + str(IDs)
 
Q = cur.execute(query1)


I'd first start by printing query1 to see the full query string that you will be passing in to build the cursor and hopefully you will be able to immediately see the problem.

Just as a quick test, you can try this version:

 
query1 = """SELECT * FROM table WHERE ID IN (""" + str(IDs) + """)"""
 

[DPolis]

Hello and thank you for replying. Actually, query1 works. However, the other two do not work. I assume it has something to do with the functionality of cursor.execute function, or the syntax of the code (SQL, or Python) I'm attempting to execute. I am in an Oracle 11g environment, if that helps at all.

[JamesCrandall]

Hello and thank you for replying. Actually, query1 works. However, the other two do not work. I assume it has something to do with the functionality of cursor.execute function, or the syntax of the code (SQL, or Python) I'm attempting to execute. I am in an Oracle 11g environment, if that helps at all.

Do those queries actually work in an actual query (outside of your python environment like SQL Developer)?

Edit: I don't think your SQL syntax is correct in query2 and query3 and has nothing to do with your cursor.  You should go into SQL Developer to re-write and test to make sure it is returning the correct information.  Once you have that completed then just copy/paste into your python script.

[DPolis]

Well, after much trial and error I figured out that the 'AS' table alias naming convention is simply not recognized in my current environment cx_Oracle version 5.1.2, Oracle 11.1, python 2.7.  Query 3 was the syntax I settled with as it was more straightforward, and didn't require a nested subquery. Thanks.

PS. Yes, a version of each query was tested in SQLPlus and they worked.