How to Get the Size of a Table in MySQL using Python?

Last Updated : 26 Dec, 2020

Prerequisite: Python: MySQL Create Table

In this article, we are going to see how to get the size of a table in MySQL using Python. Python allows the integration of a wide range of database servers with applications. A database interface is required to access a database from Python. MySQL Connector-Python module is an API in python for communicating with a MySQL database.

Approach:

Import module.
Make a connection request with the database.
Create an object for the database cursor.
Execute the following MySQL query:

SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = ‘DataBase_name’ AND table_name = ‘Table_name’;

Example 1:

In this example we are using this database table with the following query;

Below is the implementation:

Python3

# Import required module
import mysql.connector
  
# Establish connection
# to MySQL database
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root123",
    database="geeks")
  
# Create cursor object
mycursor = mydb.cursor()
  
# Execute query
query = "SELECT  table_name AS `Table`, \
        round(((data_length + index_length) \
        / 1024 / 1024), 2) `Size in MB` \
        FROM information_schema.TABLES \
        WHERE table_schema = 'Geeks' AND \
        table_name = 'Persons';"
  
mycursor.execute(query)
  
# Display size of each table
myresult = mycursor.fetchall()
  
for item in myresult:
    print(item[0], "Size in MB: ", item[-1])

Output:

Example 2:

In this example, we are going to get all table sizes in a database.

Below is the implementation:

Python3

# Import required module
import mysql.connector
  
# Establish connection
# to MySQL database
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root123",
    database="geeks")
  
# Create cursor object
mycursor = mydb.cursor()
  
# Execute query
query = "SELECT  TABLE_NAME AS `Table`, \
         ROUND(((DATA_LENGTH + INDEX_LENGTH) \
         / 1024 / 1024),2) AS `Size (MB)` \
         FROM information_schema.TABLES WHERE \
         TABLE_SCHEMA = 'Geeks' ORDER BY \
         (DATA_LENGTH + INDEX_LENGTH) DESC;"
  
mycursor.execute(query)
  
# Display size of each table
myresult = mycursor.fetchall()
  
for item in myresult:
    print(item[0], "Size in MB: ", item[-1])

Output:

Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning – Basic Level Course

My Personal Notes

Related Articles

How to Get the Size of a Table in MySQL using Python?

Python3

Python3