Dmitry RomanoffThis 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$
