获取mysql表字段信息

数据迁移之前需要列出相关表的所有字段信息

import pymysql.cursors
import xlwt

import configUtil

schema = configUtil.schema
tableList = configUtil.table_list

# 连接数据库
connect = pymysql.Connect(
    host=configUtil.dbHost,
    port=configUtil.dbPort,
    user=configUtil.dbUser,
    passwd=configUtil.dbPassword,
    db=schema,
    charset='utf8'
)
cursor = connect.cursor()
workbook = xlwt.Workbook("")

style = xlwt.XFStyle()  # 创建一个样式对象，初始化样式
al = xlwt.Alignment()
al.horz = 0x02  # 设置水平居中
al.vert = 0x01  # 设置垂直居中
style.alignment = al
font = xlwt.Font()  # 为样式创建字体
font.name = 'Times New Roman'
font.bold = False  # 黑体
font.underline = False  # 下划线
font.italic = False  # 斜体字
font.height = 280
style.font = font  # 设定样式


def runData(tableName, sheetName):
    sql = """
        select aa.COLUMN_NAME,aa.DATA_TYPE,aa.COLUMN_COMMENT, cc.TABLE_COMMENT 
                            from information_schema.`COLUMNS` aa LEFT JOIN 
                            (select DISTINCT bb.TABLE_SCHEMA,bb.TABLE_NAME,bb.TABLE_COMMENT 
                            from information_schema.`TABLES` bb ) cc  
                            ON (aa.TABLE_SCHEMA=cc.TABLE_SCHEMA and aa.TABLE_NAME = cc.TABLE_NAME )
                            where aa.TABLE_SCHEMA = '%s' and aa.TABLE_NAME = '%s'
                            """
    sheet = workbook.add_sheet(sheetName)
    sql = sql % (schema, tableName)
    cursor.execute(sql)
    sheet.col(0).width = 6333
    sheet.col(1).width = 6333
    sheet.col(2).width = 6333
    sheet.write(0, 0, label='字段', style=style)
    sheet.write(0, 1, label='类型', style=style)
    sheet.write(0, 2, label='注释', style=style)
    i = 1
    for row in cursor.fetchall():
        sheet.write(i, 0, row[0], style)
        sheet.write(i, 1, row[1], style)
        sheet.write(i, 2, row[2], style)
        i = i + 1


if __name__ == '__main__':
    sql = "SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = '%s';"
    sql = sql % (schema)
    cursor.execute(sql)
    for row in cursor.fetchall():
        table_name = str(row[0])
        # excel的sheet名不能超过31位，如果表名过长需要截取，截取容易重复报错，需要进行表名和sheet名的映射
        runData(table_name, table_name)
    workbook.save("field.xlsx")

过程：

根据配置文件获取相关的mysql连接信息、表信息

查询出每张表的所有字段、字段类型、注释

每张表对应excel的sheet，导出