概述
使用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下所有表的表结构: