#!/usr/bin/env python
# coding: utf-8

# In[ ]:


#!pip3 install jupyter_kernel_gateway


# In[ ]:


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


# In[ ]:


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

jupyter kernelgateway  --KernelGatewayApp.api='kernel_gateway.notebook_http'  --KernelGatewayApp.seed_uri='./SimpleAPI.ipynb' 
# In[ ]:


# 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'))


# In[ ]:


# 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'))


# In[ ]: