async def note_create(*,
body: str = Form(...),
db: database.Session = Depends(get_db)):
note = models.Note(body=body)
db.add(note)
db.commit()
return RedirectResponse(f'/note/{note.id}')
async def note_delete(note_id: str, db: database.Session = Depends(get_db)):
note = get_note(db, note_id)
db.delete(note)
db.commit()
return RedirectResponse('/')
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 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
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()
async def patch_house(pk: int,
data: InputValidator,
db: Session = Depends(get_db)):
instance = db.query(House).get(pk)
for key, value in data.dict(exclude_unset=True).items():
setattr(instance, key, value)
db.commit()
return instance
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 inner(*args, **kwargs):
if len(args):
args = [arg for arg in args]
args.pop(0)
self.db_session = Session()
self.auth()
res = fun(*args, **kwargs)
Session.remove()
return res
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 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]
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 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()
class Manage():
def __init__(self, task):
self.task = task
self.db_session = Session()
def run(self):
self.set_executing()
for action in self.task.actions:
if not self.run_action(action):
break
else:
self.set_success()
def run_action(self, action):
self.logger = ActionLog(self.db_session, action)
self.logger.executing_status("开始执行")
try:
action_config = ACTION_CONFIG.get(action.action_code)
action_module = importlib.import_module(
action_config.get("package"))
params = {
params.get("name"): params.get("value")
for params in action.parameter
}
self.logger.info("参数：{}".format(params))
action_obj = getattr(action_module,
action_config.get("action_class"))(
self, self.logger, **params)
action_obj.run()
except Exception as e:
self.logger.failed_status(str(e))
self.set_failed()
return False
self.logger.success_status("执行成功")
return True
def set_failed(self):
self.set_status("failed")
def set_executing(self):
self.set_status("executing")
def set_cancel(self):
self.set_status("cancel")
def set_waiting(self):
self.set_status("waiting")
def set_success(self):
self.set_status("success")
def set_status(self, status):
self.task.status = status
self.db_session.add(self.task)
self.db_session.commit()
async def note_update(note_id: str,
body: str = Form(...),
db: database.Session = Depends(get_db)):
note = get_note(db, note_id)
note.body = body
note.touch()
db.add(note)
db.commit()
return RedirectResponse(f'/note/{note_id}')
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'
}
async def note_view(request: Request,
note_id: str,
db: database.Session = Depends(get_db)):
note = get_note(db, note_id)
note.touch()
db.add(note)
db.commit()
return templates.TemplateResponse('note_view.html',
context={
'request': request,
'note': note,
})
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)
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 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 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 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
async def create_export(data: InputValidator, db: Session = Depends(get_db)):
data.dict(exclude_unset=True)
items = get_temps(db,
sensor_ids=data.sensor_ids,
start_date=data.start_date,
end_date=data.end_date,
limit=None,
skip=None,
export=True)
if not os.path.exists(os.path.join(BASE_DIR, DOWNLOADS_DIR)):
os.mkdir(os.path.join(BASE_DIR, DOWNLOADS_DIR))
file_name = os.path.join(DOWNLOADS_DIR, uuid.uuid4().hex + '.xlsx')
full_path = os.path.join(BASE_DIR, file_name)
raw_counts: List[Tuple[Sensor, int]] = db.query(
Sensor,
func.count(Sensor.house_id)).filter(Sensor.disabled != True).order_by(
desc(Sensor.house_id)).group_by(Sensor.house_id).all()
house_counts: Dict[int, Dict[str, Any]] = {}
print(full_path)
for sensor, count in raw_counts:
house = db.query(House).get(
sensor.house_id) if sensor.house_id else None
house_counts[sensor.house_id] = {'count': count, 'house': house}
asyncio.create_task(save_to_excel(items, house_counts, full_path))
instance = Download(label=data.label, filename=file_name)
db.add(instance)
db.commit()
return instance
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 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 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 _start_flask_admin_page() -> None:
admin = Admin(flask_app, name='movies blog', template_mode='bootstrap3')
session = Session()
admin.add_view(MovieView(models.Movie, session, name='Movie'))
admin.add_view(UserView(models.User, session, name='User'))
admin.add_view(
ReviewUserView(models.Review,
session,
name='Reviews-Users',
endpoint='reviews-users'))
admin.add_view(
ReviewMovieView(models.Review,
session,
name='Reviews-Movies',
endpoint='reviews-movies'))
flask_app.run(port=5000)
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 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 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
