In [2]:
from sqlalchemy import create_engine
from connect import mysqlusername, mysqlpassword, mysqlserver, mysqldbname
from collections import defaultdict
#from sqlalchemy.orm import sessionmaker
#from db_models import Base, ClinicalStudy, Facilities, Sponsors
from sqlalchemy.sql import func, select, and_, or_, not_, desc
from db_tables import metadata, InstitutionDescription, InstitutionLookup, ConditionDescription, ConditionLookup, InstitutionSponsors, InstitutionFacilities
from db_tables import RatingsCriteriaStars, InstitutionRatings, TrialSummary, Interventions, Conditions, ConditionSynonym,\
ClinicalStudy, TrialPublications, Users, CriteriaText, CriteriaTagged, CriteriaConcept, CriteriaConceptLookup, \
ConceptTerms
In [3]:
mysqlserver = 'localhost'
engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' % (mysqlusername, mysqlpassword, mysqlserver, mysqldbname))
conn = engine.connect()
metadata.create_all(engine)
# Session = sessionmaker(bind=engine)
# session = Session()
In [13]:
rand_select = 145
r = conn.execute(select([CriteriaTagged.c.tagged_text]).where(CriteriaTagged.c.random_select == rand_select)).fetchall()
In [19]:
data = [eval(s.tagged_text) for s in r]
In [20]:
data[:5]
Out[20]:
In [17]:
r[:5]
Out[17]:
In [44]:
for i, b in enumerate(a):
print i, ' '.join(w[0].decode('unicode_escape') for s in eval(b[3]) for w in s)
In [ ]:
In [ ]:
In [ ]:
In [3]:
def construct_address(city,state,zipcode,country):
if city:
if country == 'United States':
return '%s, %s %s<br/>%s' % (city, state, zipcode, country)
else:
return '%s, %s' % (city, country)
else:
return 'No location information provided'
# construct star rating from number
def gen_stars(s):
''' s is a number between 1 and 5 '''
if s > 5:
s = 5
elif s < 1:
s = 1
rating_str = "<span class='rating'><i class='fa fa-star'></i>"
ok = [1] * (int(s * 2) - 2)
ok.extend([0] * (8 - len(ok)))
for i in range(0,7,2):
if sum(ok[i:i+2]) == 2:
rating_str += "<i class='fa fa-star'></i>"
elif sum(ok[i:i+2]) == 1:
rating_str += "<i class='fa fa-star-half-o'></i>"
else:
rating_str += "<i class='fa fa-star-o'></i>"
return rating_str + "</span>"
# calculate overall rating
def calc_overall(r_array):
''' r_array is a list or tuple of category ratings, in the order:
date, mesh, sites, description, criteria
'''
r_wgt_avg = (r_array[0] * 0.10) + \
(r_array[1] * 0.25) + \
(r_array[2] * 0.25) + \
(r_array[3] * 0.25) + \
(r_array[4] * 0.15)
return 1 + (round((r_wgt_avg) * 2 - 2) / 2.0)
# turn a query result with multiple rows per key into a lookup dictionary
def dictify(qresult):
''' qresult is a list of 2-tuples '''
output = defaultdict(set)
for k, v in qresult:
output[k].add(v)
return output
# create a comma-separated list of words
def add_commas(strlist, conj='and'):
''' strlist is a list of strings to be concatenated with comma
conj is the final conjunction, defaults to 'and'
'''
if 'Other' in strlist:
strlist.remove('Other')
strlist.append('Other')
if len(strlist) <= 1:
return ''.join(strlist)
elif len(strlist) == 2:
return '%s %s %s' % (strlist[0], conj, strlist[1])
else:
return '%s, %s %s' % (', '.join(strlist[:-1]), conj, strlist[-1])
# create a layman's terms string that describes the trial
def layman_desc(phase, status, inv_dict, stype):
missing_data = []
if phase != 'N/A':
lay_str = "This is a <span style='font-weight: bold'>%s</span> trial" % phase
else:
lay_str = "This is a trial"
missing_data.append('phase')
lay_str += " that is <span style='font-weight: bold'>%s</span>." % status
inv_str = add_commas(inv_dict, 'and/or')
if inv_str:
if stype.lower() != 'n/a':
stype = " <span style='font-weight: bold'>%s</span>" % stype
else:
stype = ''
missing_data.append('study type')
lay_str += " It is an%s assessment of a(n) <span style='font-weight: bold'>%s</span> treatment." % (stype, inv_str.lower())
else:
missing_data.append('intervention(s)')
if missing_data:
lay_str += " (No information was provided about the %s of the trial.)" % add_commas(missing_data, 'or')
return lay_str
In [7]:
inst_id = 68233
inst_data = conn.execute(InstitutionDescription.\
select().\
where(InstitutionDescription.c.institution_id == inst_id)).\
fetchone()
sponsors = conn.execute(InstitutionSponsors.\
select().\
where(InstitutionSponsors.c.institution_id == inst_id).\
order_by(desc('trial_count'))).\
fetchall()
facilities = conn.execute(InstitutionFacilities.\
select().\
where(InstitutionFacilities.c.institution_id == inst_id).\
order_by(desc('trial_count'))).\
fetchall()
sponsor_list = [{'name': t[1], 'cnt': t[2]} for t in sponsors]
facility_list = [{'name': t[1],
'cnt': t[6],
'address': construct_address(t[2],t[3],t[4],t[5])}
for t in facilities[:50]]
if len(facilities) > 50:
facility_list += [{'name': '...and %d more' % (len(facilities) - 50),
'cnt': sum([t[6] for t in facilities[50:]]),
'address': ''}]
rating_info = conn.execute(InstitutionRatings.select().where(InstitutionRatings.c.institution_id == inst_id)).fetchone()
rating_obj = {'dates': gen_stars(float(rating_info[1])),
'mesh': gen_stars(float(rating_info[2])),
'sites': gen_stars(float(rating_info[3])),
'desc': gen_stars(float(rating_info[4])),
'crit': gen_stars(float(rating_info[5])),
'overall': gen_stars(4.0)}
In [11]:
rating_obj
Out[11]:
In [55]:
params = {}
params['id'] = '115854'
trials = conn.execute(select([TrialSummary]).\
select_from(TrialSummary.join(InstitutionLookup,
and_(InstitutionLookup.c.nct_id == TrialSummary.c.nct_id,
InstitutionLookup.c.institution_id == int(params['id'])))).\
limit(50)).\
fetchall()
inv = conn.execute(select([Interventions.c.nct_id, Interventions.c.intervention_type]).\
select_from(Interventions.join(InstitutionLookup,
and_(InstitutionLookup.c.nct_id == Interventions.c.nct_id,
InstitutionLookup.c.institution_id == int(params['id']))))).\
fetchall()
inv_dict = dictify(inv)
# compile JSON object
out_obj = []
for nct_id, title, status, phase, stype in trials:
out_obj.append({'nct_id': nct_id,
'trial_title': title,
'lay_str': layman_desc(phase, status, list(inv_dict[nct_id]) if nct_id in inv_dict else '', stype)
})
In [ ]:
In [39]:
cond_id=3121
cond_data = conn.execute(ConditionDescription.\
select().\
where(ConditionDescription.c.condition_id == cond_id)).\
fetchone()
cond_syn = conn.execute(select([ConditionDescription.c.mesh_term]).\
select_from(ConditionSynonym.join(ConditionDescription,
and_(ConditionDescription.c.condition_id == ConditionSynonym.c.synonym_id,
ConditionSynonym.c.condition_id == cond_id)))).\
fetchall()
top_inst = conn.execute(select([InstitutionDescription.c.institution_id,
InstitutionDescription.c.name,
InstitutionDescription.c.image_url,
InstitutionDescription.c.location,
func.count(ConditionLookup.c.nct_id).label('cnt')]).\
select_from(InstitutionDescription.join(InstitutionLookup.join(ConditionLookup,
and_(ConditionLookup.c.condition_id == cond_id,
ConditionLookup.c.source == 'CTGOV',
ConditionLookup.c.syn_flag == 0,
ConditionLookup.c.nct_id == InstitutionLookup.c.nct_id)),
InstitutionLookup.c.institution_id == InstitutionDescription.c.institution_id)).\
group_by(InstitutionDescription.c.institution_id,
InstitutionDescription.c.name,
InstitutionDescription.c.image_url,
InstitutionDescription.c.location).\
order_by(desc('cnt')).\
limit(5)).\
fetchall()
inst_rating = conn.execute(InstitutionRatings.select().\
where(or_(InstitutionRatings.c.institution_id == t[0] for t in top_inst))).\
fetchall()
rating_dict = {t[0]: gen_stars(calc_overall([float(f) for f in t[1:6]])) for t in inst_rating}
inst_obj = [{'inst_id': t[0],
'inst_name': t[1],
'inst_img': t[2],
'inst_loc': t[3],
'inst_stars': rating_dict[t[0]]}
for t in top_inst]
In [4]:
nct_id = 'NCT01457924'
summary_data = conn.execute(select([ClinicalStudy.c.brief_title,
ClinicalStudy.c.brief_summary,
ClinicalStudy.c.detailed_description,
ClinicalStudy.c.overall_status,
ClinicalStudy.c.phase,
ClinicalStudy.c.study_type,
ClinicalStudy.c.criteria,
ClinicalStudy.c.gender,
ClinicalStudy.c.minimum_age,
ClinicalStudy.c.maximum_age,
ClinicalStudy.c.healthy_volunteers,
ClinicalStudy.c.lastchanged_date]).
select_from(ClinicalStudy).\
where(ClinicalStudy.c.nct_id == nct_id)).\
fetchone()
inv = conn.execute(select([Interventions.c.nct_id, Interventions.c.intervention_type]).\
select_from(Interventions).\
where(Interventions.c.nct_id == nct_id)).\
fetchall()
inv_dict = dictify(inv)
cond = conn.execute(select([ConditionDescription.c.condition_id,
ConditionDescription.c.mesh_term]).\
select_from(ConditionDescription.join(ConditionLookup,
and_(ConditionLookup.c.nct_id == nct_id,
ConditionLookup.c.source == 'CTGOV',
ConditionLookup.c.syn_flag == 0,
ConditionLookup.c.condition_id == ConditionDescription.c.condition_id)))).\
fetchall()
cond_suggest = conn.execute(select([ConditionDescription.c.condition_id,
ConditionDescription.c.mesh_term]).\
select_from(ConditionDescription.join(ConditionLookup,
and_(ConditionLookup.c.nct_id == nct_id,
ConditionLookup.c.source != 'CTGOV',
ConditionLookup.c.syn_flag == 0,
ConditionLookup.c.condition_id == ConditionDescription.c.condition_id)))).\
fetchall()
In [5]:
cond_suggest
Out[5]:
In [6]:
[{'id': c[0],
'name': c[1]}
for c in set(tuple(t) for t in cond_suggest)]
Out[6]:
In [ ]:
In [1]:
a = '#REDIRECT [[Brenner tumour]]'
In [5]:
a[12:a.index(']]')]
Out[5]:
In [3]:
a[13:]
Out[3]:
In [ ]: