Python导出SQLServer数据库的表结构（使用SSH隧道）

有很多时候在连接数据库时，都需要通过一个跳板机进行跳转才能连接上数据库，在数据库连接工具中一般称之为“SSH隧道”。

1、安装依赖

pip install sshtunnel pyodbc  pandas  openpyxl

2、安装  ODBC  Driver for SQL Server 

msodbcsql.msi

3、完整代码

下面是在Python代码中通过SSH隧道连接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
from sshtunnel import SSHTunnelForwarder


def apply_formatting(excel_path):
    """应用格式设置"""
    wb = load_workbook(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(excel_path)


def add_hyperlinks(excel_path):
    """添加双向超链接"""
    wb = load_workbook(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(excel_path)


def sql_to_dataframe(cursor):
    """将 pyodbc cursor 结果转换为 DataFrame"""
    # 获取列名
    columns = [column[0] for column in cursor.description]
    # 获取数据并创建DataFrame
    data = cursor.fetchall()
    return pd.DataFrame.from_records(data, columns=columns)


def get_tables_info(cursor):
    """获取所有表信息"""
    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
    """
    # 执行查询
    cursor.execute(query)
    return sql_to_dataframe(cursor)


def get_columns_info(cursor, 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
    """

    # 执行查询
    cursor.execute(query)
    return sql_to_dataframe(cursor)


if __name__ == '__main__':

    # ================== 配置信息 ==================
    # SSH 隧道配置
    ssh_config = {
        "ssh_address": "100.100.20.20",  # SSH服务器地址
        "ssh_port": 22,                  # SSH端口
        "ssh_username": "root",          # SSH用户名
        "ssh_password": "Root@1234",     # SSH密码
    }

    # 数据库配置
    db_config = {
        "db_host": "100.100.10.10",  # 数据库实际地址（内网地址）
        "db_port": 1433,             # 数据库端口
        "db_username": "sa",         # 数据库用户名
        "db_password": "SA@1234",    # 数据库密码
        "db_name": "master"          # 数据库名称
    }

    # 本地绑定端口（随机可用端口）
    local_bind_port = 3307

    # ================== 建立SSH隧道 ==================
    try:
        with SSHTunnelForwarder(
                (ssh_config["ssh_address"], ssh_config["ssh_port"]),
                ssh_username=ssh_config["ssh_username"],
                ssh_password=ssh_config["ssh_password"],
                remote_bind_address=(db_config["db_host"], db_config["db_port"])
        ) as tunnel:
            print(f"SSH隧道建立成功，本地端口：{tunnel.local_bind_port}")

            # ================== 连接数据库 ==================
            # 方法一：使用 pyodbc
            conn = pyodbc.connect(
                driver='{ODBC Driver 17 for SQL Server}',
                server='127.0.0.1,' + str(tunnel.local_bind_port),
                database=db_config["db_name"],
                uid=db_config["db_username"],
                pwd=db_config["db_password"],
                timeout=30,
                autocommit=False  # 显式事务控制
            )

            cursor = conn.cursor()
            # 获取所有表的信息
            df_summary = get_tables_info(cursor)
            df_summary.insert(0, '序号', range(1, len(df_summary) + 1))

            # 写入的Excel名称
            output_file = "数据库" + db_config["db_name"] + "表结构.xlsx"

            # 创建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 = get_columns_info(cursor, table_name)
                    df_columns.insert(0, '序号', range(1, len(df_columns) + 1))
                    df_columns.to_excel(
                        writer,
                        sheet_name=table_name[:30],  # Excel表名最长31字符
                        index=False,
                        header=["序号", "表名", "字段名称", "字段注释", "字段类型", "长度", "标度", "精度", "非空"]
                    )

            # 应用格式和超链接
            add_hyperlinks(output_file)    # 设置超链接
            apply_formatting(output_file)   # 设置边框

            print(f"表结构已成功导出至 {output_file}")

    except Exception as e:
        print(f"连接失败: {str(e)}")
    finally:
        if 'conn' in locals():
            conn.close()