PEP 249 (database api) -- executemany() with iterable?

[M.-A. Lemburg]

Terry Reedy wrote:
> On 10/12/2010 11:10 AM, Roy Smith wrote:
>> PEP 249 says about executemany():
>>
>>          Prepare a database operation (query or command) and then
>>          execute it against all parameter sequences or mappings
>>          found in the sequence seq_of_parameters.
>>
>> are there any plans to update the api to allow an iterable instead of
>> a sequence?
> 
> That question would best be addressed to the pep author
> Marc-André Lemburg <mal at lemburg.com>

Questions about the DB-API should be discussed on the Python DB-SIG
list (put on CC):

http://mail.python.org/mailman/listinfo/db-sig

Regarding your question:

At the time the PEP was written, Python did not have iterables.

However, even with iterables, please keep in mind that pushing
the data row-per-row over a network does not result in good
performance, so using an iterable will make you update slower.

cursor.executemany() is meant to allow the database module
to optimize sending bulk data to the database and ideally,
it will send the whole sequence to the database in one go.

If you want to efficiently run an update with millions of
entries based on an iterable, it is better to use an intermediate
loop which builds sequences of say 1000 rows and then processes
those with a cursor.executemany() call.

You will likely also do this in multiple transactions to
prevent the database from creating a multi-GB transaction log
for the upload.

Another aspect to keep in mind is error reporting. When sending
bulk data to a database, some databases only report "error"
for the whole data block, so finding the problem can be
troublesome. For that reason, using smaller blocks is better
even when having the data available as real sequence.

Hope that helps,
-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Oct 14 2010)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/