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
--
http://mail.python.org/mailman/listinfo/python-list
