import os
import requests
from pprint import pprint
from time import sleep
%pip install jupysql -q
# store the key in an Integration/Environment Variable
API_KEY = os.environ.get("API_KEY")
currencies = ["EUR", "USD", "GBP"]
amounts = [1, 10, 20, 50, 100, 1000]
convert_from
convert_to
convert_amount
url = f"https://api.apilayer.com/exchangerates_data/convert?to={convert_to}&from={convert_from}&amount={convert_amount}"
payload = {}
headers= {
"apikey": f"{API_KEY}"
}
response = requests.get(url, headers=headers, data=payload)
# API response
if response.status_code == 200:
pprint(response.json())
print("Conversion value: ", response.json()["result"]) # value
else:
print("Error, API issue:", response.status_code)
%load_ext sql
%sql sqlite:///exchange_rates.db
%sql PRAGMA database_list;
%%sql
CREATE TABLE IF NOT EXISTS exchange_rates (
conv_id INTEGER PRIMARY KEY,
conv_from TEXT NOT NULL,
conv_to TEXT NOT NULL,
conv_rate TEXT NOT NULL,
conv_time TIMESTAMP NOT NULL
);
%sql PRAGMA table_info(exchange_rates);
# list tables in database
%sql SELECT name FROM sqlite_master WHERE type='table';
url = f"https://api.apilayer.com/exchangerates_data/convert?to=EUR&from=USD&amount=1"
def fetchRate():
try:
response = requests.get(url, headers = {"apikey": f"{API_KEY}"}, data = {})
response.raise_for_status()
conv_rate = response.json()["info"]["rate"]
conv_time = response.json()["info"]["timestamp"]
conv_from = response.json()["query"]["from"]
conv_to = response.json()["query"]["to"]
%sql INSERT INTO exchange_rates (conv_from, conv_to, conv_rate, conv_time) \
VALUES ("{{conv_to}}", "{{conv_from}}", "{{conv_rate}}", datetime("{{conv_time}}", 'unixepoch'))
print("Data received and stored")
return True
except requests.exceptions.RequestException as e:
print("Http/Api error", e)
return False
# run fetch until HTTP request is succesful
while not fetchRate():
fetchRate()
sleep(300) # wait 5mins between attempts
result = %sql SELECT * FROM exchange_rates;
df = result.DataFrame()
%%sql
-- delete duplicates records of needed
-- DELETE FROM exchange_rates
-- WHERE conv_id = 8 OR conv_id = 9;
-- verify records in tables
SELECT * FROM exchange_rates;