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

Heroku

Built for developers, by developers.

Whether you're building a simple prototype or a business-critical product, Heroku's fully-managed platform gives you the simplest path to delivering apps quickly — using the tools and languages you already love!

Learn More

Top comments (0)

👋 Kindness is contagious

Explore a trove of insights in this engaging article, celebrated within our welcoming DEV Community. Developers from every background are invited to join and enhance our shared wisdom.

A genuine "thank you" can truly uplift someone’s day. Feel free to express your gratitude in the comments below!

On DEV, our collective exchange of knowledge lightens the road ahead and strengthens our community bonds. Found something valuable here? A small thank you to the author can make a big difference.

Okay