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

[Bob Kline]

On Mon, 6 Aug 2001, Andy Dustman wrote:

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

I certainly don't expect my client library to have anything to do with
interpreting the Python code.  That's the task of the driver
implementation, to translate this statement into code which the client
library for the DBMS understands.  In this case the API spec says that,
absent an appropriate array operation, the driver is supposed to invoke
the action identified in the first argument to executemany once for each
of the sets of parameters supplied in the second argument.

What I am trying to say (and so are you, it appears) is that such
behavior doesn't make sense if the SQL behind that action contains
SELECT statements.  What's more, the API should say so, since - as you
point out - we know that the driver code will break under such a
condition.

> So... don't use executemany() in this case. INSERT is the only SQL
> statement I have ever used with executemany(), AFAIK.

This is good advice, and sounds like an informal way of presenting what
I would like the API to say.  What am I missing here?  Has the spec for
this API been handed to the SIG by some testy deity who would be
offended by suggestions for improvement?  What's so terrible about
having the language of the spec amended to identify those conditions for
which use of this method constitutes undefined behavior, subject to
having an exception raised?

Is there a formal process that I should be following for submitting
proposed amendments to the spec?  If so, I'll be happy to use it.  Just
let me know.

-- 
Bob Kline
mailto:bkline@rksystems.com
http://www.rksystems.com