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()