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

[Dittmar, Daniel]

> 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.

> 
> 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.

> 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.

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.

Daniel

--
Daniel Dittmar
daniel.dittmar@sap.com
SAP DB, SAP Labs Berlin
http://www.sapdb.org/