In [1]:

    

import cPickle as pickle, codecs, requests, json, random, re, datetime
from collections import Counter, defaultdict


# SQLAlchemy setup
from sqlalchemy import create_engine
from sqlalchemy.sql import func, select
from connect import mysqlusername, mysqlpassword, mysqlserver, mysqldbname
from db_tables import metadata, InstitutionDescription, ConditionDescription


    

In [2]:

    

mysqlserver = 'localhost'
engine = create_engine('mysql://%s:%s@%s/%s' % (mysqlusername, mysqlpassword, mysqlserver, mysqldbname), pool_recycle=3600)
conn = engine.connect()
metadata.create_all(engine)


    

In [3]:

    

cond_rank = conn.execute('''
                        SELECT d.condition_id,
                          mesh_term,
                          count(*) total
                        FROM condition_description d
                          JOIN condition_lookup c
                          USING (condition_id)
                        WHERE source = 'CTGOV'
                        GROUP BY 1, 2
                        ''').fetchall()

inst_rank = conn.execute('''
                        SELECT inst.institution_id,
                          inst.name,
                          count(*) total
                        FROM institution_description inst
                          JOIN institution_lookup look
                          USING (institution_id)
                        WHERE source = 'GOLD'
                        GROUP BY 1, 2
                        ''').fetchall()


    

In [7]:

    

conditions = [{'value': str(i), 'text': c, 'type': 'cond'}
              for i, c, cnt in sorted(cond_rank, key=lambda x: x[2], reverse=True)]
institutions = [{'value': str(i), 'text': c, 'type': 'inst'}
                for i, c, cnt in sorted(inst_rank, key=lambda x: x[2], reverse=True)]


    

In [8]:

    

with open('../data/conditions.json','wb') as fp:
    json.dump(conditions, fp)
with open('../data/institutions.json','wb') as fp:
    json.dump(institutions, fp)


    

In [6]:

    

inst_loc = conn.execute('''
                        SELECT latitude,
                          longitude,
                          name,
                          institution_id
                        FROM institution_description
                        WHERE latitude between -90 and 90
                          AND longitude between -180 and 180
                        ''').fetchall()


    

In [7]:

    

locations = [list(j) for j in inst_loc]


    

In [9]:

    

with open('../data/locations.json','wb') as fp:
    json.dump(locations, fp)


    

In [6]:

    

conn.close()


    

In [ ]: