August 12, 2024August 19, 2024 vibssingh

Reading Excel Data in Python using openpyxl

Last Updated On

August 19, 2024

Reading data from Excel files is a common practice in software development and testing. Excel provides a tabular format which is easy to understand and navigate. Data can be read from Excel for manipulation, analysis and visualization using Java, Python, R and soon. There are several ways to read the data from Excel in Python, but we will discuss openpyxl.

Prerequisite:

Python is installed
PIP is installed
PyCharms is installed

Sample Excel

Implementation Steps

Step 1 – Install openpyxl

Openpyxl can be installed using pip

pip install openpyxl

Step 2 – Load the workbook

workbook = load_workbook("C:\\Users\\Vibha\\Automation\\SearchInBing.xlsx")

Step 3 – Select the active worksheet

sheet = workbook.active

Step 4 – Read the Excel (.xlsx) file

for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column):
    for cell in row:
        print(cell.value)

The complete program is

from openpyxl import load_workbook
from tabulate import tabulate

#Load the workbook
workbook = load_workbook("C:\\Users\\Vibha\\Automation\\SearchInBing.xlsx")

#Select the active worksheet
sheet = workbook.active

#Iterate over rows and columns
for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column):
    for cell in row:
        print(cell.value)

The output of the above program is

sheet.iter_rows() is used to iterate over the specified rows and columns

cell.value extracts the data from the each cell.

As we can see that the output is not displayed in structured format.

To display the output in the structure format, use the below program:

from openpyxl import load_workbook
from tabulate import tabulate

#Load the workbook
workbook = load_workbook("C:\\Users\\Vibha\\Automation\\SearchInBing.xlsx")

#Select the active worksheet
sheet = workbook.active

#Display data in a structured format
for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column):
    row_data =[]
    for cell in row:
        row_data.append((cell.value))
    print(row_data)

The output of the above program is

Display the Excel data in Tabular Format

Install the tabulate library

pip install tabulate

Below is the complete program:

from openpyxl import load_workbook
from tabulate import tabulate

#Load the workbook
workbook = load_workbook("C:\\Users\\Vibha\\Automation\\SearchInBing.xlsx")

#Select the active worksheet
sheet = workbook.active

#Display data in a tabular format
data =[]
for row in sheet.iter_rows(values_only = True):
    data.append(list(row))

print(tabulate(data, headers='firstrow', tablefmt='grid'))

sheet.iter_rows(values_only = True) reads the rowss in the sheet and values_only=True ensures that we get the cell values only. Collect the data onto a list of lists.

tabulate(data, headers=’firstrow’, tablefmt=’grid’) is used to print the data nicely in grid format. headers=firstrow uses the first row as headers and tablefmt=grid sets the format to a grid style.

The output of the above program is

We are done! Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!

August 8, 2024 vibssingh

Status Code, Line, Body, and Header Verification in Python Requests

HOME

Python requests are generally used to fetch the content from a particular resource URI. Whenever we make a request to a specified URI through Python, it returns a response object. Now, this response object would be used to access certain features such as status code, status line, body, content, headers, etc.

Table of Contents:

Prerequisite:

Prerequisite:

Python is installed on the machine
PIP is installed on the machine
PyCharm or other Python Editor is installed on the machine

If you need help in installing Python, please refer to this tutorial – How to Install Python on Windows 11.

If you need help in installing PyCharms, please refer to this tutorial – How to install PyCharms on Windows 11.

If you are using PyCharm IDE then you need to install the Request library. Execute the below command using pip.

pip install -U requests

Install pytest

pip install -U pytest

Verifying HTTP Response Status Code

A response code indicating the status of the request (e.g., 200 OK, 404 Not Found). The status code that the server returns tells us whether the request was successful or not. If the request was successful, the server sends the status code in the range of 200-299. If the request was not successful, then the status code other than the range is returned.

response.status_code attribute allows us to access the HTTP status code returned by an HTTP request.

import requests


