Data Driven Testing Using Excel(.xlsx) in Python Pytest

In the previous tutorial, we have done data-driven testing (DDT) using JSON data in pytest. now in this tutorial, we will be using excel(.xlsx) for data-driven testing.

As we have done the Automation of the Facebook login form with the selenium web driver in the previous tutorial. now in this scenario, we will be automating the Facebook login form and giving input data from the excel file in the form of keys then testing the login verification using the pytest framework.

Prerequisites

Python 3.6+ or pypy3
Install pytest pip install pytest
Install Data-Driven Testing pip install ddt

Data-Driven Testing Using Excel(.xlsx) in Python Pytest

Create a project folder with the name data_driven.
Create a test file in the folder with the name test_excell.py
Create another file with the name read.py to write a python module for reading excel data from data.xlsx.

test_excell.py

from lib2to3.pgen2 import driver
import pytest,unittest
from selenium import webdriver
from ddt import ddt, data,unpack
from read import getData
from selenium.webdriver.chrome.options import Options
from webdriver_manager.chrome import ChromeDriverManager
from selenium.webdriver.common.by import By

@ddt
class TestClass(unittest.TestCase):
   
    @classmethod
    def setUpClass(cls):
        cls.driver = webdriver.Chrome(executable_path=ChromeDriverManager().install(),options=Options())
        cls.driver.maximize_window()
        cls.driver.get("https://www.facebook.com")

    @data(*getData())
    @unpack
    def test_login_with_invalid_credential(self,uname,pword):
        self.driver.find_element(By.NAME,"email").send_keys(uname) 
        self.driver.find_element(By.NAME,"pass").send_keys(pword) 
        self.driver.find_element(By.NAME,"login").click()
        assert True

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

Code Explanation

Import necessary modules

import pytest,unittest
from selenium import webdriver
from ddt import ddt, data,unpack
from read import getData
from selenium.webdriver.chrome.options import Options
from webdriver_manager.chrome import ChromeDriverManager
from selenium.webdriver.common.by import By

Define decorator @ddt and class Testclass with input parameter as unittest.TestCase.
```
@ddt
class TestClass(unittest.TestCase):
```

Inside the Testclass create another decorator @classmethod and define the setup function to get the website URL and to set the selenium chrome, web driver to it.

@classmethod
def setUpClass(cls):
    cls.driver = webdriver.Chrome(executable_path=ChromeDriverManager().install(),options=Options())
    cls.driver.maximize_window()
    cls.driver.get("https://www.facebook.com")

Next use @data decorator and give input parameters as a function that have been created to get data from external data sources here from the excel file. unpack the data by using @unpack decorator and define the

test_login_with_invalid_credential function to inspect the Facebook login form input field and to give input data from the excel file.

@data(*getData())
    @unpack
    def test_login_with_invalid_credential(self,uname,pword):
        self.driver.find_element(By.NAME,"email").send_keys(uname) 
        self.driver.find_element(By.NAME,"pass").send_keys(pword) 
        self.driver.find_element(By.NAME,"login").click()
        assert True

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

read.py

import xlrd
def getData():
    data=[]
    wb = xlrd.open_workbook("data.xlsx")
    sheet = wb.sheet_by_index(0)
    for i in range(1, sheet.nrows):
        data.append((sheet.cell_value(i, 0),sheet.cell_value(i, 1)))
    return data

Code Explanation

Import xlrd to process excel data.
Define function getData() and by using xlrd.open_workbook() function open excel file name with data.xlsx.
Iterate loop over rows of excel file to get data by using wb.sheet_by_index() function.
Append data to the empty list and then return that list.

Data.xlsx

Your excel sheet must have this format

Execute the above code using the command pytest test_excell.py and here is the output test case passed successfully.

Thus, in this tutorial, we have learned how to do data-driven testing using excel files using ddt and automation of facebook login form using selenium then testing through pytest framework.