Extracting Test Execution Data from Xray Cloud and Writing to Excel using GraphQL

Codegen using OpenAPI

In this tutorial, we will walk through the process of extracting test execution data from Xray Cloud using the GraphQL API and writing the data to an Excel file. We will use Python and the following libraries:

  • gql for making GraphQL queries.
  • openpyxl for working with Excel files.
  • requests for making HTTP requests.

The tutorial will be structured as follows:

  1. Prerequisites
  2. Set Up Authentication
  3. Define GraphQL Query
  4. Extract Test Execution Data
  5. Write Data to Excel
  6. Full Python Script
  7. Conclusion

Prerequisites

Before you begin, make sure you have the following prerequisites in place:

  • Python installed on your system.
  • Necessary Python libraries installed (gql, openpyxl, and requests).
  • Access to Xray Cloud with a client ID and client secret.
  • An Excel file where you want to store the extracted data.

Set Up Authentication

In order to access the Xray Cloud GraphQL API, you need to obtain an access token using your client ID and client secret. The get_token function in the provided code can be used to do this. Replace the client_id and client_secret with your own values.

client_id = "your_client_id"
client_secret = "your_client_secret"
token_endpoint = 'https://xray.cloud.getxray.app/api/v1/authenticate'
 
access_token = get_token(client_id, client_secret, token_endpoint)
client_id = "your_client_id"
client_secret = "your_client_secret"
token_endpoint = 'https://xray.cloud.getxray.app/api/v1/authenticate'
 
access_token = get_token(client_id, client_secret, token_endpoint)

Define GraphQL Query

The GraphQL query in the code retrieves test execution data from Xray Cloud. You can modify the query to suit your specific needs. The gql library allows you to create complex queries easily.

graphql_query = gql("""
query($limit: Int!, $start: Int!, $jql: String!)
{
    getTestExecutions(jql: $jql, limit: 1) {
        total
        start
        results {
        issueId
        jira(fields: ["key", "summary"])
        projectId
        testRuns(limit: $limit, start: $start) {
            total
            start
            results {
            status {
                name
            }
            test {
              jira(fields:["key", "summary", "assignee"])
            }
            }
        }
        }
    }
}
""")
graphql_query = gql("""
query($limit: Int!, $start: Int!, $jql: String!)
{
    getTestExecutions(jql: $jql, limit: 1) {
        total
        start
        results {
        issueId
        jira(fields: ["key", "summary"])
        projectId
        testRuns(limit: $limit, start: $start) {
            total
            start
            results {
            status {
                name
            }
            test {
              jira(fields:["key", "summary", "assignee"])
            }
            }
        }
        }
    }
}
""")

Extract Test Execution Data

The get_xray_data function is responsible for making GraphQL requests to Xray Cloud, paginating through the results, and returning the data as a list of test executions.

data = get_xray_data(jql_query_API, access_token)
data = get_xray_data(jql_query_API, access_token)

Write Data to Excel

The write_to_excel function is used to write the extracted test execution data to an Excel file. The function takes the worksheet and data as arguments and clears any existing data before writing the new data.

file_path = "path_to_your_excel_file.xlsx"
worksheet_name = 'Sheet1'
 
wb = openpyxl.load_workbook(filename=file_path)
ws = wb[worksheet_name]
 
if data:
    write_to_excel(ws, data)
    wb.save(filename=file_path)
file_path = "path_to_your_excel_file.xlsx"
worksheet_name = 'Sheet1'
 
wb = openpyxl.load_workbook(filename=file_path)
ws = wb[worksheet_name]
 
if data:
    write_to_excel(ws, data)
    wb.save(filename=file_path)

Full Python Script

Here's the full Python script that combines all the steps above:

from gql import Client, gql
from gql.transport.requests import RequestsHTTPTransport
import openpyxl
import requests
 
# Set up your authentication
client_id = "your_client_id"
client_secret = "your_client_secret"
token_endpoint = 'https://xray.cloud.getxray.app/api/v1/authenticate'
access_token = get_token(client_id, client_secret, token_endpoint)
 
# Define your GraphQL query
graphql_query = gql("""
query($limit: Int!, $start: Int!, $jql: String!)
{
    getTestExecutions(jql: $jql, limit: 1) {
        total
        start
        results {
        issueId
        jira(fields: ["key", "summary"])
        projectId
        testRuns(limit: $limit, start: $start) {
            total
            start
            results {
            status {
                name
            }
            test {
              jira(fields:["key", "summary", "assignee"])
            }
            }
        }
        }
    }
}
""")
 
# Get API Execution Data
worksheet_name = 'API_Execution_Raw'
jql_query_API = "project=VIVAQE and summary ~ 'API Automation'"
data = get_xray_data(jql_query_API, access_token)
file_path = "path_to_your_excel_file.xlsx"
wb = openpyxl.load_workbook(filename=file_path)
ws = wb[worksheet_name]
 
if data:
    write_to_excel(ws, data)
    wb.save(filename=file_path)
    print(f'Test Execution: {executionKey} - {executionSummary}')
    print(f'Total tests found: {len(data)}')
 
