Mysql表结构信息导出Excel

将表结构信息导出到Excel文档

先看效果图预览下

• 目录页
  
• 详情页
  

然后，上代码！！！

# _*_ coding:utf-8 _*_
import xlwt
import pymysql
import sys
reload(sys)
sys.setdefaultencoding('utf8')


"""
    可配置参数
"""
# 定义数据库连接信息
db_host = '127.0.0.1'
db_user = 'root'
db_passwd = '123456'
db_port = 3306
# 需要导出表结构的库
export_db_scheme = 'mysql_database'
# 导出索引
show_index_flag = True
# True:只导出 TABLE_COMMENT 不为空的表;False:导出所有表
existCommentFlag = True
# 使用表名作为sheet的名称;False以comment作为sheet名，True以tableName作为sheet名
sheetNameEqualToTableNameFlag = False
"""
    ----------------------------
"""
# 从 information_schema 中导出表信息
db_scheme = 'information_schema'

#链接数据库需要先导入库
#python3中不支持mysqldb
print("连接数据库中...")
db=pymysql.connect(host=db_host,user=db_user,passwd=db_passwd,db=db_scheme,port=db_port,charset='utf8mb4')
print("连接成功")

# 使用cursor()方法获取操作游标
cur = db.cursor()

#查询操作 注意:数据列表的顺序同SELECT语句字段排序一致
select_sql = "SELECT col.TABLE_NAME, tab.TABLE_COMMENT, col.COLUMN_NAME, col.COLUMN_TYPE, col.IS_NULLABLE, col.COLUMN_DEFAULT, col.COLUMN_COMMENT, col.COLUMN_KEY, col.EXTRA"
from_sql = " FROM COLUMNS col,TABLES tab"
where_sql = " WHERE col.TABLE_SCHEMA= '"+export_db_scheme+"' AND tab.TABLE_NAME = col.TABLE_NAME AND tab.TABLE_SCHEMA = col.TABLE_SCHEMA"
order_sql = " ORDER BY TABLE_NAME"
sql =  select_sql + from_sql + where_sql + order_sql

tableStructureData = [];

sheetSet = set();

try:
    cur.execute(sql)
    results = cur.fetchall()
    # 遍历结果，处理数据
    table_name = "";
    for row in results :
        if existCommentFlag and row[1] == '' :
            continue
        if row[0] != table_name and row[0] != '' :
            table_name = row[0]
            table = {
   }
            table["table_name"] = table_name
            if sheetNameEqualToTableNameFlag:
                table["table_comment"] = row[0]
            else:
                if row[1] == "" :
                    table["table_comment"] = row[0]
                else:
                    table["table_comment"] = row[1]
            # 对字符串长度进行处理，确保不要超出excelsheet页长度
            if len(table["table_comment"])>30:
                table["table_comment"]=table["table_comment"][0:30]
            if(table["table_comment"] not in sheetSet):
                sheetSet.add(table["table_comment"]) 
                tableStructureData.append(table)
            table["column"] = []
        else:
            pass
        colMap = {