Uploaded image for project: 'MariaDB Connector/Python'
  1. MariaDB Connector/Python
  2. CONPY-225

"Cursor.rowcount" and "Cursor.affected_rows" both return the same value, return 0 for UPDATE queries.

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 1.1.4
    • 1.1.5
    • DBAPI 2.0, Documentation
    • None
    • Windows 11, PyCharm IDE with a virtualenv. Connecting to an Ubuntu LTS server with MariaDB 1:10.7.3+maria~focal running in Docker.
    • 3.10.7

    Description

      ----------------
      		 
      Query: SELECT * FROM REDACTED.employees WHERE pin=%(auth)s
      		 
      cursor.affected_rows: 1
      		 
      cursor.rowcount: 1
      		 
      ----------------
      		 
      ----------------
      		 
      Query: SELECT employee FROM REDACTED.customer_relations WHERE incremental=%(id)s
      		 
      cursor.affected_rows: 1
      		 
      cursor.rowcount: 1
      		 
      ----------------
      		 
      ----------------
      		 
      Query: UPDATE REDACTED.customer_relations SET hidden=1 WHERE incremental=%(id)s
      		 
      cursor.affected_rows: 0
      		 
      cursor.rowcount: 0
      		 
      ----------------

      I have a class that handles my database connection and pooling, above is the output directly from an instance of that class. I'm very confused as to why 'rowcount' and 'affected_rows' both return the same result, and neither return the correct rows when running 'UPDATE' on a row. The above is also called before I return the connection back to the pool. At first, I figured it was just MariaDB ignoring the UPDATE because the row wouldn't change. No matter the state of the existing data, both 'affected_rows' and 'rowcount' both return 0.

      Edit: My code now randomly works, I apologize for any time I might've wasted. I do not see a clear way to close this issue or delete it. Thank you.

      Attachments

        Activity

          georg Georg Richter added a comment -

          As stated in PEP-249 you should use rowcount to determine the number of rows in a result set or the number of rows from a INSERT/UPDATE/DELETE statement.

          Affected_rows was added for compatibility with other drivers and needs to be fixed.

          For UPDATE/DELETE operations rowcount will return 0, if no row was affected, e.g.

          cursor.execute("UPDATE t1 SET a=3 WHERE a=3")

          You can change this behavior, by specifying client_flag when connecting to your server:

          from mariadb.constants import CLIENT
          		 
          connection= mariadb.connect(db="test", client_flag=CLIENT.FOUND_ROWS)

          georg Georg Richter added a comment - As stated in PEP-249 you should use rowcount to determine the number of rows in a result set or the number of rows from a INSERT/UPDATE/DELETE statement. Affected_rows was added for compatibility with other drivers and needs to be fixed. For UPDATE/DELETE operations rowcount will return 0, if no row was affected, e.g. cursor.execute( "UPDATE t1 SET a=3 WHERE a=3" ) You can change this behavior, by specifying client_flag when connecting to your server: from mariadb.constants import CLIENT connection= mariadb.connect(db= "test" , client_flag=CLIENT.FOUND_ROWS)

          People

            georg Georg Richter
            hlafaille Hunter LaFaille
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.