import os

# SHOW TABLES like 't_score%'

# 安装python docx
cmd = 'pip install python_docx -i https://pypi.doubanio.com/simple'
os.system(cmd)

cmd = 'pip install pymysql  -i https://pypi.doubanio.com/simple'
os.system(cmd)

cmd = 'python -m pip install --upgrade pip -i https://pypi.doubanio.com/simple'
os.system(cmd)

import docx
from util.MysqlHelper import *
from docx.enum.style import WD_STYLE_TYPE
from docx import Document
from docx.shared import Cm, Pt, RGBColor
from docx.enum.table import WD_TABLE_ALIGNMENT

# 数据库
host = '10.10.14.187'
port = 22066
database = 'huanghai_test'
user = 'root'
password = 'DsideaL147258369'

# 实例化mysql数据库实例
db = MySQLHelper(host, port, database, user, password)
# 创建一个文档
doc = docx.Document()

tables = []
# 读取哪些表
with open('BackupMysqlTables.txt', 'r') as f:
    tables = f.readlines()

for t in tables:
    t = t.replace('\n', '')
    # 添加这个表的信息到文档中
    p = doc.add_paragraph('')
    # 描述
    sql = "select table_comment from information_schema.tables  where table_schema = '%s' and table_name='%s'  order by table_name desc" % (
        database, t)

    if db.query(sql):
        table_comment = db.query(sql)[0]['table_comment']
    else:
        table_comment = '无'

    p.add_run(t + "(" + table_comment + ")", style="Heading 1 Char")

    # 数据库
    sql = "select column_name,column_comment,column_type from information_schema.columns where " \
          "table_schema ='%s' and table_name = '%s'" % (database, t)
    columns = db.query(sql)

    table = doc.add_table(rows=len(columns) + 1, cols=3, style='Colorful List Accent 4')
    i = 0

    for row in table.rows:
        # 行高
        row.height = Cm(1)
        if i > 0:
            row.cells[0].text = columns[i - 1]['column_name']
            row.cells[1].text = columns[i - 1]['column_comment']
            row.cells[2].text = columns[i - 1]['column_type']
        else:
            row.cells[0].text = '字段名'
            row.cells[1].text = '描述'
            row.cells[2].text = '数据类型'
        i = i + 1
    doc.add_paragraph('')

    # 添加数据
    sql = "select * from %s" % (t)
    dataList = db.query(sql)
    print(dataList)

# 关闭数据库
db.close()
# 保存数据库文档
doc.save('数据库结构文档.docx')
print('恭喜，文档生成成功！')