pip  install  pymysql  pandas  openpyxl 

import pymysql
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Border, Side, PatternFill, Font, Alignment
from openpyxl.utils import get_column_letter


class MySQLSchemaExporter:
    def __init__(self, host,  port, user, password, database):
        self.conn = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            database=database,
            charset='utf8mb4'
        )
        self.db_name = database
        self.excel_path = None

    def _get_tables_info(self):
        """獲取所有表信息"""
        query = f"""
            SELECT TABLE_NAME AS table_name, TABLE_COMMENT AS table_comment
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_SCHEMA = '{self.db_name}'
        """
        return pd.read_sql_query(query, self.conn)

    def _get_columns_info(self, table_name):
        """獲取指定表結(jié)構(gòu)詳情"""
        query = f"""
            SELECT
                ORDINAL_POSITION AS seq,
                TABLE_NAME AS  table_name,
                COLUMN_NAME AS column_name,
                COLUMN_TYPE AS data_type,
                COLUMN_COMMENT AS column_comment
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = '{self.db_name}'
                AND TABLE_NAME = '{table_name}'
            ORDER BY ORDINAL_POSITION
        """
        return pd.read_sql_query(query, self.conn)


    def export_schema(self, output_file):
        """主導(dǎo)出方法"""
        try:
            # 獲取所有表信息
            df_summary = self._get_tables_info()
            df_summary.insert(0, '序號(hào)', range(1, len(df_summary) + 1))
            self.excel_path = output_file

            # 創(chuàng)建Excel寫(xiě)入對(duì)象
            with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
                # 寫(xiě)入總覽頁(yè)
                df_summary.to_excel(
                    writer,
                    sheet_name='首頁(yè)總覽',
                    index=False,
                    header=['序號(hào)', '表名', '表注釋']
                )

                # 遍歷寫(xiě)入各表結(jié)構(gòu)
                for _, row in df_summary.iterrows():
                    table_name = row['table_name']
                    df_columns = self._get_columns_info(table_name)

                    df_columns.to_excel(
                        writer,
                        sheet_name=table_name[:30],  # Excel表名最長(zhǎng)31字符
                        index=False,
                        header=['序號(hào)', '表名', '字段名稱', '字段類型', '字段注釋']
                    )

            # 應(yīng)用格式和超鏈接
            self._add_hyperlinks()
            self._apply_formatting()

            return True

        except Exception as e:
            print(f"導(dǎo)出失敗: {str(e)}")
            return False

        finally:
            if self.conn:
                self.conn.close()

    def _apply_formatting(self):
        """應(yīng)用格式設(shè)置"""
        wb = load_workbook(self.excel_path)

        # 定義樣式
        blue_fill = PatternFill(
            start_color="0070C0",
            end_color="0070C0",
            fill_type="solid"
        )
        thin_border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )

        for sheet in wb.worksheets:
            # 設(shè)置標(biāo)題行樣式
            for cell in sheet[1]:
                cell.fill = blue_fill
                cell.font = Font(color="FFFFFF", bold=True)

            # 設(shè)置邊框
            for row in sheet.iter_rows(min_row=1):
                for cell in row:
                    cell.border = thin_border

            # 自適應(yīng)列寬
            for column in sheet.columns:
                max_length = max(
                    len(str(cell.value)) for cell in column
                )
                adjusted_width = max_length + 2
                sheet.column_dimensions[
                    get_column_letter(column[0].column)
                ].width = adjusted_width

        wb.save(self.excel_path)


    def _add_hyperlinks(self):
        """添加雙向超鏈接"""
        wb = load_workbook(self.excel_path)
        ws_summary = wb["首頁(yè)總覽"]

        # 總覽表到分表
        for row in ws_summary.iter_rows(min_row=2):
            table_name = row[1].value
            sheet_name = table_name[:30]
            hyperlink = f"#'{sheet_name}'!A1"
            row[1].hyperlink = hyperlink
            row[1].style = "Hyperlink"

        # 分表到總覽表
        for sheet_name in wb.sheetnames[1:]:
            ws = wb[sheet_name]
            last_row = ws.max_row + 1
            ws.cell(row=last_row, column=1, value="返回\"首頁(yè)總覽\"")
            ws.cell(row=last_row, column=1).hyperlink = "#'首頁(yè)總覽'!A1"
            ws.cell(row=last_row, column=1).style = "Hyperlink"

        wb.save(self.excel_path)


# 使用示例
if __name__ == "__main__":

    # 數(shù)據(jù)庫(kù)連接配置
    host_input = "localhost"
    port_input = 3306
    user_input = "root"
    password_input = "Root@1234"
    db_input = "mysql"

    exporter = MySQLSchemaExporter(host_input, port_input, user_input, password_input, db_input)

    success = exporter.export_schema(f"數(shù)據(jù)庫(kù){db_input}表結(jié)構(gòu).xlsx")
    if success:
        print(f"表結(jié)構(gòu)已成功導(dǎo)出至 數(shù)據(jù)庫(kù){db_input}表結(jié)構(gòu).xlsx")