Python retrieve data from an API & store in a table

This script combines many Python features, I used in the last few months.

The idea is a file is read that contains parameters that are used is a API call. The result is then analysed and split in different data element. Finally, some data are stored in a dataframe that is subsequently read in a table.

Reading the data is done with a method within the dataframes. The dataframe library is extensively used here. One method is “read_csv” which allows to read a csv file. While reading such a csv file, we are allowed to use parameters that describe the csv file. One parameter is the name of the file; another is the header that indicates if a header record exists or not. It is good to realise that the headers are used as column names. This implies that we may use the header to address in the column that is created with the dataframe.

The result from that read is subsequently stored in a dataframe.

That dataframe is then processed row by row. The row is a series that is transformed into a dictionary (key / value structure).

This dictionary is then used as a parameter in an API call.

See below

import requests
 import csv
 import pyodbc
 import glob
 import pandas as pd
 import pyodbc
 from sqlalchemy import create_engine
 teller = 0
 resultaat = pd.DataFrame([['','']])
 print(resultaat.shape[0],resultaat.shape[1])
 df = pd.read_csv("C:\Users\tmaanen\documents\Cloudstation\pensioenfonds\moeilijk\c.csv", header=0, squeeze=True)
 top = len(df)
 for i in range(top):
     rij = df.iloc[i]
     dict_from_csv = rij.to_dict()
     print(dict_from_csv)
     response = requests.get('http://api.open-notify.org/iss-pass.json', params=dict_from_csv)
     dataframe = pd.DataFrame.from_dict(response.json(), orient="Index")
     ff=dataframe.iloc[0]
     for key, value in ff.items():
         print('key ',key,' value ',value)
     ff=dataframe.iloc[1] 
     for key, value in ff.items():     
          print('key ',key,' value ',value)     
           w = value     
           for key, value in w.items():         
                 print('key ',key,' value ',value)   
      ff=dataframe.iloc[2] 
      print(ff) 
      for i in range(len(ff)):     
      w = ff[i]     
      print(len(w),' ',w)     
      for i in range(len(w)):         
           w1 = w[i]         
           for key, value in w1.items():             
               print('key ',key,' value ',value)             
               if key == 'duration': 
                  resultaat.iloc[teller,0] = value             
               if key == 'risetime':  
               resultaat.iloc[teller,1] = value 
                   teller = teller + 1                 
                   resultaat =        resultaat.append(pd.DataFrame([['','']]), ignore_index=True)                  print(resultaat.shape[0],resultaat.shape[1])
 
engine = create_engine("mssql+pyodbc://tomvanmaanen:*******@AzureSQL")
resultaat.to_sql('nix', schema='dbo', con = engine, chunksize=100, method='multi', index=False, if_exists='replace')

The c.csv file looks like:

lat,lon
5, 50
6, 45
-1, 50
10,45
15,40