为了体验python连接mysql数据库和导出excel，写一个导出表结构的脚本

创建、激活虚拟环境

python3 -m venv py_env
source py_env/bin/activate

安装依赖包

pip install xlwt
pip install pymysql

coding

"""
    导出表结构文档到excel
    @author Jenson
"""
import xlwt
import pymysql

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

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

# 使用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 = [];

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 = {}
            tableStructureData.append(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]
            table["column"] = []
        else:
            pass
        colMap = {}
        colMap["name"] = row[2]
        if row[3].count("(") :
            colMap["type"] = row[3][0:row[3].find("(")]
            colMap["length"] = row[3][row[3].find("(")+1:-1]
        else:
            colMap["type"] = row[3]
            colMap["length"] = ""
        if row[4]=="YES" :
            colMap["nullable"] = "是"
        else:
            colMap["nullable"] = "否"
        colMap["default"] = row[5]
        if row[6] == '' and row[7] == 'PRI':
            if row[8] == 'auto_increment':
                colMap["description"] = '表ID，主键，自增，供其他表做外键'
            else:
                colMap["description"] = '表ID，主键，供其他表做外键'
        else:
            colMap["description"] = row[6]

        table["column"].append(colMap)
    # 如果需要导出索引，再次遍历组合好的数据，插入索引
    if show_index_flag :
        for table in tableStructureData:
            #查询索引
            cur.execute("SHOW index FROM "+export_db_scheme+"."+table["table_name"])
            indexResults = cur.fetchall()
            indexMap = {}
            for indexRow in indexResults:
                if indexRow[2] not in indexMap.keys():
                    indexMap[indexRow[2]] = []
                    if indexRow[1] == 0 :
                        unique = "是"
                    else:
                        unique = "否"
                    # 插入 是否唯一约束
                    indexMap[indexRow[2]].append(unique)
                indexMap[indexRow[2]].append(indexRow[4])
            table["index"] = indexMap
except Exception as e:
    raise e
finally:
    #关闭连接
    db.close()

"""
以下，导出excel，数据格式模版

tableStructureData = [{
    "table_name": "hap_template",
    "table_comment": "模版表",
    "column": [
        {
            "name": "template_id",
            "type": "序列",
            "length": "",
            "nullable": "否",
            "default": "",
            "description": "表ID，主键，供其他表做外键",
        },
    ],
    "index":{
        "amdm_asset_locations_u1":[
            "location_name",
            "tenant_id",
        ]
    }
}]
"""
# 创建一个workbook 设置编码
workbook = xlwt.Workbook(encoding = 'utf-8')
tall_style = xlwt.easyxf('font:height 300;') # 36pt,类型小初的字号
# 设置字体
font = xlwt.Font()
# 比如设置字体加粗
font.bold = True
# 设置超链接字体
linkFont = xlwt.Font()
linkFont.underline = True
linkFont.colour_index = 4
# 边框
borders = xlwt.Borders()
borders.left_colour = 0x40
# 设置虚线，其他类似
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN

# 准备三种背景颜色的style
# 背景色--无
style = xlwt.XFStyle()
style.borders = borders
# 背景色--黄色
patternYellow = xlwt.Pattern()
patternYellow.pattern = xlwt.Pattern.SOLID_PATTERN
patternYellow.pattern_fore_colour = 5
styleYellow = xlwt.XFStyle()
styleYellow.pattern = patternYellow
styleYellow.borders = borders

# 背景色--淡绿色
patternLightGreen = xlwt.Pattern()
patternLightGreen.pattern = xlwt.Pattern.SOLID_PATTERN
patternLightGreen.pattern_fore_colour = 42
styleLightGreen = xlwt.XFStyle()
styleLightGreen.pattern = patternLightGreen
styleLightGreen.borders = borders
styleLightGreen.font = font

# 背景色---黄色， 超链接字体
linkStyleYellow = xlwt.XFStyle()
linkStyleYellow.pattern = patternYellow
linkStyleYellow.borders = borders
linkStyleYellow.font = linkFont

# 背景色---无， 超链接字体
linkStyle = xlwt.XFStyle()
linkStyle.borders = borders
linkStyle.font = linkFont


worksheet_0 = workbook.add_sheet("表清单")

# 第0行
worksheet_0.row(0).set_style(tall_style)
worksheet_0.write(0, 0, '表名', styleLightGreen)
worksheet_0.write(0, 1, '描述', styleLightGreen)
worksheet_0.write(0, 2, '备注', styleLightGreen)
worksheet_0.col(0).width = 8500
worksheet_0.col(1).width = 8500
worksheet_0.col(2).width = 12000

