Python 批量导出mysql数据库表结构
前言解决方法1. mysql 数据库 表信息查询2.连接数据库代码3.数据查询处理代码3.0 配置信息3.1查询数据库表3.2 查询对应表结构3.3 pandas进行数据保存导出excel 总结前言
最近在公司售前售后同事遇到一些奇怪的需求找到我，需要提供公司一些项目数据库所有表的结构信息（字段名、类型、长度、是否主键、***、备注），虽然不是本职工作，但是作为python技能的拥有者看到这种需求还是觉得很容易的，但是如果不用代码解决确实非常棘手和浪费时间。于是写了一个轻量小型项目来解决一些燃眉之急，希望能对一些人有所帮助，代码大神、小神可以忽略此贴。
代码直达: GITEE、GitHub
解决方法
1. mysql 数据库 表信息查询
想要导出mysql数据库表结构必须了解一些相关数据库知识，mysql数据库支持通过SQL语句进行表信息查询：
查询数据库所有表名
SHOW TABLES
查询对应数据库对应表结构信息
SELECT COLUMN_NAME,COLUMN_TYPE,COLUMN_KEY,IS_NULLABLE, COLUMN_COMMENT FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA='{dbName}' AND TABLE_NAME='{tableName}'
COLUMN_NAME：字段名COLUMN_TYPE：数据类型COLUMN_KEY：主键IS_NULLABLE：非空COLUMN_COMMENT：字段描述
还有一些其他字段，有需要可自行百度
2.连接数据库代码
以下是一个较为通用的mysql数据库连接类，创建 MysqlConnection 类，放入对应数据库连接信息即可使用sql,通过query查询、update增删改、close关闭连接。
*注：数据量过大时不推荐直接使用query查询。
import pymysqlclass MysqlConnection():def __init__(self, host, user, passw, port, database, charset="utf8"):self.db = pymysql.connect(host=host, user=user, password=passw, port=port,database=database, charset=charset)self.cursor = self.db.cursor()# 查def query(self, sql):self.cursor.execute(sql)results = self.cursor.fetchall()return results# 增删改def update(self, sql):try:self.cursor.execute(sql)mit()return 1except Exception as e:print(e)self.db.rollback()return 0# 关闭连接def close(self):self.cursor.close()self.db.close()
3.数据查询处理代码
3.0 配置信息
config.yml,这里使用了配置文件进行程序参数配置，方便配置一键运行
# 数据库信息配置db_config:host: 127.0.0.1# 数据库所在服务IPport: 3306# 数据库服务端口username: root# ~用户名password: 12346# ~密码charset: utf8# 需要进行处理的数据名称列表 《《 填入数据库名db_names: ['db_a','db_b']# 导出配置excel_conf:# 导出结构Excel表头，长度及顺序不可调整，仅支持更换名称column_name: ['字段名', '数据类型', '长度', '主键', '非空', '描述']save_dir: ./data
读取配置文件的代码
import yamlclass Configure():def __init__(self):with open("config.yaml", 'r', encoding='utf-8') as f:self._conf = yaml.load(f.read(), Loader=yaml.FullLoader)def get_db_config(self):host = self._conf['db_config']['host']port = self._conf['db_config']['port']username = self._conf['db_config']['username']password = self._conf['db_config']['password']charset = self._conf['db_config']['charset']db_names = self._conf['db_config']['db_names']return host, port, username, password, charset, db_namesdef get_excel_title(self):title = self._conf['excel_conf']['column_name']save_dir = self._conf['excel_conf']['save_dir']return title, save_dir
3.1查询数据库表
利用上面创建的数据库连接和SQL查询获取所有表
class ExportMysqlTableStructureInfoToExcel():def __init__(self):conf = Configure()# 获取配置初始化类信息self.__host, self.__port, self.__username, self.__password, self.__charset, self.db_names = conf.get_db_config()self.__excel_title, self.__save_dir = conf.get_excel_title()```省略```def __connect_to_mysql(self, database):# 获取数据库连接方法connect = MysqlConnection(self.__host,self.__username,self.__password,self.__port, database,self.__charset)return connectdef __get_all_tables(self, con):# 查询所有表res = con.query("SHOW TABLES")tb_list = []for item in res:tb_list.append(item[0])return tb_list``````
3.2 查询对应表结构
循环获取每一张表的结构数据，根据需要对中英文做了一些转换，字段长度可以从类型中分离出来，
这里使用yield返回数据，可以利用生成器加速处理过程（外包导出保存和数据库查询可以并行）
class ExportMysqlTableStructureInfoToExcel():```省略```def __struct_of_table_generator(self, con, db_name):tb_list = self.__get_all_tables(con)for index, tb_name in enumerate(tb_list):sql = "SELECT COLUMN_NAME,COLUMN_TYPE,COLUMN_KEY,IS_NULLABLE, COLUMN_COMMENT " \"FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA='{}' AND TABLE_NAME='{}'".format(db_name, tb_name)res = con.query(sql)struct_list = []for item in res:column_name, column_type, column_key, is_nullable, column_comment = itemlength = "0"if str(column_type).find('(') > -1:column_type, length = str(column_type).replace(")", '').split('(')if column_key == 'PRI':column_key = "是"else:column_key = ''if is_nullable == 'YES':is_nullable = '是'else:is_nullable = '否'struct_list.append([column_name, column_type, length, column_key, is_nullable, column_comment])yield [struct_list, tb_name]```省略```
3.3 pandas进行数据保存导出excel
class ExportMysqlTableStructureInfoToExcel():```省略```def export(self):if len(self.db_names) == 0:print("请配置数据库列表")for i, db_name in enumerate(self.db_names):# 对多个数据库进行处理connect = self.__connect_to_mysql(db_name)# 获取数据库连接if not os.path.exists(self.__save_dir):# 判断数据导出保存路径是否存在os.mkdir(self.__save_dir)file_name = os.path.join(self.__save_dir,'{}.xlsx'.format(db_name))# 用数据库名命名导出Excel文件if not os.path.exists(file_name): # 文件不存在时自动创建文件 excelwrokb = openpyxl.Workbook()wrokb.save(file_name)wrokb.close()wb = openpyxl.load_workbook(file_name)writer = pd.ExcelWriter(file_name, engine='openpyxl')writer.book = wbstruct_generator = self.__struct_of_table_generator(connect, db_name)# 获取表结构信息的生成器for tb_info in tqdm(struct_generator, desc=db_name):# 从生成器中获取表结构并利用pandas进行格式化保存，写入Excel文件s_list, tb_name = tb_infodata = pd.DataFrame(s_list, columns=self.__excel_title)data.to_excel(writer, sheet_name=tb_name)writer.close()connect.close()```省略```
总结
运行成功后会在目录下的data文件夹中看到保存的Excel文件（以数据库名为单位保存成文件），每个Excel第一个tab是空的（一个小bug暂未解决），其他每个tab以对应表名进行命名。
代码很简单，供各位学习参考。
