[DB-SIG] Cursor.executemany() and Cursor.nextset()

Andy Dustman andy@dustman.net
Mon, 6 Aug 2001 19:20:51 -0400 (EDT)

On Mon, 6 Aug 2001, Bob Kline wrote:

> Imagine a stored procedure p which takes two parameters and returns a
> single result set.  Client code invokes the stored procedure using the
> executemany() method on a cursor object:
>     c = conn.cursor()
>     c.executemany("EXEC p ?,?", [[40,75], [100,125]])
> Now, what does the driver code do?

It breaks. executemany() is mostly for doing multi-row INSERTs, i.e.

c.executemany("INSERT INTO foo (spam,eggs) VALUES (%s,%s)", \
              [(40,75), (100,125)]) # params for MySQLdb

Despite having two rows of data, only one INSERT (of two rows) is
performed. (Or at least, this is the general idea. Likely, not all
database client libraries are capable of this; they may perform two
separate INSERTs instead.) Your EXEC statement does not have a repetitive
set of parameters, so it's behavior is undefined. Mostly likely your
client library would not know what to do with this either, since it is
being passed two sequences. So... don't use executemany() in this case.
INSERT is the only SQL statement I have ever used with executemany(),

Andy Dustman         PGP: 0xC72F3F1D
    @       .net     http://dustman.net/andy
I'll give spammers one bite of the apple, but they'll
have to guess which bite has the razor blade in it.