项目交付离不开项目数据库字典文档。下面用python轻松生成交付文档字典。
? 生成doc数据库字典python代码:
import docx
import os
from docx import Document #导入库 python-docx
import pymysql
def exportDbInfoWord (path):
host = 'xx.xx.xx.xx'
connect = pymysql.connect(host=host, user='user', password='xxxxx', db='information_schema')
db ='dbname'
cur = connect.cursor()
cur.execute("""SELECT
-- T.TABLE_SCHEMA AS '数据库名称',
T.TABLE_NAME AS '表名',
-- T.TABLE_TYPE AS '表类型',
-- T. ENGINE AS '数据库引擎',
-- C.ORDINAL_POSITION AS '字段编号',
C.COLUMN_NAME AS '字段名',
C.COLUMN_TYPE AS '数据类型',
C.IS_NULLABLE AS '允许为空',
-- C.COLUMN_KEY AS '键类型',
-- C.EXTRA AS '自增属性',
-- C.CHARACTER_SET_NAME AS '编码名称',
C.COLUMN_COMMENT AS '字段说明',
T.table_comment AS '表名注释'
FROM
COLUMNS C
INNER JOIN TABLES T ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
WHERE T.TABLE_SCHEMA = '"""+db+"' ")
document = Document()
# rowcount = cur.rowcount
i = 1
v_table_name = '表名称'
for u in cur:
if (v_table_name != u[0]):
i = 1
v_table_name = u[0]
# document.add_heading('表:dg_ad ', 0) #插入标题
document.add_paragraph()
p = document.add_paragraph('表: ') # 插入段落
p.add_run(v_table_name + '(' + u[5] + ')').bold = True
# p.add_run('italic.').italic = True
table = document.add_table(rows=1, cols=4, style='Table Grid')
# table.cell(0,0).text='字段名'
run = table.cell(0, 0).paragraphs[0].add_run('字段名')
run.font.name = '宋体'
run.bold = True # 加粗
# table.cell(0,1).text='数据类型'
run = table.cell(0, 1).paragraphs[0].add_run('数据类型')
run.font.name = '宋体'
run.bold = True # 加粗
# table.cell(0,2).text='允许为空'
run = table.cell(0, 2).paragraphs[0].add_run('允许为空')
run.font.name = '宋体'
run.bold = True # 加粗
# table.cell(0,3).text='字段说明'
run = table.cell(0, 3).paragraphs[0].add_run('字段说明')
run.font.name = '宋体'
run.bold = True # 加粗
table.add_row()
table.cell(i, 0).text = u[1]
table.cell(i, 1).text = u[2]
table.cell(i, 2).text = u[3]
table.cell(i, 3).text = u[4]
i = i + 1
document.save(path)
if __name__ == '__main__':
path = 'D:/数据库字典.docx'
exportDbInfoWord(path);
print('完成')