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

Comments

8 responses to “How to do REST API call and read/write excel file using Python 3”

  1. cbd oil Avatar
    cbd oil

    I am regular visitor, how are you everybody?
    This piece of writing posted at this web site is in fact good.

    1. We are good. I hope you too doing well. Thanks for your appreciation. Please keep visiting the blog for more tutorials.

  2. Justin Hamilton Avatar
    Justin Hamilton

    Long time reader, first time commenter — so, thought I’d drop
    a comment.. — and at the same time ask for a favor.

    Your wordpress site is very simplistic – hope you don’t mind me asking what
    theme you’re using? (and don’t mind if I steal it?

    :P)

    I just launched my small businesses site –also built in wordpress like yours– but
    the theme slows (!) the site down quite a bit.

    In case you have a minute, you can find it by searching for “royal cbd” on Google (would appreciate any feedback)

    Keep up the good work– and take care of yourself during the coronavirus
    scare!

    ~Justin

    1. Hello Justin,

      Thanks for reading my tutorial. It’s really glad to know you are a long term visitor.

      Justin, you can detect any website’s theme through https://www.wpthemedetector.com/ web application. (There are other developer-related options also available to find.)

      My blog is using an extended child theme of WordPress Twenty Nineteen.

      royal CBD uses shopkeeper theme.

      Regarding slowness, you have to check by off the plugin which you feel induce the slowness once you find the culprit. Do a little google research why it’s causing slowness and how we can improve the speed. Use some cache plugin like https://wordpress.org/plugins/w3-total-cache/ to boost the speed.

      Thanks for the appreciation and you too take care. Please quarantine yourself.

      If you like to ask any queries please feel free to ask.

  3. myinforms Avatar
    myinforms

    I got this web site from my friend who informed me about this web site and now this time I am visiting this web site and reading very informative articles at this time.|

    1. Laventure, thanks for the appreciation. Comments posted by people like you boost me to put more tutorials.
      Be safe and self-quarantine till Corona eradicated.

      Thank you.

  4. Have you ever considered about adding a little bit more than just your articles? I mean, what you say is valuable and all. Nevertheless think about if you added some great pictures or video clips to give your posts more, “pop”! Your content is excellent but with pics and clips, this website could definitely be one of the most beneficial in its niche. Great blog!|

    1. Thanks for your suggestion. I will add images wherever needed.