#!pip3 install jupyter_kernel_gateway

import json
import pandas as pd
import sqlite3
con = sqlite3.connect("onsgeocodes.sqlite")

REQUEST = json.dumps({
    'path' : {},
    'args' : {}
})

# GET /ons/:code
request = json.loads(REQUEST)
code = request['path'].get('code')

q='SELECT * FROM codelist WHERE "GEOGCD"="{code}"'.format(code=code)


print('{"codes":%s}' % pd.read_sql_query(q, con).to_json(orient='records'))

# GET /ons/current/:name
request = json.loads(REQUEST)
name = request['path'].get('name')

q='''
SELECT *  FROM codelist JOIN metadata 
WHERE "GEOGNM"="{name}" AND codeAbbrv=sheet AND codelist.STATUS="live"
'''.format(name=name)

print('{"codes":%s}' % pd.read_sql_query(q, con).to_json(orient='records'))