rowcount : Number of rows affected by SELECT statement or by UPDATE INSERT DELETE in Python MySQL

We can get number of rows affected by the query by using rowcount. We will use one SELECT query here. ( What is a SELECT query ? )
We defined my_cursor as connection object.
Here is the code to create connection object

import mysql.connector

my_connect = mysql.connector.connect(
  host="localhost",
  user="userid",
  passwd="password",
  database="database_name"
)
####### end of connection ####
my_cursor = my_connect.cursor()

Using my_cusor we will manage our database.

my_cursor = my_connect.cursor(buffered=True) # my_connect is the connection 
my_cursor.execute("SELECT * FROM  student Where class='Five'")
print("Rows returned = ",my_cursor.rowcount)

Output is here

Rows returned =  11

buffered=True

We have used my_cursor as buffered cursor.

my_cursor = my_connect.cursor(buffered=True)

This type cursor fetches rows and buffers them after getting output from MySQL database. We can use such cursor as iterator. There is no point in using buffered cursor for single fetching of rows.

If we don’t use buffered cursor then we will get -1 as output from rowcount

my_cursor = my_connect.cursor(buffered=False) # my_connect is the connection 
my_cursor.execute("SELECT * FROM  student Where class='Five'")
print("Rows returned = ",my_cursor.rowcount)

Output

Rows returned =  -1

Here is an update query to change the records. ( What is an UPDATE query ?)

my_cursor = my_connect.cursor() #
my_cursor.execute("UPDATE student SET class='Five' Where class='Four'")
my_connect.commit()
print("Rows updated = ",my_cursor.rowcount)

Output is here

Rows updated = 9

Let us use one DELETE query ( What is a DELETE Query ? )

my_cursor = my_connect.cursor() # Cursor 
my_cursor.execute("DELETE FROM student Where class='Five'")
print("Rows Deleted = ",my_cursor.rowcount)
my_connect.commit()

Output is here

Rows Deleted = 11

By using INSERT query ( What is an INSERT Query ? )

my_cursor = my_connect.cursor() # Cursor 
my_cursor.execute("INSERT INTO  `my_tutorial`.`student` (`id` ,`name` ,`class` ,`mark` ,`sex`) \
                  VALUES ('36',  'King',  'Five',  '45',  'male')")
print("Rows Added  = ",my_cursor.rowcount)
my_connect.commit()

Output is here

Rows Added  = 1

MySQL affected rows

Python functions for MySQL Collect records Python- Tutorials

Subscribe to our YouTube Channel here

rowcount : Rows affected by Query

buffered=True

Subscribe