主要作用是将MYSQL库里的字段按设计的表结构转换成excel表，方便数据库逻辑审查以及业务学习。
工具运行效果：
EXE文件的百度网盘连接：https://pan.baidu.com/s/1jwTuL9IBuSRNsR4xRWqWEA
--来自百度网盘超级会员V5的分享
提取码：52pj
有个问题，就是如果数据库很大，可能会影响数据库性能，大佬们有没有解决方案。
源代码。
[Python] 纯文本查看 复制代码
import pymysql
from openpyxl import Workbook
from past.builtins import raw_input
import datetime
# 输入数据库基本信息
host = input("数据库地址:")
port = int(input("数据库端口:"))
shujukumingcheng = input("数据库名称:")
user = input("用户名称:")
passwd = input("用户密码:")
def lianjieshujuku_01():
"""连接MySQL数据库"""
try:
db = pymysql.connect(
host=host,
port=port,
user=user,
passwd=passwd,
db=shujukumingcheng,
charset='utf8'
)
return db
except Exception:
raise Exception("数据库连接失败")
# 输出一些提示语
print("正在链接数据库获取数据信息，获取速度取决于您的数据库大小和性能，请勿关闭软件。")
print("MySQL数据结构为“库-表-字段”，“表”的名称为EXCEL的sheet名称；")
print("表里的第一行是字段名称，第二行是字段注释，第三行是字段类型，每行类型。")
print("第一行：Field")
print("第二行：Comment")
print("第三行：Type")
print("第四行：Collation")
print("第六行：Null-Key")
print("第七行：Default")
print("第八行：Extra")
print("第九行：Privileges")
print("正在生成中......请稍后")
def shujukubiao_qingdan():
# 列出数据库所有的表
db = lianjieshujuku_01()
cursor = db.cursor()
cursor.execute("show tables") # 给MySQL发送的指令
shujukubiao = [shujukubiao[0] for shujukubiao in cursor.fetchall()]
db.close()
return shujukubiao
shujukubiaoqingdan = shujukubiao_qingdan()
def ziduanzhushi_xiangqing(shujukubiao_name):
# 查询所有字段及其详细信息
db = lianjieshujuku_01()
cursor = db.cursor()
sql = "show full columns from %s" % shujukubiao_name
cursor.execute(sql)
db.close()
return cursor.fetchall()
# 创建工作簿
workbook = Workbook()
# 定义工作表名称
sheet_name = shujukubiaoqingdan
y = 0
for i in sheet_name:
y += 1
x = list(range(y))
for n, shujukubiao in zip(range(len(x)), shujukubiao_qingdan()):
# 开始写入工作表内容
ziduanqingdan = [ziduan[0] for ziduan in ziduanzhushi_xiangqing(shujukubiao)]
zixingqingdan = [zixing[1] for zixing in ziduanzhushi_xiangqing(shujukubiao)]
jiaoduiqingdan = [jiaodui[2] for jiaodui in ziduanzhushi_xiangqing(shujukubiao)]
kongweiqingdan = [kongwei[3] for kongwei in ziduanzhushi_xiangqing(shujukubiao)]
jianzhiqingdan = [jianzhi[4] for jianzhi in ziduanzhushi_xiangqing(shujukubiao)]
morenzhiqingdan = [morenzhi[5] for morenzhi in ziduanzhushi_xiangqing(shujukubiao)]
fujiaxinxiqingdan = [fujiaxinxi[6] for fujiaxinxi in ziduanzhushi_xiangqing(shujukubiao)]
yonghuquanxianqingdan = [yonghuquanxian[7] for yonghuquanxian in ziduanzhushi_xiangqing(shujukubiao)]
ziduanzhushiqingdan = [ziduanzhushi[8] for ziduanzhushi in ziduanzhushi_xiangqing(shujukubiao)]
# 创建跟数据库里的表一样名字的表单
sheet = workbook.create_sheet('%s' % sheet_name[n], n)
# 按顺序写入每个表单的的详情
sheet.append(ziduanqingdan)
sheet.append(ziduanzhushiqingdan)
sheet.append(zixingqingdan)
sheet.append(jiaoduiqingdan)
sheet.append(kongweiqingdan)
sheet.append(jianzhiqingdan)
sheet.append(morenzhiqingdan)
sheet.append(fujiaxinxiqingdan)
sheet.append(yonghuquanxianqingdan)
# 保存工作簿
workbook.save(
"%s更新·数据库“%s”的数据结构易读表.xlsx" % (datetime.datetime.now().strftime('%Y%m%d-%H%M%S'), shujukumingcheng))
print("《数据库“%s”的数据结构易读表》获取成功，请在本工具所在文件夹查看。" % shujukumingcheng)
raw_input("按回车键结束")
