共计 7850 个字符，预计需要花费 20 分钟才能阅读完成。
自动化生成数据库文档，简略的 3 个步骤即可实现，理解一下。
1 前言
平时工作中，大家应该都会遇到须要导出数据库阐明文档（也叫数据字典）的状况，即把各数据表的字段信息整顿成一个个的表阐明，而后用 excel/word/html/md 等文档格局进行保留。很多小伙伴还在用原始的手工形式，复制粘贴数据库的字段阐明（名称、类型、长度、正文等），不得不说这种形式效率切实太低。作为程序员，能用编程解决的问题，就不是问题。上面介绍的办法很简略，只须要 3 个步骤。本文将对这 3 个步骤应用 python 进行编码实现，把数据表信息阐明输入到 excel 文档中。因而，次要蕴含以下内容：
- 生成数据库文档的 3 步骤阐明
- 获取数据库表元信息
- 获取数据表列的元信息
- 生成数据库阐明 excel 文档
- （可选）设置 excel 文档格局
2 生成数据库阐明文档的 3 步骤
因为数据库都会保留相应的元数据信息（即形容数据库、数据表、数据字段自身的信息，如表名，字段表、类型等等），因而，总的来说，生成数据库阐明文档的思路很简略，分为 3 步：
- 1）依据数据库名，从数据库中获取数据表元信息，次要是表名，表正文等
- 2）依据数据表名，获取数据字段的元信息，次要是字段名、字段类型、是否可空、字段正文等
- 3）依据元数据信息生成文档
依据这个思路，把这 3 个步骤通过编码即可主动生成文档。获取元数据信息，各种数据库会有不同的查问语句，具体能够查问相干官网文档，上面简略列一下 mysql 及 oracle 的：
# mysql 查问表信息及字段信息
SELECT * FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = %db_name%
SELECT * FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA = %db_name% AND TABLE_NAME = %table_name%
# oracle 查问表信息及字段信息
SELECT * FROM all_tables WHERE where owner= %db_name%
SELECT * FROM all_COL_COMMENTS WHERE owner = %db_name% and TABLE_NAME=%table_name%实现形式也能够依据各人喜爱的编程语言来实现。在本文中，以 MySQL 为例，应用 python 编程实现，把数据信息输入到 excel 文档（具体 excel 操作，可参考我上一篇文章《Python 解决 Excel 文件》）。输入成果如下所示：
上面就跟着我进行实现吧。
3 获取数据库表元信息
3.1 客户端 pymysql 根本应用
应用 python 进行 MySQL 读写操作，应用的是 pymysql，读者能够拜访它的官网文档理解它的装置和应用。简略来说，对数据库进行读数据，须要以下几步：
- 连贯数据库：
connect - 获取读数据的游标（cursor）：
connection.cursor() - 执行 SQL 语句获取数据：
cursor.execute(sql,args)，cursor.fetchall()，cursor.fetchone(),cursor.fetchmany() - 敞开游标和连贯：
connection.close() - 获取正在应用的工作表：
workbook.active，cursor.close()
因而，咱们在类的初始化（__init__）和敞开（__del__）时，进行数据库连贯和敞开操作。代码如下：
def __init__(self, host, port, user, password, db_name, charset):
# 初始化数据库操作
self.db = pymysql.connect(host=host, port=port, user=user,password=password, database=db_name, charset=charset)
self.cursor = self.db.cursor()
def __del__(self):
# 敞开数据库连贯
self.db.close()
self.cursor.close()3.2 获取数据库表元信息
依据下面说的 pymysql 的基本操作，只须要执行查问数据表元信息的 SQL 即可。后面曾经提到，MySQL 的查问表元信息的 SQL 语句是 SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = %db_name%，而咱们只须要表名及表正文即可。因而实现如下：
def get_table_info(self, db_name):
# 获取数据表信息
sql = '''SELECT table_schema, table_name, table_comment FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = %s order by table_name'''
params = [db_name]
# 查问数据
self.cursor.execute(sql, params)
return self.cursor.fetchall()此函数性能：传入数据库名，返回所有表信息。
4 获取数据表字段的元信息
获取到表信息后，同样的情理，须要遍历每一个表，依据表名获取每个表的字段信息。后面曾经到，MySQL 获取表的字段是查问表 information_schema.COLUMNS 即可，而对于字段信息，咱们次要关注字段名、字段类型、是否容许为空、字段的正文形容等信息。代码如下：
def get_table_column_info(self, database_name, table_name):
# 获取数据表列信息
params = [database_name, table_name]
sql = '''SELECT
TABLE_SCHEMA AS '库名',TABLE_NAME AS '表名',
COLUMN_NAME AS '列名',ORDINAL_POSITION AS '列的排列程序',
COLUMN_DEFAULT AS '默认值',IS_NULLABLE AS '是否为空',
DATA_TYPE AS '数据类型',CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
NUMERIC_PRECISION AS '数值精度 (最大位数)',NUMERIC_SCALE AS '小数精度',
COLUMN_TYPE AS '列类型',COLUMN_COMMENT AS '正文'
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
ORDER BY TABLE_NAME, ORDINAL_POSITION'''
# 查问数据
self.cursor.execute(sql, params)
return self.cursor.fetchall()此函数性能，依据数据库名及表名，获取此表的字段信息。
5 生成 excel 文档
5.1 输入表字段信息到 excel 文档
对于 excel 的操作，咱们应用 openpyxl 进行读写操作，具体 excel 操作，可参考我上一篇文章《Python 解决 Excel 文件》。而当初咱们须要实现的性能是把每个表的字段信息，以表格的形式写入到 excel 表中，并按字段名、容许为空、字段类型、字段形容进行输入。
还有一点就是，咱们常常在设计表的的过程中，根本都会有一些公共的字段，比方 id，创立工夫、创建人、批改工夫、批改人等，这些咱们在导出字典时，能够抉择过滤掉。因而，应用如下代码进行实现：
def create_file(self, file_path):
# 获取文件，若文件不存在则创立，存在则删除后从新创立
if os.path.exists(file_path):
os.remove(file_path)
wb = Workbook()
wb.save(file_path)
def save_column_info_to_excel(self, table_name, table_comment, column_info, file_path, col_names_skip):
# 写入表信息到 excel 文件
workbook = openpyxl.load_workbook(file_path)
# 依据下标获取（下标从 0 开始）sheet = workbook.worksheets[0]
row_data = [table_name]
if table_comment:
row_data = [table_name + "(" + table_comment + ")"]
sheet.append(row_data)
rurrent_max_row = sheet.max_row
# 空行分隔
sheet.insert_rows(rurrent_max_row)
# 列名
col_name_data = ["字段名", "容许为空", "类型", "字段形容"]
sheet.append(col_name_data)
for row in column_info:
# 须要过滤的
if col_names_skip and row[2].lower() in col_names_skip:
print("#" * 10, "跳过此字段：", row[2])
continue
print(row[2] + "," + row[5] + "," + row[10] + "," + row[11])
row_data = [row[2], row[5], row[10], row[11]]
sheet.append(row_data)
# 保存文档
workbook.save(file_path) 此处蕴含两个函数，create_file 性能次要是创立文档，若文档存在则先删除。save_column_info_to_excel 性能是依据表字段信息及须要过滤的字段名，应用 for 语句按行输入到 excel 中，输入过程中，若有须要过滤的字段则跳过，最初把文档保留到指定的门路中。
5.2 把各性能连接起来
后面曾经实现数据库表元信息获取、数据表字段元信息获取及字段信息输入到 excel 文档三个性能。当初把这三个性能连接起来，就能够造成残缺的数据库文档导出性能了。思路是遍历生成的表元信息 (get_table_info)，依据表元信息获取表的字段信息 (gen_table_column_info)，而后输入 excel 文档 (save_column_info_to_excel)，如下所示：
def gen_db_table_info_skip_col(self, db_name, file_path, col_names_skip):
# 过滤指定列，导出数据表信息到文档
table_info_rows = self.get_table_info(db_name)
for table_row in table_info_rows:
print("\n", "*" * 10, "生成表信息：", table_row[1])
self.gen_table_column_info(table_row, file_path, col_names_skip)
def gen_table_column_info(self, table_info_row, file_path, col_names_skip=None):
# 导出字段信息表到文档
database_name = table_info_row[0]
table_name = table_info_row[1]
table_comment = table_info_row[2]
# 从数据库获取表信息
column_info = self.get_table_column_info(database_name, table_name)
# 写入 excel 文件
self.save_column_info_to_excel(table_name, table_comment, column_info, file_path, col_names_skip) 此处蕴含两个函数，gen_db_table_info_skip_col 性能是依据数据库名、文件保留门路、须要过滤的字段名导出表元信息，而后应用 for 语句进行遍历。gen_table_column_info 是依据表信息及须要过滤的字段，先读表字段信息，而后写入到 excel 文档。留神，此处 col_names_skip 默认值为 None，即如果不须要过滤，不输出此参数即可。至此，咱们主动生成数据库文档的性能已实现。在 __main__ 中执行看一下输入状况：
if __name__ == '__main__':
# 输入文档地址
excel_path = "E:/pythontest/test_tableinfo.xlsx"
# 数据库连贯信息
host = "localhost"
port = 3306
user = "root"
password = "123456"
db_name = "test"
charset = 'utf8'
# 须要过滤的字段
col_names_to_skip = ["id", "sys_create_time", "sys_create_user", "sys_update_time", "sys_update_user", "record_version"]
# 初始化类，创立文件，生成数据库阐明文档
dbInfoGenerator = DbInfoGenerator(host, port, user, password, db_name, charset)
dbInfoGenerator.create_file(excel_path)
dbInfoGenerator.gen_db_table_info_skip_col(db_name, excel_path, col_names_to_skip)后果如下：
表的字段阐明已输入到 excel 文档中，对应的字段也已过滤。只是格局不是很难看，因而，有须要的能够用 openpyxl 对 excel 文档的格局进行设置即可。
6（可选）设置 excel 文档格局
如果须要对 excel 文档进行格局设置，以下是我的一个根本格局设置，有须要的能够参考一下，制作适宜本人的文档格局。格局的设置思路次要如下：
- 设置各列的宽度
- 遍历 excel 表每一行，如果是表名，则合并为一行作为表头，设置表头格局为加粗、加彩色边框、居中对齐、填充背景色。
- 如果表字段信息内容，则设置彩色边框即可。
def set_file_format(self, file_path):
# 设置表格局
if not os.path.exists(file_path):
print("文件不存在，不解决")
return
workbook = openpyxl.load_workbook(file_path)
sheet = workbook.worksheets[0]
# 设置各列宽
sheet.column_dimensions["A"].width = 16
sheet.column_dimensions["B"].width = 10
sheet.column_dimensions["C"].width = 20
sheet.column_dimensions["D"].width = 40
# 设置表名格局
max_row = sheet.max_row
for i in range(1, max_row + 1):
col1_value = sheet.cell(i, 1).value
col2_value = sheet.cell(i, 2).value
# 首列有数据，第 2 列无数据，则为表名
if col1_value and not col2_value:
# 合并表名
sheet.merge_cells(start_row=i, start_column=1, end_row=i, end_column=4)
# 加粗字体
font = Font(name="微软雅黑", size=12, bold=True, italic=False, color="000000")
# 彩色边框
side_style = Side(style="thin", color="000000")
border = Border(left=side_style, right=side_style, top=side_style, bottom=side_style)
# 居中对齐
cell_alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
# 填充背景色
p_fill = PatternFill(fill_type="solid", fgColor="BFBFBF")
# 表名 cell 格局
for j in range(1, 5):
sheet.cell(i, j).font = font
sheet.cell(i, j).border = border
sheet.cell(i, j).alignment = cell_alignment
sheet.cell(i, j).fill = p_fill
# 若首列和第 2 列都有数据，则是表内容
if col1_value and col2_value:
# 彩色边框
side_style = Side(style="thin", color="000000")
border = Border(left=side_style, right=side_style, top=side_style, bottom=side_style)
# 表名 cell 格局
for j in range(1, 5):
sheet.cell(i, j).border = border
# 保存文档
workbook.save(file_path)当生成数据库阐明文档后，调用此函数，即可批改其文档格局，成果如下：
7 总结
本文次要针对数据库阐明文档（数据字典）的自动化生成进行解说。通过应用 SQL 读取数据库表及字段元信息，而后输入到 excel 文档的思路，以 python 的实现形式实现主动生成文档性能。如果你还在手工生成数据库阐明文档，能够试试这种办法，肯定让你效率大增。心愿能够帮忙到有须要的人。如果想看残缺的代码，可到我 github 地址中查看： https://github.com/mianshenglee/my-example/tree/master/python/tool-gen-db-doc
依据本文实现的思路，最初能够留几个思考题给大家，想想如何做：
- 不应用 python，用其它你相熟的语言来实现此性能。
- 如何只须要生成指定表的字段信息或者过滤指定表，怎么做？
- 数据库表名个别都会有前缀或后缀，是否依据前缀或后缀来过滤生成或者过滤，怎么做？
- 本文是生成 excel 文档，如果须要生成 word、html、md、pdf 等格局的文档，怎么做？
参考资料
- openpyxl 官网文档:
https://openpyxl.readthedocs.io/ - pymysql 官网文档:
https://pymysql.readthedocs.io/en/latest/
往期文章
- Python 解决 Excel 文件
- python 基本操作 - 文件、目录及门路
- MinIO 的分布式部署
- 利用 MinIO 轻松搭建动态资源服务
- 搞定 SpringBoot 多数据源 (3)：参数化变更源
- 搞定 SpringBoot 多数据源 (2)：动静数据源
- 搞定 SpringBoot 多数据源 (1)：多套源策略
- java 开发必学常识: 动静代理
- 2019 读过的好书举荐
我的公众号（搜寻 Mason 技术记录 ），获取更多技术记录：