导出mysql库的中所有表的结构信息

#coding: utf-8
#author: Alien
#date  : 2019年1月17日

import pymysql, re, xlsxwriter, os

ROW_IDX = 0

class MySQLHelper(object):

    def __init__(self, host, port, user, passwd, db_name=None):
        
        self.conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd,
                        db=db_name, cursorclass = pymysql.cursors.DictCursor, autocommit=True)
        self.cur = self.conn.cursor()

    def __del__(self):
        self.cur.close()
        self.conn.close()
        
    def execute(self, sql):
        self.cur.execute(sql)
        res = self.cur.fetchall()
        if not res: res = []
        return res

class ExportTableInfo(object):

    def __init__(self, host, port, user, passwd, db_name):
        self.db = MySQLHelper(host, port, user, passwd, db_name)
        self.we = WriteExcel('%s.xlsx' % db_name)
        self.add_title(['表名', '表注释', '字段名', '字段类型', '字段注释'])
        
        table_names = self.get_all_table_name()
        
        all_table_info_list = []
        for table_name in table_names:
            sql = self.get_create_table_sql(table_name)
            all_table_info_list.append(self.get_comment(sql))
            print('从数据库读取 "%s.%s"的表结构成功!' % (db_name, table_name))
            
        for table_info in all_table_info_list:
            for field_dict in table_info['field']:
                desc = field_dict['desc'] if 'desc' in field_dict else ''
                self.add_field_info(['', '', field_dict['name'], field_dict['type'], desc])
                
            desc = table_info['desc'] if 'desc' in table_info else ''
            print('开始写入表信息: %s %s' % (table_info['name'], desc))
            self.add_table_name_desc(table_info['name'], desc, table_info['field_count'])
            
        self.we.save()
        
    def add_title(self, rows):
        font_format = {
            'bold':  True,    #字体加粗
            'bg_color': 'yellow',
            'font_size' : 14,
            'font_name' : '微软雅黑'
        }
        
        self.we.write_rows(rows, font_format)
        
    def add_table_name_desc(self, name, desc, count):
        font_format = {
            'font_color': 'red',   #单元格背景颜色
            'font_size' : 13,
            'font_name' : '微软雅黑',
            'align' : 'center',#水平居中
            'valign' :'vcenter',#垂直居中
        }

        end_row = ROW_IDX - 1
        start_row = ROW_IDX - count
        cell_format = self.we.create_format(font_format)
        self.we.merge_range(start_row, 0, end_row, 0, name, cell_format)
        self.we.merge_range(start_row, 1, end_row, 1, desc, cell_format)

    def add_field_info(self, rows):
        font_format = {
            'font_size' : 13,
            'font_name' : '微软雅黑'
        }
        
        self.we.write_rows(rows, font_format)

    def get_all_table_name(self):
        dict_list = self.db.execute('show tables')
        table_names = []
        for dict_items in dict_list:
            for key in dict_items:
                table_names.append(dict_items[key])
        return table_names
    
    def get_create_table_sql(self, table_name):
        res = self.db.execute('show create table %s' % table_name)
        create_table_sql = res[0]['Create Table']
        return create_table_sql
        
    def get_comment(self, create_table_sql):
        table_dict = {}
        field_list = []
        field_count = 0
        
        for line in create_table_sql.split('\n'):
            if 'CREATE TABLE' in line:
                table_dict['name'] = re.compile('`(.*)`').findall(line)[0]
            
            elif re.compile("COMMENT=\'(.*)'").search(line):
                table_dict['desc'] = re.compile("COMMENT=\'(.*)'").findall(line)[0]
                
            elif re.compile('^`').search(line.strip()):
                
                field_dict = {}
                line = line.replace(',', ' ,')
                field_match_list = re.compile('\s*(.*?)\s').findall(line)
                
                field_dict['name'] = field_match_list[0].replace('`', '')
                field_dict['type'] = field_match_list[1]
                if 'COMMENT' in field_match_list:
                    comment_idx = field_match_list.index('COMMENT') + 1
                    field_dict['desc'] = field_match_list[comment_idx].replace("'", '')
                field_list.append(field_dict)
                field_count += 1
        
        table_dict['field'] = field_list
        table_dict['field_count'] = field_count
        
        return table_dict    
            
class WriteExcel():
    def __init__(self, file_name):
        if os.path.exists(file_name):
            os.remove(file_name)
        self.workbook = xlsxwriter.Workbook(file_name)
        
        self.worksheet = self.workbook.add_worksheet('sheet 1')
        self.row_num = 0
        
        self.file_name = file_name
        self.max_col_num = 0
        
    #格式
    def create_format(self, format_dict):
        new_format = self.workbook.add_format(format_dict)
        return new_format
        
    #合并单元格
    def merge_range(self, first_row, first_col, last_row, last_col, data, cell_format=None):
        self.worksheet.merge_range(first_row, first_col, last_row, last_col, data,  cell_format)
        
    #写一行
    def write_rows(self, rows=[], format_dict=None):
        global ROW_IDX
        
        col_num = len(rows)
        if col_num != 0:
            for col_idx in range(0, col_num):
                text = rows[col_idx]
                if not text: continue
                self.worksheet.write(ROW_IDX, col_idx, text, self.create_format(format_dict))
                
        if col_num > self.max_col_num: self.max_col_num = col_num
        
        ROW_IDX += 1
    
    def save(self):
        self.worksheet.set_column(0, self.max_col_num, 40)   #设置单元格宽度
        self.workbook.close()
        print('写入成功')
        
if __name__ == '__main__':
    ExportTableInfo('localhost', 3306, 'root', '', 'test')