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

M.-A. Lemburg mal@lemburg.com
Wed, 21 Mar 2001 11:28:37 +0100

Federico Di Gregorio wrote:
> [using .executemany() with SELECT statements]
> > This is of course more efficient than sending three SELECTs and fetching and
> > closing three result sets.
> agreed.


Like I said before, using .executemany() with SELECT is not
a good practice. 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.

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.

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

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