# python 代码将指定的mysql 数据的表结构写入到excel,用于表的分析 代码如下
import pymysql
import xlwt
connect = pymysql.connect(host="host",user="user",password="password",port=3306,db="CN_Proj_DB")
cursor = connect.cursor()
# 创建一个workbook 设置编码
workbook = xlwt.Workbook(encoding='utf-8')
# 创建一个worksheet
worksheet = workbook.add_sheet('whois')
head = ("表名","字段","类型","默认值","字段说明")
row = 0
col = 0
def read_table(file):
rfile = open(file, mode='r')
tables = rfile.readlines()
return tables
def write_tuple(tuple):
global row,col
tmp = col
for key in tuple:
worksheet.write(row,col,key)
col = col + 1
row = row +1
col = tmp
def write_to_excel(table_info):
global row,col
write_tuple(head)
for table in table_info:
feilds = table_info[table]
worksheet.write_merge(row,row+len(feilds)-1,0,0,table)
col = 1
for feild in feilds:
feild_attr = (feild[0],feild[1],feild[4])
write_tuple(feild_attr)
def save_book():
workbook.save("test.xls") #excel文件
def getTableInfo(table):
cursor.execute("describe {}".format(table))
feilds = cursor.fetchall()
return feilds
file = "table" #表文件
tables = read_table(file)
table_info = {}
for table in tables:
feild_tuple = getTableInfo(table)
table_info[table]=feild_tuple
write_to_excel(table_info)
save_book()