Vijayan's Blog

Category: Python

  • How to do REST API call and read/write excel file using Python 3

    Introduction

    1. Today we are going to see how to read the excel’s each cell and pass the value to the REST API request
    2. On successful response we will parse the JSON response
    3. Read the JSON element and write it to the excel

    Prerequisite

    1. In your terminal or CLI run pip3 install package_name (Based on your OS instead of pip3 run pip. In some OS both python2 and python3 installed then check your pip version pip --version or pip3 --version and execute the command)
    2. We need the following packages to be installed requests and openpyxl
      1. Command: pip3 install requests openpyxl
    3. Once installed please follow next heading to do the coding part

    Coding

    1. The first three lines import the packages
    2. requests on line 1 is used to do the REST API call
    3. openpyxl on line 2 is used to do excel related things
    4. time on line 3 is used to delay the API call to a certain limit of time
    5. In line 6 we assigned API URL to the variable API_URL
    6. From line 9 to line 23 the function extract_country_details() declaration is present
    7. Lets we see inner working of this function
      1. In line 9, we declared function with single parameter search_term
      2. In line between 11 and 14 we declared the dictionary variable with two element which will be pass as a query parameter for the API call
      3. In line 16, we declared a new variable response and assigned it with the response we get from API call by using/importing the package import requests and call it’s method get() with two arguments
      4. The first argument is the URL which we used to fetch the content
      5. The second argument is keyword argument which means we assign our dictionary variable’s value to the argument params it will be query string for our API call
      6. In line 18 convert/decode the received response to JSON object by calling json() method
      7. In line 20 and 21 we check response status is not equal to 200 if so then return the string 'error'
      8. If everything goes well in line 23 we return the first index content of variable json
    8. The second function from line 26 to 42 which is write_country_details() is used to learn read and write excel using the package openpyxl let’s we see the inner workings
      1. In line 27 we are assigning our excel filename top5_populated_countries.xlsx to the variable filename. The excel file is in same path as our country.py
      2. In line 29 we are calling the openpyxl package’s method load_workbook(filename) with file path as parameter and assigning it to the variable wb (wb is short notation of workbook)
      3. In line 30 for the variable ws (worksheet) we assigning the workbook object’s member variable wb.active which holds the active sheet by default it will be the first worksheet
      4. In line 31 for the variable wc (workcolumn) we assigning the A column of the worksheet
      5. In line 33 we assign value 1 to the variable i
      6. In line 34 to 40 the for loop is present we will see for loop in detail
      7. In line 34 we looping workbook’s column A if you closely noted we left the first row of workbook by calling wc[1:] the [1:] denotes that start from 1st index to till the last index instead of 0th index
      8. In line 35 during each looping we increment the variable i
      9. In line 36 we are calling the method extract_country_details by passing A column’s cell value and assigning to the variable country_details
      10. In line 37 we called time.sleep(10) which is for pausing the script for 10 seconds so it won’t execute the next line till 10 seconds
      11. The purpose of sleep timer is to avoid continuous hit to the API server because some servers may block the API call if we hitting continuously without a gap
      12. In line 38 we fetching associative/named index capital from dictionary variable country_details and assigning it to the worksheet column B’s cell B2 in ws object
      13. In line 39 & 40 the logic is similar to line 38
      14. The loop between line 34 and 40 will loop until the last value of A column which have list of country name
      15. Finally in line 42 we calling workbook’s method save with filepath as argument which will write the fetched details such as each country’s capital, region and population in B, C & D column
    9. We explored two functions definition finally at the line 45 we calling our function write_country_details()

    Conclusion

    1. We learned how to call API service, read excel and write excel
    2. Please click here to download the excel file used in this script exercise