（超实用）导出数据字典（python-mysql-docx）

最新推荐文章于 2023-11-01 14:28:16 发布

Marst·Developer

最新推荐文章于 2023-11-01 14:28:16 发布

阅读量928

点赞数 1

分类专栏：开发文档文章标签： python mysql doc

本文链接：https://blog.csdn.net/marst437730201/article/details/121489161

版权

开发文档专栏收录该内容

5 篇文章 0 订阅

订阅专栏

前言

平时存储完数据后，常常要为用户提供数据字典，表数量少还可以手动整理一下，超过50+，人手就处理不过来了？

一、思路

从mysql查询得到表结构信息;
创建docx,创建表，设置标题和表格,并把表信息逐个存入;
给表格润润色，设置样式;
保存docx文档.

二、实战步骤

1.查询库表信息

show databases;

①获取数据库里所有的表清单

这里是需要TABLE_NAME,TABLE_COMMENT （英文表名和中文表名）

SELECT
	TABLE_NAME,
	TABLE_COMMENT 
FROM
	INFORMATION_SCHEMA.TABLES 
WHERE
	table_schema = '库名'

**INFORMATION_SCHEMA.TABLES 提供的全部信息**

②查询表中所有字段和注释

SELECT
	column_name,
	column_comment 
FROM
	information_schema.COLUMNS 
WHERE
	table_name = '表名' 
	AND table_schema = '库名';

2. 创建docx并写入表字段信息

import os
import pymysql
from docx import Document
from docx.shared import Pt
from docx.oxml.ns import qn
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT

def test_doc():
    document = Document()
    document.add_heading('Document Title', 0)

    p = document.add_paragraph('A plain paragraph having some ')
    p.add_run('bold').bold = True
    p.add_run(' and some ')
    p.add_run('italic.').italic = True

    document.add_heading('Heading, level 1', level=1)
    document.add_paragraph('Intense quote', style='Intense Quote')

    document.add_paragraph(
        'first item in unordered list', style='List Bullet'
    )
    document.add_paragraph(
        'first item in ordered list', style='List Number'
    )

    # document.add_picture('monty-truth.png', width=Inches(1.25))
    records = (
        (3, '101', 'SpamHAHa'),
        (7, '422', 'Eggs'),
        (4, '631', 'Spam, spam, eggs, and spam')
    )

    tb_styles = [
        "Normal Table",
        "Table Grid",
        "Light Shading",
        "Light Shading Accent 1",
        "Light Grid",
        "Light Grid Accent 1",
        "Medium Shading 1",
        "Medium Shading 2 Accent 1",
        "Medium List 1",
        "Medium List 1 Accent 1",
        "Medium List 2",
        "Medium Grid 3 Accent 1",
        "Colorful Shading Accent 1",
        "Colorful List",
        "Colorful List Accent 2",
        "Colorful Grid Accent 5",
        "Dark List"
    ]

    for s in tb_styles:
        document.add_paragraph(s, style='Intense Quote')
        table = document.add_table(rows=1, cols=3)
        table.style = s

        # 对第一行进行赋值标题
        hdr_cells = table.rows[0].cells
        hdr_cells[0].text = 'Qty'
        hdr_cells[0].color = 'red'
        hdr_cells[1].text = 'Id'
        hdr_cells[2].text = 'Desc'
        for qty, id, desc in records:
            row_cells = table.add_row().cells
            row_cells[0].text = str(qty)
            row_cells[1].text = id
            row_cells[2].text = desc

        document.add_paragraph('')

    document.add_page_break()

    save_folder = r"C:\test"
    doc_path = os.path.join(save_folder, f"test.docx")
    print(doc_path)
    document.save(doc_path)

三、实例

# -*- coding: utf-8 -*-
# !/usr/bin/python3
# author : Marst.gy

"""
Task : 
    
steps:
    step1:
    step2:

"""
import sys
import os
import pymysql
from docx import Document
from docx.shared import Pt
from docx.oxml.ns import qn
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT


def mysql_conn(host, user, password, db, port=3306, charset="utf8"):
    import pymysql
    conn = pymysql.connect(host=host, user=user, password=password, database=db, port=port, charset=charset)
    return conn


def tb_format_base(doc, order, tb_name_code, tb_name):
    """

    :param doc: 文档
    :param order: 序号
    :param tb_name: 表中文名称
    :param tb_name_code: 表英文名称
    :return:
    """
    table_explain_sub1 = f"{order}.1 基本信息"
    sub = doc.add_heading(table_explain_sub1, level=2)

    table = doc.add_table(rows=3, cols=2)
    tabBgColor(table, 3, 'C7D9F1', isRow=True, isCol=False)
    table.style = 'TableGrid'  # 'LightShading-Accent1'

    table.cell(0, 0).text = "表英文名称"
    table.cell(1, 0).text = "表中文名称"
    table.cell(2, 0).text = "备注"

    table.cell(0, 1).text = tb_name_code
    table.cell(1, 1).text = tb_name
    table.cell(2, 1).text = ""


