introductory
Using python for interface testing often need interface test cases to test data, assert interface functionality, verify the interface response status, etc. If a large number of interface test case scripts will interface test case data written in the script file, so that the entire interface test case script code will look very redundant and difficult to clearly read and maintain, imagine if all the interface test data written in the code, interface parameters or test data need to be changed, then each code file must be changed one by one? Imagine if all the interface test data are written in the code, the interface parameters or test data need to be modified, then each code file must be changed one by one. Therefore, this inefficient model is not what we want. Therefore, there is an important idea in automation testing: test data and test script separation, that is, there is only one test script, which requires the input data will be replaced by variables, and then put the test input data in a separate file, this file to store the test input data, usually in the form of a table or other format files, such as excel files, json files, xml file, txt text file and so on. In python for interface automation testing, in order to facilitate the management and storage of test case data, test data is generally written and stored in excel file, the test script reads the excel file to achieve the test data load, and run to get the results of the test case data execution, and write back the test results to the excel file, so as to achieve the separation of the test script and data. This realizes the separation of test scripts and data. And python operation excel file read and write, here you need to install and introduce third-party modules: xlrd and xlwt and xlutils, xlrd for reading excel module, xlwt for writing data to excel module, xlutils can copy excel and modify the data in excel. The following is a specific introduction to xlrd and xlwt operation excel file provides general methods and techniques, and xlutils how to copy and modify excel, to operate excel read and write purposes.
I. xlrd, xlwt and xlutils installation
1. Use pip to install
pip install xlrd pip install xlwt pip install xlutils
2. Installation in PyCharm
Simply retrieve the name of the module that needs to be installed, e.g. xlrd.
Second, xlrd operation excel file data reading
Create a new excel file, file name: excel_test.xlsx, the file editor has two sheet tables with the following contents:
sheet1：
sheet2：
(1) open the excel file, get the excel sheet name
Edit the following code:
import xlrd
file = xlrd.open_workbook("excel_test.xlsx")
all_sheet = file.sheet_names() # Get all workbook names
sheet_name1 = file.sheet_names()[0] # Get by sheet subscript, the first sheet subscript is 0
sheet_name2 = file.sheet_by_index(0).name # Get sheet name by sheet index
print(all_sheet)
print(sheet_name1)
print(sheet_name2)
-----------------------------------------
# Return results
['Employee Information Sheet', 'api test cases']
Employee Information Sheet
Employee Information Sheet
(2) Get the object of the sheet page.
Code Example:
import xlrd
file = xlrd.open_workbook("excel_test.xlsx")
sheet_name1 = file.sheet_names()[0]
sheet1_obj = file.sheet_by_name(sheet_name1) # Get sheet object by sheet name
sheet2_obj = file.sheet_by_index(1) # Get sheet object by sheet index
print(sheet1_obj)
print(sheet2_obj)
------------------------------------
# Return results
< object at 0x0000000002AA09B0>
< object at 0x0000000002AA0978>
(3) get sheet worksheet rows, columns, whole rows and columns of data, specific cell data
Code Example:
import xlrd
file = xlrd.open_workbook("excel_test.xlsx")
sheet = file.sheet_by_index(0) # Get sheet object by sheet index
nrows = # Get the number of rows
ncols = # Get the number of columns
nrows_data = sheet.row_values(1) # Get the second row of data, which is returned as a list
ncols_data = sheet.col_values(0) # Get the first column of data, returns the list
cell = (1,2) # Get cell data, such as the second row, the third column of data
print(nrows)
print(ncols)
print(nrows_data)
print(ncols_data)
print(cell)
-------------------------------
# Return results
6
5
['Wang Wu', 'Male', 32883.0, 'java development engineer', 233.0]
['Name', 'Wang Wu', 'Li Si', 'Zhang San', 'Little Red', 'Little Ming']
xldate:32883.0 # Here the date data is returned as a floating point number.
Commonly read excel different data types return problems, such as reading data in date format
General use (rowx,colx) method to obtain cell data, cell data type judgment can use the following code:
print((1,2).ctype) ------------ # Returns results of date data 3
classifier for sums of money：ctype : 0 empty,1 string, 2 number,3 date, 4 boolean, 5 error
Handling of reading cell date data as floating point numbers:
The code is as follows:
import xlrd
from datetime import date
file = xlrd.open_workbook("excel_test.xlsx")
sheet = file.sheet_by_index(0) # Get sheet object by sheet index
nrows_data = sheet.row_values(1) # Get the second row of data, which is returned as a list
ncols_data = sheet.col_values(0) # Get the first column of data, returns the list
cell = (1,2) # Get cell data, such as the second row, the third column of data, the return is a floating point number
data_value = xlrd.xldate_as_tuple(sheet.cell_value(1,2) ,) # xldate_as_tuple() method gets the date data year, month, day, hour, minute, and second values and returns them as a tuple
datatime2 = date(*data_value[:3]).strftime('%Y/%m/%d') # intercept the first three digits of the tuple, i.e., the year, month, and day values, and pass them to data, and format the time
print(cell)
print(data_value)
print(datatime2)
-----------------------
# Return results
xldate:32883.0
(1990, 1, 10, 0, 0, 0)
1990/01/10
Therefore, in reading excel cell data, such as encountered is the date format of the data, you can add the following code to determine and deal with:
if ((row,col).ctype == 3):
date_value = xlrd.xldate_as_tuple(sheet.cell_value(row,col),)
date_tmp = date(*date_value[:3]).strftime('%Y/%m/%d')
Third, xlwt write data to excel file
xlwt is generally used to write data to an excel file, a simple example is as follows:
import xlwt
workbook = (encoding = 'utf-8') # Create workbooks
sheet = workbook.add_sheet('api_test') # Add a sheet
data = (0,0,'test') # Write data to the first row and column: test
('') # Save to Medium
After the completion of the run will be in the py file at the same level of the directory to generate an excel file, and a new api_test sheet name, the first line of the first column to write data: test
When writing data to excel, you can set the cell length and width, merge cells, write time-formatted data, and add hyperlinks.
Code Example:
import xlwt
import datetime
workbook = (encoding = 'utf-8') # Create workbooks
sheet = workbook.add_sheet('api_test') # Add a sheet
data = (4,0,'test')
(0).width = 5000 # Set cell width
style = () # Initialize form styles
style.num_format_str = 'M/D/YY' # Set the time format, e.g. M/D/YYY
(5, 1, (), style) # Write time-formatted data
# Merge multiple columns and merge multiple rows
# Indicates that the number of rows to be merged is: indexed by row, from row 0 to row 0, indexed by column, from column 0 to column 3, merged and written to the data: test1
sheet.write_merge(0, 0, 0, 3, 'test1')
# Indicates that the number of rows to be merged is: indexed by row, from row 1 to row 2, and indexed by column, from column 0 to column 3, after merging and writing data: test2
sheet.write_merge(1, 2, 0, 3, 'test2')
# Add hyperlinks to cells
(6, 0, ('HYPERLINK("/";"baidu")'))
('') # Save to Medium
The output excel effect after running is as follows:
Fourth, xlutils operation excel file
(1) Copy the excel sheet
xlutils module under the copy can copy copy excel file, code example:
import xlrd
from import copy
excel_file = xlrd.open_workbook("")
new_file = copy(excel_file) # Copy the file object
new_file.save("") # save as (a file)excelfile
Run the above code will be generated in the same level of directory excel file, the file and the same, only the name of the file is not the same, that is, copy the excel file!
(2) Modification of excel file contents
In addition to copy for other excel files, you can also directly copy the file to modify the file, save the file with the same name, that modified the content will directly cover the original excel file to achieve the purpose of the modification
Example:
import xlrd
from import copy
excel_file = xlrd.open_workbook("")
new_file = copy(excel_file)
sheet = new_file.get_sheet(0) # Get the first sheet of the table
(0,1,"Testing.") # 1st row 2nd column write: test
(1,1,"Test 1") # Write in second row, second column: test 1
new_file.save("")
The table will be modified and updated after running
V. Packaging operations excel read and write classes
Through the above introduction, basically have the use of xlrd, xlwt, xlutils module for excel file data reading and writing data to the excel file, in the interface test, we said that we need to: test data and test scripts are separated, the subsequent interface test case data, we unify the data written into the excel table, and then by operating excel to read the test data and backfill the test results into excel. Therefore, we need to read data to excel and write data to excel operations are encapsulated.
We operate the above api test case this sheet to encapsulate the class that reads excel data, code example:
from xlrd import open_workbook class Readexcel(): def excel_data_list(self, filename, sheetname): ''' :param filename: excelName of the document :param sheetname: excelChinese versionsheetname (of a thing) :return: data_list ''' data_list = [] wb = open_workbook(filename) # Open excel sh = wb.sheet_by_name(sheetname) # Positioning worksheets header = sh.row_values(0) # Get the data for the header row for i in range(1, ): # Skip the header row and get data from the second row onwards col_datas = dict(zip(header, sh.row_values(i))) # Take each row of data and assemble it into a dictionary # data_list.append(col_datas) # Add a dictionary to a list with nested dictionaries, each element is a dictionary return data_list if __name__ == '__main__': Readexcel()
The above code encapsulates the class that reads the excel data, reads each row of data and assembles it into a dictionary and adds it to the list.
Instantiation runs:
data_list = Readexcel().excel_data_list('excel_test.xlsx','api test cases')
print(data_list)
-----------------------
# Return results
[
{'module': 'Video Security', 'url': ':18092/console_api/recep/tv/list', 'id': 1.0, 'params': '{ "queryMsg":"","regionCode":"","devtypeCode":"","online":"","offset":"","limit":1,"type":""}', 'method': 'get', 'actual_res': '', 'data': '', 'expect_res': '', 'test_res': '', 'case_name': 'Pagination Query Video Security Equipment List', 'files': ''},
{'module': 'Platform Management', 'url': ':18092/console_api/manage/appsys/info', 'id': 2.0, 'params': '', 'method': 'post', 'actual_res': '', 'data': '{"appName": "hahh","appId": "34434343","appUrl": "","appCode": "89","remark":""}', 'expect_res': '{"code": 200,"errMsg": ""}', 'test_res': '', 'case_name': 'application management-Individual application system additions', 'files': ''},
{'module': 'Platform Management', 'url': ':18092/console_api/manage/appsys/info/upappid/89', 'id': 3.0, 'params': '', 'method': 'put', 'actual_res': '', 'data': '{"appId": "3232327676888"}', 'expect_res': '{"code": 200,"errMsg": ""}', 'test_res': '', 'case_name': 'application management-Modify the applicationappId', 'files': ''},
{'module': 'Platform Management', 'url': ':18092/console_api/manage/devtype/list', 'id': 4.0, 'params': '{ "queryMsg":"15002","offset":"","limit":""}', 'method': 'get', 'actual_res': '', 'data': '', 'expect_res': '', 'test_res': '', 'case_name': 'Equipment Classification-Paging to get device type', 'files': ''}
]
Class that encapsulates writing data to excel, code example:
from import copy
from xlrd import open_workbook
class Write_excel():
def write_result(self, filename, row, col1,col2,actual_res, test_res,sheet_name):
'''
:param filename: filename
:param row: The row to write back
:param col1: Column to write back to
:param col2: Column to write back to
:param actual_res: Actual results
:param test_res: Test results: pass/failed
:param sheet_name: The specified sheet index.
:return.
'''
old_workbook = open_workbook(filename)
# Copy existing excel into new excel
new_workbook = copy(old_workbook)
# Get sheet
new_worksheet = new_workbook.get_sheet(sheet_name) # nth sheet, 0 means the first sheet
# Write data
new_worksheet.write(row, col1, actual_res)
new_worksheet.write(row, col2, test_res)
# Save
new_workbook.save("")
if __name__ == '__main__':
Write_excel()
In this way, we have completed the reading and writing excel operation of the package, the subsequent interface to test the reading and writing of data rely on these two classes.
To this article on the Python-based interface automation read and write excel file is introduced to this article, more related Python interface automation content, please search for my previous posts or continue to browse the following related articles I hope you will support me in the future!