Data-Driven Automation Testing with Python: Complete Tutorial Using Excel, CSV and PyTest

 

Introduction

Automation testing becomes more effective when the same test scenario can be executed with multiple sets of input data.

Consider an application login page. A tester may need to verify:

• Valid username and password.

• Invalid username.

• Incorrect password.

• Empty username.

• Empty password.

• Both fields left blank.

Writing a separate automation script for every combination creates unnecessary duplication. Data-driven testing solves this problem by separating test data from test logic.

Using Python, Selenium WebDriver and PyTest, testers can execute the same test with data stored in Excel files, CSV files or other external sources.

In this tutorial, we will explore how to implement data-driven automation testing using Python with practical examples.

1. What Is Data-Driven Testing?

Data-driven testing is a software testing approach in which the same test script is executed with different input values and expected results.

Instead of hardcoding every test value inside the automation script, test data is maintained separately.

For example:

Test Case	Username	Password	Expected Result
TC01	validuser	valid123	Login successful
TC02	invaliduser	wrong123	Error message
TC03	validuser	wrong123	Error message
TC04	Blank	valid123	Validation message
TC05	validuser	Blank	Validation message

The automation script reads each row and executes the login scenario.

This makes it easier to expand test coverage without repeatedly modifying the test code.

2. Why Use Data-Driven Testing in Selenium Python?

Data-driven testing offers several advantages:

Reusability: One test function can execute multiple scenarios.

Better test coverage: Different input combinations can be tested systematically.

Easy maintenance: Test data can be updated without changing the automation logic.

Reduced duplication: Common workflows do not need separate scripts for every input.

Business-friendly test data: Excel files can be reviewed and maintained by testers and business users.

Scalability: Large test suites can be organised around reusable test functions and external data sources.

3. Tools Required

For this tutorial, we will use:

• Python.

• Selenium WebDriver.

• PyTest.

• OpenPyXL for Excel files.

• CSV module for CSV data.

• VS Code or PyCharm.

Install the required packages:

python -m pip install selenium pytest openpyxl

For HTML reports, install:

python -m pip install pytest-html

Create a virtual environment to keep project dependencies separate from other Python projects.

4. Understanding Different Test Data Sources

Python supports several methods of storing and reading test data.

Data Source	Python Tool	Typical Use
Excel	OpenPyXL	Structured test data maintained in spreadsheets
CSV	Built-in csv module	Lightweight tabular test data
JSON	Built-in json module	Structured test configuration and data
Database	Database connector	Large or centrally managed test datasets
PyTest parameters	pytest.mark.parametrize	Small, directly defined test datasets

The choice depends on the size of the test suite, data maintenance requirements and project architecture.

5. Creating an Excel Test Data File

Create an Excel workbook named login_data.xlsx.

Add a worksheet named LoginData with the following columns:

Username	Password	Expected
validuser	valid123	success
invaliduser	wrong123	error
validuser	wrong123	error
blank	valid123	validation

For a real project, use a dedicated test environment and appropriate test accounts. Avoid storing production passwords or sensitive personal information in spreadsheets.

6. Reading Excel Data Using OpenPyXL

OpenPyXL allows Python scripts to read and write Excel workbooks.

Create a file called excel_reader.py:

from openpyxl import load_workbook

def read_excel_data(file_path, sheet_name):

    workbook = load_workbook(
        file_path,
        data_only=True
    )

    sheet = workbook[sheet_name]

    data = []

    headers = [
        cell.value
        for cell in sheet[1]
    ]

    for row in sheet.iter_rows(
        min_row=2,
        values_only=True
    ):
        if all(value is None for value in row):
            continue

        record = dict(zip(headers, row))
        data.append(record)

    workbook.close()

    return data

The function reads the worksheet and converts each data row into a Python dictionary.

For example:

records = read_excel_data(
    "testdata/login_data.xlsx",
    "LoginData"
)

print(records)

The returned data can be passed to PyTest test functions.