def tb_format_tb_info(doc, order, tableColumnInfoList):
    """
    表说明
    :param doc: 文档
    :param order: 序号
    :param tableColumnInfoList: 表结构信息
    :return:
    """
    # 创建表2  表说明
    table_explain_sub2 = f"{order}.2 表说明"
    sub = doc.add_heading(table_explain_sub2, level=2)

    table = doc.add_table(rows=1, cols=5)
    tabBgColor(table, 5, 'C7D9F1')
    table.style = 'TableGrid'  # 'LightShading-Accent1'   'TableGrid'

    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 tableColumn in tableColumnInfoList:
        new_cells = table.add_row().cells
        COLUMN_NAME = tableColumn["COLUMN_NAME"]
        COLUMN_COMMENT = tableColumn["COLUMN_COMMENT"]
        if COLUMN_NAME == "id":
            COLUMN_COMMENT = "id"
        new_cells[0].text = COLUMN_NAME
        new_cells[1].text = COLUMN_COMMENT
        new_cells[2].text = tableColumn["COLUMN_TYPE"]
        new_cells[3].text = tableColumn["EXTRA"]
        new_cells[4].text = tableColumn["GENERATION_EXPRESSION"]

    doc.add_page_break()


def add_tb(doc, order, tb_name_code, tb_name, tableColumnInfoList):
    """
    :param doc:
    :param order:
    :param tb_name_code:
    :param tb_name:
    :param tableColumnInfoList:
    :return:
    """
    p = doc.add_paragraph('')
    table_explain = f"{order}﹑ {tb_name} "
    para_heading = doc.add_heading('', level=1)
    # title = p.add_run(table_explain, style="Heading 1 Char")
    run = para_heading.add_run(table_explain)
    run.size = Pt(15)

    # 基本信息
    tb_format_base(doc, order, tb_name_code, tb_name)
    # 表说明
    tb_format_tb_info(doc, order, tableColumnInfoList)


def tabBgColor(table, title_length, colorStr, isCol=True, isRow=False):
    """

    :param table: 表格实例
    :param title_length: 标题长度
    :param colorStr: 底色编码
    :param isCol: 是否横标题加底色 默认不加
    :param isRow: 是否竖标题加底色 默认不加
    :return:
    """
    shading_list = locals()
    from docx.oxml import parse_xml
    from docx.oxml.ns import nsdecls

    if isCol:
        for i in range(title_length):
            shading_list['shading_elm_' + str(i)] = parse_xml(
                r'<w:shd {} w:fill="{bgColor}"/>'.format(nsdecls('w'), bgColor=colorStr))
            table.rows[0].cells[i]._tc.get_or_add_tcPr().append(shading_list['shading_elm_' + str(i)])

    if isRow:
        for i in range(title_length):
            shading_list['shading_elm_' + str(i)] = parse_xml(
                r'<w:shd {} w:fill="{bgColor}"/>'.format(nsdecls('w'), bgColor=colorStr))
            table.rows[i].cells[0]._tc.get_or_add_tcPr().append(shading_list['shading_elm_' + str(i)])


def to_doc(title_info, write_content, doc_path=None):
    """

    :param title_info: 正标题
    :param write_content:  写入表结构信息
    {
    "tableName": "表英文名",
     "tableComment": "表中文名",
     "tableColumnInfoList": "字段说明"}

    :param doc_path: 存储文件路径
    :return:
    """
    doc = Document()
    doc.styles["Normal"].font.name = u"Times New Roman"  # 设置全局字体
    doc.styles["Normal"]._element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')  # 设置中文字体
    doc.styles["Normal"].font.size = Pt(10)

    doc.styles["Heading 1"].font.size = Pt(15)  # 设置一级标题大小
    doc.styles["Heading 2"].font.size = Pt(13)  # 设置二级标题大小

    ml = doc.add_heading(title_info, level=1)
    ml.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER  # 位置居中

    for order, tb_info in enumerate(write_content, 1):
        tableColumnInfoList = tb_info.get("tableColumnInfoList")
        tableName = tb_info["tableName"]
        tableComment = tb_info["tableComment"]
        add_tb(doc, order, tableName, tableComment, tableColumnInfoList)

    print(doc_path)
    doc.save(doc_path)