def test_get_statuscode():

    # Define the API endpoint
    url = "https://reqres.in/api/users/2"

    # Make a GET request to the API endpoint
    response = requests.get(url)

    # Retrieve status code
    status_code = response.status_code

    # Display the results
    print("Status Code:", status_code)
    assert status_code == 200

Save above file as ResponseStatusCode_test.py and run using the below command:

pytest ResponseStatusCode_test.py -s

Note: -s is shortcut for –capture=no

The output of the above program is

Handling different status code

The requests library handles different status codes through the response.status_code attribute.

import requests


def test_get_statuscode():

    # Define the API endpoint
    url = "https://reqres.in/api/users/200000"

    # Make a GET request to the API endpoint
    response = requests.get(url)

    # Retrieve status code
    status_code = response.status_code

    # Display the results
    if status_code == 200:
        print("Request is successful")
    elif status_code == 401:
        print("Unauthorized Request")
    elif status_code == 404:
        print("Resource not found")
    elif status_code == 500:
        print("Server Error")
    elif status_code == 503:
        print("Service Unavailable")
    else:
        print("Status Code:", status_code)

The output of the above program is

Raising an Exception for HTTP Errors

response.raise_for_status() method is use to raise an HTTP Error if the HTTP response returned an unsuccessful status code like 4xx, 5xx.

import requests


def test_get_statuscode():

    # Define the API endpoint
    url = "https://reqres.in/api/users/200000"

    # Make a GET request to the API endpoint
    response = requests.get(url)

    # Retrieve status code
    status_code = response.status_code

    # Display the results
    try:
        response.raise_for_status()
        print("Request is successful")
    except requests.exceptions.HTTPError as http_err:
        print(f"HTTP error occurred : {http_err}")
    except Exception as err:
        print(f"Other error occurred: {err}")

The output of the above program is

Verifying HTTP Response Status Line

import requests


def test_get_statusline():

    # Define the API endpoint
    url = "https://reqres.in/api/users/2"

    # Make a GET request to the API endpoint
    response = requests.get(url)

    # Retrieve status line (in this example, requests library does not provide status line directly, so we can construct it)
    status_line = f"{response.status_code} {response.reason}"

    # Display the results
    print("Status Line:", status_line)
    assert status_line == "200 OK"

The output of the above program is

Verifying HTTP Response Body

response.content returns the content of the response, in bytes.

import requests


def test_get_responsebody():

    # Define the API endpoint
    url = "https://reqres.in/api/users/2"

    # Make a GET request to the API endpoint
    response = requests.get(url)

    # Retrieve response content
    content = response.content

    # Display the results
    print("Response Content:", content)

The output of the above program is

Verifying HTTP Response Header Content Type

response.headers returns a dictionary of response headers. Headers are used for different purposes such as Authentication/ Passing and retrieving data from the server, etc.

import requests


def test_get_headers():

    # Define the API endpoint
    url = "https://reqres.in/api/users/2"

    # Make a GET request to the API endpoint
    response = requests.get(url)

    # Retrieve content type
    content_type = response.headers['Content-Type']

    # Display the results
    print("Content Type:", content_type)
    assert content_type == "application/json; charset=utf-8"

The output of the above program is

We are done! Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!

August 1, 2024August 1, 2024 vibssingh

Deleting Directories in Java: Complete Tutorial

HOME

In this tutorial I will explain the steps to delete a directory in Java.

The delete() method of the File class deletes the files and empty directory represented by the current File object. If a directory is not empty or has files, then it can’t be deleted directly. First, empty the directory, then delete the folder.

Suppose there exists a directory with path Desktop\Test. The next image displays the files and directories present inside Test folder. The Test directory has folders Demo_1, Demo_2, and Demo_3.

The next java programs illustrate how to delete a directory. This example uses a recursive method to delete the directory and all its contents, including subdirectories.

package org.example;

import java.io.File;

public class DeleteDirectoryDemo {

    public static void main(String[] args) {

        String directoryPath = "C:\\Users\\Vibha\\Desktop\\Test";

        //Create a file object for the directory
        File directory = new File(directoryPath);

        if(directory.exists()&& directory.isDirectory()) {
            boolean successful = deleteDirectory(directory);
            if (successful) {
                System.out.println("Directory deleted :" + directoryPath);
            } else {
                System.out.println("Failed to delete Directory :" + directoryPath);
            }
        } else {
            System.out.println("Directory does not exists :" + directoryPath);

            }
        }

        private static boolean deleteDirectory(File directory) {
            File[] allContents = directory.listFiles();

            if (allContents != null) {
                for (File file : allContents) {
                    deleteDirectory(file);
                    System.out.println("File deleted :" + file);
                }
            }
            return directory.delete();
        }
    }

Explanation

Step 1 – Check if the directory exists.

  if(directory.exists()&& directory.isDirectory())

Step 2 – List files in the directory.

File[] allContents = directory.listFiles();

Step 3 – Delete files and subdirectories recursively

 for (File file : allContents) {
                    deleteDirectory(file);
                    System.out.println("File deleted :" + file);
                }
            }

Step 4 – Delete the empty directory.

directory.delete();

The output of the above program is

That’s it! Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!

July 17, 2024August 10, 2024 vibssingh

REST Assured: Validating HTTP Response Status Code, Line, Body, Headers, Content Type

Last Updated On

August 10, 2024

HOME

In this tutorial, we will discuss how to validate the HTTP response status code, status like, header, body and Content Type using REST Assured. Every HTTP Response received from a server in response to an HTTP request sent by the client has a status code.

Add the Rest Assured dependency in the pom.xml.

 <dependency>
            <groupId>io.rest-assured</groupId>
            <artifactId>rest-assured</artifactId>
            <version>5.5.0</version>
            <scope>test</scope>
</dependency>

Verifying HTTP Response Status Code with Rest Assured

The getStatusCode() method returns an integer and then we can verify its value.

Below is an example.

@Test
    public void verifyStatusCode() {

        String BaseURL = "https://dummy.restapiexample.com/api";

        // GIVEN
       Response response =  given()

                // WHEN
                .when()
                .get("https://reqres.in/api/users/2")

                // THEN
                .then()
               .extract().response();

       int actualStatusCode = response.getStatusCode();
       System.out.println("Status Code : " + actualStatusCode);

        Assert.assertEquals(200, actualStatusCode);

    }

The output of the above program is

Verifying HTTP Response Status Line with Rest Assured

The getStatusLine() method returns a string and then we can verify its value.

 @Test
    public void verifyStatusLine() {

        // GIVEN
        Response response =  given()

                // WHEN
                .when()
                .get("https://reqres.in/api/users/2")

                // THEN
                .then()
                .extract().response();

        String actualStatusLine = response.getStatusLine();
        System.out.println("Status Line : " + actualStatusLine);

        Assert.assertEquals("HTTP/1.1 200 OK", actualStatusLine);

    }

The output of the above program is

Verifying HTTP Response Body with Rest Assured

In the below example, we are obtaining the body content of the HTTP response using the getBody().

We have used the JsonPath to query the JSON body of the response. To know more about the JsonPath, please refer this tutorial – Extraction from JSON in Rest Assured – JsonPath.

  @Test
    public void verifyResponseBody() {

        // GIVEN
        Response response =  given()

                // WHEN
                .when()
                .get("https://reqres.in/api/users/2")

                // THEN
                .then()
                .extract().response();

        String actualResponseBody = response.getBody().asString();
        System.out.println("Response Body : " + actualResponseBody);

        JsonPath jsonPath = response.jsonPath();
        String actualResponse_Id = jsonPath.getString("data.id");
        System.out.println("Response Id : " + actualResponse_Id);

        Assert.assertEquals("2", actualResponse_Id);

    }

The output of the above program is

Verifying HTTP Response Header with Rest Assured

getHeaders() method is used to retrieve all the headers from a HTTP response.

@Test
    public void verifyResponseHeader() {

        // GIVEN
        Response response =  given()

                // WHEN
                .when()
                .get("https://reqres.in/api/users/2")

                // THEN
                .then()
                .extract().response();

        Headers allHeaders = response.getHeaders();
        System.out.println("All Headers : " + allHeaders);

       for(Header header: allHeaders){
           System.out.println(header.getName() +  ":" + header.getValue());
       }

       Assert.assertTrue(allHeaders.getValue("Content-Encoding").contains("gzip"));

    }

