Getting data from Oracle table where two columns equal separate lists-CodePudding

I am pulling data using python, from an ORACLE table. I can pull the rows I need for one of the lists using

office_list = [aaa, aab, aac, aad]

&& the actual list is much longer

"""
SELECT * 
  FROM (
        SELECT distinct(id) as id, 
               office, 
               cym,
               type
          FROM oracle1.table1
         WHERE office IN ({0})
        ) 
""".format("'"   "','".join([str(i) for i in office_list])   "'")

What I can't figure out is how to also include another filter from a different list.

In this case it is a list of types

type_list = [type1, type2, type3, type4]

Any help would be appreciated. thanks

CodePudding user response：

In cx_oracle, pass collections to the query. Using bind variables will help to prevent SQL injection attacks and will allow the SQL engine to re-use execution plans for different lists which will give greater performance (conversely, using string concatenation would make your code vulnerable to SQL injection attacks and not allow re-use of the execution plans).

In SQL:

CREATE OR REPLACE TYPE string_list AS TABLE OF VARCHAR2(25);
/

In Python:

list_type = connection.gettype("STRING_LIST")

office_list = list_type.newobject()
office_list.extend(["aaa", "aab", "aac", "aad"])

type_list = list_type.newobject()
type_list.extend(["xxx", "xxy", "xxz", "xyz"])

cursor.execute(
    """SELECT DISTINCT
           id,         -- DISTINCT is NOT a function.
           office, 
           cym,
           type
    FROM   oracle1.table1
    WHERE  office in (SELECT column_value FROM TABLE(:1))
    AND    type   in (SELECT column_value FROM TABLE(:2))""",
    [office_list, type_list],
)
for row in cursor:
    print(row)

You may be able to simplify the WHERE clause to:

WHERE  office MEMBER OF TABLE(:1)
AND    type   MEMBER OF TABLE(:2)

CodePudding user response：


"""select * from(
    select
                     distinct(id) as id, 
                     office, 
                     cym,
                     type
                     from oracle1.table1
                     where office in ({0})
                       and type in ({1})
                 ) 
""".format("'"   "','".join(office_list)   "'", "'"   "','".join(type_list)   "'")