Python 批量导出mysql数据库表结构_python 导出mysql 库中表结构

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 pymysql
class 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)
            self.db.commit()
            return 1
        except 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	# 数据库所在服务IP
  port: 3306		# 数据库服务端口
  username: root	# ~用户名
  password: 12346	# ~密码
  charset: utf8
  # 需要进行处理的数据名称列表 《《 填入数据库名
  db_names: ['db_a','db_b']
# 导出配置
excel_conf:
  # 导出结构Excel表头，长度及顺序不可调整，仅支持更换名称
  column_name: ['字段名', '数据类型', '长度', '主键', '非空', '描述']
  save_dir: ./data
读取配置文件的代码 
import yaml
class 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_names
    def 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 connect
	def __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 = item
                length = "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):  # 文件不存在时自动创建文件 excel
                wrokb = openpyxl.Workbook()
                wrokb.save(file_name)
                wrokb.close()
            wb = openpyxl.load_workbook(file_name)
            writer = pd.ExcelWriter(file_name, engine='openpyxl')
            writer.book = wb
            struct_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_info
                data = 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以对应表名进行命名。
 代码很简单，供各位学习参考。
                                    Mysql数据库表定期备份的实现
实际开发环境中，前端程序需要在给定时间段内，将数据更新/插入到MySQL指定的库表中。随着数据量的增多，基础库表基数的增长，每更新一次都会有5s左右的卡顿。 
改进方案一：批量更新，累计数10条或者100条进行一次更新入库操作； 
改进方案二：将当前日期前1个月之前的数据进行备份操作，并删除当前库表中1个月前的数据。经测定，该方法一定程度提高了访问效率。根因：基础表基数少，查询效率相对提高。
1.库表的定时备份总结
步骤1：备份Mysql指定数据库中的制定库表。
使用 mysqldump，设定周期30天。
步骤2：对于日期60天前的已备份的文件及
                                    执行 show full columns from  from  获取表的字段信息。执行 show tables 获取所有表。调用to_excel方法存入excel。将暂存的字典转为DataFrame。pymysql链接数据库。
                                    本文实例为大家分享了Python读取MySQL数据库表数据的具体代码，供大家参考，具体内容如下
环境：Python 3.6 ,Window 64bit
目的：从MySQL数据库读取目标表数据，并处理
# -*- coding: utf-8 -*-
import pandas as pd
import pymysql
## 加上字符集参数，防止中文乱码
dbconn=pymysql.connect(
  host="**********",
  database="kimbo",
  user="kimbo_test",
  password="******",
  port=330
                                    python爬虫所用示例一、准备工作（一）找网址（二）找数据的位置:方法一：元素（element）方法二：网络（network）+刷新（三）编码规范（四）引入模块二、构建流程三、urllib四、BeautifulSoup五、正则表达式六、保存数据1.txt保存2.Excel保存3.Pymysql保存三级目录
1.任务：爬取豆瓣top250
2.网址：https://movie.douban.com/top250
3.爬取内容:电影名称、豆瓣评分、评价数、电影概况、电影链接
一、准备工作
                                    结构化数据也称作行数据，是由二维表结构来逻辑表达和实现的数据，严格地遵循数据格式与长度规范，主要通过关系型数据库进行存储和管理。与其对应的是非结构化数据，如语音、文本、图像、视频等。结构化数据是普通的数据分析经常用到的数据类型，其本地存储的文件格式又有所不同，如 excel、csv、txt 等。下面我们将介绍三种常用的数据文件Python pandas 库读取方式。
                                    python 获取mysql 库信息/表信息/表结构/索引核心代码import pymysqlclass SQLgo(object):def __init__(self, ip=None, user=None, password=None, db=None, port=None):self.ip = ipself.user = userself.password = passwordself.db...
                                    需求：Python将mysql中表导出为Excel表格mysql表中的数据：测试环境：平台：Windows10、centos7python版本：python3.7(以下脚本兼容python2)python库安装：pip install pymysqlpip install MySQLdbpip install xlwt新建py——python_mysql_excel.py# coding: utf...
第一步：连接到mysql数据库
import pymysql
conn = pymysql.connect(host='localhost',user='root',password='1234',db='ishop1',charset=&quot;utf8&quot;)
第二步：创建游标  对象
cursor = conn.cursor()   #cursor当前的...

Python 批量导出mysql数据库表结构

1. mysql 数据库 表信息查询

2.连接数据库代码

3.数据查询处理代码

3.0 配置信息

3.1查询数据库表

3.2 查询对应表结构

3.3 pandas进行数据保存导出excel

1. mysql 数据库表信息查询