lists in cx_Oracle

[Daniel Dittmar]

Andrew Dalke wrote:
> I want to execute a query with an "IN" in the WHERE clause
> and with the parameter taken from a Python variable.  That
> is, I wanted something like this to work
> 
> id_list = ["AB001", "AB002", "AB003"]
> 
> c.execute("""SELECT s.smiles FROM smiles_database s WHERE """
>          """  s.id IN :id_list""", id_list = id_list)
> 
> I couldn't get it to work.  It complained
> 
>     arrays can only be bound to PL/SQL statements
> 

Possible workarounds:
- use executemany: a few databases allow to execute several sets of 
input parameters at once. And even fewer allow this for SELECTs, where 
the result is one cursor created from the UNION of a SELECt for each set 
of input parameters. Apart from being unlikely to work, this also 
requires that *all* input parameters are lists of the same length 
(unless the driver is smart enough to expand skalars to lists in this 
context)

- specify a maximum number of input parameters 's.id in (:id0, :id1, 
...)' and fill missing values with the first value

- create a class for this purpose. Statement are created on the fly, but 
with placeholders so you don't run into the SQL Injection problem. As 
it's an object, you could cache these generated statements base on the 
size of the list

- create a temporary table, insert all the values into that table 
(executemany is great for INSERTS) and then join with that table

You could also search comp.language.java.database where this is a 
frequent question.

It is unlikely that this can be solved at the driver level. Without 
support from the database, the driver would have to manipulate the SQL 
statement. And there are few predicates where a list parameter is 
useful. Expanding a list always yould lead to very bizarre error 
messages. Expanding them only where useful would require a SQL parser.

Daniel