For production frameworks, it is also useful to validate required columns and handle missing or malformed workbook data explicitly.

7. Implementing Excel-Based Testing with PyTest

PyTest provides pytest.mark.parametrize for executing a test function with multiple data sets.

Create test_login_excel.py:

import pytest

from excel_reader import read_excel_data

test_data = read_excel_data(
    "testdata/login_data.xlsx",
    "LoginData"
)

@pytest.mark.parametrize(
    "record",
    test_data
)
def test_login_data(record):

    username = record["Username"]
    password = record["Password"]
    expected = record["Expected"]

    assert expected in [
        "success",
        "error",
        "validation"
    ]

    print(
        username,
        password,
        expected
    )

This example demonstrates how Excel data is supplied to a parameterized test. The assertion validates the expected-result category, not an actual application login.

In a complete Selenium test, the test function would enter the username and password, submit the form and compare the application's observed result with expected.

8. Integrating Selenium WebDriver with Excel Test Data

Now let us combine Excel data with browser automation.

Assume that the test application has:

• Username field with ID username.

• Password field with ID password.

• Login button with ID login.

• Success message with ID success.

• Error message with ID error.

• Validation message with ID validation.

These locators are illustrative. Replace them with the actual locators from your application.

Example:

import pytest

from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC

from excel_reader import read_excel_data

test_data = read_excel_data(
    "testdata/login_data.xlsx",
    "LoginData"
)

@pytest.mark.parametrize("record", test_data)
def test_login(driver, record):

    driver.get("https://example.com/login")

    username = record["Username"]
    password = record["Password"]
    expected = record["Expected"]

    username_field = driver.find_element(
        By.ID, "username"
    )

    password_field = driver.find_element(
        By.ID, "password"
    )

    username_field.clear()

    if username and username != "blank":
        username_field.send_keys(username)

    password_field.clear()

    if password and password != "blank":
        password_field.send_keys(password)

    driver.find_element(
        By.ID, "login"
    ).click()

    wait = WebDriverWait(driver, 10)

    if expected == "success":
        result = wait.until(
            EC.visibility_of_element_located(
                (By.ID, "success")
            )
        )

    elif expected == "error":
        result = wait.until(
            EC.visibility_of_element_located(
                (By.ID, "error")
            )
        )

    else:
        result = wait.until(
            EC.visibility_of_element_located(
                (By.ID, "validation")
            )
        )

    assert result.is_displayed()

This example demonstrates the overall workflow. In a production test, validate the actual message content or resulting application state, not just whether a message is visible.

The driver fixture should be defined in conftest.py to create and close a browser for each test.

9. Data-Driven Testing Using CSV Files

CSV is a simple format for storing tabular test data.

Create login_data.csv:

username,password,expected
validuser,valid123,success
invaliduser,wrong123,error
validuser,wrong123,error
blank,valid123,validation

Python provides a built-in csv module, so no additional package is needed.

Create csv_reader.py:

import csv

def read_csv_data(file_path):

    data = []

    with open(
        file_path,
        mode="r",
        newline="",
        encoding="utf-8"
    ) as file:

        reader = csv.DictReader(file)

        for row in reader:
            data.append(row)

    return data

Use the CSV data in PyTest:

import pytest

from csv_reader import read_csv_data

test_data = read_csv_data(
    "testdata/login_data.csv"
)

@pytest.mark.parametrize(
    "record",
    test_data
)
def test_login_csv(record):

    assert record["expected"] in [
        "success",
        "error",
        "validation"
    ]

The same Selenium login workflow can be reused with this CSV reader.

10. Parameterization Without External Files

For smaller test suites, PyTest can use test data directly inside the test script.

import pytest

@pytest.mark.parametrize(
    "username,password,expected",
    [
        ("validuser", "valid123", "success"),
        ("invaliduser", "wrong123", "error"),
        ("validuser", "wrong123", "error"),
        ("", "valid123", "validation")
    ]
)
def test_login(username, password, expected):

    assert expected in [
        "success",
        "error",
        "validation"
    ]

