Skip to content
Related Articles

Related Articles

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

View Discussion
Improve Article
Save Article
  • Last Updated : 26 Dec, 2020
View Discussion
Improve Article
Save Article

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:


My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!