DEV Community

Dmitry Romanoff
Dmitry Romanoff

Posted on

1

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}")

Enter fullscreen mode Exit fullscreen mode

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$ 
Enter fullscreen mode Exit fullscreen mode

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay