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 mysql.connector
mydb = mysql.connector.connect(
host = "localhost" ,
user = "root" ,
password = "root123" ,
database = "geeks" )
mycursor = mydb.cursor()
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)
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 mysql.connector
mydb = mysql.connector.connect(
host = "localhost" ,
user = "root" ,
password = "root123" ,
database = "geeks" )
mycursor = mydb.cursor()
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)
myresult = mycursor.fetchall()
for item in myresult:
print (item[ 0 ], "Size in MB: " , item[ - 1 ])
|
Output:
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...