Python导出MySQL某个数据库的表结构到Excel

一、环境准备与库安装

pip  install  pymysql  pandas  openpyxl 

 

二、完整实现代码

导出MySQL数据库的表结构到Excel

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):
        """获取指定表结构详情"""
        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):
        """主导出方法"""
        try:
            # 获取所有表信息
            df_summary = self._get_tables_info()
            df_summary.insert(0, '序号', range(1, len(df_summary) + 1))
            self.excel_path = output_file

            # 创建Excel写入对象
            with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
                # 写入总览页
                df_summary.to_excel(
                    writer,
                    sheet_name='首页总览',
                    index=False,
                    header=['序号', '表名', '表注释']
                )

                # 遍历写入各表结构
                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表名最长31字符
                        index=False,
                        header=['序号', '表名', '字段名称', '字段类型', '字段注释']
                    )

            # 应用格式和超链接
            self._add_hyperlinks()
            self._apply_formatting()

            return True

        except Exception as e:
            print(f"导出失败: {str(e)}")
            return False

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

    def _apply_formatting(self):
        """应用格式设置"""
        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:
            # 设置标题行样式
            for cell in sheet[1]:
                cell.fill = blue_fill
                cell.font = Font(color="FFFFFF", bold=True)

            # 设置边框
            for row in sheet.iter_rows(min_row=1):
                for cell in row:
                    cell.border = thin_border

            # 自适应列宽
            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["首页总览"]

        # 总览表到分表
        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="返回\"首页总览\"")
            ws.cell(row=last_row, column=1).hyperlink = "#'首页总览'!A1"
            ws.cell(row=last_row, column=1).style = "Hyperlink"

        wb.save(self.excel_path)


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

    # 数据库连接配置
    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"数据库{db_input}表结构.xlsx")
    if success:
        print(f"表结构已成功导出至 数据库{db_input}表结构.xlsx")

 

三、核心功能解析

1. ​​元数据采集​​

   • 通过INFORMATION_SCHEMA系统表获取精确表结构
   • 自动处理字段顺序、数据类型等详细信息
   • 支持中文字符集（utf8mb4编码）

2. ​​专业格式控制​​

   • 全表格统一细线边框
   • 标题行采用蓝色填充（RGB：#0070C0）
   • 列宽根据内容自动调整
   • 标题文字白色加粗显示

3. ​​双向超链接功能​​

• 总览页表名可点击跳转到对应分表
• 每个分表最后一行添加返回总览页的链接
• 自动处理Excel表名长度限制（截断超过30字符的表名）。

四、生成文档示例

​​首页总览：​​

序号	表名（可点击）	表注释
1	user	用户信息表
2	order	订单主表

​​

 

分表结构页（user表）：​​

序号	字段名称	数据类型	字段注释
1	id	int(11)	主键ID
2	username	varchar(50)	登录用户名
...	...	...	...
返回"首页总览"