Building Automated HTTP/HTTPS API Test Scripts with Python (requests, pandas, openpyxl)

This article explains how to rapidly create Python scripts for automated HTTP/HTTPS API testing, leveraging the requests library to send requests, pandas to manage test case data, and openpyxl to export and style results in Excel.

The testing requirement involves constructing varied request bodies (typically JSON), sending them to HTTP/HTTPS endpoints, extracting and validating response fields, and storing the outcomes in an Excel file for easy review.

Request construction is performed by reading a JSON template (e.g., text.json) and customizing fields such as request ID, username, or text content. Headers and query parameters are set as needed, and SSL verification can be disabled with verify=False for HTTPS calls. The request is sent using

requests.post(url, data=json.dumps(body), headers=..., timeout=...)

.

The response is captured in a Response object (named r). JSON content is accessed via r.json() or by loading r.text with json.loads, converting it into a Python dictionary for easy field extraction.

Test cases are maintained in an Excel sheet. They are read with pandas.read_excel, producing a list of dictionaries where each dictionary represents a single test case. This structure enables flexible mapping of case parameters to request bodies.

A simple for loop iterates over the case list, sends the corresponding request, and appends each response (converted to a dictionary) to a case_result_list. The list is then turned into a DataFrame and written back to Excel.

Result styling is achieved with openpyxl. Cells containing error information are highlighted in red and bolded, and additional rows can be added to summarize metrics such as total cases, error count, and success rate.

The article concludes with links to the official documentation for requests , pandas , and openpyxl , encouraging further exploration of these powerful Python libraries.