Python cx_oracle bind variable with a list of items

Question

I have a query like this:

SELECT prodId, prod_name , prod_type FROM mytable WHERE prod_type in (:list_prod_names)

I want to get the information of a product, depending on the possible types are: "day", "week", "weekend", "month". Depending on the date it might be at least one of those option, or a combination of all of them.

This info (List type) is returned by the function prod_names(date_search)

I am using cx_oracle bindings with code like:

def get_prod_by_type(search_date :datetime):

  query_path = r'./queries/prod_by_name.sql'
  raw_query = open(query_path).read().strip().replace('\n', ' ').replace('\t', ' ').replace('  ', ' ')

  print(sql_read_op)
  # Depending on the date the product types may be different
  prod_names(search_date)  #This returns a list with possible names
  qry_params = {"list_prod_names": prod_names} # See attempts bellow
  try:
      db = DB(username='username', password='pss', hostname="localhost")
      df = db.get(raw_query,qry_params)
  except Exception:
      exception_error = traceback.format_exc()
      exception_error = 'Exception on DB.get_short_cov_op2() : %s\n%s' % exception_error
      print(exception_error)
  return df

For this: qry_params = {"list_prod_names": prod_names} I have tried multiple different things such as:

prod_names = ''.join(prod_names) 
prod_names = str(prod_names)
prod_names =." \'"+''.join(prod_names)+"\'"

The only thing I have managed to get it work is by doing:

new_query = raw_query.format(list_prod_names=prodnames_for_date(search_date)).replace('[', '').replace(']','')

df = db.query(new_query)

I am trying not to use .format() because is bad practie to do a .format to an sql to prevent attacks.

db.py contains among other functions:

def get(self, sql, params={}):
cur = self.con.cursor()
            cur.prepare(sql)
            try:
                cur.execute(sql, **params)
                df = pd.DataFrame(cur.fetchall(), columns=[c[0] for c in cur.description])
            except Exception:
                exception_error = traceback.format_exc()
                exception_error = 'Exception on DB.get() : %s\n%s' % exception_error
                print(exception_error)
                self.con.rollback()
            cur.close()
            df.columns = df.columns.map(lambda x: x.upper())
         return df

I would like to be able to do a type binding.

I am using:

• python = 3.6
• cx_oracle = 6.3.1

I have read the followig articles but I a still unable to find a solution:

• Python cx_Oracle bind variables
• Python cx_Oracle SQL with bind string variable
• Search for name in cx_Oracle

Trici

Answer 1

I have finally manage to do it. It might not be pretty but it works.

I have modified my sql query to include an extra select which returns the value of my list of descriptors:

inner join (
   SELECT regexp_substr(:my_list_of_items, '[^,]+', 1, LEVEL) as mylist
   FROM dual
   CONNECT BY LEVEL <= length(:my_list_of_items) - length(REPLACE(:my_list_of_items, ',', '')) + 1
) d
on d.mylist= a.corresponding_columns

Trici

Answer 2

Unfortunately you cannot bind an array directly unless you convert it to a SQL type and use a subquery -- which is fairly complex. So instead you need to do something like this:

inClauseParts = []
for i, inValue in enumerate(ARRAY_VALUE):
    argName = "arg_" + str(i + 1)
    inClauseParts.append(":" + argName)
clause = "%s in (%s)" % (columnName, ",".join(inClauseParts))

This works fine but be aware that if the number of elements in the array changes regularly that using this technique will create a separate statement that must be parsed for each number of elements. If you know that (in general) you won't have more than (for example) 10 elements in the array it would be better to append None to the incoming array so that the number of elements is always 10.

Hopefully that is clear enough!

Dorsal