for tableStructure in tableStructureData :
    # 编写清单
    worksheet_0.row(tableStructureData.index(tableStructure)+1).set_style(tall_style)
    worksheet_0.write(tableStructureData.index(tableStructure)+1, 0, tableStructure["table_name"], styleYellow)
    # 写入超链接
    link = 'HYPERLINK("#'+tableStructure["table_comment"].replace("/", "")+'!B1";"'+tableStructure["table_comment"]+'")'
    formula = xlwt.Formula(link)
    worksheet_0.write(tableStructureData.index(tableStructure)+1, 1, formula, linkStyleYellow)
    worksheet_0.write(tableStructureData.index(tableStructure)+1, 2, "", style)
    # 创建一个worksheet
    worksheet = workbook.add_sheet(tableStructure["table_comment"].replace("/", ""))
    # 设置单元格宽度
    worksheet.col(0).width = 4200
    worksheet.col(1).width = 4200
    worksheet.col(2).width = 3000
    worksheet.col(3).width = 3000
    worksheet.col(4).width = 3000
    worksheet.col(5).width = 12000

    # 第 0 行
    # 设置高度
    worksheet.row(0).set_style(tall_style)
    worksheet.write(0, 0, '表名/描述', styleLightGreen)
    worksheet.write_merge(0, 0, 1, 2, tableStructure["table_name"], styleYellow)
    worksheet.write_merge(0, 0, 3, 5, tableStructure["table_comment"], style)
    link = 'HYPERLINK("#表清单!B1";"->返回表清单")'
    formula = xlwt.Formula(link)
    worksheet.write(0, 6, formula, linkStyle)
    # 第 1 行
    worksheet.row(1).set_style(tall_style)
    worksheet.write(1, 0, '开发简要设计', styleLightGreen)
    worksheet.write_merge(1, 1, 1, 5, "", style)

    # 第 2 行
    worksheet.row(2).set_style(tall_style)
    worksheet.write(2, 0, '数据量估算', styleLightGreen)
    worksheet.write_merge(2, 2, 1, 5, "", style)

    # 第 3 行
    worksheet.row(3).set_style(tall_style)
    worksheet.write(3, 0, '字段名', styleLightGreen)
    worksheet.write(3, 1, '类型', styleLightGreen)
    worksheet.write(3, 2, '长度', styleLightGreen)
    worksheet.write(3, 3, '允许空值', styleLightGreen)
    worksheet.write(3, 4, '默认值', styleLightGreen)
    worksheet.write(3, 5, '描述', styleLightGreen)

    # 第 4 行之后 -- 循环输出字段内容
    inx = 4
    for column in tableStructure["column"] :
        worksheet.row(inx).set_style(tall_style)
        worksheet.write(inx, 0, column["name"], styleYellow)
        worksheet.write(inx, 1, column["type"], styleYellow)
        worksheet.write(inx, 2, column["length"], style)
        worksheet.write(inx, 3, column["nullable"], styleYellow)
        worksheet.write(inx, 4, column["default"], style)
        worksheet.write(inx, 5, column["description"], style)
        inx += 1
    # 输出索引
    if "index" in tableStructure.keys() :
        worksheet.row(inx).set_style(tall_style)
        worksheet.write(inx, 0, "索引名称", styleLightGreen)
        worksheet.write(inx, 1, "唯一", styleLightGreen)
        worksheet.write(inx, 2, "字段1", styleLightGreen)
        worksheet.write(inx, 3, "字段2", styleLightGreen)
        worksheet.write(inx, 4, "字段3", styleLightGreen)
        worksheet.write(inx, 5, "更多", styleLightGreen)
        inx += 1
        for k, v  in tableStructure["index"].items() :
            worksheet.row(inx).set_style(tall_style)
            # 索引名称
            worksheet.write(inx, 0, k, styleYellow)
            for inxColName in v:
                moreName = ""
                #字段
                if(v.index(inxColName)+1 < 5) :
                    worksheet.write(inx, v.index(inxColName)+1, inxColName, style)
                else:
                    if len(v) == v.index(inxColName)+1:
                        # 最后一个,写入excel
                        worksheet.write(inx, v.index(inxColName)+1, moreName, style)
                    elif moreName:
                        moreName = moreName + "," + inxColName
                    else:
                        moreName = inxColName

            inx += 1


# 保存
workbook.save('表设计文档('+export_db_scheme+').xls')