Python MySQL cursor.rowcount: Rows Returned or Affected

The MySQL Connector/Python cursor rowcount property reports the number of rows returned by a SELECT statement or affected by data-changing statements such as INSERT, UPDATE and DELETE.

count=my_cursor.rowcount

Create MySQL Connection and Cursor 🔝

First create a MySQL connection using mysql.connector.

import mysql.connector

my_connect=mysql.connector.connect(
    host='localhost',
    user='userid',
    password='password',
    database='database_name'
)

my_cursor=my_connect.cursor()

The cursor executes SQL statements and provides properties such as rowcount.

SELECT rowcount with buffered=True 🔝

A buffered cursor fetches all rows returned by the server after the statement is executed.

my_cursor=my_connect.cursor(buffered=True)

sql='SELECT * FROM student WHERE class=%s'
my_cursor.execute(sql, ('Five',))

print('Rows returned =', my_cursor.rowcount)

Rows returned = 11

The actual number depends on the data in the table.

Because the complete result has already been buffered, rowcount contains the total number of selected rows immediately after execute().

Fetch the Buffered Rows

rows=my_cursor.fetchall()

for row in rows:
    print(row)

SELECT rowcount with a Non-Buffered Cursor 🔝

The default cursor is normally non-buffered.

my_cursor=my_connect.cursor()

my_cursor.execute(
    'SELECT * FROM student WHERE class=%s',
    ('Five',)
)

print(my_cursor.rowcount)

-1

This does not mean that the query returned no rows. It means the non-buffered cursor has not yet fetched enough rows to know the complete result count.

rowcount Increases as Rows Are Fetched 🔝

With a non-buffered cursor, rowcount changes as records are retrieved.

my_cursor=my_connect.cursor()
my_cursor.execute('SELECT * FROM student WHERE class=%s', ('Five',))

print('Before fetching:', my_cursor.rowcount)

row=my_cursor.fetchone()
print('After fetchone():', my_cursor.rowcount)

remaining=my_cursor.fetchall()
print('After fetchall():', my_cursor.rowcount)

Before fetching: -1
After fetchone(): 1
After fetchall(): 11

The cursor starts with an unknown row count and updates it as rows are fetched.

Iterating over the Cursor

for row in my_cursor:
    print(row)

print('Rows fetched =', my_cursor.rowcount)

After all returned rows are consumed, rowcount reflects how many records were fetched.

cursor.rowcount vs SELECT COUNT(*) 🔝

If the application only needs to know how many database rows match a condition, use SQL COUNT(*) instead of fetching every record.

sql='SELECT COUNT(*) FROM student WHERE class=%s'
my_cursor.execute(sql, ('Five',))

count=my_cursor.fetchone()[0]
print('Students in class Five =', count)

Students in class Five = 11

rowcount after INSERT 🔝

For an INSERT statement, rowcount reports the number of rows inserted by the operation.

sql='''INSERT INTO student
       (id, name, class, mark, sex)
       VALUES (%s, %s, %s, %s, %s)'''

data=(36, 'King', 'Five', 45, 'male')

my_cursor.execute(sql, data)

print('Rows added =', my_cursor.rowcount)

my_connect.commit()

Rows added = 1

rowcount after UPDATE 🔝

For an UPDATE statement, rowcount reports the rows affected by the statement.

sql='UPDATE student SET class=%s WHERE class=%s'

my_cursor.execute(sql, ('Five', 'Four'))

print('Rows updated =', my_cursor.rowcount)

my_connect.commit()

Rows updated = 9

The result depends on the current contents of the table.

rowcount after DELETE 🔝

For a DELETE statement, rowcount reports the number of deleted rows.

sql='DELETE FROM student WHERE class=%s'

my_cursor.execute(sql, ('Five',))

print('Rows deleted =', my_cursor.rowcount)

my_connect.commit()

Rows deleted = 11

Use Parameterized MySQL Queries 🔝

Values should normally be passed separately from the SQL statement rather than concatenated into the query string.

Use:

sql='SELECT * FROM student WHERE class=%s'
my_cursor.execute(sql, ('Five',))

The comma is required because a one-item Python tuple is written as:

('Five',)

Avoid constructing queries like:

# Do not build SQL from untrusted input this way
sql="SELECT * FROM student WHERE class='" + user_value + "'"

Parameterized execution lets MySQL Connector/Python safely bind values to placeholders.

rowcount and commit() 🔝

INSERT, UPDATE and DELETE commonly require commit() when transaction autocommit is not enabled.

my_cursor.execute(sql, data)

print(my_cursor.rowcount)

my_connect.commit()

rowcount describes the result of the executed statement. commit() makes the transaction permanent.

If the transaction is rolled back later, seeing a positive rowcount does not mean that the change was permanently stored.

Close Cursor and Connection

After completing the database work:

my_cursor.close()
my_connect.close()

Important rowcount Behaviour 🔝

Summary of MySQL cursor.rowcount 🔝

• cursor.rowcount is a read-only cursor property.
• For a buffered SELECT, it gives the number of returned rows immediately after execution.
• A buffered cursor fetches the complete result set into the client.
• For a non-buffered SELECT, rowcount initially returns -1.
• The non-buffered row count increases as records are fetched.
• After all rows are fetched, it represents the number of rows retrieved.
• If only the number of matching database records is needed, prefer SQL COUNT(*).
• For INSERT, rowcount reports rows inserted.
• For UPDATE, it reports affected rows according to MySQL's affected-row behaviour.
• For DELETE, it reports deleted rows.
• Use parameterized queries instead of concatenating user values into SQL.
• Use commit() to make data-changing transactions permanent when autocommit is disabled.
• A positive rowcount does not itself mean a transaction was committed.
• Close the cursor and connection when database work is complete.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: