How to Count the Number of Rows in a MySQL Table in Python?

Counting the number of rows in a MySQL table is a common operation when working with databases. In Python, you can use the MySQL Connector module to establish a connection to a MySQL database and execute SQL queries to fetch data from tables. There are different ways to count the number of rows in a MySQL table using Python, and the method you choose will depend on the specific requirements of your project.

This article will guide you on how to obtain the count of rows in a particular MySQL table that resides in a database. To begin with, we will establish a connection to the database where the desired MySQL table is located.

To count the number of rows in a MySQL table in Python, you need to execute a SQL SELECT statement that fetches all the rows from the table.

Consider the SQL query shown below.

select * from <tableName>

Once you have the rows, you can use the rowcount property of the cursor object to determine the number of rows returned by the query. Alternatively, you can use the COUNT() function in SQL to directly count the number of rows in the table, and retrieve the result using Python.

In this article, we will first explore a single database named insillion in which we will have a table named bikes that contains 4 rows of data.

For reference, consider the queries shown below in a sequential order.

Selecting a Specific Database

use insillion

Once we have selected a specific database, we need to choose a specific table that we want to run our query on.

Consider the SQL command shown below.

select * from bikes;

Output

Once I ran the above command on my local MySQL, I got the following output.

mysql> select * from bikes;
+----+-------+-------+
| id | name  | price |
+----+-------+-------+
|  1 | Bajaj |  2543 |
|  2 | KTM   |  4789 |
|  3 | TVS   |  2790 |
|  4 | Hero  |  2100 |
+----+-------+-------+

From the above output, you can clearly see that there are 4 rows of data present in our bikes table.

Now let's focus on the python examples where we will use two different approaches to calculate the number of rows present in the bikes table.

Using pymysql and mysqldb

pymysql and mysqlclient are two popular Python libraries used for connecting to and interacting with MySQL databases.

• pymysql is a pure-Python implementation of the MySQL client-server protocol, which means it does not require any external libraries or dependencies.

• mysqlclient is a Python interface to the MySQL database that is based on the MySQL C API. It provides a faster and more efficient implementation than pymysql.

Consider the code shown below. This code imports the pymysql and MySQLdb modules to connect Python with a MySQL database, establishes a database connection using the specified credentials, retrieves a cursor object to interact with the database, executes a SQL query to get the number of rows in a table, and prints the number of rows.

# import required modules
import pymysql
pymysql.install_as_MySQLdb()
import MySQLdb

# connect python with mysql with your hostname,
# username, password and database
db= MySQLdb.connect(host='localhost', user='root', password='yourpassword'', database='insillion')

# get cursor object
cursor= db.cursor()

# get number of rows in a table and give your table
# name in the query
number_of_rows = cursor.execute("SELECT * FROM bikes")

# print the number of rows
print("Number of rows are:" ,number_of_rows)

To run the above code, we first need to install the pymysql and mysqlclient library in our machine, and for that we can make use of the command shown below.

pip3 install pymysql
pip3 install mysqlclient

Output

Now, on executing the code, you will get the following output:

Number of rows are: 4

Using mysql-connector

mysql-connector-python is a Python library that provides an interface for connecting to and interacting with MySQL databases. It is a pure-Python implementation of the MySQL client-server protocol, which means it does not require any external libraries or dependencies.

Consider the code shown below.

import mysql.connector

# Establish a connection to the MySQL database
with mysql.connector.connect(
    host="localhost",
    user="root",
    password="domain@immukul",
    database="insillion"
) as conn:

   # Create a cursor object to interact with the database
   with conn.cursor() as cursor:
        
   # Execute a SQL query that counts the number of rows in the table
   cursor.execute("SELECT COUNT(*) FROM bikes")
        
   # Get the result of the query
   result = cursor.fetchone()
        
   # The result is a tuple with one element, which contains the count
   row_count = result[0]
        
   print("Number of rows in the table:", row_count)

To run the above code, we first need to install the mysql-connector-python and mysqlclient library in our machine, and for that we can make use of the command shown below.

pip3 install mysql-connector-python

Output

On execution, you will get the following output:

Number of rows are: 4

Conclusion

In conclusion, counting the number of rows in a MySQL table in Python is a simple task that can be accomplished using various methods. The three main approaches are using the rowcount() method, executing a SQL query to retrieve the row count, and using the SELECT COUNT(*) statement.

Python libraries such as pymysql, mysqlclient, and mysql-connector-python can be used to connect to MySQL databases and perform row counting operations.