Using Python requests and xlwt for API Automation Testing

In the era of internet services, API automation testing has become a crucial part of software quality assurance, and Python—thanks to its simplicity and extensibility—serves as a popular language for implementing such tests. The standard libraries requests and xlwt together enable developers to quickly create robust test suites.

Benefits of requests :

Conveniently send HTTP requests (GET, POST, PUT, DELETE, etc.) and handle responses.

Support for complex header manipulation and authentication schemes such as Basic, Digest, and OAuth.

Built‑in handling of cookies and sessions for multi‑step API workflows.

Example:

import requests
response = requests.get('http://example.com')
response = requests.get('http://example.com', auth=('user', 'pass'))

Benefits of xlwt :

Export test results to Excel, allowing easy generation of readable reports.

Customize cell styles, fonts, colors, and borders for clear presentation.

Example:

import xlwt
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet('Sheet1')
sheet.write(0, 0, '接口名称')
sheet.write(0, 1, '测试结果')
sheet.write(1, 0, 'test API')
sheet.write(1, 1, 'Pass')
book.save('result.xls')

The full demo combines these libraries with unittest and xlrd to read test cases from an Excel file, dynamically generate test methods, execute them, and finally write a result report back to Excel.

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)

This simple Python project demonstrates how to manage test cases with Excel, support multiple HTTP methods and data formats, and produce automated test reports, making it suitable for a wide range of API testing scenarios.