This blog article shows you how to create a Data Dictionary for MySQL Database using Python. To generate a data dictionary from a MySQL database using Python, you can use the mysql-connector-python library to connect to the database and query information about tables and columns. Additionally, you may use the pandas library to create a DataFrame and then export it to a dictionary.
import mysql.connector
import pandas as pd
def generate_data_dictionary(host, user, password, database, output_file):
# Connect to the MySQL database
connection = mysql.connector.connect(
host=host,
user=user,
password=password,
database=database
)
cursor = connection.cursor(dictionary=True)
# Get the list of tables in the database
cursor.execute(“SHOW TABLES”)
tables = [table[‘Tables_in_’ + database] for table in cursor.fetchall()]
# Create an ExcelWriter object
with pd.ExcelWriter(output_file, engine=’openpyxl’) as writer:
for table in tables:
# Get the columns for each table
cursor.execute(f”DESCRIBE {table}”)
columns = cursor.fetchall()
# Create a DataFrame for the table
df = pd.DataFrame(columns)
df.columns = [‘Column’, ‘Type’, ‘Null’, ‘Key’, ‘Default’, ‘Extra’]
# Write the DataFrame to a separate sheet
df.to_excel(writer, sheet_name=table, index=False)
# Close the cursor and connection
cursor.close()
connection.close()
if __name__ == “__main__”:
# Replace these values with your MySQL connection details and output file path
host = “your_mysql_host”
user = “your_mysql_user”
password = “your_mysql_password”
database = “your_mysql_database”
output_file = “output_data_dictionary.xlsx”
generate_data_dictionary(host, user, password, database, output_file)
print(f”Data dictionary has been saved to {output_file}.”)
Replace the placeholders (your_mysql_host, your_mysql_user, your_mysql_password, and your_mysql_database) with your actual MySQL connection details.
This script connects to the MySQL database, retrieves a list of tables, and then fetches the columns for each table. The information is stored in a data dictionary and printed. You can modify or extend this script based on your specific needs.
Source code download: https://github.com/chanmmn/python/tree/main/2023/MySQLDataDictionary/?WT.mc_id=DP-MVP-36769
Reference: https://chanmingman.wordpress.com/2023/04/26/python-read-data-from-mysql/?WT.mc_id=DP-MVP-36769
Pingback: Read PDF using Python | Chanmingman's Blog
Pingback: Generate Data Dictionary for MS SQL Database using Python | Chanmingman's Blog