executemany ('SELECT ...') (was: [DB-SIG] DBAPI-2.0 clarifications)

[M.-A. Lemburg]

"Dittmar, Daniel" wrote:
> 
> > > The point was: should the use of .nextset () be mandatory or is the
> > > driver/database allowed to put everything into one result set.
> >
> > Yes, please make .nextset() mandatory. We will need to clarify this
> > in the DB API spec. (still waiting for those patches ;-)
> 
> Just want to state my formal protest against this (mainly because I would
> have to treat INSERT/UPDATE/DELETE different from SELECT *before* executing,
> something I avoided so far).

It is really the only way to deal with this problem. Also, putting
all rows into one result set doesn't really make sense, because
in that case you could just as well use .execute() and a SELECT
with multiple WHERE conditions or a UNION of SELECTS.

An alternative would be outruling the use of SELECT with .executemany()
in the DB API spec altogether and declaring the interface behaviour
an implementation detail.

> > > What I would suggest:
> > >
> > > Add a chapter 'Implementation defined features' to the standard.
> > > - this would allow to be specific about being non specific
> > > - that's a place to add example code which would implement
> > a specific
> > > behaviour. For the .executemany ('SELECT ...'), it could be a cursor
> > > implementation which does .nextset () implicit, thus
> > imitating a single
> > > result set. Or an implementation of .executemany () which
> > does an .execute
> > > ('SELECT ..') with the next parameter set on a .nextset ().
> > This example
> > > code helps both driver implementors and driver users.
> > > - it gives a structure to the driver documentation, as the
> > writer would have
> > > to fill in the details for each implementation specific feature.
> >
> > Good idea. Would you be willing to write up a starter ?
> 
> OK, I'll try. Being fairly new to the DB API, I would appreciate if those
> with more experience could send me some topics that were portability issues
> in the past.

Great :-)

Here's a list:
- transaction behaviour (implicit start of transactions when creating
  a cursor, implicit rollback when cursor is closed)
- handling of multiple result returns using .nextset()
- handling of SELECT with .executemany()
- how to treat special database data types such as decimals
  and file column bindings

-- 
Marc-Andre Lemburg
______________________________________________________________________
Company & Consulting:                           http://www.egenix.com/
Python Pages:                           http://www.lemburg.com/python/