数据驱动模式

1.封装read_excel方法

#coding:utf-8
# coding:utf-8
import xlrd,os
class ExcelUtil():
    def __init__(self, excelPath, sheetName):
        self.data = xlrd.open_workbook(excelPath)
        self.table = self.data.sheet_by_name(sheetName)
        # 获取第一行作为key值
        self.keys = self.table.row_values(0)
        # 获取总行数
        self.rowNum = self.table.nrows
        # 获取总列数
        self.colNum = self.table.ncols

    def dict_data(self):
        if self.rowNum <= 1:
            print("总行数小于1")
        else:
            r = []
            j=1
            for i in range(self.rowNum-1):
                s = {}
                # 从第二行取对应values值
                values = self.table.row_values(j)
                for x in range(self.colNum):
                    s[self.keys[x]] = values[x]
                r.append(s)
                j+=1
            return r

if __name__ == "__main__":
    xlsx_path = os.path.dirname(os.path.realpath(__file__))
    print(xlsx_path)
    filepath = xlsx_path + r"\test_data\test.xlsx"
    print(filepath)
    sheetName = "Sheet1"
    data = ExcelUtil(filepath, sheetName)
    print(data.dict_data())

 

2.准备测试用例

#准备测试数据
xlsx_path = os.path.dirname(os.path.realpath(__file__))
filepath = xlsx_path + r"\test_data\historical_agrometeorological_disasters.xlsx"
#完整用例
sheetName = "完整"
datas1 = ExcelUtil(filepath, sheetName).dict_data()
sheetName = "单引号验证"
datas6 = ExcelUtil(filepath, sheetName).dict_data()
#必填字段校验用例
sheetName = "cropCode不填"
datas2 = ExcelUtil(filepath, sheetName).dict_data()
sheetName = "districtCode不填"
datas3 = ExcelUtil(filepath, sheetName).dict_data()
sheetName = "startDate不填"
datas4 = ExcelUtil(filepath, sheetName).dict_data()
sheetName = "endDate不填"
datas5 = ExcelUtil(filepath, sheetName).dict_data()

3.在test_case中如何引用

#coding:utf-8
from common.environment_variable import EnvironmentVariableNew
import ddt,os,unittest,warnings
from common.read_excel import ExcelUtil

#实例化环境变量
evn = EnvironmentVariableNew()
#准备测试数据
xlsx_path = os.path.dirname(os.path.realpath(__file__))
filepath = xlsx_path + r"\test_data\historical_agrometeorological_disasters.xlsx"
#完整用例
sheetName = "完整"
datas1 = ExcelUtil(filepath, sheetName).dict_data()
sheetName = "单引号验证"
datas6 = ExcelUtil(filepath, sheetName).dict_data()
#必填字段校验用例
sheetName = "cropCode不填"
datas2 = ExcelUtil(filepath, sheetName).dict_data()
sheetName = "districtCode不填"
datas3 = ExcelUtil(filepath, sheetName).dict_data()
sheetName = "startDate不填"
datas4 = ExcelUtil(filepath, sheetName).dict_data()
sheetName = "endDate不填"
datas5 = ExcelUtil(filepath, sheetName).dict_data()

@ddt.ddt
class HistoricalAgrometeorologicalDisasters(unittest.TestCase):
    '''历史农业气象灾害'''
    @classmethod
    def setUpClass(cls):
        warnings.filterwarnings(action="ignore")
        pass

    @classmethod
    def tearDownClass(cls):
        pass

    @ddt.data(*datas1)
    def test_01(self,testdata):
        '''历史农业气象灾害'''
        print("测试数据：%s"%testdata)
        url = "/akat/disaster/agrometeo_disaster/multi_history_data_list"
        payload= {
            "cropCode": testdata["cropCode"],
            "districtCode": testdata["districtCode"],
            "startDate": testdata["startDate"],
            "endDate": testdata["endDate"],
            "disasterCodes": [testdata["disasterCodes"]]
            }
        expect_result = eval(testdata["expect"])
        actural_result = evn.log_in(url,payload)
        print("实际结果：",actural_result)
        self.assertEqual(expect_result,actural_result)

    @ddt.data(*datas2)
    def test_02(self,testdata):
        '''cropCode不填，提示：参数格式不正确'''
        print("测试数据：%s"%testdata)
        url = "/akat/disaster/agrometeo_disaster/multi_history_data_list"
        payload= {
            # "cropCode": testdata["cropCode"],
            "districtCode": testdata["districtCode"],
            "startDate": testdata["startDate"],
            "endDate": testdata["endDate"],
            "disasterCodes": [testdata["disasterCodes"]]
            }
        expect_result = eval(testdata["expect"])
        actural_result = evn.log_in(url,payload)
        print("实际结果：",actural_result)
        self.assertEqual(expect_result,actural_result)

    @ddt.data(*datas3)
    def test_03(self,testdata):
        '''districtCode不填，提示：参数格式不正确'''
        print("测试数据：%s"%testdata)
        url = "/akat/disaster/agrometeo_disaster/multi_history_data_list"
        payload= {
            "cropCode": testdata["cropCode"],
            # "districtCode": testdata["districtCode"],
            "startDate": testdata["startDate"],
            "endDate": testdata["endDate"],
            "disasterCodes": [testdata["disasterCodes"]]
            }
        expect_result = eval(testdata["expect"])
        actural_result = evn.log_in(url,payload)
        print("实际结果：",actural_result)
        self.assertEqual(expect_result,actural_result)

    @ddt.data(*datas4)
    def test_04(self,testdata):
        '''startDate不填，提示：参数格式不正确'''
        print("测试数据：%s"%testdata)
        url = "/akat/disaster/agrometeo_disaster/multi_history_data_list"
        payload= {
            "cropCode": testdata["cropCode"],
            "districtCode": testdata["districtCode"],
            # "startDate": testdata["startDate"],
            "endDate": testdata["endDate"],
            "disasterCodes": [testdata["disasterCodes"]]
            }
        expect_result = eval(testdata["expect"])
        actural_result = evn.log_in(url,payload)
        print("实际结果：",actural_result)
        self.assertEqual(expect_result,actural_result)

    @ddt.data(*datas5)
    def test_05(self,testdata):
        '''endDate不填，提示：参数格式不正确'''
        print("测试数据：%s"%testdata)
        url = "/akat/disaster/agrometeo_disaster/multi_history_data_list"
        payload= {
            "cropCode": testdata["cropCode"],
            "districtCode": testdata["districtCode"],
            "startDate": testdata["startDate"],
            # "endDate": testdata["endDate"],
            "disasterCodes": [testdata["disasterCodes"]]
            }
        expect_result = eval(testdata["expect"])
        actural_result = evn.log_in(url,payload)
        print("实际结果：",actural_result)
        self.assertEqual(expect_result,actural_result)

    @ddt.data(*datas6)
    def test_06(self,testdata):
        '''历史农业气象灾害 - 参数单引号验证'''
        print("测试数据：%s"%testdata)
        url = '/akat/disaster/agrometeo_disaster/multi_history_data_list'
        payload= {
            'cropCode': testdata['cropCode'],
            'districtCode': testdata['districtCode'],
            'startDate': testdata['startDate'],
            'endDate': testdata['endDate'],
            'disasterCodes': [testdata['disasterCodes']]
            }
        expect_result = eval(testdata['expect'])
        actural_result = evn.log_in(url,payload)
        print('实际结果：',actural_result)
        self.assertEqual(expect_result,actural_result)

if __name__=="__main__":
    unittest.main()