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