def get_data_from_mysql(schema, cursor, ignore_tb_list=None):
    """
   
    :param schema: 数据库名
    :param cursor:
    :param ignore_tb_list: list 跳过不需要的表结构信息
    :return:
    """
    sql = f"select table_name,table_comment from information_schema.tables where TABLE_SCHEMA = '{schema}'"
    cursor.execute(sql)
    tableInfoList = cursor.fetchall()

    write_content = []
    for order, tableInfo in enumerate(tableInfoList, 1):
        tableName = tableInfo["table_name"]
        tableComment = tableInfo["table_comment"]

        if ignore_tb_list and tableName in ignore_tb_list:
            continue

        tableInfoSql = f"SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = '{schema}' AND table_name = '{tableName}'"
        cursor.execute(tableInfoSql)
        tableColumnInfoList = cursor.fetchall()
        if tableColumnInfoList:
            # 设置大标题
            write_content.append(
                {
                    "tableName": tableName,
                    "tableComment": tableComment,
                    "tableColumnInfoList": tableColumnInfoList
                }
            )
    return write_content

def data_struc_func(schema, write_content):
    """
    添加目录: docx在生成后， 引用 - > 目录  可生成

    :param schema:
    :param cursor:
    :return:
    """

    title_info = "数据库说明"
    save_folder = r"存储文件夹"

    doc_path = os.path.join(save_folder, f"{schema}.docx")
    print(doc_path)
    to_doc(title_info, write_content, doc_path)

def run():
    """
    :return:
    """


    schema = '数据库名称'
    SQLConnInfo = {
        "IP": "172.100.10.xx",
        "user": "root",
        "pwd": "123456",
        "port": 3306,
        "db": schema
    }


    conn = mysql_conn(SQLConnInfo["IP"], SQLConnInfo["user"], SQLConnInfo["pwd"],
                      SQLConnInfo["db"])
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    # 获取数据库表结构信息
    write_content = get_data_from_mysql(schema, cursor)    
    # 写入doc
    data_struc_func(schema, write_content)

    cursor.close()
    conn.close()


def test_doc():
    document = Document()
    document.add_heading('Document Title', 0)

    p = document.add_paragraph('A plain paragraph having some ')
    p.add_run('bold').bold = True
    p.add_run(' and some ')
    p.add_run('italic.').italic = True

    document.add_heading('Heading, level 1', level=1)
    document.add_paragraph('Intense quote', style='Intense Quote')

    document.add_paragraph(
        'first item in unordered list', style='List Bullet'
    )
    document.add_paragraph(
        'first item in ordered list', style='List Number'
    )

    # document.add_picture('monty-truth.png', width=Inches(1.25))
    records = (
        (3, '101', 'SpamHAHa'),
        (7, '422', 'Eggs'),
        (4, '631', 'Spam, spam, eggs, and spam')
    )

    tb_styles = [
        "Normal Table",
        "Table Grid",
        "Light Shading",
        "Light Shading Accent 1",
        "Light Grid",
        "Light Grid Accent 1",
        "Medium Shading 1",
        "Medium Shading 2 Accent 1",
        "Medium List 1",
        "Medium List 1 Accent 1",
        "Medium List 2",
        "Medium Grid 3 Accent 1",
        "Colorful Shading Accent 1",
        "Colorful List",
        "Colorful List Accent 2",
        "Colorful Grid Accent 5",
        "Dark List"
    ]

    for s in tb_styles:
        document.add_paragraph(s, style='Intense Quote')
        table = document.add_table(rows=1, cols=3)
        table.style = s
        # tabBgColor(table, 3, 'C7D9F1')
        hdr_cells = table.rows[0].cells
        hdr_cells[0].text = 'Qty'
        hdr_cells[0].color = 'red'
        hdr_cells[1].text = 'Id'
        hdr_cells[2].text = 'Desc'
        for qty, id, desc in records:
            row_cells = table.add_row().cells
            row_cells[0].text = str(qty)
            row_cells[1].text = id
            row_cells[2].text = desc

        document.add_paragraph('')

    document.add_page_break()

    save_folder = r"C:\TEST"
    doc_path = os.path.join(save_folder, f"test.docx")
    print(doc_path)
    document.save(doc_path)


if __name__ == '__main__':
    test_doc()

    run()

总结

无论从表结构信息的获取和写入doc，都可以用程序去实现。最后，还有点小瑕疵，目录无法用程序实现（主要是从目录进行跳转的那个功能）,只能手动点击.

生成目录: ‘引用’ -> '目录' -> 生成目录.

Marst·Developer

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
（超实用）导出数据字典（python-mysql-docx）

文章目录前言一、思路二、实战步骤 1.引入库 2.读入数据总结前言平时存储完数据后，常常要为用户提供数据字典，表数量少还可以手动整理一下，超过50+，这还是人干的？一、思路从mysql查询得到表结构信息; 创建docx,创建表，设置标题和表格,并把表信息逐个存入; 给表格润润色，设置样式; 保存docx文档.二、实战步骤1.2.读入数据代码如下（示例）：data = pd.read_csv( 'https:...
复制链接

扫一扫