# Get UI Execution Data (you can repeat this section for different data)
worksheet_name = 'UI_Execution_Raw'
jql_query_UI = "project=VIVAQE and summary ~ 'UI Automation'"
data = get_xray_data(jql_query_UI, access_token)
wb = openpyxl.load_workbook(filename=file_path)
ws = wb[worksheet_name]
 
if data:
    write_to_excel(ws, data)
    wb.save(filename=file_path)
    print(f'Test Execution: {executionKey} - {executionSummary}')
    print(f'Total tests found: {len(data)}')
 
# Get UAT Execution Data (you can repeat this section for different data)
worksheet_name = 'UAT_Execution_Raw'
jql_query_UAT = "project=VIVAQE and key=VIVAQE-873"
data = get_xray_data(jql_query_UAT, access_token)
wb = openpyxl.load_workbook(filename=file_path)
ws = wb[worksheet_name]
 
if data:
    write_to_excel(ws, data)
    wb.save(filename=file_path)
    print(f'Test Execution: {executionKey} - {executionSummary}')
    print(f'Total tests found: {len(data)}')
from gql import Client, gql
from gql.transport.requests import RequestsHTTPTransport
import openpyxl
import requests
 
# Set up your authentication
client_id = "your_client_id"
client_secret = "your_client_secret"
token_endpoint = 'https://xray.cloud.getxray.app/api/v1/authenticate'
access_token = get_token(client_id, client_secret, token_endpoint)
 
# Define your GraphQL query
graphql_query = gql("""
query($limit: Int!, $start: Int!, $jql: String!)
{
    getTestExecutions(jql: $jql, limit: 1) {
        total
        start
        results {
        issueId
        jira(fields: ["key", "summary"])
        projectId
        testRuns(limit: $limit, start: $start) {
            total
            start
            results {
            status {
                name
            }
            test {
              jira(fields:["key", "summary", "assignee"])
            }
            }
        }
        }
    }
}
""")
 
# Get API Execution Data
worksheet_name = 'API_Execution_Raw'
jql_query_API = "project=VIVAQE and summary ~ 'API Automation'"
data = get_xray_data(jql_query_API, access_token)
file_path = "path_to_your_excel_file.xlsx"
wb = openpyxl.load_workbook(filename=file_path)
ws = wb[worksheet_name]
 
if data:
    write_to_excel(ws, data)
    wb.save(filename=file_path)
    print(f'Test Execution: {executionKey} - {executionSummary}')
    print(f'Total tests found: {len(data)}')
 
# Get UI Execution Data (you can repeat this section for different data)
worksheet_name = 'UI_Execution_Raw'
jql_query_UI = "project=VIVAQE and summary ~ 'UI Automation'"
data = get_xray_data(jql_query_UI, access_token)
wb = openpyxl.load_workbook(filename=file_path)
ws = wb[worksheet_name]
 
if data:
    write_to_excel(ws, data)
    wb.save(filename=file_path)
    print(f'Test Execution: {executionKey} - {executionSummary}')
    print(f'Total tests found: {len(data)}')
 
# Get UAT Execution Data (you can repeat this section for different data)
worksheet_name = 'UAT_Execution_Raw'
jql_query_UAT = "project=VIVAQE and key=VIVAQE-873"
data = get_xray_data(jql_query_UAT, access_token)
wb = openpyxl.load_workbook(filename=file_path)
ws = wb[worksheet_name]
 
if data:
    write_to_excel(ws, data)
    wb.save(filename=file_path)
    print(f'Test Execution: {executionKey} - {executionSummary}')
    print(f'Total tests found: {len(data)}')

Conclusion

This tutorial demonstrates how to extract test execution data from Xray Cloud using Python and GraphQL and write it to an Excel file. You can adapt the code to retrieve different sets of data and customize the Excel file to meet your reporting needs.

Extracting Test Execution Data from Xray Cloud and Writing to Excel using GraphQLXray-Jira Reporting

Previous Article

Next Article
Testingfly

About Testingfly

Testingfly is my spot for sharing insights and experiences, with a primary focus on tools and technologies related to test automation and governance.

Comments

Want to give your thoughts or chat about more ideas? Feel free to leave a comment here.

Instead of authenticating the giscus application, you can also comment directly on GitHub.

Related Articles

Testing iFrames using Playwright

Automated testing has become an integral part of web application development. However, testing in Safari, Apple's web browser, presents unique challenges due to the browser's strict Same-Origin Policy (SOP), especially when dealing with iframes. In this article, we'll explore known issues related to Safari's SOP, discuss workarounds, and demonstrate how Playwright, a popular automation testing framework, supports automated testing in this context.

Overview of SiteCore for Beginners

Sitecore is a digital experience platform that combines content management, marketing automation, and eCommerce. It's an enterprise-level content management system (CMS) built on ASP.NET. Sitecore allows businesses to create, manage, and publish content across all channels using simple tools.