Appium Python - Data Driven Test using Excel and PyTest Parameterization

now we'll be creating a data driven test and in data driven test we'll be reading the test data from the excel sheet so for that purpose let's say i've created a folder over here excel and i've kept this excel sheet over here if i uh open this excel sheet then the sheet has got a search test uh like search test sheet name and then we have uh city and country as the columns and then the data start from the second row right so uh it's it's something like uh what we have done earlier that we have hard coded the data over here so country like although we are not using country at the moment but in case if we have more test data uh just that one column uh we'll gonna write it manually like this so this part is what we need to replace it from the excel part so from the utility that we have seen uh like we have created excel reader over here if you remember in the utilities section we have created this accelerator file we have seen how to do reading and writing from excel so what we need to do if you take a look over here this hole is basically returning a list and in that list we have two more list which is storing the uh rows information like these are the two columns so this is actually storing each row so that is what uh the logic that we need to convert that uh we need to treat them as a row and these two should be columns and then these are list and this is also a list so we need to replace something over here which should uh actually read this data from the excel sheet and then return it so what i'll do i'll be using open pi excel so first what i'll do i'm gonna save workbook workbook equal to open [Music] by excel so we're gonna add this import this open pi excel and say dot load workbook first i'll be loading the workbook the workbook is in the excel package excel folder and then the workbook name is testdata.xlsx once we load the workbook i'll be calling the sheet i'll say sheet equal to uh workbook and the sheet name that we can give over here is search test is the sheet name that i've given right will be getting the total rows in total columns so we'll say total rows equal to sheet dot one of the method is max max row and then the total columns total columns equal to sheet dot max column so this is going to give us uh the row count and the column count right so so now i'll be running two loops over here one for rows and one for columns so the loop is gonna go like this for uh i in uh let's say i in uh like i'll say range and the range i'll be defining it from two starting from two because uh the first one is the header so i'm excluding the header i'm starting the data from the second row so start from second row and go up till total rows plus one so this will be the range of the rows and same way columns columns will say we'll gonna write j in range columns are starting from one and should go total columns plus one and then what i'll be doing i'll be creating one main list so this is the main list that holds other list over here so what i'll do i'll uh just going to create a variable over here name it as main list so this is basically a list and then i'll be creating a data list so this is going to be a data list so this is data list this is data list so what i'll do i'll say uh data list so this is again a list and what data will be storing once we are inside the loop we first need to get the data so how to get the data uh we'll need to say sheet dot cell if you remember cell is the function that accepts your uh row so we're gonna say uh a row equal to uh row we are getting from i and then we need to say column column c-o-l-u-m-n column equal to uh j so the column is coming from j and then we need to get the value so this is how we'll be getting the value and then we need to store this value we need to add this value into the data list so how to do that data list dot insert and how we're going to insert it we need to provide the column and we need to provide the data so add like the first column get this particular data so data is this and then uh once the columns are over it is going to go to the row and what we're gonna do in the main list we're gonna say main list dot insert and uh in the first row so first row we're going to get it from i uh first rather than we're starting from second so it should be the second row it should add all the data so data is coming from the data list so whatever data that is stored in both the columns we're gonna uh add it to the second row and this way it will be iterated to the third row as well and at the end it should uh return you the main list now this get data is not returning you this static data it is going to return you the dynamic data which is there inside uh the excel sheet right so you need not to worry about how many total number columns are there total number of rows are there it's automatically going to get the row count and the column count and then gonna get the data and we're gonna store it in the main list and each data row set will be stored as an individual list right and each list contains the columns data so now you just need to pass this get data over here and when you are getting the city information so the very first column is city you can get the 3d information you can get the like country information so we have uh let's say how many columns we have we have two columns so we need to pass the country as well over here and then we need to pass the country over here as well right and let's run this now and let's see if the data is written right from the excel we have commented out this code right so now the data that should be read should come from the excel and it is going to become a data driven test so let us run this and let's see and let's see if we are able to integrate our excel test with apm and see delhi is coming from excel sheet right first test is over it's pass okay it has not navigated to the second test so let's see why i didn't navigate to the second test let us open the excel sheet okay so we have delhi we have dubai we have two set of data such as city country everything is good starting from two just a moment should actually execute both set of data so if i try to print the main list and try to print the total columns total columns are stay our total columns and same way total rows are let's see what output we are getting it should not execute only one time it should execute the test twice and let's see over here that's saying first as successfully executed and [Music] passed delhi okay so it's not printing so what we can do is we can try running hi-fi knife and s and hyphen v to get the output so turtle rows are three and uh see it is actually getting the data uh delhi in india but let's see if it is uh getting for the first test or what so the test is being executed and it just collected uh item as one okay so it's only only only only getting the first item so let's see why it is getting only the first item okay okay okay i i can see this thing i mean i'm actually returning the mail list with the column data it should return it uh once the rows are over so that that was actually the issue i guess so let's run it and let's see if we are getting two set of data this time see now both the data are being collected so two items are collected now we should be able to see two test cases getting executed let's see so executing the first set successfully executed and now it should get the second test now getting the second test and now it should get it worth the way perfect so the data driven test is working fine absolutely so with the parameterized data for the same test case right so this is how you do the data driven testing uh while working with apm you can integrate your data driven test through the excel files all right so that's on that's all in this lecture thank you

Channel: Way2Automation - Rahul Arora

Views: 2,394

Keywords:

Id: xSEzJaP0naQ

Length: 12min 50sec (770 seconds)

Published: Wed Jun 09 2021