Python导出Oracle数据库的表结构

1、安装依赖

pip install oracledb  pandas  openpyxl  

 

2、下载 Oracle Instant Client​​

（1）从 Oracle 官网 选择与操作系统匹配的版本（如 19c 或 21c）

         下载地址： Oracle Instant Client - Free tools and libraries for connecting to Oracle Database

（2）解压到无空格/中文的路径。比如:  C:\Software\instantclient-basic-windows\ 

说明：（1）、oracledb 的 Thin 模式 仅支持 Oracle 12.1 及以上版本。如果Oracle 12.1 及以上版本，可以不用下载安装Oracle Instant Client​​。

         （2）、若连接的数据库版本低于 12.1（如 Oracle 11g、10g），则会触发错误：DPY-3010: connections to this database server version are not supported by python-oracledb in thin mode
                   解决办法：在代码中显式调用 init_oracle_client() 加载 Oracle 客户端库。

3、实现代码

import oracledb
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):

        # oracledb 的 Thin 模式 仅支持 Oracle 12.1 及以上版本。
        # 若连接的数据库版本低于 12.1（如 Oracle 11g、10g），则会触发错误：
        # DPY-3010: connections to this database server version are not supported by python-oracledb in thin mode
        # 解决办法：在代码中显式调用 init_oracle_client() 加载 Oracle 客户端库。
        # 指定 Oracle Instant Client 路径（需提前下载安装）
        oracledb.init_oracle_client(lib_dir=r"C:\Software\instantclient-basic-windows\instantclient_19_26")  # Windows

        # 配置连接
        dsn = oracledb.makedsn(
            host=host,
            port=port,
            service_name=database
        )
        self.conn = oracledb.connect(
            user=user,
            password=password,
            dsn=dsn
        )
        self.db_name = database
        self.excel_path = None

    def _get_tables_info(self):
        """获取所有表信息"""
        query = f"""
                SELECT 
                    T.TABLE_NAME  AS  table_name,
                    C.COMMENTS  AS  table_comment
                FROM USER_TABLES T
                LEFT JOIN USER_TAB_COMMENTS C 
                    ON T.TABLE_NAME = C.TABLE_NAME
                ORDER BY T.TABLE_NAME
        """
        return pd.read_sql_query(query, self.conn)

    def _get_columns_info(self, table_name):
        """获取指定表结构详情"""
        query = f"""
                SELECT
                    t.TABLE_NAME AS table_name,
                    t.COLUMN_NAME AS column_name,
                    REGEXP_REPLACE(c.COMMENTS, '[^[:print:]]', '') AS column_comments,
                    t.DATA_TYPE || 
                    CASE 
                        WHEN t.DATA_TYPE IN ('VARCHAR2', 'CHAR', 'NVARCHAR2', 'NCHAR') THEN '(' || t.CHAR_LENGTH || ')' 
                        WHEN t.DATA_TYPE IN ('NUMBER') THEN 
                            CASE 
                                WHEN t.DATA_PRECISION IS NOT NULL THEN '(' || t.DATA_PRECISION || ',' || NVL(t.DATA_SCALE, 0) || ')' 
                                ELSE '' 
                            END 
                        ELSE '' 
                    END  AS  column_type,
                    CASE t.NULLABLE WHEN 'N' THEN '[√]' ELSE '[]' END AS  is_notnull
                FROM USER_TAB_COLUMNS t
                LEFT JOIN USER_COL_COMMENTS c 
                    ON t.TABLE_NAME = c.TABLE_NAME 
                    AND t.COLUMN_NAME = c.COLUMN_NAME
                WHERE  t.TABLE_NAME = '{table_name}' 
                ORDER BY t.TABLE_NAME, t.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']
                    print("正在导出表", 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.5
                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', 'F']
            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 = "10.10.10.10"
    port_input = 1521
    user_input = "C##GB"
    password_input = "GB123456"
    db_input = "ORCL"

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

    success = exporter.export_schema(f"数据库{db_input}_{host_input}表结构.xlsx")
    if success:
        print(f"表结构已成功导出至 数据库{db_input}_{host_input}表结构.xlsx")