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 MySQL connector libraries to establish a connection and execute SQL queries. This article demonstrates two effective approaches: using the cursor's execute() method and the SQL COUNT(*) function.

Database Setup

For our examples, we'll use a database named insillion with a table called bikes containing the following data:

mysql> SELECT * FROM bikes;
+----+-------+-------+
| id | name  | price |
+----+-------+-------+
|  1 | Bajaj |  2543 |
|  2 | KTM   |  4789 |
|  3 | TVS   |  2790 |
|  4 | Hero  |  2100 |
+----+-------+-------+
4 rows in set

Method 1: Using PyMySQL with rowcount

The first approach uses PyMySQL to execute a SELECT * query and returns the number of affected rows:

import pymysql

# Connect to MySQL database
connection = pymysql.connect(
    host='localhost',
    user='root',
    password='yourpassword',
    database='insillion'
)

try:
    with connection.cursor() as cursor:
        # Execute SELECT query and get row count
        row_count = cursor.execute("SELECT * FROM bikes")
        print("Number of rows:", row_count)
        
finally:
    connection.close()

Install PyMySQL using:

pip install pymysql

Output:

Number of rows: 4

Method 2: Using mysql-connector-python with COUNT(*)

The second approach uses the SQL COUNT(*) function, which is more efficient for large tables as it doesn't retrieve actual data:

import mysql.connector

# Establish connection using context manager
try:
    with mysql.connector.connect(
        host="localhost",
        user="root",
        password="yourpassword",
        database="insillion"
    ) as connection:
        
        with connection.cursor() as cursor:
            # Execute COUNT query
            cursor.execute("SELECT COUNT(*) FROM bikes")
            
            # Fetch the result
            result = cursor.fetchone()
            row_count = result[0]
            
            print("Number of rows in the table:", row_count)
            
except mysql.connector.Error as err:
    print(f"Error: {err}")

Install mysql-connector-python using:

pip install mysql-connector-python

Output:

Number of rows in the table: 4

Comparison of Methods

Conclusion

Use SELECT COUNT(*) for efficient row counting, especially with large tables. The SELECT * approach is suitable when you need both the count and the actual data. Both PyMySQL and mysql-connector-python provide reliable connectivity options for MySQL databases in Python.