'''
定义导出数据库表数据到excel
1.init方法初始化获取配置文件数据
2.export2excel为主方法
'''
from xlrd import open_workbook
from xlutils.copy import copy
import os,logging,datetime
from public import config
from common import opmysql
operation_db=opmysql.OperationDbInterface(link_type=1)
class AnalyseData(object):
'''
定义对接口测试数据分析的类,包含的方法有:
1.导出测试数据到excel
'''
def __init__(self):
self.field= config.field_excel
def export2excel(self,names_export):
'''
:param names_export: 待导出的接口名称,列表形式数据
:return:
'''
counts_export=len(names_export)
fail_export=[]
try:
src = open_workbook(config.src_path + '/report/report_module.xls',formatting_info=True)
destination = copy(src)
dt=datetime.datetime.now().strftime("%Y%m%d%H%M%S")
filepath=config.src_path+'/report/'+str(dt)+'.xls'
destination.save(filepath)
for name_interface in names_export:
cases_interface=operation_db.select_all("select * from case_interface where case_status=1 and name_interface='%s'"
%(name_interface))
if len(cases_interface['data'])!=0 and cases_interface['code']=='0000':
src = open_workbook(filepath,formatting_info=True)
destination = copy(src)
sheet = destination.add_sheet(name_interface,cell_overwrite_ok=True)
for col in range(0,len(self.field)):
sheet.write(0,col,self.field[col])
for row in range(1,len(cases_interface['data'])+1):
for col in range(0,len(self.field)):
sheet.write(row,col,'%s' %cases_interface['data'][row-1][col])
destination.save(filepath)
elif len(cases_interface['data'])==0 and cases_interface['code']=='0000':
fail_export.append(name_interface)
else:
fail_export.append(name_interface)
result={'code':'0000','message':'导出总数:%s,失败数:%s'
%(counts_export,len(fail_export)),'data':fail_export}
except Exception as error:
result={'code':'9999','message':'导出过程异常|导出总数:%s,失败数:%s'
%(counts_export,len(fail_export)),'data':fail_export}
logging.basicConfig(filename = os.path.join(os.getcwd(), config.src_path + '/log/syserror.log'),level = logging.DEBUG,format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s')
logger = logging.getLogger(__name__)
logger.exception(error)
return result
if __name__ == "__main__":
names_export=operation_db.select_one("select value_config from config_total where status=1 and key_config='name_export'")
if names_export['code']=='0000':
temp_export=eval(names_export['data'][0])
test_analyse_data=AnalyseData()
result_export=test_analyse_data.export2excel(temp_export)
print (result_export)
else:
print('获取导出接口集失败')