发布时间:2018-11-03 22:50:09编辑:admin阅读(6856)
线上有一台MySQL服务器,里面有几十个数据库,每个库有N多表。
现在需要将每个表的信息,统计到excel中,格式如下:
库名 | 表名 | 表说明 | 建表语句 |
db1 | users | 用户表 | CREATE TABLE `users` (...) |
怎么做呢?
1. 手动录入(太TM苦逼了,那么多表呀...)
2. 使用Python自动录入(Great)
需要利用的技术点,有2个。一个是pymysql(连接mysql),一个是xlwt(写入excel)
pip3 install pymysql xlwt
请确保有一个账号,能够远程连接MySQL,并且有对应的权限。
我用的是本机的MySQL,目前只有一个数据库db1
新建文件tj.py,内容如下:
import pymysql conn = pymysql.connect( host="127.0.0.1", # mysql ip地址 user="root", passwd="", port=3306 # mysql 端口号,注意:必须是int类型 ) cur = conn.cursor() # 创建游标 # 获取mysql中所有数据库 cur.execute('SHOW DATABASES') data_all = cur.fetchall() # 获取执行的返回结果 print(data_all)
执行输出:
(('information_schema',), ('db1',), ('mysql',), ('performance_schema',), ('sys',))
结果是一个元组,里面的每一个元素也是元组。使用for循环
import pymysql conn = pymysql.connect( host="127.0.0.1", # mysql ip地址 user="root", passwd="", port=3306 # mysql 端口号,注意:必须是int类型 ) cur = conn.cursor() # 创建游标 # 获取mysql中所有数据库 cur.execute('SHOW DATABASES') data_all = cur.fetchall() # 获取执行的返回结果 # print(data_all) for i in data_all: print(i[0]) # 获取库名
执行输出:
information_schema db1 mysql performance_schema sys
要获取所有的表,必须要切换到对应的数据库中,使用show tables 才可以获取。
import pymysql conn = pymysql.connect( host="127.0.0.1", # mysql ip地址 user="root", passwd="", port=3306 # mysql 端口号,注意:必须是int类型 ) cur = conn.cursor() # 创建游标 # 获取mysql中所有数据库 cur.execute('SHOW DATABASES') data_all = cur.fetchall() # 获取执行的返回结果 # print(data_all) for i in data_all: conn.select_db(i[0]) # 切换到指定的库中 cur.execute('SHOW TABLES') # 查看库中所有的表 ret = cur.fetchall() # 获取执行结果 print(ret)
执行输出:
(('CHARACTER_SETS',), ('COLLATIONS',), ('COLLATION_CHARACTER_SET_APPLICABILITY',), ('COLUMNS',), ...
结果也是一个大的元组,使用for循环,提取表名
import pymysql conn = pymysql.connect( host="127.0.0.1", # mysql ip地址 user="root", passwd="", port=3306 # mysql 端口号,注意:必须是int类型 ) cur = conn.cursor() # 创建游标 # 获取mysql中所有数据库 cur.execute('SHOW DATABASES') data_all = cur.fetchall() # 获取执行的返回结果 # print(data_all) for i in data_all: conn.select_db(i[0]) # 切换到指定的库中 cur.execute('SHOW TABLES') # 查看库中所有的表 ret = cur.fetchall() # 获取执行结果 for j in ret: print(j[0]) # 获取每一个表名
执行输出:
CHARACTER_SETS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY COLUMNS ...
使用命令 show create table 表名 获取
import pymysql conn = pymysql.connect( host="127.0.0.1", # mysql ip地址 user="root", passwd="", port=3306 # mysql 端口号,注意:必须是int类型 ) cur = conn.cursor() # 创建游标 # 获取mysql中所有数据库 cur.execute('SHOW DATABASES') data_all = cur.fetchall() # 获取执行的返回结果 # print(data_all) for i in data_all: conn.select_db(i[0]) # 切换到指定的库中 cur.execute('SHOW TABLES') # 查看库中所有的表 ret = cur.fetchall() # 获取执行结果 for j in ret: # 获取每一个表的建表语句 cur.execute('show create table `%s`;' % j[0]) ret = cur.fetchall() print(ret)
执行输出:
(('CHARACTER_SETS', "CREATE TEMPORARY TABLE `CHARACTER_SETS` (\n `CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '',\n `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL DEFAULT '',\n `DESCRIPTION` varchar(60) NOT NULL DEFAULT '',\n `MAXLEN` bigint(3) NOT NULL DEFAULT '0'\n) ENGINE=MEMORY DEFAULT CHARSET=utf8"),) ...
从结果中可以看出,建表语句中有大量的 \n 这个是换行符。注意:是\n后面还有2个空格
领导肯定是不想看到有这种符号存在,怎么去除呢?使用eval+replace
import pymysql conn = pymysql.connect( host="127.0.0.1", # mysql ip地址 user="root", passwd="", port=3306 # mysql 端口号,注意:必须是int类型 ) cur = conn.cursor() # 创建游标 # 获取mysql中所有数据库 cur.execute('SHOW DATABASES') data_all = cur.fetchall() # 获取执行的返回结果 # print(data_all) for i in data_all: conn.select_db(i[0]) # 切换到指定的库中 cur.execute('SHOW TABLES') # 查看库中所有的表 ret = cur.fetchall() # 获取执行结果 for j in ret: # 获取每一个表的建表语句 cur.execute('show create table `%s`;' % j[0]) ret = cur.fetchall() # print(ret) for k in ret: # 替换反斜杠,使用\\。替换换行符\n,使用下面的 structure = eval(repr(k[1]).replace('\\n ', '')) print(structure)
执行输出:
CREATE TABLE "api_teacher" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar(32) NOT NULL, "role" smallint NOT NULL, "title" varchar(64) NOT NULL, "signature" varchar(255) NULL, "image" varchar(128) NOT NULL, "brief" text NOT NULL); CREATE TEMPORARY TABLE `CHARACTER_SETS` (`CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '',`DEFAULT_COLLATE_NAME` varchar(32) NOT NULL DEFAULT '',`DESCRIPTION` varchar(60) NOT NULL DEFAULT '',`MAXLEN` bigint(3) NOT NULL DEFAULT '0' ) ENGINE=MEMORY DEFAULT CHARSET=utf8 ...
结果真的很多,但我都需要吗?no no no,其实我只需要db1而已,其他的都是系统自带的表,我并不关心!
定义一个排序列表,使用if排除,注意:i[0] 是库名
import pymysql conn = pymysql.connect( host="127.0.0.1", # mysql ip地址 user="root", passwd="", port=3306 # mysql 端口号,注意:必须是int类型 ) cur = conn.cursor() # 创建游标 # 获取mysql中所有数据库 cur.execute('SHOW DATABASES') data_all = cur.fetchall() # 获取执行的返回结果 # print(data_all) for i in data_all: # 排序列表,排除mysql自带的数据库 exclude_list = ["sys", "information_schema", "mysql", "performance_schema"] if i[0] not in exclude_list: # 判断不在列表中时 conn.select_db(i[0]) # 切换到指定的库中 cur.execute('SHOW TABLES') # 查看库中所有的表 ret = cur.fetchall() # 获取执行结果 for j in ret: # 获取每一个表的建表语句 cur.execute('show create table `%s`;' % j[0]) ret = cur.fetchall() # print(ret) for k in ret: # 替换反斜杠,使用\\。替换换行符\n,使用下面的 structure = eval(repr(k[1]).replace('\\n ', '')) print(structure)
执行输出:
CREATE TABLE `school` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci CREATE TABLE `users` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '姓名',PRIMARY KEY (`id`),KEY `ix_users_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表'
看下面一条建表语句
CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '姓名', PRIMARY KEY (`id`), KEY `ix_users_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';
我需要获取 用户表 这3个字,如何操作?
1. 使用正则匹配?Oh, I'm sorry ,本人正则水平太烂了...
2. 使用COMMENT切割?那可不行,name字段也有COMMENT。
仔细发现,可以看出这2个COMMENT还是有区别的。最后一个COMMENT,后面有一个等号。
OK,那么就可以通过COMMENT=来切割了。
import pymysql conn = pymysql.connect( host="127.0.0.1", # mysql ip地址 user="root", passwd="", port=3306 # mysql 端口号,注意:必须是int类型 ) cur = conn.cursor() # 创建游标 # 获取mysql中所有数据库 cur.execute('SHOW DATABASES') data_all = cur.fetchall() # 获取执行的返回结果 # print(data_all) for i in data_all: # 排序列表,排除mysql自带的数据库 exclude_list = ["sys", "information_schema", "mysql", "performance_schema"] if i[0] not in exclude_list: # 判断不在列表中时 conn.select_db(i[0]) # 切换到指定的库中 cur.execute('SHOW TABLES') # 查看库中所有的表 ret = cur.fetchall() # 获取执行结果 for j in ret: # 获取每一个表的建表语句 cur.execute('show create table `%s`;' % j[0]) ret = cur.fetchall() # print(ret) for k in ret: # 替换反斜杠,使用\\。替换换行符\n,使用下面的 structure = eval(repr(k[1]).replace('\\n ', '')) # 使用COMMENT= 切割 res = structure.split("COMMENT=") print(res)
执行输出:
['CREATE TABLE `school` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'] ["CREATE TABLE `users` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '姓名',PRIMARY KEY (`id`),KEY `ix_users_name` (`name`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ", "'用户表'"]
注意: 有些表,是没有写表注释的,所以获取表说明时,要加一个判断
import pymysql conn = pymysql.connect( host="127.0.0.1", # mysql ip地址 user="root", passwd="", port=3306 # mysql 端口号,注意:必须是int类型 ) cur = conn.cursor() # 创建游标 # 获取mysql中所有数据库 cur.execute('SHOW DATABASES') data_all = cur.fetchall() # 获取执行的返回结果 # print(data_all) for i in data_all: # 排序列表,排除mysql自带的数据库 exclude_list = ["sys", "information_schema", "mysql", "performance_schema"] if i[0] not in exclude_list: # 判断不在列表中时 conn.select_db(i[0]) # 切换到指定的库中 cur.execute('SHOW TABLES') # 查看库中所有的表 ret = cur.fetchall() # 获取执行结果 for j in ret: # 获取每一个表的建表语句 cur.execute('show create table `%s`;' % j[0]) ret = cur.fetchall() # print(ret) for k in ret: # 替换反斜杠,使用\\。替换换行符\n,使用下面的 structure = eval(repr(k[1]).replace('\\n ', '')) # 使用COMMENT= 切割,获取表说明 res = structure.split("COMMENT=") if len(res) > 1: # 判断有表说明的情况下 explain = res[1] # 表说明 print(explain)
执行输出:
'用户表'
输出结果是带有引号的,要去除引号,怎么操作?
使用strip就可以了
import pymysql conn = pymysql.connect( host="127.0.0.1", # mysql ip地址 user="root", passwd="", port=3306 # mysql 端口号,注意:必须是int类型 ) cur = conn.cursor() # 创建游标 # 获取mysql中所有数据库 cur.execute('SHOW DATABASES') data_all = cur.fetchall() # 获取执行的返回结果 # print(data_all) for i in data_all: # 排序列表,排除mysql自带的数据库 exclude_list = ["sys", "information_schema", "mysql", "performance_schema"] if i[0] not in exclude_list: # 判断不在列表中时 conn.select_db(i[0]) # 切换到指定的库中 cur.execute('SHOW TABLES') # 查看库中所有的表 ret = cur.fetchall() # 获取执行结果 for j in ret: # 获取每一个表的建表语句 cur.execute('show create table `%s`;' % j[0]) ret = cur.fetchall() # print(ret) for k in ret: # 替换反斜杠,使用\\。替换换行符\n,使用下面的 structure = eval(repr(k[1]).replace('\\n ', '')) # 使用COMMENT= 切割 res = structure.split("COMMENT=") if len(res) > 1: explain = res[1] # 表说明 # print(explain) explain_new = explain.strip("'") # 去掉引号 print(explain_new)
执行输出:
用户表
接下来就需要将数据写入到excel中,但是,我们需要知道。写入excel,要不断的调整方位。
由于线上表众多,获取一次数据,需要几分钟时间,时间上耗费不起!
所以为了避免这种问题,需要将获取到的数据,写入json文件中。
既然要构造json数据,那么数据格式,要规划好才行!我构造的数据格式如下:
dic = { "库名":{ 'name': "库名", 'table_list': [ {'tname':"表名",'structure':"建表语句",'explain':"表说明"} ] } }
定义一个大字典,写入数据
import pymysql conn = pymysql.connect( host="127.0.0.1", # mysql ip地址 user="root", passwd="", port=3306 # mysql 端口号,注意:必须是int类型 ) cur = conn.cursor() # 创建游标 # 获取mysql中所有数据库 cur.execute('SHOW DATABASES') data_all = cur.fetchall() # 获取执行的返回结果 # print(data_all) dic = {} # 大字典,第一层 for i in data_all: if i[0] not in dic: # 判断库名不在dic中时 # 排序列表,排除mysql自带的数据库 exclude_list = ["sys", "information_schema", "mysql", "performance_schema"] if i[0] not in exclude_list: # 判断不在列表中时 # 写入第二层数据 dic[i[0]] = {'name': i[0], 'table_list': []} conn.select_db(i[0]) # 切换到指定的库中 cur.execute('SHOW TABLES') # 查看库中所有的表 ret = cur.fetchall() # 获取执行结果 for j in ret: # 获取每一个表的建表语句 cur.execute('show create table `%s`;' % j[0]) ret = cur.fetchall() # print(ret) for k in ret: # 替换反斜杠,使用\\。替换换行符\n,使用下面的 structure = eval(repr(k[1]).replace('\\n ', '')) # 使用COMMENT= 切割 res = structure.split("COMMENT=") if len(res) > 1: explain = res[1] # 表说明 # print(explain) explain_new = explain.strip("'") # 去掉引号 # 写入第三层数据,分别是表名,建表语句,表说明 dic[i[0]]['table_list'].append({'tname': k[0], 'structure': structure,'explain':explain_new}) print(dic)
执行输出:
{'db1': {'name': 'db1', 'table_list': [{'tname': 'users', 'structure': "CREATE TABLE `users` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '姓名',PRIMARY KEY (`id`),KEY `ix_users_name` (`name`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表'", 'explain': '用户表'}]}}
import pymysql import json conn = pymysql.connect( host="127.0.0.1", # mysql ip地址 user="root", passwd="", port=3306 # mysql 端口号,注意:必须是int类型 ) cur = conn.cursor() # 创建游标 # 获取mysql中所有数据库 cur.execute('SHOW DATABASES') data_all = cur.fetchall() # 获取执行的返回结果 # print(data_all) dic = {} # 大字典,第一层 for i in data_all: if i[0] not in dic: # 判断库名不在dic中时 # 排序列表,排除mysql自带的数据库 exclude_list = ["sys", "information_schema", "mysql", "performance_schema"] if i[0] not in exclude_list: # 判断不在列表中时 # 写入第二层数据 dic[i[0]] = {'name': i[0], 'table_list': []} conn.select_db(i[0]) # 切换到指定的库中 cur.execute('SHOW TABLES') # 查看库中所有的表 ret = cur.fetchall() # 获取执行结果 for j in ret: # 获取每一个表的建表语句 cur.execute('show create table `%s`;' % j[0]) ret = cur.fetchall() # print(ret) for k in ret: # 替换反斜杠,使用\\。替换换行符\n,使用下面的 structure = eval(repr(k[1]).replace('\\n ', '')) # 使用COMMENT= 切割 res = structure.split("COMMENT=") if len(res) > 1: explain = res[1] # 表说明 # print(explain) explain_new = explain.strip("'") # 去掉引号 # 写入第三层数据,分别是表名,建表语句,表说明 dic[i[0]]['table_list'].append({'tname': k[0], 'structure': structure,'explain':explain_new}) # print(dic) with open('tj.json','w',encoding='utf-8') as f: f.write(json.dumps(dic))
执行程序,查看tj.json文件内容
{"db1": {"name": "db1", "table_list": [{"tname": "users", "structure": "CREATE TABLE `users` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '\u59d3\u540d',PRIMARY KEY (`id`),KEY `ix_users_name` (`name`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='\u7528\u6237\u8868'", "explain": "\u7528\u6237\u8868"}]}}
a1单元格的坐标为0,0。在xlrd模块里面,坐标都是数字,所以不能用a1表示。
坐标如下:
先来写入一段示例数据
新建文件excel.py,代码如下:
import xlwt f = xlwt.Workbook() sheet1 = f.add_sheet('学生',cell_overwrite_ok=True) row0 = ["姓名","年龄","出生日期","爱好"] colum0 = ["张三","李四","王五"] #写第一行 for i in range(0,len(row0)): sheet1.write(0,i,row0[i]) #写第一列 for i in range(0,len(colum0)): sheet1.write(i+1,0,colum0[i]) # 写入一行数据 sheet1.write(1,1,"23") sheet1.write(1,2,"1990") sheet1.write(1,3,"女") f.save('test.xls')
执行程序,查看excel文件内容
熟悉语法之后,就可以写入到excel中了
编辑 excel.py,代码如下:
import xlwt import json f = xlwt.Workbook() sheet1 = f.add_sheet('统计', cell_overwrite_ok=True) row0 = ["库名", "表名", "表说明", "建表语句"] # 写第一行 for i in range(0, len(row0)): sheet1.write(0, i, row0[i]) # 加载json文件 with open("tj.json", 'r') as load_f: load_dict = json.load(load_f) # 反序列化文件 num = 0 # 计数器 for i in load_dict: # 写入库名 sheet1.write(num + 1, 0, i) f.save('test1.xls')
执行程序,查看excel文件
import xlwt import json f = xlwt.Workbook() sheet1 = f.add_sheet('统计', cell_overwrite_ok=True) row0 = ["库名", "表名", "表说明", "建表语句"] # 写第一行 for i in range(0, len(row0)): sheet1.write(0, i, row0[i]) # 加载json文件 with open("tj.json", 'r') as load_f: load_dict = json.load(load_f) # 反序列化文件 num = 0 # 计数器 for i in load_dict: # 写入库名 sheet1.write(num + 1, 0, i) # 遍历所有表 for j in load_dict[i]["table_list"]: # 写入表名 sheet1.write(num + 1, 1, j['tname']) f.save('test1.xls')
执行程序,查看excel文件
import xlwt import json f = xlwt.Workbook() sheet1 = f.add_sheet('统计', cell_overwrite_ok=True) row0 = ["库名", "表名", "表说明", "建表语句"] # 写第一行 for i in range(0, len(row0)): sheet1.write(0, i, row0[i]) # 加载json文件 with open("tj.json", 'r') as load_f: load_dict = json.load(load_f) # 反序列化文件 num = 0 # 计数器 for i in load_dict: # 写入库名 sheet1.write(num + 1, 0, i) # 遍历所有表 for j in load_dict[i]["table_list"]: # 写入表名 sheet1.write(num + 1, 1, j['tname']) # 写入表说明 sheet1.write(num + 1, 2, j['explain']) # 写入建表语句 sheet1.write(num + 1, 3, j['structure']) num += 1 # 自增1 f.save('test1.xls')
注意:默认的num必须要自增,否则多个数据库写入会有问题
执行程序,查看excel文件
总结:
案例只是写入一个数据库,那么多个数据库,也是同样的代码。
假设说,excel的内容是这样的
对于python 3.6之前,默认的字典都是无序的。如果需要将普通字典转换为有序字典,需要使用OrderedDict
举例:
from collections import OrderedDict dic = {"k":1,"a":2,"d":4} order_dic = OrderedDict() # 实例化一个有序字典 for i in sorted(dic): # 必须先对普通字典key做排序 order_dic[i] = dic[i] # 写入有序字典中 print(order_dic)
执行输出:
OrderedDict([('a', 2), ('d', 4), ('k', 1)])
注意:将普通字典转换为有序字典时,必须要先对普通字典,做一次排序。那么之后写入到有序字典之后,顺序就是有序了!
不论执行多少次print,结果都是一样的!
编辑 excel.py,代码如下:
import xlwt import json from collections import OrderedDict f = xlwt.Workbook() sheet1 = f.add_sheet('统计', cell_overwrite_ok=True) row0 = ["库名", "表名", "表说明", "建表语句"] # 写第一行 for i in range(0, len(row0)): sheet1.write(0, i, row0[i]) # 加载json文件 with open("tj.json", 'r') as load_f: load_dict = json.load(load_f) # 反序列化文件 order_dic = OrderedDict() # 有序字典 for key in sorted(load_dict): # 先对普通字典key做排序 order_dic[key] = load_dict[key] # 再写入key num = 0 # 计数器 for i in order_dic: # 写入库名 sheet1.write(num + 1, 0, i) # 遍历所有表 for j in order_dic[i]["table_list"]: # 写入表名 sheet1.write(num + 1, 1, j['tname']) # 写入表说明 sheet1.write(num + 1, 2, j['explain']) # 写入建表语句 sheet1.write(num + 1, 3, j['structure']) num += 1 # 自增1 f.save('test1.xls')
执行程序,效果同上(因为数据太少了)
上一篇: python 获取网卡实时流量
下一篇: Ubuntu CEPH快速安装
47303
45507
36587
34101
28810
25424
24274
19469
18914
17483
5304°
5904°
5403°
5488°
6375°
5220°
5219°
5731°
5687°
6996°