Re: executemany rowcount

Hi,

On Sat, Feb 07, 2009 at 06:39:57PM +0100, Federico Di Gregorio wrote:
> Il giorno ven, 06/02/2009 alle 12.00 +0100, Markus Demleitner ha
> scritto:
> > Hi,
> > 
> > At some point between 2.0.6 and 2.0.8 the old executemany/rowcount
> > behaviour of just leaving the rowcount of the last execute alone got
> > fixed to have executemany always leave a rowcount of -1.
[...]
> >    We want meaningful rowcounts for an executemany.  The rules are:
> >    The rowcount is the sum of all individual rowcounts, except when
> >    a single rowcount is -1.  In that case, and when an error occurs,
> >    the whole rowcount is -1.
> 
> Seems fine and it also passes the DBAPI tests. I integrated your patch
> and it is now in bzr. Many thanks.
Except when I was cycling home, I seemed to remember that rowcount
was also used in fetchXXX.  And sure enough, the program

import psycopg2

conn = psycopg2.connect("dbname=test")
curs = conn.cursor()
curs.execute("CREATE TEMP TABLE foo (x INTEGER)")
curs.executemany("INSERT INTO foo VALUES (%(x)s)", (
	{"x": i} for i in range(20)))
curs.executemany("SELECT * FROM foo WHERE x=%(x)s",
	[{"x":1}, {"x":2}])
print curs.fetchall()
print curs.rowcount

(assuming you have a database test) goes bad.  Its output is:

[(2,), (None,)]
2

Now, I'm happy that psycopg didn't segfault, but this certainly is
not desirable behaviour.

You could argue that people shouldn't use SELECT in executemany, but
that's kinda cheating.  pysqlite has special code to keep people from
doing that, though, so we wouldn't be the first cheaters.  And I
guess we can't do better than to give people the result of the last
query anyway, which is also not great.

A quick glance at the psycopg code reveals that a quick glance isn't
enough to figure out why it doesn't work that way (i.e., return the
result of the last query) already -- I thought the FETCH statement
should overwrite rowcount but then got lost in prefetch and friends.

Before I spend any more time on this: What do you think should be
done?  Forbid SELECT in executemany like pysqlite does?  Or return
the result of the last query?  Or still something else?  I'd
volunteer to try to come up with a patch if necessary (since the
executemany rowcount in the UPDATE case is quite important to me), but
I'm not sure what behaviour to implement.

Cheers,

        Markus