[DB-SIG] definition of .rowcount

M.-A. Lemburg mal at egenix.com
Mon Jun 25 17:15:28 CEST 2012


M.-A. Lemburg wrote:
> 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.

Done. The web site should update in a few hours.

Here's the new footnote:

        .rowcount

            This read-only attribute specifies the number of rows that
            the last .execute*() produced (for DQL statements like
            'select') or affected (for DML statements like 'update' or
            'insert'). [9]

            ...

    [9] The term "number of affected rows" generally refers to the
        number of rows deleted, updated or inserted by the last
        statement run on the database cursor. Most databases will
        return the total number of rows that were found by the
        corresponding WHERE clause of the statement. Some databases
        use a different interpretation for UPDATEs and only return the
        number of rows that were changed by the UPDATE, even though
        the WHERE clause of the statement may have found more matching
        rows. Database module authors should try to implement the more
        common interpretation of returning the total number of rows
        found by the WHERE clause, or clearly document a different
        interpretation of the rowcount attribute.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jun 25 2012)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2012-07-17: Python Meeting Duesseldorf ...                 22 days to go
2012-07-02: EuroPython 2012, Florence, Italy ...            7 days to go

::: 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/


More information about the DB-SIG mailing list