ExchangeRate_Solution.py

#%%
# Setup

RESULT_FILE='fores-insert.sql'
RATES_URL='https://freecurrencyapi.net/api/v1/rates?base_currency=USD'

#%%
# Load JSON data from API

from pprint import pprint

import requests
r = requests.get(RATES_URL)

if r.status_code == 200:
    currency_data=r.json()
    pprint(currency_data)
else:
    print(r)
    exit(1)

#%%
# Extract rates from the first date in the dataset

rates = list(currency_data['data'].values())[0]

# Print values
for k,v in rates.items():
    print(k, v)

#%%
# Setup SQL Alchemy, to generate queries

from sqlalchemy import *
from sqlalchemy.dialects import *

# engine = create_engine('sqlite:///:memory:', echo=True)

metadata = MetaData()

forex = Table('forex', metadata,
    Column('code', String(length=3), primary_key=True),
    Column('rate', Numeric(), nullable=False)
    # Column('rate', Float(), nullable=False)
)

#%%
# Generate Create Table Query

from sqlalchemy.schema import CreateTable
q_create_table=CreateTable(forex).compile(engine)
print(q_create_table)

#%%
# Generate Insert Values Query

ins = forex.insert().values(list(rates.items()))
q_insert = ins.compile(dialect=sqlite.dialect(),
                       compile_kwargs={"literal_binds": True})

print(q_insert)

#%%
# Save queries into file

with open(RESULT_FILE,'w') as f:
    f.writelines([
        str(q_create_table) + ";",
        str(q_insert)
    ])
{"mode":"full","isActive":false}