DBI cursor behaviour with multiple statements?

[Gerhard Häring]

* Mark Charsley <mark.charsley at REMOVE_THIS.radioscape.com> [2002-10-01 11:32 +0100]:
> paul at boddie.net (Paul Boddie) wrote:
> 
> > An interesting, related issue is the treatment of the IN operator, and
> > this doesn't necessarily yield uniform treatment across database
> > modules, even for the same database system (compare pyPgSQL and
> > psycopg on PostgreSQL). For some applications of your query (where you
> > want the union), the IN operator is probably what is desired:
> > 
> >   "SELECT * FROM table WHERE column IN %s"
> > 
> > This appears highly counter-intuitive, of course, since the
> > Python-style %s parameter marker actually represents a list in this
> > case - another reason for choosing a better parameter notation.
> 
> Does that work?

It will in pyPgSQL 3.0. In the meantime, you need a workaround because
pyPgSQL thinks that if you use a list, you want to quote it as a
PostgreSQL ARRAY. The solution will be to introduce a new PgArray type
for ARRAYs in 3.0.

> I've never been able to pass in a list of things like that 
> in either ODBC or ADO from C++. Which means:
> a) the python library isn't cacheing the execution plan, and is just 
> building a new building a new SQL statement with each call (which is going 
> to have serious consequences), or 

Yep. But IMO the performance consequences aren't _that_ serious,
especially if the RDBMS doesn't support prepared statements, anyway ;-)
PostgreSQL btw. will support prepared statements in 7.3 and it's working
nicely with the beta I've tried them on. OTOH the prepared statement
syntax is such that you can hardly apply them automatically (because you
also need to provide the _types_ of the parameters).

Anyway, there _are_ Python database adapters that can automatically use
prepared statements instead and thus do it.  mxODBC, for example.

> b) it's possible to make DB calls from python that can't be made from C++, 
> or
> c) I've been an idiot

-- Gerhard