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_collections_sizes(): # It's a draft/idae. It doesn't work sess = Session() collections = sess.query(Collections) for collection in collections: coll_size = sess.query(SetsCollections).filter( SetsCollections.collection_id == collection.id) return None
def upgrade(): sess = Session(bind=op.get_bind()) for geneset in sess.query(models_08_2a9652d72819.Geneset): if type(geneset.meta) == str: geneset.meta = json.loads(geneset.meta) # for drugset in sess.query(models_08_2a9652d72819.Drugset): if type(drugset.meta) == str: drugset.meta = json.loads(drugset.meta) # sess.commit()
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 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)))
async def download_excel(pk: int, db: Session = Depends(get_db)): download: Download = db.query(Download).get(pk) filename = download.label + '.xlsx' return FileResponse( os.path.join(BASE_DIR, download.filename), media_type= 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', filename=filename)
async def patch_pair_sensor(db: Session, instance: Sensor): pair_sensors: Union[List[Sensor], None] = db.query(Sensor).filter( Sensor.pair == instance.id).all() if not pair_sensors: return for pair_sensor in pair_sensors: pair_sensor.house_id = instance.house_id db.commit()
def upgrade(): sess = Session(bind=op.get_bind()) # Ensure all genes have a uppercase instance uppercase_instances = { instance.symbol: instance for instance in models_05_8f87d34cbe90.Gene.resolve_set( sess, { symbol.upper() for ( symbol, ) in sess.query(models_05_8f87d34cbe90.Gene.symbol) }) } # Explicitly set it on the instance to be sure (mysql) for instance in uppercase_instances.values(): instance.symbol = instance.symbol.upper() sess.commit() # find all genes that must be transfered gene_to_remove = [] geneset_genes_to_remove = [] geneset_genes_to_add = [] for gene in sess.query(models_05_8f87d34cbe90.Gene).filter( models_05_8f87d34cbe90.Gene.id.notin_( tuple(v.id for v in uppercase_instances.values()))): gene_to_remove.append(gene.id) for geneset in gene.genesets: geneset_genes_to_remove.append( dict(gene=gene.id, geneset=geneset.id)) geneset_genes_to_add.append( dict(gene=uppercase_instances[gene.symbol.upper()].id, geneset=geneset.id)) # update m2m for item in geneset_genes_to_remove: sess.delete(sess.query(models_05_8f87d34cbe90.GenesetGene).get(item)) for item in geneset_genes_to_add: sess.add(models_05_8f87d34cbe90.GenesetGene(**item)) sess.commit() # add any orphaned genes to remove for gene in sess.query(models_05_8f87d34cbe90.Gene): if len(gene.genesets) == 0: gene_to_remove.append(gene.id) # update genes for item in set(gene_to_remove): sess.delete(sess.query(models_05_8f87d34cbe90.Gene).get(item)) sess.commit()
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 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 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 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 upgrade(): sess = Session(bind=op.get_bind()) # Ensure all drugs have a lowercase instance lowercase_instances = { instance.symbol: instance for instance in models_05_8f87d34cbe90.Drug.resolve_set( sess, { symbol.lower() for ( symbol, ) in sess.query(models_05_8f87d34cbe90.Drug.symbol) }) } # Explicitly set it on the instance to be sure (mysql) for instance in lowercase_instances.values(): instance.symbol = instance.symbol.lower() sess.commit() # find all drugs that must be transfered drug_to_remove = [] drugset_drug_to_remove = [] drugset_drug_to_add = [] for drug in sess.query(models_05_8f87d34cbe90.Drug).filter( models_05_8f87d34cbe90.Drug.id.notin_( tuple(v.id for v in lowercase_instances.values()))): drug_to_remove.append(drug.id) for drugset in drug.drugsets: drugset_drug_to_remove.append( dict(drug=drug.id, drugset=drugset.id)) drugset_drug_to_add.append( dict(drug=lowercase_instances[drug.symbol.lower()].id, drugset=drugset.id)) # update m2m for item in drugset_drug_to_remove: sess.delete(sess.query(models_05_8f87d34cbe90.DrugsetDrug).get(item)) for item in drugset_drug_to_add: sess.add(models_05_8f87d34cbe90.DrugsetDrug(**item)) sess.commit() # add any orphaned drugs to remove for drug in sess.query(models_05_8f87d34cbe90.Drug): if len(drug.drugsets) == 0: drug_to_remove.append(drug.id) # update drugs for item in set(drug_to_remove): sess.delete(sess.query(models_05_8f87d34cbe90.Drug).get(item)) sess.commit()
async def patch_sensor(pk: int, data: PatchValidator, db: Session = Depends(get_db)): instance = db.query(Sensor).get(pk) for key, value in data.dict(exclude_unset=True).items(): setattr(instance, key, value) db.commit() print(data.dict(exclude_unset=True)) asyncio.create_task(patch_pair_sensor(db, instance)) return instance
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 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 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 processRdata(filename, annotfile, outputfile): db_session = Session() # Instantiate the session from Session class in database.py # Does this file already exist? if (db_session.query(File).filter_by(filename=outputfile).count() > 0): print('File already exists.') else: proc = ("R --no-save --args working_dir=", app.config['DATA_FOLDER'], " rdata_file=", filename, " outputfile=", outputfile, " < R/ProcessRdata.R") p = subprocess.call("".join(proc), shell=True)#, stdout=subprocess.PIPE, stderr=subprocess.PIPE) f = File(filename=outputfile, filedescr='ratios', loaded=True) db_session.add(f) db_session.commit() # Parse RData file to get genes and conditions (adding them to DB as well: user -> file -> genes -> conditions -> values) parseRatiosFile(os.path.join(app.config['DATA_FOLDER'],outputfile),os.path.join(app.config['DATA_FOLDER'],annotfile)) return 0
def parseRatiosFile(ratiosfile,annotfile): db_session = Session() # Instantiate the session from Session class in database.py location = app.config['DATA_FOLDER'] dbfile = File() basenm = os.path.basename(ratiosfile) print(basenm) dbfile = db_session.query(File).filter_by(filename=basenm).first() # This file should already exist, made in processRdata print(dbfile.id) # Parse annotations file (for Mtb - from Eliza) condsub2annot = {} condsub22annot = {} condsub32annot = {} with open(annotfile, 'rb') as f: for line in csv.DictReader(f, delimiter=','): if line['condition.subset']: condsub = line['condition.subset'] else: condsub = "" if line['condition.subset2']: condsub2 = line['condition.subset2'] else: condsub2 = "" if line['condition.subset3']: condsub3 = line['condition.subset3'] else: condsub3 = "" sample = line['sample'] expid = line['experimentID'] pmid = line['PMID'] strain = line['strain'] condition = str(expid)+':::'+sample condsub2annot[condition] = condsub condsub22annot[condition] = condsub2 condsub32annot[condition] = condsub3 lncnt = 0 with open(ratiosfile, 'rb') as file: print('opened ratiosfile for reading.') lines = file.readlines() conditions = [] for line in lines: line.rstrip() linespl = line.split(',') if lncnt == 0: for i in range(2, len(linespl)): condition = linespl[i] conditions.append(condition) lncnt+=1 continue gene = linespl[1].lower() #dbgene = Gene.query.filter_by(descr=gene,file_id=dbfile.get_id()).first() #dbgeneid = dbgene.get_id() dbgene = Gene(descr=gene,file_id=dbfile.id) db_session.add(dbgene) print('added gene '+gene) db_session.commit() dbg = db_session.query(Gene).filter_by(descr=gene).first() print(str(dbg.id) + ' ' + dbg.descr) for i in range(0,len(conditions)): ratio = linespl[i+2] condition = conditions[i] annot1 = 'na' annot2 = 'na' annot3 = 'na' # Look up annotations if condition in condsub2annot: annot1 = condsub2annot[condition].rstrip().lower() if condition in condsub22annot: annot2 = condsub22annot[condition].rstrip().lower() if condition in condsub32annot: annot3 = condsub32annot[condition].rstrip().lower() s = condition.split('.') rep = s[len(s)-1] # Get everything after last '.' dbcond = Condition(condition=condition,value=ratio, gene_id=dbgene.id, replicate=rep, annot1=annot1, annot2=annot2, annot3=annot3) #print(dbcond) db_session.add(dbcond) #print('added condition '+ condition) lncnt+=1 #if lncnt > 10: break # For testing only first 10 genes print('committing db_session.') db_session.commit() print('db_session committed.') return 0