Python获取Oracle中TB_打头表结构并生成Markdown表格

下面是一个完整的Python脚本，用于连接Oracle数据库，获取所有以"TB_"开头的表结构（包括字段名、数据类型、注释等），并按表整理成Markdown格式的表格输出。

准备工作

首先确保已安装必要的库：

pip install cx_Oracle pandas

完整脚本

import cx_Oracle
import pandas as pd
from io import StringIO

def get_oracle_table_structures(username, password, dsn, schema=None):
    """
    获取Oracle数据库中TB_打头表的结构信息并返回Markdown格式字符串
    
    参数:
        username: 数据库用户名
        password: 数据库密码
        dsn: 数据源名称(host:port/service_name)
        schema: 指定模式(所有者)，None表示当前用户
    
    返回:
        包含所有TB_表结构的Markdown字符串
    """
    # 连接数据库
    try:
        connection = cx_Oracle.connect(user=username, password=password, dsn=dsn)
        cursor = connection.cursor()
        
        # 确定查询的模式条件
        schema_condition = ""
        params = {}
        if schema:
            schema_condition = "AND t.owner = :schema"
            params['schema'] = schema.upper()
        
        # 查询所有TB_打头的表
        table_query = f"""
        SELECT 
            t.owner,
            t.table_name,
            c.comments AS table_comment
        FROM 
            all_tables t
        LEFT JOIN 
            all_tab_comments c ON t.owner = c.owner AND t.table_name = c.table_name
        WHERE 
            t.table_name LIKE 'TB\_%%' ESCAPE '\\'
            {schema_condition}
        ORDER BY 
            t.owner, t.table_name
        """
        
        cursor.execute(table_query, params)
        tables = cursor.fetchall()
        
        if not tables:
            return "没有找到以'TB_'开头的表"
        
        # 为每个表查询列信息
        markdown_output = StringIO()
        
        for owner, table_name, table_comment in tables:
            # 查询列信息
            col_query = """
            SELECT 
                c.column_name,
                c.data_type,
                c.data_length,
                c.data_precision,
                c.data_scale,
                c.nullable,
                cc.comments,
                c.column_id
            FROM 
                all_tab_columns c
            LEFT JOIN 
                all_col_comments cc ON c.owner = cc.owner AND c.table_name = cc.table_name AND c.column_name = cc.column_name
            WHERE 
                c.owner = :owner AND c.table_name = :table_name
            ORDER BY 
                c.column_id
            """
            
            cursor.execute(col_query, {'owner': owner, 'table_name': table_name})
            columns = cursor.fetchall()
            
            # 转换为DataFrame便于处理
            df = pd.DataFrame(columns, columns=[
                '列名', '数据类型', '长度', '精度', '小数位', '允许空', '注释', '序号'
            ])
            
            # 格式化数据类型显示
            df['数据类型'] = df.apply(lambda row: format_datatype(row), axis=1)
            
            # 删除不需要的列
            df = df[['序号', '列名', '数据类型', '允许空', '注释']]
            
            # 生成Markdown表格
            table_markdown = df.to_markdown(index=False)
            
            # 添加表头信息
            table_header = f"## {owner}.{table_name}"
            if table_comment:
                table_header += f" - {table_comment}"
            
            markdown_output.write(f"{table_header}\n\n")
            markdown_output.write(f"{table_markdown}\n\n")
        
        return markdown_output.getvalue()
        
    except cx_Oracle.DatabaseError as e:
        return f"数据库错误: {e}"
    finally:
        if 'connection' in locals():
            cursor.close()
            connection.close()

def format_datatype(row):
    """格式化数据类型显示"""
    dtype = row['数据类型']
    
    if dtype == 'NUMBER':
        if pd.notna(row['精度']) and pd.notna(row['小数位']):
            return f"{dtype}({int(row['精度'])},{int(row['小数位'])})"
        elif pd.notna(row['精度']):
            return f"{dtype}({int(row['精度'])})"
        else:
            return dtype
    elif dtype in ['VARCHAR2', 'CHAR', 'NVARCHAR2', 'NCHAR', 'RAW']:
        return f"{dtype}({int(row['长度'])})"
    elif dtype == 'FLOAT':
        if pd.notna(row['精度']):
            return f"{dtype}({int(row['精度'])})"
        else:
            return dtype
    else:
        return dtype

# 使用示例
if __name__ == "__main__":
    # 数据库连接信息
    DB_USER = "your_username"
    DB_PASSWORD = "your_password"
    DB_DSN = "localhost:1521/orclpdb"  # 根据实际情况修改
    SCHEMA = None  # 指定模式(所有者)，None表示当前用户
    
    # 获取表结构并生成Markdown
    markdown_result = get_oracle_table_structures(DB_USER, DB_PASSWORD, DB_DSN, SCHEMA)
    
    # 保存到文件或打印
    with open("oracle_table_structures.md", "w", encoding="utf-8") as f:
        f.write(markdown_result)
    
    print("Markdown文件已生成: oracle_table_structures.md")

脚本说明

1. 功能：

   • 连接Oracle数据库
   • 查找所有以"TB_"开头的表
   • 获取每个表的列信息、数据类型、注释等
   • 生成格式化的Markdown表格

2. 输出格式：

   • 每个表生成一个Markdown表格
   • 表格包含列序号、列名、数据类型、是否允许空值、注释等信息
   • 表名和表注释作为标题

3. 数据类型处理：

   • 脚本会智能格式化各种数据类型，如：
     • NUMBER(10,2)
     • VARCHAR2(50)
     • DATE (保持原样)

4. 使用方法：

   • 修改数据库连接信息（用户名、密码、DSN）
   • 可选指定SCHEMA参数限制查询范围
   • 运行后生成oracle_table_structures.md文件

示例输出

生成的Markdown文件内容示例：

## SCOTT.TB_EMPLOYEE - 员工信息表

| 序号 | 列名         | 数据类型      | 允许空 | 注释           |
|------|--------------|---------------|--------|----------------|
| 1    | EMP_ID       | NUMBER(10)    | N      | 员工ID         |
| 2    | EMP_NAME     | VARCHAR2(100) | N      | 员工姓名       |
| 3    | DEPT_ID      | NUMBER(5)     | Y      | 所属部门ID     |
| 4    | SALARY       | NUMBER(10,2)  | Y      | 薪资           |
| 5    | HIRE_DATE    | DATE          | N      | 入职日期       |
| 6    | STATUS       | CHAR(1)       | Y      | 状态(1:在职...)|

## SCOTT.TB_DEPARTMENT - 部门表

| 序号 | 列名         | 数据类型      | 允许空 | 注释           |
|------|--------------|---------------|--------|----------------|
| 1    | DEPT_ID      | NUMBER(5)     | N      | 部门ID         |
| 2    | DEPT_NAME    | VARCHAR2(50)  | N      | 部门名称       |
| 3    | MANAGER_ID   | NUMBER(10)    | Y      | 部门经理ID     |
| 4    | LOCATION     | VARCHAR2(200) | Y      | 办公地点       |

注意事项

1. 确保Oracle客户端已正确安装配置
2. 根据实际情况修改数据库连接信息
3. 如需查询其他用户的表，需要具有相应权限
4. 对于大型数据库，查询可能需要较长时间

这个脚本可以方便地生成数据库文档，便于分享和版本控制。