需要往这个word 里面循环打印表结构和标题,在验收的时候特别烦
import pandas as pd
import cx_Oracle
import pandas as pd
import openpyxl
from openpyxl import load_workbook
from docx import Document
from pandas import DataFrame
import re
# @user1 用户名
# @sql 要查询的sql
def query(user1, sql):
# cx_Oracle.connect("用户名 / 密码@ Oracle服务器IP / Oracle的SERVICE_NAME")
db = cx_Oracle.connect('' + user1 + '/easipass@192.168.129.149:1521/test12c')
c = db.cursor()
x = c.execute(sql) # 使用cursor进行各种操作
rows = c.fetchall()
cols = x.description # 类似 desc table_name返回结果
col = [] # 创建一个空列表以存放列名
for v in cols:
col.append(v[0]) # 循环提取列名,并添加到col空列表
dfsql = pd.DataFrame(rows, columns=col) # 将查询结果转换成DF结构,并给列重新赋值
if dfsql.empty:
return 'empty set' # 判断查询结果为空时返回的值
else:
return dfsql # 以DF结构返回查询结构,
c.close() # 关闭cursor
db.close()
def get_new_col(x):
if len(x) > 25:
se = x.split(':')[4]
return se
else:
return x
def get_one_table(shema,tablename):
sql="select a.OWNER,\
a.TABLE_NAME,\
a.COMMENTS as table_COMMENTS,\
c.COLUMN_ID,\
b.COLUMN_NAME,\
b.COMMENTS as COLUMN_COMMENTS,\
c.DATA_TYPE,\
case when c.DATA_TYPE in('TIMESTAMP(6)','DATE') then null \
else nvl(c.DATA_PRECISION, c.DATA_LENGTH)||decode(c.DATA_SCALE,null,null,0,null,'|'||c.DATA_SCALE) end as data_length \
from dba_tab_comments a \
inner join dba_tab_cols c \
on a.OWNER = c.OWNER \
and a.TABLE_NAME = c.TABLE_NAME \
and c.COLUMN_ID is not null \
inner join dba_col_comments b\
on c.OWNER = b.OWNER\
and c.TABLE_NAME = b.TABLE_NAME\
and c.COLUMN_NAME = b.COLUMN_NAME\
where a.OWNER ='"+shema+"'\
and a.TABLE_NAME='"+tablename+"'\
order by a.OWNER,a.TABLE_NAME,c.COLUMN_ID"
df1=query('system',sql)
df1['DATA_LENGTH']=df1['DATA_LENGTH'].apply(lambda x:'' if str(x)=='None' else x)
df1['TABLE_COMMENTS']=df1['TABLE_COMMENTS'].apply(lambda x:get_new_col(x))
return df1
##自动写入到doc 文档里面
def get_doc_row(df):
p_total = document.add_heading(u''+df['TABLE_COMMENTS'].iloc[0]+': '+df['OWNER'].iloc[0]+'.'+df['TABLE_NAME'].iloc[0],level=7)
table = document.add_table(1,8,style="Table Grid")
heading_cells = table.rows[0].cells
heading_cells[0].text = u'用户'
heading_cells[1].text = u'表英文名'
heading_cells[2].text = u'表中文名'
heading_cells[3].text = u'字段序号'
heading_cells[4].text = u'字段英文名称'
heading_cells[5].text = u'字段中文名称'
heading_cells[6].text = u'数据类型'
heading_cells[7].text = u'字段长度'
total = 0
for i in range(0,len(df)):
rows=table.add_row()
cells=rows.cells
cells[0].text = str(df['OWNER'][i])
cells[1].text = str(df['TABLE_NAME'][i])
cells[2].text = str(df['TABLE_COMMENTS'][i])
cells[3].text = str(df['COLUMN_ID'][i])
cells[4].text = str(df['COLUMN_NAME'][i])
cells[5].text = str(df['COLUMN_COMMENTS'][i])
cells[6].text = str(df['DATA_TYPE'][i])
cells[7].text = str(df['DATA_LENGTH'][i])
#print(len(table.rows))
if __name__ == '__main__':
#读取文件
all_df = pd.read_excel('表清单(1).xlsx', header=None)
theme_df = all_df[all_df[0] == '单一窗口']
theme_df = theme_df.reset_index()
#读取比第3列表名
table_list = theme_df[3]
#打开一个文档
document = Document()
#循环往里面写表结构
for i in range(0, len(table_list)):
df = get_one_table(theme_df[2][i], theme_df[3][i])
get_doc_row(df)
#保存的文件名
document.save(r'ff.docx')