Python实现Mysql数据字典导出

概述

        使用Python实现导出Mysql整库表或选定部份表，导出文件为Excel，包含一个目录记录表信息，各个单表sheet，支持目录超链接跳转至各sheet。

       （ 源码资源已上传，搜索“Python导出Mysql数据字典（部分表或全表）”即可，如使用时有问题可私信）

        需要Python库：PyMysql，openpyxl，json

1.实现思路

        将数据库配置信息记录在db_config.json中，python脚本读取json，连接数据库，通过数个参数控制导出某数据库schema的部分表或所有表至Excel

2.具体步骤

安装PyMysql，openpyxl，json

pip install PyMysql

pip install openpyxl

pip install json

2.1 db_config.json

{
	"host": "localhost",
	"user": "admin",
	"password": "123456",
	"database": "app",
	"charset": "utf8",
	"tables": ""
}

        db_config.json中存放数据库配置信息，其中tables中存放需要导出的数据字典表（在2.2.3中详细说明），多个表示用“,”隔开，实际使用时请自行修改。

2.2 代码实现

2.2.1 Mysql数据库系统视图介绍

        Mysql的information_schema下有大量的系统视图，其中information_schema.tables记录表基本信息，information_schema.columns记录字段信息，本文将使用这两个视图进行表结构信息的关联查询。

        （1）information_schema.tables

        TABLE_SCHEMA：表schema
        TABLE_NAME：表名
        TABLE_COMMENT：表中文注释
        TABLE_TYPE：表类型（BASE TABLE代表表，VIEW标识视图）

        （2）nformation_schema.columns

        COLUMN_NAME：字段名
        COLUMN_TYPE：字段类型
        COLUMN_DEFAULT：字段默认值
        IS_NULLABLE：是否为空
        COLUMN_COMMENT：字段中文注释

2.2.2 定义变量

        （1）表信息查询sql：TABLE_INFO_SQL

# 表信息SQL
TABLE_INFO_SQL = '''select t1.TABLE_SCHEMA as TABLE_SCHEMA,
	   t1.TABLE_NAME as TABLE_NAME,
	   t1.TABLE_COMMENT as TABLE_COMMENT,
	   concat('=HYPERLINK("#"&B',num+1,'&"!A1",">>>")')  as HYPERLINK 
from (
select
	t1.TABLE_SCHEMA,
	t1.TABLE_NAME ,
	t1.TABLE_COMMENT,
	@rownum := @rownum + 1 as num
	
from information_schema.tables t1
inner join ( SELECT @rownum := 0 ) t2
on 1 = 1
where t1.table_schema = "{}"
{}
)t1
'''

使用concat函数拼接excel中的超链接函数，concat('=HYPERLINK("#"&B',num+1,'&"!A1",">>>")')  

excel中函数展示为：=HYPERLINK("#"&B2&"!A1",">>>")

        （2）字段信息查询sql：COLUMN_INFO_SQL

# 字段信息SQL
COLUMN_INFO_SQL = '''select
    @rownum := @rownum + 1 as num,
	t1.TABLE_NAME ,
	t1.TABLE_COMMENT ,
	t2.COLUMN_NAME ,
	t2.COLUMN_COMMENT,
	t2.COLUMN_TYPE ,
	t2.COLUMN_DEFAULT ,
	t2.IS_NULLABLE 	
from information_schema.tables t1
inner join information_schema.columns t2
on t1.table_schema = t2.table_schema
and t1.table_name = t2.table_name
inner join ( SELECT @rownum := 0 ) t3
on 1 = 1
where t1.table_schema = "{}"
and t1.table_name = "{}"
order by t1.TABLE_NAME ,t2.ORDINAL_POSITION 
'''

        （3）用户选择需要导出的表信息schema：schema

        （4）用户选择到导出模式：export_mode（all：导出所选schema下所有表，several：导出所选schema中指定表，即db_config.json中tables中内容）

2.2.3 定义方法

        （1）获取json数据：get_json_info()

def get_json_info():
    with open('db_config.json', 'r', encoding='utf8') as json_data:
        db_info = json.loads(json_data.read())
    return db_info

        （2）连接数据库：conn_mysql(db_info)

def conn_mysql(db_info):
    db = pymysql.connect(host=db_info['host'],
                         user=db_info['user'],
                         password=db_info['password'],
                         database=db_info['database'],
                         charset=db_info['charset'])
    return db

        （3）获取表信息清单：get_table_info(db)

def get_table_info(db, table_name):
    cursor = db.cursor()
    if table_name != "" and export_mode == 'several':
        table_list = ["'" + table + "'" for table in table_name.split(',')]
        sql = "and table_name in (" + (",".join(table_list)) + ")"
    else:
        sql = ""
    cursor.execute(TABLE_INFO_SQL.format(schema, sql))
    table_tuple = cursor.fetchall()
    return table_tuple

        table_name为db_config.json中tables，如果导出模式为several，则将tables通过“,”分割成list后在每个元素两边加上单引号，再使用join将list转为字符串，最终拼接成table_name in ('a','b')的sql段，如 and table_name in ('cust_identify_info','cust_info')

        （4）获取字段信息：get_column_info(db, table_schema, table_name)

