Python. Exploring MySQL Table Sizes.

This Python code is useful for monitoring the sizes of tables in a MySQL database. It connects to the database and prints out the tables and their sizes, sorting the data in descending order by table size.

import mysql.connector

# MySQL database connection parameters
config = {
  'user': 'root',
  'password': '123456',
  'host': '127.0.0.1',
  'database': 'my_database',
  'port' : 3306,
  'raise_on_warnings': True
}

# Connecting to the MySQL database
try:
    cnx = mysql.connector.connect(**config)
    cursor = cnx.cursor()

    # SQL query to retrieve table sizes
    query = ("SELECT table_name, ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'size_mb' "
             "FROM information_schema.tables "
             "WHERE table_schema = %(database)s "
             "ORDER BY size_mb DESC")

    cursor.execute(query, {'database': config['database']})

    # Printing the results
    print("Table Name\tSize (MB)")
    print("-------------------------")
    for (table_name, size_mb) in cursor:
        print(f"{table_name}\t\t{size_mb}")

    cursor.close()
    cnx.close()

except mysql.connector.Error as err:
    print(f"Error: {err}")

Example of how it works:

dmi@dmi-laptop:~/my_python$ python3 mysql_db_tables_sizes.py 
Table Name  Size (MB)
-------------------------
three       24.56
one     13.52
two     1.52
dmi@dmi-laptop:~/my_python$