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
