def top_coll(collection_id):
sess = Session()
drugs = sess.query(Drug.symbol).join(
DrugsetDrug, Drug.id == DrugsetDrug.drug).join(
Drugset, Drugset.id == DrugsetDrug.drugset).join(
SetsCollections, Drugset.id == SetsCollections.set_id).filter(
SetsCollections.type == 0).filter(
SetsCollections.collection_id == collection_id)
# Use Python's counter cause SQLs counter in SQLAlchemy is slow
drugs_freq = [{
'symbol': t[0],
'count': t[1]
} for t in Counter(drugs).most_common()[:20]]
genes = sess.query(Gene.symbol).join(
GenesetGene, Gene.id == GenesetGene.gene).join(
Geneset, Geneset.id == GenesetGene.geneset).join(
SetsCollections, Geneset.id == SetsCollections.set_id).filter(
SetsCollections.type == 1).filter(
SetsCollections.collection_id == collection_id)
genes_freq = [{
'symbol': t[0],
'count': t[1]
} for t in Counter(genes).most_common()[:20]]
sess.close()
return {'top_drugs': drugs_freq, 'top_genes': genes_freq}
def downgrade():
sess = Session(bind=op.get_bind())
gene_lookup = {
gene.id: gene.symbol
for gene in sess.query(models_03_899a089268fa.Gene)
}
for geneset in sess.query(models_02_dc18a84a5406.Geneset):
geneset.genes = [
gene_lookup[geneset_genes.gene]
for geneset_genes in sess.query(models_03_899a089268fa.GenesetGene).filter(models_03_899a089268fa.GenesetGene.geneset == geneset.id)
]
sess.commit()
# Confirm equivalence
for geneset_02, geneset_03 in zip(sess.query(models_02_dc18a84a5406.Geneset), sess.query(models_03_899a089268fa.Geneset)):
assert geneset_02.jsonify() == geneset_03.jsonify(deep=True)
drug_lookup = {
drug.id: drug.symbol
for drug in sess.query(models_03_899a089268fa.Drug)
}
for drugset in sess.query(models_02_dc18a84a5406.Drugset):
drugset.drugs = [
drug_lookup[drugset_drugs.drug]
for drugset_drugs in sess.query(models_03_899a089268fa.DrugsetDrug).filter(models_03_899a089268fa.DrugsetDrug.drugset == drugset.id)
]
sess.commit()
# Confirm equivalence
for drugset_02, drugset_03 in zip(sess.query(models_02_dc18a84a5406.Drugset), sess.query(models_03_899a089268fa.Drugset)):
assert drugset_02.jsonify() == drugset_03.jsonify(deep=True)
sess.close()
def get_collection(id):
try:
sess = Session()
r = {'sets': {'drugsets': [], 'genesets': []}}
r['sets']['drugsets'] = [
ds.jsonify() for ds in sess.query(Drugset).join(
SetsCollections, Drugset.id == SetsCollections.set_id).filter(
SetsCollections.type == 0).filter(
SetsCollections.collection_id == id)
]
r['sets']['genesets'] = [
gs.jsonify() for gs in sess.query(Geneset).join(
SetsCollections, Geneset.id == SetsCollections.set_id).filter(
SetsCollections.type == 1).filter(
SetsCollections.collection_id == id)
]
r['name'] = sess.query(Collections).filter(
Collections.id == id).first().name
r['description'] = sess.query(Collections).filter(
Collections.id == id).first().description
r['id'] = id
sess.close()
return json.dumps(r, default=str), 200, {
'ContentType': 'application/json'
}
except Exception as e:
traceback.print_exc()
return json.dumps({'error': str(e)}), 404, {
'ContentType': 'application/json'
}
def get_drug(name):
try:
sess = Session()
r = {'name': name, 'sets': [{'id': drugset.id, 'name': drugset.descrShort}
for drugset in sess.query(Drugset) \
.join(DrugsetDrug, Drugset.id == DrugsetDrug.drugset) \
.join(Drug, DrugsetDrug.drug == Drug.id) \
.filter(Drug.symbol == name,
Drugset.reviewed == 1,
sa.or_(Drugset.category == 2, Drugset.category == 3))]}
#
# drug = sess.query(Drug).filter(Drug.symbol == name).first()
# drugset_ids = sess.query(DrugsetDrug).filter(DrugsetDrug.drug == drug.id)
# r = {'name': name, 'sets': []}
# for drugset_id in drugset_ids:
# drugset = sess.query(Drugset)\
# .filter(Drugset.id == drugset_id.drugset)\
# .filter(sa.or_(Drugset.category == 2, Drugset.category == 3))\
# .filter(Drugset.reviewed == 1)\
# .first()
# if drugset:
# r['sets'].append({'id': drugset.id, 'name': drugset.descrShort})
sess.close()
return json.dumps(r, default=str), 200, {'ContentType': 'application/json'}
except Exception as e:
traceback.print_exc()
return json.dumps({'error': str(e)}), 404, {'ContentType': 'application/json'}
def drugsets_submissions():
sess = Session()
q = sess.query(
sa.cast(Drugset.date, sa.Date).label('date'),
sa.func.count(sa.cast(Drugset.date, sa.Date)).label('count')).group_by(
sa.cast(Drugset.date, sa.Date)).filter(Drugset.reviewed == 1)
sess.close()
return list(map(lambda x: {'date': str(x), 'count': dict(q)[x]}, dict(q)))
def bar_drugs(categories=0):
sess = Session()
qs = {'2': top_drugs_exp, '3': top_drugs_comp, '4': top_drugs_tw}
if categories:
td = qs[categories](sess)
else:
td = top_drugs_qs(sess)
sess.close()
return [{'symbol': t[0], 'count': t[1]} for t in td]
def get_drugset(id):
try:
sess = Session()
r = sess.query(Drugset).filter(Drugset.id == id).first().jsonify()
sess.close()
return json.dumps(r, default=str), 200, {'ContentType': 'application/json'}
except Exception as e:
traceback.print_exc()
return json.dumps({'error': str(e)}), 404, {'ContentType': 'application/json'}
def get_drugsets(reviewed=1):
try:
sess = Session()
r = [g.jsonify() for g in sess.query(Drugset).filter(Drugset.reviewed == reviewed)]
sess.close()
return json.dumps(r, default=str), 200, {'ContentType': 'application/json'}
except Exception as e:
traceback.print_exc()
return json.dumps({'error': str(e)}), 404, {'ContentType': 'application/json'}
def get_session() -> Generator[Session, None, None]:
session = Session()
try:
yield session
session.commit()
except Exception as e:
print(e)
session.rollback()
finally:
session.close()
def load():
sess = Session()
cur_type = None
for item in map(_load_item, filter(None, sys.stdin)):
if item.__class__ != cur_type and cur_type != None:
sess.commit()
cur_type = item.__class__
sess.add(item)
sess.commit()
sess.close()
def genesets(category=0, collection=0):
sess = Session()
if category:
for geneset in sess.query(Geneset).filter(
Geneset.reviewed == 1).filter(Geneset.category == category):
yield geneset.to_gmt() + '\n'
else:
for geneset in sess.query(Geneset).filter(Geneset.reviewed == 1):
yield geneset.to_gmt() + '\n'
sess.close()
def add_drugset(form):
source = form['source']
drug_set = list(filter(None, (drug.strip() for drug in drug_splitter.split(form['drugSet']))))
descr_full = form['descrFull']
desc_short = form['descrShort']
author_name = form['authorName']
author_email = form['authorEmail']
author_aff = form['authorAff']
show_contacts = 1 if 'showContacts' in form else 0
enrichr_ids = enrichr_submit(drug_set, desc_short, 'DrugEnrichr')
enrichr_shortid = enrichr_ids['shortId']
enrichr_userlistid = enrichr_ids['userListId']
meta = {}
if 'experimental' in form and form['experimental'] == 'on':
category = 2
elif 'computational' in form and form['computational'] == 'on':
category = 3
elif 'twitter' in form and form['twitter'] == 'on':
category = 4
else:
category = 1
# '-show_contacts' is correction for, well, show_contacts presence
if len(form) - show_contacts > 7:
meta = match_meta(form, 7)
try:
sess = Session()
sess.add(
Drugset.create(
sess,
enrichrShortId=enrichr_shortid,
enrichrUserListId=enrichr_userlistid,
descrShort=desc_short,
descrFull=descr_full,
authorName=author_name,
authorAffiliation=author_aff,
authorEmail=author_email,
showContacts=show_contacts,
drugs=drug_set,
source=source,
meta=meta,
category=category,
)
)
sess.commit()
sess.close()
return json.dumps({'success': True}), 200, {'ContentType': 'application/json'}
except Exception as e:
traceback.print_exc()
return json.dumps({'success': False, 'error': str(e)}), 500, {'ContentType': 'application/json'}
def change_category(form):
drugset_id = form['id']
category = form['category']
try:
sess = Session()
drugset = sess.query(Drugset).get(drugset_id)
drugset.category = category
sess.commit()
sess.close()
return json.dumps({'success': True}), 200, {'ContentType': 'application/json'}
except Exception as e:
traceback.print_exc()
return json.dumps({'success': False, 'error': str(e)}), 500, {'ContentType': 'application/json'}
def approve_drugset(form):
drugset_id = form['id']
reviewed = form['reviewed']
try:
sess = Session()
drugset = sess.query(Drugset).get(drugset_id)
drugset.reviewed = reviewed
sess.commit()
sess.close()
return json.dumps({'success': True}), 200, {'ContentType': 'application/json'}
except Exception as e:
traceback.print_exc()
return json.dumps({'success': False, 'error': str(e)}), 500, {'ContentType': 'application/json'}
def add_geneset(form):
source = form['source']
gene_set = list(
filter(None, (gene.strip()
for gene in gene_splitter.split(form['geneSet']))))
descr_full = form['descrFull']
desc_short = form['descrShort']
author_name = form['authorName']
author_email = form['authorEmail']
author_aff = form['authorAff']
show_contacts = 1 if 'showContacts' in form else 0
enrichr_ids = enrichr_submit(gene_set, desc_short, 'Enrichr')
enrichr_shortid = enrichr_ids['shortId']
enrichr_userlistid = enrichr_ids['userListId']
meta = {}
# '-show_contacts' is correction for, well, show_contacts presence
if len(form) - show_contacts > 9:
meta = match_meta(form, 9)
try:
sess = Session()
sess.add(
Geneset.create(
sess,
enrichrShortId=enrichr_shortid,
enrichrUserListId=enrichr_userlistid,
descrShort=desc_short,
descrFull=descr_full,
authorName=author_name,
authorAffiliation=author_aff,
authorEmail=author_email,
showContacts=show_contacts,
genes=gene_set,
source=source,
meta=meta,
))
sess.commit()
sess.close()
return json.dumps({'success': True}), 200, {
'ContentType': 'application/json'
}
except Exception as e:
traceback.print_exc()
return json.dumps({
'success': False,
'error': str(e)
}), 500, {
'ContentType': 'application/json'
}
def get_gene(name):
try:
sess = Session()
r = {'name': name, 'sets': [{'id': geneset.id, 'name': geneset.descrShort}
for geneset in sess.query(Geneset) \
.join(GenesetGene, Geneset.id == GenesetGene.geneset) \
.join(Gene, GenesetGene.gene == Gene.id) \
.filter(Gene.symbol == name, Geneset.reviewed == 1)]}
sess.close()
return json.dumps(r, default=str), 200, {
'ContentType': 'application/json'
}
except Exception as e:
traceback.print_exc()
return json.dumps({'error': str(e)}), 404, {
'ContentType': 'application/json'
}
def stats():
sess = Session()
ret = {
'n_genesets': sess.query(Geneset).filter(Geneset.reviewed == 1).count(),
'n_drugsets': sess.query(Drugset).filter(Drugset.reviewed == 1).count(),
'n_unique_genes': sess.query(Gene).distinct(Gene.id) \
.join(GenesetGene, GenesetGene.gene == Gene.id) \
.join(Geneset, GenesetGene.geneset == Geneset.id) \
.filter(Geneset.reviewed == 1) \
.count(),
'n_unique_drugs': sess.query(Drug).distinct(Drug.id) \
.join(DrugsetDrug, DrugsetDrug.drug == Drug.id) \
.join(Drugset, DrugsetDrug.drugset == Drugset.id) \
.filter(Drugset.reviewed == 1) \
.count(),
}
sess.close()
return ret
def twitter_drug_submission(name):
try:
sess = Session()
drug = sess.query(Drug).filter(Drug.symbol == name).first()
drugset_ids = sess.query(DrugsetDrug).filter(DrugsetDrug.drug == drug.id)
r = []
for drugset_id in drugset_ids:
date = sess.query(sa.cast(Drugset.date, sa.Date)) \
.filter(Drugset.id == drugset_id.drugset) \
.filter(Drugset.category == 4) \
.filter(Drugset.reviewed == 1).first()
if date:
r.append({'count': 1, 'date': date[0]})
sess.close()
return json.dumps(r, default=str), 200, {'ContentType': 'application/json'}
except Exception as e:
traceback.print_exc()
return json.dumps({'error': str(e)}), 404, {'ContentType': 'application/json'}
def route(qs, draw=0, start=0, length=100, order=[], search={}, **kwargs):
sess = Session()
draw = int(draw)
start = int(start)
length = int(length)
qs = qs(sess)
if type(search) == dict and 'value' in search:
filtered_qs = qs.filter(search_filter(sess, search['value']))
else:
filtered_qs = qs
if type(order) == list:
ordered_qs = filtered_qs.order_by(*[
cols[int(o['column'])][0] if o['dir'] ==
'asc' else cols[int(o['column'])][0].desc() for o in order
])
else:
ordered_qs = filtered_qs
if length > 0:
limited_qs = ordered_qs.offset(start).limit(length)
else:
limited_qs = ordered_qs.offset(start)
result = {
'draw':
draw,
'recordsTotal':
qs.count(),
'recordsFiltered':
filtered_qs.count(),
'data': [{cols[ind][1]: cell
for ind, cell in enumerate(record)}
for record in limited_qs]
if aggregator is None else aggregator(limited_qs),
}
sess.close()
return result
def get_db():
try:
db = Session()
yield db
finally:
db.close()
def bar_genes():
sess = Session()
tg = top_genes_qs(sess)
sess.close()
return [{'symbol': t[0], 'count': t[1]} for t in tg]
def dump():
sess = Session()
for Model in _get_models():
for item in sess.query(Model):
print(_dump_item(item), file=sys.stdout)
sess.close()
def upgrade():
sess = Session(bind=op.get_bind())
# Get all unique genes
genes = set.union(set(), *[
set(genes)
for (genes,) in sess.query(models_02_dc18a84a5406.Geneset.genes)
])
# Add genes
for symbol in genes:
sess.add(models_03_899a089268fa.Gene(symbol=symbol))
sess.commit()
# Construct a symbol lookup table for these genes
gene_lookup = {
gene.symbol: gene.id
for gene in sess.query(models_03_899a089268fa.Gene)
}
# Add GenesetGene
for geneset in sess.query(models_02_dc18a84a5406.Geneset):
for gene_id in set(map(gene_lookup.get, geneset.genes)):
sess.add(models_03_899a089268fa.GenesetGene(geneset=geneset.id, gene=gene_id))
sess.commit()
# Confirm equivalence
for geneset_02, geneset_03 in zip(sess.query(models_02_dc18a84a5406.Geneset), sess.query(models_03_899a089268fa.Geneset)):
assert geneset_02.jsonify() == geneset_03.jsonify(deep=True), '{} != {}'.format(
str(geneset_02.jsonify()),
str(geneset_03.jsonify(deep=True))
)
# Get all unique drugs
drugs = set.union(set(), *[
set(drugs)
for (drugs,) in sess.query(models_02_dc18a84a5406.Drugset.drugs)
])
# Add drugs
for symbol in drugs:
sess.add(models_03_899a089268fa.Drug(symbol=symbol))
sess.commit()
# Construct a symbol lookup table for these drugs
drug_lookup = {
drug.symbol: drug.id
for drug in sess.query(models_03_899a089268fa.Drug)
}
# Add drugsetdrug
for drugset in sess.query(models_02_dc18a84a5406.Drugset):
for drug_id in set(map(drug_lookup.get, drugset.drugs)):
sess.add(models_03_899a089268fa.DrugsetDrug(drugset=drugset.id, drug=drug_id))
sess.commit()
# Confirm equivalence
for drugset_02, drugset_03 in zip(sess.query(models_02_dc18a84a5406.Drugset), sess.query(models_03_899a089268fa.Drugset)):
assert drugset_02.jsonify() == drugset_03.jsonify(deep=True)
sess.close()