[SCRIPT] export data dictionary database mariadb - fourslickz/notes GitHub Wiki

INSTALL

sudo apt install -y python3 python3-pip
python3 -m venv venv
source venv/bin/activate
pip3 install pymysql pandas openpyxl sqlalchemy

RUN

import pandas as pd
from sqlalchemy import create_engine
from openpyxl.styles import Font, PatternFill
from openpyxl.utils import get_column_letter

# ======================
# KONFIGURASI DATABASE
# ======================
DB_HOST = "localhost"
DB_PORT = 3307
DB_USER = "root"
DB_PASS = "PASSWORD_DB"
DB_NAME = "nama_database"

OUTPUT_FILE = "data_dictionary.xlsx"

engine = create_engine(
    f"mysql+pymysql://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
)

tables = pd.read_sql("SHOW TABLES", engine)
table_list = tables.iloc[:, 0].tolist()

with pd.ExcelWriter(OUTPUT_FILE, engine="openpyxl") as writer:
    for table in table_list:

        # ======================
        # AMBIL STRUKTUR TABLE
        # ======================
        query = f"""
        SELECT
            COLUMN_NAME AS Field,
            COLUMN_TYPE AS Type,
            IS_NULLABLE AS `Null`,
            COLUMN_KEY AS `Key`,
            COLUMN_DEFAULT AS `Default`,
            EXTRA AS Extra,
            '' AS Comment
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = '{DB_NAME}'
          AND TABLE_NAME = '{table}'
        ORDER BY ORDINAL_POSITION
        """

        df = pd.read_sql(query, engine)

        # ======================
        # TULIS DATA (MULAI ROW 5)
        # ======================
        df.to_excel(
            writer,
            sheet_name=table[:31],
            index=False,
            startrow=4
        )

        ws = writer.book[table[:31]]

        # ======================
        # HEADER ATAS
        # ======================
        ws["A2"] = "Table"
        ws["B2"] = table
        ws["A3"] = "Description"
        ws["B3"] = ""

        ws["F2"] = "PK"
        ws["F2"].fill = PatternFill("solid", fgColor="4CAF50")
        ws["F2"].font = Font(bold=True, color="FFFFFF")

        # ======================
        # STYLING HEADER TABLE
        # ======================
        header_fill = PatternFill("solid", fgColor="4DC3C7")
        bold = Font(bold=True)

        for col in range(1, ws.max_column + 1):
            cell = ws.cell(row=5, column=col)
            cell.font = bold
            cell.fill = header_fill
            ws.column_dimensions[get_column_letter(col)].width = 22

        ws.freeze_panes = "A6"

        # ======================
        # HIGHLIGHT PRIMARY KEY
        # ======================
        pk_fill = PatternFill("solid", fgColor="A5D6A7")

        for row in range(6, ws.max_row + 1):
            if ws.cell(row=row, column=4).value == "PRI":
                for col in range(1, ws.max_column + 1):
                    ws.cell(row=row, column=col).fill = pk_fill

print(f"✔ Data Dictionary format laporan berhasil dibuat: {OUTPUT_FILE}")