Python3实现从mysql导出数据到excel（二）

import pymysql.cursors
import xlwt
import sys
import importlib
importlib.reload(sys)
def export(table_name,outputpath):
conn=pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    passwd='XXXX',
    db='数据库',
    charset='utf8')
cur=conn.cursor()

cur.execute("create table TestCase(Id int(11),Module varchar(220),Test_name varchar(220),Handle_step varchar(220),Expect_result varchar(220),Complete_status varchar(220),Test_result varchar(220),Bug_principal varchar(220),Acceptor varchar(220))")
sqli="insert into TestCase values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
cur.executemany(sqli,[
('1','微站','banner','python','python','python','python','bug负责人','验收人'),
('2','PC端','python','python','python','python','python','bug负责人','验收人')
])

sql="select * from TestCase"
count=cur.execute(sql)  
print(count)

# 重置游标的位置
cur.scroll(0,mode='absolute')
result=cur.fetchall()


fields=cur.description
workbook=xlwt.Workbook()
sheet=workbook.add_sheet(table_name,cell_overwrite_ok=True)

# 写上字段信息
for field in range(0,len(fields)):
    sheet.write(0,field,fields[field][0])

# 获取并写入数据段信息
row=1
col=0
for row in range(1,len(result)+1):
    for col in range(0,len(fields)):
        sheet.write(row,col,u'%s'%result[row-1][col])

workbook.save(outputpath)

cur.close()  
conn.commit() 
conn.close()

if __name__=='__main__':
  export('TestCase',r'./TestCase.xlsx')