DBI cursor behaviour with multiple statements?

[Paul Boddie]

Henrik.Weber at sys.aok.de (Henrik Weber) wrote in message news:<7e964d1d.0209270137.568a3f3 at posting.google.com>...
> Hello,
> 
> I'm trying to write a DBI2 compliant module for Microsoft Access
> databases. Now I'm not quite sure how to interpret the DBI2
> specification when it comes to the execution of multiple data
> returning statements with the same cursor.
> 
> For example if c is my cursor and I do:
> c.executemany("SELECT * FROM table WHERE column = '%s'", [["parm1"],
> ["parm2"], ["parm3"]])

Note that you probably don't want the quoting inside the SQL
statement. In other words, it should be...

  "SELECT * FROM table WHERE column = %s"

Personally, I'd recommend supporting different parameter notations,
since this issue always confuses people.

> should the result be the union of all three statements or just the
> result of the first or the last statement? Or should the user switch
> from one resultset to the next with nextset?

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.

Paul