The output of the above program is

Verifying HTTP Response Content Type with Rest Assured

getContentType() retrieves the “Content-Type” of the header of the response body.

 @Test
    public void verifyContentType() {

        // GIVEN
        Response response =  given()

                // WHEN
                .when()
                .get("https://reqres.in/api/users/2")

                // THEN
                .then()
                .extract().response();

        String actualContentType = response.getContentType();
        System.out.println("Actual ContentType : " + actualContentType);

         Assert.assertEquals("application/json; charset=utf-8", actualContentType);

    }

The output of the above program is

We are done! Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!

July 12, 2024 vibssingh

XmlPath in Rest Assured

HOME

What is XML Path?

XML Path Language(XPath) is a query language for selecting nodes from an XML document. XPath provides a syntax for navigating through elements and attributes in an XML document. Paths can be absolute or relative:

Absolute paths starts from the root node, using a single slash (/) e.g., “//rootelement”.

Relative paths start from the current context node, using the double slashes (//) e.g., “//element”.

What is XmlPath in Rest Assured?

XmlPath is a utility that allows us to parse and query XML responses. XmlPath is analogous to JsonPath, but it is specifically designed for handling the XML data.

Add the below mentioned dependency to the pom.xml.

 <dependency>
            <groupId>io.rest-assured</groupId>
            <artifactId>xml-path</artifactId>
            <version>5.5.0</version>
 </dependency>

Sample XML File

<shopping>
 <category type="groceries">
 <item>
 <name>Chocolate</name>
 <price>10</price>
 </item>
 <item>
 <name>Coffee</name>
 <price>20</price>
 </item>
 </category>
 <category type="supplies">
 <item>
 <name>Paper</name>
 <price>5</price>
 </item>
 <item quantity="4">
 <name>Pens</name>
 <price>15</price>
 </item>
 </category>
 <category type="present">
 <item when="Aug 10">
 <name>Kathryn's Birthday</name>
 <price>200</price>
 </item>
 </category>
 </shopping>

Below is an example of using the XmlPath.

import io.restassured.path.xml.XmlPath;
import io.restassured.path.xml.element.Node;
import org.junit.Test;

import java.util.List;


public class XMLPath_Demo {

    String body = "<shopping>\n" +
            " <category type=\"groceries\">\n" +
            " <item>\n" +
            " <name>Chocolate</name>\n" +
            " <price>10</price>\n" +
            " </item>\n" +
            " <item>\n" +
            " <name>Coffee</name>\n" +
            " <price>20</price>\n" +
            " </item>\n" +
            " </category>\n" +
            " <category type=\"supplies\">\n" +
            " <item>\n" +
            " <name>Paper</name>\n" +
            " <price>5</price>\n" +
            " </item>\n" +
            " <item quantity=\"4\">\n" +
            " <name>Pens</name>\n" +
            " <price>15</price>\n" +
            " </item>\n" +
            " </category>\n" +
            " <category type=\"present\">\n" +
            " <item when=\"Aug 10\">\n" +
            " <name>Kathryn's Birthday</name>\n" +
            " <price>200</price>\n" +
            " </item>\n" +
            " </category>\n" +
            " </shopping>";

    @Test
    public void test() {
        XmlPath xmlPath = new XmlPath(body);

        //Get the name of the first category item:
        String name = xmlPath.get("shopping.category.item[0].name");
        System.out.println("Item Name: " + name);

        // Get the price of the first category price:
        String chocolatePrice = xmlPath.get("shopping.category.item[0].price");
        System.out.println("chocolatePrice :" + chocolatePrice);

        // Get the price of the second category price:
        String coffeePrice = xmlPath.get("shopping.category.item[1].price");
        System.out.println("coffeePrice :" + coffeePrice);

        //To get the number of category items:
        int itemSize = xmlPath.get("shopping.category.item.size()");
        System.out.println("Item Size: " + itemSize);

       // Get a specific category:
        Node category = xmlPath.get("shopping.category[0]");
        System.out.println("category :" + category);
        
        //To get the number of categories with type attribute equal to 'groceries':
        int groceryCount = xmlPath.get("shopping.category.findAll { it.@type == 'groceries' }.size()");
        System.out.println("groceryCount :" + groceryCount);

        //Get all items with price greater than or equal to 10 and less than or equal to 20:
        List<Node> itemsBetweenTenAndTwenty = xmlPath.get("shopping.category.item.findAll { item -> def price = item.price.toFloat(); price >= 10 && price <= 20 }");
        System.out.println("itemsBetweenTenAndTwenty :" + itemsBetweenTenAndTwenty);

       // Get the chocolate price:
        int priceOfChocolate = xmlPath.getInt("**.find { it.name == 'Chocolate' }.price");
        System.out.println("priceOfChocolate :" + priceOfChocolate);
    }
}

The output of the above program is

That’s it! Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!

July 9, 2024July 9, 2024 vibssingh

Parameterizing REST Assured Tests with junit4-dataprovider Dependency

HOME

In this tutorial we’re going to talk about parameterizing the REST Assured tests.

What is Parameterization?

Parameterization refers to the process of passing different set of data to the same code. This allows you to verify that the code behaves as expected for various inputs without writing multiple test cases. It enhances the coverage of the test suite by testing the functionality with different data sets and helps identify edge cases or potential bugs that could occur with different input values.

We are using junit4-dataprovider dependency here.

<!-- Data Provider -->
 <dependency>
      <groupId>com.tngtech.junit.dataprovider</groupId>
      <artifactId>junit4-dataprovider</artifactId>
      <version>${junit.dataprovider.version}</version>
      <scope>test</scope>
</dependency>

Add the necessary dependencies to the project.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.example</groupId>
  <artifactId>Parameterized_APITests</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>Parameterized_APITests</name>
  <url>http://maven.apache.org</url>

  <properties>
    <rest-assured.version>5.4.0</rest-assured.version>
    <junit.version>4.13.2</junit.version>
    <junit.dataprovider.version>2.10</junit.dataprovider.version>
    <maven.compiler.plugin.version>3.12.1</maven.compiler.plugin.version>
    <maven.surefire.plugin.version>3.2.3</maven.surefire.plugin.version>
    <maven.compiler.source>17</maven.compiler.source>
    <maven.compiler.target>17</maven.compiler.target>
  </properties>

  <dependencies>
    <!-- Rest-Assured Dependency -->
    <dependency>
      <groupId>io.rest-assured</groupId>
      <artifactId>rest-assured</artifactId>
      <version>${rest-assured.version}</version>
      <scope>test</scope>
    </dependency>

    <!-- JUnit4 Dependency -->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>${junit.version}</version>
      <scope>test</scope>
    </dependency>

    <!-- Data Provider -->
    <dependency>
      <groupId>com.tngtech.junit.dataprovider</groupId>
      <artifactId>junit4-dataprovider</artifactId>
      <version>${junit.dataprovider.version}</version>
      <scope>test</scope>
    </dependency>
    
  </dependencies>

  <build>

    <plugins>

      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>${maven.compiler.plugin.version}</version>
        <configuration>
          <source>${maven.compiler.source}</source>
          <target>${maven.compiler.target}</target>
        </configuration>
      </plugin>

      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-surefire-plugin</artifactId>
        <version>${maven.surefire.plugin.version}</version>
        <configuration>
          <testFailureIgnore>true</testFailureIgnore>
        </configuration>
      </plugin>

    </plugins>
  </build>
</project>

Create a test data collection

The first step is to create a test data collection, which is a collection of input and expected outcome values that we want to feed to our test.

@RunWith(DataProviderRunner.class): This annotation tells JUnit to run the tests in this class using the DataProviderRunner, which allows the use of the @DataProvider annotation for parameterized tests

@RunWith(DataProviderRunner.class)

@DataProvider: This annotation marks the method responseData as a provider of test data. The method returns a 2D array of objects (Object[][]), where each inner array represents a set of parameters for a single test run.
The responseData method provides four sets of parameters: an ID, a first name, and a last name.

 @DataProvider
    public static Object[][] responseData() {
        return new Object[][] {
                { "1", "George", "Bluth" },
                { "2", "Janet", "Weaver" },
                { "3", "Emma", "Wong" },
                { "4", "Eve", "Holt" }
        };
    }

Create the Test using the parameterized data

import com.tngtech.java.junit.dataprovider.DataProvider;
import com.tngtech.java.junit.dataprovider.DataProviderRunner;
import com.tngtech.java.junit.dataprovider.UseDataProvider;
import org.junit.Test;
import org.junit.runner.RunWith;

import static io.restassured.RestAssured.given;
import static org.hamcrest.CoreMatchers.equalTo;

@RunWith(DataProviderRunner.class)
public class ParameterizedTets {

    @DataProvider
    public static Object[][] responseData() {
        return new Object[][] {
                { "1", "George", "Bluth" },
                { "2", "Janet", "Weaver" },
                { "3", "Emma", "Wong" },
                { "4", "Eve", "Holt" }
        };
    }

    @Test
    @UseDataProvider("responseData")
    public void verifyResponse(String id, String expectedFirstName, String expectedLastName)
    {
        given().
                when().
                pathParam("id", id).
                get("https://reqres.in/api/users/{id}").
                then().
                assertThat().
                statusCode(200).
                body("data.first_name", equalTo(expectedFirstName)).
                body("data.last_name", equalTo(expectedLastName));
    }
}

This code defines a parameterized test that verifies the first name and last name of users returned by the API https://reqres.in/api/users/{id} for given user IDs. The test runs multiple times, once for each set of parameters provided by the responseData method. The RestAssured library is used to perform the API requests and assertions.

The output of the above program is

Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!! Cheers!!

July 3, 2024August 7, 2024 vibssingh

How to handle HTTP Query Parameters in Python

Last Updated On

August 7, 2024

HOME

Query parameters are a way to pass information to an API flexibly and simply. They are added to the end of the API endpoint URL as a series of key-value pairs. To append query params to the end of a URL, a ‘?’ Is added followed immediately by a query parameter.

Prerequisite:

Python is installed on the machine
PIP is installed on the machine
PyCharm or other Python Editor is installed on the machine
Install pytest and requests

If you need help in installing Python, please refer to this tutorial – How to Install Python on Windows 11.

If you need help in installing PyCharms, please refer to this tutorial – How to install PyCharms on Windows 11.

Query Param Example

https://reqres.in/api/users?page=2

Below is an example of Query Param in Postman

Below is an example of Query Param in Python

import requests

ENDPOINT = 'https://reqres.in/api/users/'


def test_verify_header():
    params = {
        'pages': 2
    }

    response = requests.get(ENDPOINT, params)
    print(response.json())

    print("Response Header", response.headers)

    #Assert status code
    assert response.status_code == 200

    # Assert Response Body
    response_body = response.json()
    assert response_body["per_page"] == 6

    assert response_body["data"][0]["id"] == 1

The output of the above program is

The tests can be run using the command line using the below command:

pytest QueryParam_test.py

The output of the above program is

Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!

July 1, 2024July 1, 2024 vibssingh

How to handle async requests in API Testing

HOME

What is async requests?

Asynchronous request is where the client continues execution after initiating the request and processes the result whenever the AppServer makes it available.

The primary use case for Asynchronous HTTP is where the client polls the server for a delayed response. A common example is an AJAX chat client that pushes/pulls from both the client and the server. In this scenario, the client blocks a long period of time on the server’s socket while waiting for a new message.

Async is multi-thread, which means operations or programs can run in parallel. Sync is a single-thread, so only one operation or program will run at a time. Async is non-blocking, which means it will send multiple requests to a server.

We need to add the below mentioned dependency to pom.xml to test the async requests.

<dependency>
            <groupId>org.asynchttpclient</groupId>
            <artifactId>async-http-client</artifactId>
            <version>3.0.0.Beta3</version>
</dependency>

Below is the example of async request in Postman.

Below is an example of handling async request in API Testing.

package org.example;

import com.jayway.jsonpath.JsonPath;
import org.asynchttpclient.Dsl;
import org.asynchttpclient.Response;
import org.junit.Assert;
import org.junit.Test;

import java.util.concurrent.ExecutionException;
import java.util.concurrent.Future;

import static org.hamcrest.MatcherAssert.assertThat;


public class AsyncRequest_Demo {

    @Test
    public void verifyResponse() throws ExecutionException, InterruptedException {
         Future<Response> futureResponse = Dsl.asyncHttpClient().prepareGet("https://reqres.in/api/users?delay=5").execute();
         Response response = futureResponse.get();

         System.out.println("Response :" + response);

         Assert.assertEquals(200, response.getStatusCode());
         Assert.assertTrue(response.toString().contains("george.bluth@reqres.in"));

    }
}

The output of the above programs is

June 26, 2024August 8, 2024 vibssingh

How to insert data in SQL Server using Java

Last Updated on

August 8, 2024

HOME

Database testing is an essential aspect of software testing that focuses on verifying the correctness, reliability, and performance of the database components of an application. Database testing automation eliminates manual efforts involved in executing repetitive and time-consuming tasks, such as data setup, data validation, and query optimization. This article will teach us how to insert a new row in the table in Microsoft SQL Server by establishing a Java connection

Why do we need to connect to databases in automation?

To get test data – If the database is automated, test data may be immediately retrieved and processed using a test automation script.
To verify the result – To confirm the outcome, we may use automation to cross-check the front-end result to the database’s back-end record.
To delete test data created – It is best practice in automation to remove test data that has been created. To do this, we use database automation and immediately execute the delete query.
To update certain data – Update queries can be used to update test data per the requirements of the test script.

Before inserting a new row in the table

After inserting a row

Connecting to SQL Server in Java

Get a connection to Database
Create a statement object
Execute the SQL query
Process the Result Set

Get a connection to Database

Need a connection string in the form of a JDBC URL

jdbc:<driver protocol>:<driver connection details>

You can connect to MySQL, MS MySql Server, or Oracle using the below syntax:

MS MySql Server - jdbc:odbc:DemoDSN
MySQL - jdbc:mysql://localhost:3306/demodb
Oracle - jdbc:orac;e:thin@myserver:1521:demodb

Create a statement object

The statement object is based on connection. It will be used later to execute SQL query

String dbUrl = "jdbc:mysql://localhost:3306/demo";
String username = "student";
String password = "student1$";

Connection conn = DriverManager.getConnection(dbUrl,username,password)
Statement stmt = conn.createStatement();

Execute the SQL query

Pass in the SQL query. Here, we want to insert data, so we need to use executeUpdate.

executeUpdate – Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
This method cannot be called on a PreparedStatement or CallableStatement.

 int rowAffected = stmt.executeUpdate(
                    "insert into employees (last_name, first_name, email, department,salary) values ('Singh', 'Vibha','vibha.test@gmail.com', 'QA', 85000)");

Process the resultSet

Result Set is initially placed before the first row.
Method: Result.next() -moves forward one row returns true if there are more rows to process
Looping through the result set.

DriverManager helps to connect an application based on the database connection string
JDBC API is defined by package – java.sql and javax.sql

Add mysql-connector-j dependency to the pom.xml.

<dependencies>
    <dependency>
      <groupId>com.mysql</groupId>
      <artifactId>mysql-connector-j</artifactId>
      <version>8.2.0</version>
</dependency>

Sample code to connect to SQL Server database

package org.example;

import java.sql.*;

public class InsertRow_Demo {
    public static void main(String[] args) throws SQLException {

        Connection conn;
        Statement stmt = null;
        ResultSet result = null;
        ResultSet result1 = null;
        ResultSet result2 = null;

        String dbUrl = "jdbc:mysql://localhost:3306/demo";
        String username = "student";
        String password = "student1$";

        try {
            //Get a connection to database
            conn = DriverManager.getConnection(dbUrl, username, password);

            System.out.println("Database connection is successful\n");

            //Create a statement
            stmt = conn.createStatement();

            System.out.println("Inserting a new employee\n");

            int rowAffected = stmt.executeUpdate(
                    "insert into employees (last_name, first_name, email, department,salary) values ('Singh', 'Vibha','vibha.test@gmail.com', 'QA', 85000)");

            System.out.println("No of rows inserted :" + rowAffected);

            //Execute the SQL Query
            result = stmt.executeQuery("Select * from employees");

            //Process the result set
            while (result.next()) {
                System.out.println("First_Name :" + result.getString("first_name") + " , " + ("Last_Name :" + result.getString("last_name")));

            }


        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

The output of the above program is

We are done! Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!

June 25, 2024 vibssingh

How to Use dependsOnMethods() in TestNG for Selenium Test Case Dependency

HOME

In the previous tutorial, we discussed How to disable Test Cases using TestNG. In this tutorial, we will see how we can create a dependency between Selenium Test Cases using TestNG.

Imagine there is a situation in which a test case can only be executed if a particular test case executes successfully. This can be achieved in TestNG by dependsOnMethod().

Below is an example.

To Run the TestNG program, right-click on the Java Program – here it is TestNGMethodDependencyDemo, select Run As TestNG Test(Eclipse).

Below is an example of dependsOnMethod() method.

import org.testng.annotations.Test;

public class TestNGMethodDependencyDemo {

    @Test
    public static void FirstTest() {
        System.out.println("This is Test Case 1");
    }

    @Test(dependsOnMethods = "FirstTest")
    public static void SecondTest() {
        System.out.println("This is Test Case 2 and will be executed after Test Case 1 successfully executed");
    }

    @Test
    public static void ThirdTest() {
        System.out.println("This is Test Case 3");
    }

    @Test
    public static void FourthTest() {
        System.out.println("This is Test Case 4");
    }
}

The output of the above program is

In the below scenario, Test Case 2 is dependent on Test CASE 1. If Test Case 1 fails, then Test Case 2 will skip.

package TestNGDemo;

import org.testng.annotations.Test;
public class TestNGMethodDependencyErrorDemo {

      @Test
      public static void FirstTest() {
            System.out.println("This is Test Case 1");
            throw new RuntimeException();
      }

      @Test(dependsOnMethods = "FirstTest")
      public static void SecondTest() {
          System.out.println("This is Test Case 2 and will be executed after Test Case 1 sucessfully executed");
     }

     @Test
     public static void ThirdTest() {
          System.out.println("This is Test Case 3");
     }

      @Test
      public static void FourthTest() {
            System.out.println("This is Test Case 4");
    }
}

The output of the above program is

We are done. Congratulations on making it through this tutorial and hope you found it useful!

QA Automation Expert

Automation solutions to build Test Framework

Author: vibssingh

Reading Excel Data in Python using openpyxl

Prerequisite:

Implementation Steps

Display the Excel data in Tabular Format

Status Code, Line, Body, and Header Verification in Python Requests

Prerequisite:

Verifying HTTP Response Status Code

Handling different status code

Raising an Exception for HTTP Errors

Verifying HTTP Response Status Line

Verifying HTTP Response Body

Verifying HTTP Response Header Content Type

Deleting Directories in Java: Complete Tutorial

REST Assured: Validating HTTP Response Status Code, Line, Body, Headers, Content Type

XmlPath in Rest Assured

What is XML Path?

What is XmlPath in Rest Assured?

Parameterizing REST Assured Tests with junit4-dataprovider Dependency

What is Parameterization?

Add the necessary dependencies to the project.

Create a test data collection

Create the Test using the parameterized data

How to handle HTTP Query Parameters in Python

Prerequisite:

How to handle async requests in API Testing

What is async requests?

How to insert data in SQL Server using Java

Connecting to SQL Server in Java

Get a connection to Database

Create a statement object

Execute the SQL query

Process the resultSet

How to Use dependsOnMethods() in TestNG for Selenium Test Case Dependency