AiDOPWarehouse/docs/db/gen_table_excel.py

#!/usr/bin/env python3
"""从 aidopdev 数据库导出全部表结构到 Excel，按业务分类分 Sheet。"""

import pymysql
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from collections import OrderedDict

DB_CFG = dict(
    host="123.60.180.165", port=3306,
    user="aidopremote", password="1234567890aiDOP#",
    database="information_schema", charset="utf8mb4",
)

CATEGORY_ORDER = OrderedDict([
    ("系统基础(Sys)", lambda t: t.startswith("Sys")),
    ("审批流程(Approval)", lambda t: t.startswith("Approval")),
    ("钉钉集成(DingTalk)", lambda t: t.startswith("DingTalk") or t.startswith("ding_talk")),
    ("GoView可视化", lambda t: t.startswith("GoView")),
    ("S0 基础主数据", lambda t: t.startswith("ado_s0_")),
    ("S1 产销协同", lambda t: t.startswith("ado_s1_")),
    ("S2 生产计划", lambda t: t.startswith("ado_s2_")),
    ("S3 物料计划", lambda t: t.startswith("ado_s3_")),
    ("S4 采购", lambda t: t.startswith("ado_s4_")),
    ("S5 仓储来料", lambda t: t.startswith("ado_s5_")),
    ("S6 生产执行", lambda t: t.startswith("ado_s6_")),
    ("S7 成品管理", lambda t: t.startswith("ado_s7_")),
    ("S8 预警异常", lambda t: t.startswith("ado_s8_")),
    ("S9 管理看板KPI", lambda t: t.startswith("ado_s9_")),
    ("智慧运营(SmartOps)", lambda t: t.startswith("ado_smart_ops_")),
    ("主数据(MD)", lambda t: t.startswith("ado_md_")),
    ("集成(Intg)", lambda t: t.startswith("ado_intg_")),
    ("移动端(Mobile)", lambda t: t.startswith("ado_mobile_")),
    ("Demo表", lambda t: t in ("ado_orders", "ado_plans", "ado_work_orders")),
    ("其他", lambda t: True),
])

HEADER_FILL = PatternFill("solid", fgColor="4472C4")
HEADER_FONT = Font(name="微软雅黑", bold=True, color="FFFFFF", size=10)
BODY_FONT = Font(name="微软雅黑", size=9)
TBL_FILL = PatternFill("solid", fgColor="D9E2F3")
TBL_FONT = Font(name="微软雅黑", bold=True, size=10)
THIN_BORDER = Border(
    left=Side(style="thin"), right=Side(style="thin"),
    top=Side(style="thin"), bottom=Side(style="thin"),
)
WRAP = Alignment(wrap_text=True, vertical="center")


def fetch_data():
    conn = pymysql.connect(**DB_CFG)
    cur = conn.cursor()
    cur.execute(
        "SELECT TABLE_NAME, TABLE_COMMENT, TABLE_ROWS "
        "FROM TABLES WHERE TABLE_SCHEMA='aidopdev' ORDER BY TABLE_NAME"
    )
    tables = {r[0]: {"comment": r[1] or "", "rows": r[2]} for r in cur.fetchall()}

    cur.execute(
        "SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, "
        "COLUMN_DEFAULT, COLUMN_COMMENT "
        "FROM COLUMNS WHERE TABLE_SCHEMA='aidopdev' "
        "ORDER BY TABLE_NAME, ORDINAL_POSITION"
    )
    columns = {}
    for row in cur.fetchall():
        columns.setdefault(row[0], []).append(row[1:])
    cur.close()
    conn.close()
    return tables, columns


def classify(tables):
    result = OrderedDict((k, []) for k in CATEGORY_ORDER)
    assigned = set()
    for cat, pred in CATEGORY_ORDER.items():
        for tname in tables:
            if tname not in assigned and pred(tname):
                result[cat].append(tname)
                assigned.add(tname)
    return result


def write_overview(ws, tables, classified):
    headers = ["序号", "分类", "表名", "表说明", "记录数"]
    widths = [6, 22, 42, 36, 10]
    for ci, (h, w) in enumerate(zip(headers, widths), 1):
        c = ws.cell(row=1, column=ci, value=h)
        c.font = HEADER_FONT
        c.fill = HEADER_FILL
        c.alignment = WRAP
        c.border = THIN_BORDER
        ws.column_dimensions[c.column_letter].width = w

    idx = 0
    for cat, tnames in classified.items():
        for tname in tnames:
            idx += 1
            info = tables[tname]
            vals = [idx, cat, tname, info["comment"], info["rows"]]
            for ci, v in enumerate(vals, 1):
                c = ws.cell(row=idx + 1, column=ci, value=v)
                c.font = BODY_FONT
                c.alignment = WRAP
                c.border = THIN_BORDER
    ws.auto_filter.ref = f"A1:E{idx + 1}"


def write_detail_sheet(ws, cat, tnames, tables, columns):
    headers = ["字段名", "类型", "可空", "默认值", "说明"]
    widths = [28, 22, 6, 14, 36]
    for ci, w in enumerate(widths, 1):
        ws.column_dimensions[ws.cell(row=1, column=ci).column_letter].width = w

    row = 1
    for tname in tnames:
        info = tables[tname]
        c = ws.cell(row=row, column=1,
                     value=f"{tname}  —  {info['comment']}  (约{info['rows'] or 0}行)")
        c.font = TBL_FONT
        c.fill = TBL_FILL
        c.border = THIN_BORDER
        for ci in range(2, 6):
            cc = ws.cell(row=row, column=ci)
            cc.fill = TBL_FILL
            cc.border = THIN_BORDER
        ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=5)
        row += 1

        for ci, (h, w) in enumerate(zip(headers, widths), 1):
            c = ws.cell(row=row, column=ci, value=h)
            c.font = HEADER_FONT
            c.fill = HEADER_FILL
            c.alignment = WRAP
            c.border = THIN_BORDER
        row += 1

        for col_info in columns.get(tname, []):
            col_name, col_type, nullable, default, comment = col_info
            vals = [col_name, col_type, nullable, str(default) if default is not None else "", comment or ""]
            for ci, v in enumerate(vals, 1):
                c = ws.cell(row=row, column=ci, value=v)
                c.font = BODY_FONT
                c.alignment = WRAP
                c.border = THIN_BORDER
            row += 1
        row += 1


def main():
    tables, columns = fetch_data()
    classified = classify(tables)

    wb = Workbook()
    ws_overview = wb.active
    ws_overview.title = "总览"
    write_overview(ws_overview, tables, classified)

    for cat, tnames in classified.items():
        if not tnames:
            continue
        safe_title = cat[:31]
        ws = wb.create_sheet(title=safe_title)
        write_detail_sheet(ws, cat, tnames, tables, columns)

    out = "/root/aidopdemo/docs/db/aidopdev_表结构汇总.xlsx"
    wb.save(out)
    print(f"Done → {out}")


if __name__ == "__main__":
    main()