def get_column_info(db, table_schema, table_name):
    cursor = db.cursor()
    cursor.execute(COLUMN_INFO_SQL.format(table_schema, table_name))
    column_info = cursor.fetchall()
    return column_info

        （5）创建Excel文件并新建sheet“目录”：create_file(table_tuple)

        该方法较长，不展示所有代码，这里说一下思路并展示一下代码切片，源码可搜索“Python导出Mysql数据字典（部分表或全表）”进行下载。

        另：本方法涉及到了openpyxl的使用，一些使用技巧可以自行百度（后面有时间单独开篇做介绍）

        1.创建excel后写入标题并将方法get_table_info(db)中的table_tuple写入目录sheet中

def create_file(table_tuple):
    workbook = openpyxl.Workbook()
    worksheet = workbook.active
    worksheet.title = '目录'
    # 写入标题
    worksheet.append(['序号', '表英文名', '表中文名', '超链接'])

    # 将表清单写入目录sheet并创建单独表sheet
    for item in table_tuple:
        worksheet.append(item)
        workbook.create_sheet(item[1])

        2.设置目录的样式（边框颜色字体等）

 # 格式
    fonts = Font(name=u'宋体', size=12, bold=True, color='FFFFFF')  # 字体
    fills = PatternFill(fill_type='solid', start_color='366092')  # 颜色填充
    # 边框设置
    borders = Border(left=Side(border_style='thin', color='000000'), right=Side(border_style='thin', color='000000'),
                     top=Side(border_style='thin', color='000000'), bottom=Side(border_style='thin', color='000000'))
    # 为A-D列设置宽度
    worksheet.column_dimensions['A'].width = 5.0
    worksheet.column_dimensions['B'].width = 40.0
    worksheet.column_dimensions['C'].width = 40.0
    worksheet.column_dimensions['D'].width = 8.0
    # 目录sheet 标题设置格式
    for row in worksheet['A1:D1']:
        for cell in row:
            cell.font = fonts
            cell.fill = fills
            cell.border = borders
    # 保存文件
    workbook.save('table_info.xlsx')

目录最终呈现效果：

        （6）创建各表sheet：write_table_info(table_name, column_info)

        主要思路为：

        打开步骤（5）中创建excel，通过接收到的参数table_name和column_info（通过方法get_column_info获取），将生成对应的“table_name”sheet，并将column_info的内容写入各自的sheet中，并设置格式，切片如下：

def write_table_info(table_name, column_info):
    wb = openpyxl.load_workbook('table_info.xlsx')
    ws = wb[table_name]
    # 写入标题和字段信息
    ws.append(['序号', '表英文名', '表中文名', '字段英文名', '字段中文名', '字段类型', '默认值', '允许为空'])
    for line in column_info:
        ws.append(line)
# 边框设置
    borders = Border(left=Side(border_style='thin', color='000000'),         right=Side(border_style='thin', color='000000'),
                     top=Side(border_style='thin', color='000000'), bottom=Side(border_style='thin', color='000000'))
    # 设置字段信息区域边框
    for col in range(1, 9):
        for row in range(2, len(column_info) + 2):
            ws.cell(row=row, column=col).border = borders
    wb.save('table_info.xlsx')

目录最终呈现效果：

        （7）mian方法：main()

def main():
    if __name__ == '__main__':
        db_info = get_json_info()  # 数据库配置信息
        db = conn_mysql(db_info)  # 数据库对象
        table_tuple = get_table_info(db, db_info['tables'])  # 获取表信息
        create_file(table_tuple)  # 创建文件
        for item in table_tuple:  # 写入字段信息
            column_info = get_column_info(db, item[0], item[1])
            print(column_info)
            write_table_info(item[1], column_info)

3. 脚本拓展

        本文之所以采用db_config.json的形式存储数据库信息，是考虑到json格式的规范性和优秀的可读性，此外也考虑到将脚本通过pyinstaller打包成可执行文件exe（主要考虑到PyMysql，openpyxl，json等库都需要额外安装，对于大部分非互联网环境开发的项目来说不是很友好，此外打包成exe后非开发人员在简单的操作指引下也可自行导出数据字典），这样脚本进行了封装，而后续数据库密码等信息如修改，则只需要修改db_config.json即可

3.1 安装pyinstaller

        联机状态下进入cmd输入pip install pyinstaller

3.2 打包成exe

        进入脚本所在目录，pyinstaller -F export_table_info_mysql.py，将在dist目录下生成export_table_info_mysql.exe：

3.3 执行exe

        进入export_table_info_mysql.exe所在目录，同时将db_config.json复制到该目录下，进入cmd执行export_table_info_mysql.exe app several将导出schema为app下所选表的表结构，如果输入export_table_info_mysql.exe app all则导出schema为app下所有表的表结构：