[DB-SIG] definition of .rowcount

[M.-A. Lemburg]

Michael Bayer wrote:
> 
> On Jun 13, 2012, at 6:14 PM, Peter Eisentraut wrote:
> 
>> On tis, 2012-06-12 at 10:07 -0400, Michael Bayer wrote:
>>> So as many of you know, MySQL has actually two options for how
>>> rowcount can be reported.  It can report the number of rows "found",
>>> that is, matched by the WHERE criterion of an UPDATE or DELETE
>>> statement, or it can report the number of rows that were actually
>>> updated or deleted, and here we're talking about an UPDATE that may or
>>> may not have had a net change in row value.   The Python MySQL drivers
>>> tend to default to the latter.    Every other database/DBAPI I've
>>> worked with only offers the former.
>>
>> Even if the value that is stored before and after an update is logically
>> the same, this could fire triggers, so the row is still "affected".
>> Also, some data types aren't comparable, so you can't always know this.
>> So I think the problem with the alternative behavior of MySQL is that it
>> isn't even generally definable, so it can't possibly be the correct
>> answer for a general interface.
>>
>> There is also the question of how this definition for the UPDATE command
>> extends to other commands.  It probably doesn't, in a sensible way
>> (well, maybe for REPLACE/INSERT ON DUPLICATE KEY UPDATE, but then you
>> might get a second-level inconsistency between the regular UPDATE and
>> the UPDATE run as part of the INSERT).
> 
> It goes without saying that this is one of a myriad number of areas where MySQL gets it wrong. 
> 
>> I agree that the terms could be clarified, nevertheless.
> 
> Yup, I'd leave the word "affected" in, then just add a note that this can mean different things depending on backend.

I'll add a footnote next week, mentioning the possible different
interpretation.

BTW: I don't think the MySQL interpretation is particularly
useful, since you typically use .rowcount to check whether the
UPDATE actually found any rows. Even an update that doesn't
change the current row values is an explicit update and should
be counted. If you really don't want to count such rows, it's
well possible to add a check the WHERE clause of the UPDATE to
prevent (and not count) such updates.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source
>>> 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/