MYSQL导出 word格式所有表结构

网上看到几个都是单表导出,而且还要手工输入表名,表注释,我优化了一下,可以批量导出所有表结构

安装2个依赖

pip install pymysql

pip install python-docx

# 导包
import pymysql
from docx import Document
from docx.enum.table import WD_CELL_VERTICAL_ALIGNMENT
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT
from docx.oxml import parse_xml
from docx.shared import Pt, RGBColor
from docx.oxml.ns import qn, nsdecls
 
# 读取或者创建文件
document = Document()       # 新建文档

# 如下配置换成自己的数据库
host = "localhost"
db="test"
user="test"
password = "test123"
 
def load_data_from_mysql(table_name):
    conn = pymysql.connect(host=host,
                           port=3306,
                           user=user,
                           password=password,
                           db=db,
                           charset="utf8")
    cursor = conn.cursor()
    sql = "SELECT UPPER(COLUMN_NAME) 字段名, COLUMN_COMMENT 字段名称, DATA_TYPE 类型, " \
          "(CASE WHEN DATA_TYPE = 'float' OR DATA_TYPE = 'double' OR DATA_TYPE = 'TINYINT' OR DATA_TYPE = 'SMALLINT' OR DATA_TYPE = 'MEDIUMINT' " \
          "OR DATA_TYPE = 'INT' OR DATA_TYPE = 'INTEGER'  OR DATA_TYPE = 'decimal' OR DATA_TYPE = 'bigint' THEN NUMERIC_PRECISION " \
          "ELSE CHARACTER_MAXIMUM_LENGTH END )  长度,  " \
          "CASE  WHEN IS_NULLABLE = 'YES'  THEN '是' ELSE '否' END 空否, CASE WHEN column_key = 'PRI' THEN '是' ELSE '' END  主键,'' 说明 " \
          "FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema ='"+db+"' AND table_name  = '{}'".format(table_name)
    print('执行sql：',sql)
    cursor.execute(sql)
    result = cursor.fetchall()
    return result

def load_tables():
    sql = "select TABLE_NAME ,TABLE_COMMENT  from INFORMATION_SCHEMA.TABLES t where TABLE_SCHEMA = '"+db+"'"  
    conn = pymysql.connect(host=host,
                           port=3306,
                           user=user,
                           password=password ,
                           db=db,
                           charset="utf8")
    cursor = conn.cursor()  
    print('执行sql：',sql)
    cursor.execute(sql)
    result = cursor.fetchall()
    return result
 
'''
总的大标题部分
'''
# 第二种设置标题的方式，此方式还可以设置文本的字体、颜色、大小等属性
run = document.add_heading("", level=1).add_run("表结构")
# 设置西文字体
run.font.name = u'宋体'
# 设置中文字体
run._element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
# 设置字体颜色
run.font.color.rgb = RGBColor(0,0,0)  # 黑色
# 设置字体大小
run.font.size = Pt(30)
# 设置下划线
# run.font.underline = True
# 设置删除线
# run.font.strike = True
# 设置加粗
run.bold = True
# 设置斜体
# run.italic = True
 
# 设置正文全局字体
document.styles['Normal'].font.name = u'宋体'
document.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')

tuple0 = load_tables()

for item in tuple0:
    # 查询sql获得二维元组
    tuple1 = load_data_from_mysql(item[0])
    # 二维元组转化为二维列表 ,且将其他类型转化为字符串 且将None转化为 ''
    table_data_list = list(list([( '' if it is None else str(it)) for it in items]) for items in list(tuple1))
    # 写入小标题
    title = item[0] + '   ' + item[1];
    document.add_heading(title, level=3)
    # 表格标题
    table_name = ['字段名', '字段名称', '类型', '长度', '空否', '主键', '说明']
    # 创建表格行列
    table = document.add_table(rows=len(table_data_list)+1, cols=len(table_name),style='Table Grid')
    # 首行设置背景色
    rows = table.rows[0]
    for cell in rows.cells:
        shading_elm = parse_xml(r'<w:shd {} w:fill="D9D9D9"/>'.format(nsdecls('w')))
        cell._tc.get_or_add_tcPr().append(shading_elm)
    # 写入表格标题
    for i in range(len(table_name)):
        cell = table.cell(0, i)
        cell.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.CENTER  # 水平居中
        cell.paragraphs[0].add_run(table_name[i])
        cell.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中
 
    # 写入表格内容
    for i in range(len(table_data_list)):
        for j in range(len(table_name)):
            table.cell(i+1, j).text = table_data_list[i][j]
 
 
# 4
document.save("database.docx")