import os
import requests
from pprint import pprint
from time import sleep
%pip install jupysql -q
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv
WARNING: You are using pip version 22.0.4; however, version 23.2.1 is available.
You should consider upgrading via the '/usr/local/bin/python -m pip install --upgrade pip' command.
Note: you may need to restart the kernel to use updated packages.
# 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)
{'date': '2023-09-01',
'info': {'rate': 1.084581, 'timestamp': 1693566003},
'query': {'amount': 1, 'from': 'EUR', 'to': 'USD'},
'result': 1.084581,
'success': True}
Conversion value: 1.084581
%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
Data received and stored
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;