Python导出SQLServer数据库的表结构

完整代码：

import pyodbc
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 SQLServerSchemaExporter:

    def __init__(self, host, port, user, password, database):
        # 构建连接字符串
        conn_str = (
            f"DRIVER={{ODBC Driver 17 for SQL Server}};"
            f"SERVER={host};"
            f"PORT={port};" 
            f"DATABASE={database};"
            f"UID={user};"
            f"PWD={password};"
            f"charset=UTF-8;"         # 关键参数
            f"unicode_results=True;"  # 关键参数
            f"autocommit=True"
        )

        self.conn = pyodbc.connect(conn_str)
        self.db_name = database
        self.excel_path = None

    def _get_tables_info(self):
        """获取所有表信息"""
        query = f"""
                SELECT
                    t.name AS table_name,
                    ep.value AS table_comment
                FROM sys.tables t
                LEFT JOIN sys.extended_properties ep
                    ON ep.major_id = t.object_id
                    AND ep.minor_id = 0
                    AND ep.name = 'MS_Description'
                ORDER BY t.name
        """
        return pd.read_sql_query(query, self.conn)

    def _get_columns_info(self, table_name):
        """获取指定表结构详情"""
        query = f"""
            SELECT object_name(c.object_id)  table_name,
                   c.name  column_name,
                   ep.value  AS column_comment,
                   ty.name AS column_type,
                   c.max_length,
                   c.scale,
                   c.precision,
                   IIF(c.is_nullable='TRUE','[]','[√]')  is_nonull
            FROM sys.columns c
            JOIN sys.types ty ON c.user_type_id = ty.user_type_id
            LEFT JOIN sys.extended_properties ep
                ON ep.major_id = c.object_id
                AND ep.minor_id = c.column_id
                AND ep.name = 'MS_Description'
            WHERE c.object_id = OBJECT_ID('{table_name}')
            ORDER BY c.column_id
        """
        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.insert(0, '序号', range(1, len(df_columns) + 1))
                    # print(df_columns.to_string())
                    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) * 1.2
                sheet.column_dimensions[
                    get_column_letter(column[0].column)
                ].width = adjusted_width

            # 定义居中对齐样式（水平居中 + 垂直居中）
            alignment = Alignment(
                horizontal='center',  # 水平居中：'left', 'center', 'right', 'justify'
                vertical='center'  # 垂直居中：'top', 'center', 'bottom'
            )
            # 对指定的列设置居中
            columns_to_center = ['A', 'I']
            for col in columns_to_center:
                for cell in sheet[col]:
                    cell.alignment = alignment

        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 = "your_host"
    port_input = 1433
    user_input = "your_username"
    password_input = "your_password"
    db_input = "your_database"

    exporter = SQLServerSchemaExporter(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")