python 导出oracle表结构到word文档

• 安装oracle client
  https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html
  
  解压后把这几个文件放到python的site-package里面
  

• 安装依赖 pip install cx_Oracle
  pip install python-docx

import cx_Oracle as cx
from docx import Document


def func():
    username = "admin"
    kuName = "ip:1521/ORCL"
    password = "123456"
    con = cx.connect(username, password, kuName)  # 建立连接
    cursor = con.cursor()  # 创建游标

    cursor.execute("select * from all_tables where owner='" + username.upper() + "' order by TABLE_NAME")
    data = cursor.fetchall()  # 获取所有表名
    tableNameList = []
    # 得到表名列表
    for x in range(len(data)):
        tableNameList.append(data[x][1])
        # 获取每个表的结构
    tableStruct = {}
    for tableName in tableNameList:
        tableStruct[tableName] = {}


        cursor.execute(
            "select COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE from user_tab_cols where table_name='" + tableName + "' order by COLUMN_ID")
        data1 = cursor.fetchall()
        # 获取每个列的数据类型和数据长度
        for x in data1:
            datadict = {'data_type': x[1], 'data_length': x[2],'nullable':x[3]}
            tableStruct[tableName][x[0]] = datadict
        cursor.execute("select COLUMN_NAME , COMMENTS from user_col_comments where table_name ='" + tableName + "'")
        data2 = cursor.fetchall()
        # 获取每个列的备注
        for y in data2:
            tableStruct[tableName][y[0]]['comment'] = y[1]
    cursor.close()  # 关闭游标
    con.close()  # 关闭数据库连接
    return tableStruct


def write_to_word(tableStruct, docName, headingName):
    document = Document()
    # 写一个标题
    document.add_heading(f"总共有{len(tableStruct)}个表", 0)
    for x in tableStruct:
        document.add_heading(x, 1)
        # 插入一个表格
        table = document.add_table(rows=len(tableStruct[x]) + 1, cols=5, style='Table Grid')
        hc = table.rows[0].cells
        hc[0].text = '字段名称'
        hc[1].text = '数据类型'
        hc[2].text = '长度'
        hc[3].text = '是否必填'
        hc[4].text = '备注说明'
        m = 1
        for columnName in tableStruct[x]:
            mx = table.rows[m].cells
            mx[0].text = columnName
            mx[1].text = tableStruct[x][columnName]['data_type']
            data_length = tableStruct[x][columnName]['data_length']

            mx[2].text = str(data_length)
            mx[3].text = tableStruct[x][columnName]['nullable']
            if 'comment' in tableStruct[x][columnName] and tableStruct[x][columnName]['comment'] is not None and len(
                    tableStruct[x][columnName]['comment']) > 0:
                mx[4].text = tableStruct[x][columnName]['comment']
            m += 1
        print("已生成表" + x)
        document.add_paragraph('next page')
    document.save(docName + ".docx")


if __name__ == '__main__':
    data = func()
    write_to_word(data,"demo29","表结构")

效果如下图