python 查询mysql数据导出excl_Mysql数据导出到excel-基于python

import pymysql

import xlwt

#连接数据库函数1

def get_conn():

coon = pymysql.connect(user='root',passwd='123456',db='autotest',port=3306,host=192.168.0.1,charset='utf8')

return coon

#执行查询数据函数2

def query_all(cur, sql, args):

cur.execute(sql, args)

return cur.fetchall()

#导出测试用例到export_to_excel_app_case.xls函数3

def read_mysql_to_xlsx2(filename):

list_table_head = ['用例编号', '用例名称', '测试结果', '测试人员', '测试时间', '项目']

workbook = xlwt.Workbook()

sheet = workbook.add_sheet('data', cell_overwrite_ok=True)

for i in range(len(list_table_head)):

sheet.write(0,i, list_table_head[i])

conn = get_conn() #调用连接数据库函数

cur = conn.cursor()

sql = 'SELECT * from app_interface_appcase where app_interface_appcase.module = "testcase"' #查询用例sql语句

results = query_all(cur, sql, None) #调用函数，定义记录查询到的数据 conn.commit()

cur.close()

conn.close()

row = 1

for resultin results:

col = 0

print(type(result))

print(result)

foritem in result:

print(item)

sheet.write(row, col, item)

col += 1

row += 1

workbook.save(filename)

#导出测试用例步聚到export_to_excel_app_casestep函数4

def read_mysql_to_xlsx(filename):

list_table_head = ['用例编号', '用例描述', '测试步聚', '测试对象名称描述', '定位方式', '控件元素类型', '控件元素', '操作方法', '测试数据', '验证数据', '步聚测试结果', '测试时间', '用例测试结果', '测试人员'] #定义表头

workbook = xlwt.Workbook()

sheet = workbook.add_sheet('data', cell_overwrite_ok=True)

for i in range(len(list_table_head)):

sheet.write(0,i, list_table_head[i])

conn = get_conn() #调用连接数据库函数

cur = conn.cursor()

sql = 'SELECTa.id,appcasename,appteststep,apptestobjname,appfindmethod,appevelementtype,appevelement,appoptmethod,apptestdata,appassertdata,b.apptestresult,b.create_time,a.apptestresult,apptesterfrom app_interface_appcase as a,app_interface_appcasestep as b wherea.id=b.Appcase_id and module="testcase"' #查询用例及步聚数据

results = query_all(cur, sql, None) #调用函数，定义记录查询到的数据

conn.commit()

cur.close()

conn.close()

row = 1

for result in results: #把结果循环写入到sheet

col = 0

print(type(result))

print(result)

for item in result:

print(item)

sheet.write(row, col, item)

col += 1

row += 1

workbook.save(filename) #保存到excel文件

if __name__ == '__main__':

#调用导出测试用例函数

read_mysql_to_xlsx2('export_to_excel_app_case.xls')

#调用导出用例步聚函数

read_mysql_to_xlsx('export_to_excel_app_casestep.xls')