Python 生成MySQL数据库的表结构到word文档

原理：读取系统表的数据，调用python-docx库生成word文档。

import pymysql
from docx import Document
from docx.shared import Inches


document = Document()
document.add_heading('数据库表结构', 0)

conn = pymysql.connect(host='192.168.1.17', user='root', passwd="password", db='db1')
cur = conn.cursor()
cur.execute('''SELECT
                    t.table_schema,
                    t.table_name,
                    t.column_name,
                    t.column_type,
                    t.is_nullable,
                    t.column_default,
                    t.column_comment     
                FROM
                    INFORMATION_SCHEMA.COLUMNS t 
                WHERE table_schema in('db1')
                ORDER BY table_schema,table_name''')
col_list = cur.fetchall()

db_tb_list = []
for table_schema,table_name,column_name,column_type, is_nullable,column_default,column_comment  in col_list: 
    result = [r for r in db_tb_list if r[0] == table_schema and r[1] == table_name]
    if(len(result) == 0):  
        db_tb_list.append((table_schema,table_name))

db_tmp = ''
for db,tb in db_tb_list:
    if (db_tmp != db):
        document.add_heading('数据库' + db, 1)
    db_tmp = db
    document.add_heading('表' + db + '.' + tb, 5)
 
    this_table = [r for r in col_list if r[0] == db and r[1] == tb]
    #添加表格：
    # 表格样式参数style可选：
    # Normal Table
    # Table Grid
    # Light Shading、 Light Shading Accent 1 至 Light Shading Accent 6
    # Light List、Light List Accent 1 至 Light List Accent 6
    # Light Grid、Light Grid Accent 1 至 Light Grid Accent 6
    # 其它省略...  
    table = document.add_table(rows=1, cols=5, style='Light Grid')
    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 table_schema,table_name,column_name,column_type, is_nullable,column_default,column_comment  in this_table:
        row_cells = table.add_row().cells       
        row_cells[0].text = column_name
        row_cells[1].text = column_type
        row_cells[2].text = '是' if is_nullable is 'YES' else '否'
        row_cells[3].text = '' if column_default is None else column_default
        row_cells[4].text = column_comment

document.save('数据库表结构.docx')

cur.close()
conn.close()