-
安装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","表结构")
效果如下图