日常记录：python3 生成word数据库文档，并使用ssh代理连接数据库

因为有个非自研项目，需要导出数据表说明

requirements.txt

mysql-connector-python==8.0.25
python-docx==0.8.11
sshtunnel==0.4.0

代码

# -*- coding:utf-8 -*-

import mysql.connector
from docx import Document
from sshtunnel import SSHTunnelForwarder

doc_path = './doc.docx'
doc = Document()
doc.styles['Normal'].font.name = u'宋体'

def to_doc(rows, table):

    # 标题
    doc.add_heading(table + '表', 3)
    t = doc.add_table(rows=1, cols=6, style='Table Grid')
    head_cells = t.rows[0].cells
    for cell in head_cells:
        # font = cell.paragraphs[0].style.font
        # font.bold = True
        p = cell.paragraphs[0]
        run = p.add_run('dcolor')
        run.font.bold = True

    header = ['字段名称', '字段名', '数据类型', '是否可空', '默认值', '备注']
    for i in range(len(header)):
        head_cells[i].text = header[i]

    # 内容
    for r in rows:
        row = t.add_row().cells
        cols = [r[8], r[0], r[1], r[3], r[4], r[8]]
        for i in range(len(cols)):
            # row[i].paragraphs[0].style.font.bold = False
            row[i].text = cols[i] if cols[i] else ''

    # doc.add_page_break()
    doc.save(doc_path)
    
# 使用ssh 代理连接数据库,获取DDL
# ssh_address  ssh 跳板机
# ssh_port  ssh跳板机端口
# remote_address 数据库server 
def main():
    with SSHTunnelForwarder((ssh_address, ssh_port) ,ssh_password= '123456' , ssh_username='root',remote_bind_address=(remote_address,3306)) as server:
        # 数据库连接 host 必须为 127.0.0.1
        db_connect =  mysql.connector.connect(host='127.0.0.1',  
                                 port=server.local_bind_port,
                                 user='root',
                                 passwd='123456',
                                 database='db')
        try:
            with db_connect.cursor() as cursor:
                sql = 'show tables'
                cursor.execute(sql)
                table_list = cursor.fetchall()
                for t in table_list:
                    sql = 'show full columns from '+ t[0]
                    cursor.execute(sql)
                    rows = cursor.fetchall()
                    to_doc(rows, t[0])
        finally:
            db_connect.close()
                
#mycursor = conn.cursor()

if __name__ == "__main__":
    main()

不好看！！！！