This approach is convenient for a small number of test cases. External files are generally more practical when test data is large or frequently updated.

11. Recommended Project Structure

A data-driven Selenium Python framework can be organised as follows:

selenium_data_driven/
│
├── pages/
│   ├── login_page.py
│   └── home_page.py
│
├── tests/
│   ├── test_login_excel.py
│   └── test_login_csv.py
│
├── testdata/
│   ├── login_data.xlsx
│   └── login_data.csv
│
├── utilities/
│   ├── excel_reader.py
│   └── csv_reader.py
│
├── conftest.py
├── pytest.ini
└── requirements.txt

This structure separates page interactions, test execution, test data and reusable utilities.

12. Best Practices for Data-Driven Automation Testing

1. Keep test data separate from automation logic.

2. Use meaningful column names in Excel and CSV files.

3. Validate test data before executing browser actions.

4. Include positive, negative and boundary test scenarios.

5. Avoid storing confidential passwords in plain-text files.

6. Use dedicated test accounts and test environments.

7. Prefer reusable data readers.

8. Keep test cases independent.

9. Generate clear reports for failed data combinations.

10. Use version control to manage changes to test scripts and non-sensitive test data.

For large projects, consider whether a database, test management system or controlled test-data service would be more appropriate than a spreadsheet.

13. Career Opportunities in Data-Driven Automation Testing

Data-driven testing is an important skill for automation testers working on complex applications.

After learning these concepts, learners can progress towards:

• Selenium Python framework development.

• PyTest automation.

• Excel and CSV-based test data management.

• Page Object Model implementation.

• API automation testing.

• Database testing.

• CI/CD integration.

• SDET and Automation Test Engineer roles.

Practical experience with reusable frameworks and multiple test scenarios is particularly useful during technical interviews.

Learn Selenium Python Automation Testing with Palium Skills

Palium Skills offers practical Automation Testing training for freshers, graduates, manual testers and working professionals.

The Selenium Python learning pathway can include:

• Python programming fundamentals.

• Selenium WebDriver.

• Web element handling and locators.

• PyTest framework.

• Data-driven testing using Excel and CSV.

• Page Object Model.

• Automation framework development.

• API testing fundamentals.

• Real-time automation projects.

• Interview preparation.

Training is available through classroom sessions at South Kolkata and Salt Lake, Kolkata, and instructor-led live online sessions for learners across India.

Contact Palium Skills

• Website: www.paliumskills.com

• Call: 8420594969 / 7044871915

• WhatsApp: 9903130500

• Email: info@paliumskills.com

South Kolkata Centre: 1st Floor, Sheeba Bhavan, 1/22 Poddar Nagar, Near South City Mall, Kolkata – 700068.

Salt Lake Centre: 5th Floor, RDB Boulevard, Block GP, Salt Lake Electronic Complex, Kolkata – 700091.

Frequently Asked Questions

1. What is data-driven automation testing?

It is an approach in which the same test script executes multiple times using different input data and expected results.

2. Can Selenium Python read Excel files?

Yes. Python's OpenPyXL library can read and write Excel workbooks and can be integrated with Selenium and PyTest.

3. How do I use CSV data in PyTest?

Read the CSV file using Python's built-in csv module and pass the resulting records to a parameterized test using pytest.mark.parametrize.

4. What is the difference between Excel and CSV testing data?

Excel supports multiple worksheets, formatting and richer spreadsheet features. CSV is a lightweight text-based tabular format.

5. What is parameterization in PyTest?

Parameterization allows one test function to execute with multiple input combinations using pytest.mark.parametrize.

6. Is data-driven testing useful for login automation?

Yes. It helps test valid credentials, invalid credentials, empty fields and other authentication scenarios systematically.

7. Can data-driven testing be integrated with Page Object Model?

Yes. Test data can be supplied to test functions while page objects handle browser interactions. This keeps test data, page operations and validation logic organised.