import pymysql
from pathlib import Path
from openpyxl import Workbook

out = Path(r"d:/Projects/Ai-DOP/SourceCode/ai-dop-platform/docs/db/aidopdev_远端落库表字段说明.xlsx")
conn = pymysql.connect(host='123.60.180.165', port=3306, user='aidopremote', password='1234567890aiDOP#', charset='utf8mb4', autocommit=True)
try:
    with conn.cursor() as cur:
        cur.execute("""
            SELECT table_name, table_comment
            FROM information_schema.tables
            WHERE table_schema='aidopdev' AND table_name LIKE 'ado\\_%'
            ORDER BY table_name
        """)
        tables = cur.fetchall()

        wb = Workbook()
        ws1 = wb.active
        ws1.title = '表清单'
        ws1.append(['序号','表名','表说明'])
        for i, (t, c) in enumerate(tables, 1):
            ws1.append([i, t, c])

        ws2 = wb.create_sheet('字段说明')
        ws2.append(['表名','字段序号','字段名','类型','可空','默认值','字段说明'])
        for t, _ in tables:
            cur.execute("""
                SELECT ordinal_position, column_name, column_type, is_nullable, column_default, column_comment
                FROM information_schema.columns
                WHERE table_schema='aidopdev' AND table_name=%s
                ORDER BY ordinal_position
            """, (t,))
            cols = cur.fetchall()
            for pos, name, ctype, nullable, dft, cm in cols:
                ws2.append([t, pos, name, ctype, '是' if nullable=='YES' else '否', '' if dft is None else str(dft), cm])

        wb.save(out)
        print(str(out))
        print(f'tables={len(tables)}')
finally:
    conn.close()