zen562

cx_Oracle Python List Querying Question

Discussion created by zen562 on Jun 16, 2014
Latest reply on Jun 17, 2014 by zen562
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!

Outcomes