提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
代码目的
通过与数据库进行交互,读取需要表格的字段名等属性信息。
使用的库
pymysql,docx
代码
#!/usr/bin/env python
# -*- coding: UTF-8 -*-
'''
@Project :Python项目
@File :2-数据字典处理.py
@IDE :PyCharm
@Author :Pan Youxuan
@Date :2023/4/10 11:04
@Descib :
'''
import pymysql
import datetime
from docx import Document
db_info = {'user': 'user',
'password': "password",
'host': '111.111.111.111',
'port': 3306,
'database': 'data'}
add_Table = ['sch_vessel_schedule','sch_vy_task_bak','sch_yard_slots_bak','sch_yv_task_bak']
def link(): # 链接数据库
# host=input("请输入主机名:")
global dblink
global dbin
global dname
dblink = pymysql.connect(host=db_info["host"], user=db_info["user"], password=db_info["password"],
db=db_info['database'], port=db_info["port"])
# host='localhost' 默认为当前主机
dbin = dblink.cursor() # 获取游标,游标是系统为用户开设的一个数据缓冲区,存放sql语句的执行结果
def closelink(): # 关闭链接
dbin.close()
dblink.close()
if __name__ == "__main__":
link()
document = Document()
for table_name in add_Table:
sql = 'SELECT COLUMN_NAME 英文名,COLUMN_DEFAULT 中文名,COLUMN_TYPE 数据类型,CHARACTER_MAXIMUM_LENGTH 长度,COLUMN_COMMENT 备注' + \
' FROM INFORMATION_SCHEMA.COLUMNS' + ' WHERE table_schema ="smarttos_prod" AND table_name ="{}"'.format(table_name)
dbin.execute(sql)
# dblink.commit() 只有当执行插入(insert)、更新(update)、删除(delete)等对数据库修改的语句时,才需要提交事务(commit)
records = dbin.fetchall() # 获取查询结果
print(records)
print('---------------------------------')
h1 = document.add_heading(level =1)
run =h1.add_run(table_name)
# 添加表格,rows设置行 cols设置列
table = document.add_table(rows=1, cols=5)
hdr_cells = table.rows[0].cells
# 设置列名
hdr_cells[0].text = '英文名'
hdr_cells[1].text = '中文名'
hdr_cells[2].text = '数据类型'
hdr_cells[3].text = '长度'
hdr_cells[4].text = '备注'
# 操作写入行
for id, china_id, type, length, cord in records:
row_cells = table.add_row().cells
row_cells[0].text = id
try:
row_cells[1].text = china_id
except:
row_cells[1].text = '0'
try:
row_cells[2].text = type
except:
row_cells[2].text = '0'
try:
row_cells[3].text = length
except:
row_cells[3].text = '0'
try:
row_cells[4].text = cord
except:
row_cells[4].text = '0'
document.save('demo.docx')
closelink()
总结
首先通过pymysql连接数据库,之后使用docx库创建表格并填入相应数据。