Using Python requests and xlwt for API Automation Testing

In the modern internet era, API automation testing has become a crucial part of software testing, and Python, with its simplicity and extensibility, is a popular choice for developing such tests. The standard libraries requests and xlwt help developers quickly create robust API test suites.

Benefits of using these libraries:

requests simplifies sending HTTP requests (GET, POST, PUT, DELETE) and handling responses.

It supports complex header handling and authentication schemes such as Basic, Digest, and OAuth.

Cookie and session management enable multi‑step API interactions.

xlwt allows test results to be written to Excel files, facilitating clear reporting with customizable styles.

Below is a complete Python example that demonstrates reading test cases from an Excel file, executing them with unittest , and writing the results back to another Excel file.

import requests
import xlwt
import xlrd
import unittest
class BaseTestCase(unittest.TestCase):
    url = ""
    method = "GET"
    data = {}
    headers = {}
    expected_code = 200
    expected_result = {}

    def setUp(self):
        """每个测试方法执行前调用"""
        pass

    def tearDown(self):
        """每个测试方法执行后调用"""
        pass

    def run_case(self, case):
        """动态创建的测试方法，执行测试用例"""
        self.url = case['url']
        self.method = case['method']
        self.data = case['data']
        self.headers = case.get('headers', {})
        self.expected_code = case.get('expected_code', 200)
        self.expected_result = case.get('expected_result', {})
        self.test_api()

    def test_api(self):
        """测试用例实现"""
        response = requests.request(self.method, url=self.url, headers=self.headers, data=self.data)
        self.assertEqual(response.status_code, self.expected_code)
        self.assertDictEqual(response.json(), self.expected_result)

def read_excel(file_path, sheet_name):
    workbook = xlrd.open_workbook(file_path)
    sheet = workbook.sheet_by_name(sheet_name)
    rows = sheet.nrows
    cases = []
    for i in range(1, rows):
        case = {}
        case['url'] = sheet.cell_value(i, 0)
        case['method'] = sheet.cell_value(i, 1)
        case['data'] = sheet.cell_value(i, 2)
        case['headers'] = sheet.cell_value(i, 3)
        case['expected_code'] = int(sheet.cell_value(i, 4))
        case['expected_result'] = eval(sheet.cell_value(i, 5))
        case['name'] = sheet.cell_value(i, 6)
        cases.append(case)
    return cases

def write_excel(file_path, sheet_name, cases):
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet(sheet_name)
    sheet.write(0, 0, '用例编号')
    sheet.write(0, 1, '用例名称')
    sheet.write(0, 2, '测试结果')
    for i, case in enumerate(cases):
        sheet.write(i+1, 0, i+1)
        sheet.write(i+1, 1, case.get('name', '') or case['url'])
        sheet.write(i+1, 2, '通过' if case.get('result') else '失败')
    workbook.save(file_path)

if __name__ == '__main__':
    cases = read_excel('cases.xlsx', 'Sheet1')
    suite = unittest.TestSuite()
    for case in cases:
        case_name = case.get('name', '') or case['url']
        setattr(BaseTestCase, f'test_{case_name}', lambda self, case=case: self.run_case(case))
    unittest.TextTestRunner().run(suite)
    write_excel('report.xls', 'Sheet1', cases)

To get started, install the required libraries with pip install requests xlwt , then follow the steps above to create the test base class, read/write Excel utilities, and the main execution block.