13. Importing Python libraries into Vitaq

13.1. Introduction

To use the power of an opensource python package in your Vitaq Test Action Scripts, you must first make it accessible by importing it.

A python package is a directory tree with python files in it, they can contain other packages and modules in its hierarchy. These modules are python script code that contains definitions and declarations for classes, functions and variables.

This makes it very easy for you to use existing code that has been developed by other domain experts in your Vitaq Test Activity without excessive use of copy-paste.

To use the python packages for reading and writing to excel spreadsheets you need to open the Imports Script file which is in the Test Action Scripts tab

Then type import and the name of the python package:

# Recommended python package for writing to excel spreadsheets

import xlsxwriter

# Recommended python package for reading from excel spreadsheets

import xlwings

Important

If these packages have not been installed as part of your Python installation you will need to follow a python package installation process. To find out what packages you have installed, open a command window and navigate to your Python27scripts directory (in our case cd C:Python27scripts) then type the command pip freeze, see below:

C:\Python27\scripts> pip freeze

This will launch the python interpreter and display the version number of all modules installed in your Python non-standard library.

If you do not have the Python package you want to use installed, then you can use the command line below. Most python packages can be identified by pip from pypy.org, so all you need to know is the name of the package you wish to install.

C:\Python27\scripts> pip install some-python-package

If pip cannot identify the package name then you will need to find and download the Python packages with the Python Package index https://pypi.org The Python Package index is a repository for all the pre-written Python libraries. They are stored here as a wheel file which is a built-package format for Python. It is a ZIP -formatted archive with a specially formatted filename and .whl file extension designed to contain all the files for a pip install.

Once you have download the appropriate package (specific to your windows and python version) into your Python27 directory you can install it with pip.

C:\Python27\scripts> pip install some-python-package.whl

13.2. Writing into excel spreadsheets from Test Action Scripts

Many application test frameworks are created to write and read tests from a spreadsheet file structure. Vitaq can write to spreadsheets using the xlsxwriter python library. If you are not sure if you have xlsxwriter installed please refer to section 9.1 to see how to check and install if not found. To install xlsxwriter:

C:\Python27\scripts> pip install xlsxwriter

To use xlsxwriter in Vitaq we the following line of python code into the Imports script which can be found in the Test Action Scripts tab:

import xlsxwriter

Continuing with our bill split web app test activity, we will write the Vitaq Test Activity auto-generated account, participants and expense information into a spreadsheet then write an equation into the spreadsheet to calculate the balance results. This provides us with a test ‘oracle’ to ‘check’ the balance results of the web app against the balance results of the excel spreadsheet calculations.

We first need to create the excel spreadsheet file and open a workbook to write into, so we put this python script into the New_Account test action script.

self.parent.workbook = xlsxwriter.Workbook('{}.xlsx'.format(self.parent.New_Account_Name)) self.parent.worksheet = self.parent.workbook.add_worksheet()

xlswriter is very powerful. Everything you can do in excel you can do through the xlswriter python library.

For example, to make our bill split account oracle easy to read and to perform financial calculations that round to nearest cent, we will need to format spreadsheet fields and write a header row into the spreadsheet we have opened.

Add the following code into the New_account Test Action Script

# Add a bold format to use to highlight cells self.parent.bold = self.parent.workbook.add_format({'bold': True}) # Add a number format for cells with money. self.parent.money = self.parent.workbook.add_format({'num_format': '#,##0.00'}) # Add a number format for cells with units. self.parent.units = self.parent.workbook.add_format({'num_format': '#,##0'}) # Start by collecting the header data self.parent.worksheet.write('A1','What?', self.parent.bold) self.parent.worksheet.set_column(0, 0, 24) self.parent.worksheet.write('B1','Amount', self.parent.bold) self.parent.worksheet.set_column(1, 1, 12) self.parent.worksheet.write('C1`','Who Paid?', self.parent.bold) self.parent.worksheet.set_column(2, 2, 12) self.parent.worksheet.write('D1','How Many Participated?', self.parent.bold) self.parent.worksheet.set_column(3, 3, 28)

To write the auto-generated account owner name and participant names into the spreadsheet we need to use the following test action python script lines.

Add the following code into the New_Account Test Action Script:

# Write the auto-generated value of the Account owner into cell E1 of the spreadsheet workbook self.parent.worksheet.set_column(4, 4, 12) self.parent.worksheet.write('E1', self.variables.New_Account_Your_Name.get_value(), self.parent.bold)

# Write the auto-generated value of the Account owner into cell

# E1 of the spreadsheet workbook

self.parent.worksheet.set_column(4, 4, 12)

self.parent.worksheet.write('E1', self.variables.New_Account_Your_Name.get_value(),\

self.parent.bold)

By using the python method to get the length of a list len() and a python method from the xlsxwriter package to convert a column number to a name for excel, we can shift the column we write to, each time we add a new participant. Remember we already created the list of participant names into the DisallowedNameList to use it to constrain the new generation of new ‘unique’ names.

Add the following code into the Add_Participant test action script:

# Writing auto-generated test data into spreadsheets using xlsxwriter # (to create a test oracle to check bill splitting app balance results against)

self.parent.worksheet.set_column(len(self.parent.DisallowedNameList)+3, \

len(self.parent.DisallowedNameList)+3, 12)

self.parent.worksheet.write('{}1'.format(xlsxwriter.utility.xl_col_to_name \

(len(self .parent.DisallowedNameList)+3)), self.variables.Add_Participant_Name.get_value(), \ self.parent.bold)

To write the expense item information into the next rows of the spreadsheet as we Create the Add_an_Expense Test Action Script using the xlsxwriter package. The example code for this is included in the user guide deliverables, Now using the module write_array_formula we can write the balance calculation formula into the sum line of the excel spreadsheet. This will provide is with a reference ‘oracle’ balance result calculated by the excel spreadsheet that we can then use to check against the bill split app result.

13.3. Reading from excel spreadsheets into Vitaq Test Action Scripts

Once we have the balance result calculated in the excel spreadsheet, we need to read the result back into our Vitaq Test Action Script. To retrieve the calculated result rather than the formula in the spreadsheet we recommend the use of the Python package xlwings, which evaluates excel dynamically therefor getting calculated results rather than static equations.

If you are not sure if you have xlwings installed please refer to section 9.1 to see how to check and install if not found. To install use:

C:\Python27\scripts> pip install xlwings

To use xlwings in Vitaq we add the following line of python code into the Imports script which can be found in the Test Action Scripts tab:

Import xlwings

Then using the .range().value method we can read the result into a variable in our script SomeScriptVariableName = SomeWorkSheetName.range().value

Using Vitaq Test Action Scripts for results checking

Once we have the oracle balance and the app balance we can simply check them against one another as 2 decimal place numbers.

if round(Oracle_Balance_Result, 2) == round(app_balance_result, 2):

print ('Participant Balance is correct, TEST HAS PASSED')

else:

print ('Participant Balance is incorrect, TEST HAS FAILED')