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

M.-A. Lemburg mal@lemburg.com
Wed, 21 Mar 2001 13:02:18 +0100

"Dittmar, Daniel" wrote:
> > Like I said before, using .executemany() with SELECT is not
> > a good practice.
> Agreed in principle. Of course there are rare exceptions.
> > You lose too much control over what the
> > DB does with the selects and errors will be not contain enough
> > information to allow you to pinpoint the SELECT parameter
> > set which failed.
> >
> > .executemany() was intended to be used with INSERT and UPDATE
> > where many DBs offer array processing to speed up the process.
> Array INSERT and UPDATE have their problems too with regard to portability.
> When one parameter set fails, what should be the outcome
> - failure of the whole command?
> - execute succeeds up to the offending parameters?
> - should this be different for 'autocommit'?
> I guess each database will have one of these as the 'natural'
> implementation. Each database will have a hard time to implement the other
> one, possibly at the cost of not using the native capabilities, thus defying
> the whole purpose of array commands.

This is database defined. I guess most databases simply stop with
the execution in case an INSERT fails and leave the database
in a half-changed state. .rollback() then is the right thing
to do.
The DB API spec should have a note about this though, since
is could cause data damage.

> > For the SELECT case, please stick with separate calls to .execute().
> > Performance is really a non-issue here and keeping the SELECTs
> > separated helps a lot in debugging code and making the overall
> > experience clearer to the reader of the source code.
> >
> This is advice for those using the DB standard, not those implementing it.

> > If you really have to use SELECTs in .executemany() then the
> > current definition of .nextset() will allow you to switch
> > to the next result set in the queue, so you can still get
> > at the different result sets.
> 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 ;-)
> > It does not make any sense to
> > return the union of the result sets (since the same could be
> > achieved with a single SELECT and multiple WHERE conditio
> But array parameters allow you to prepare it once and execute it with a
> variable number of input parameters.

If you use the statement caching mechanism proposed in the DB API
spec then you won't lose any performance due to having to
re-prepare the command every time.
> 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 ?

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