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
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}')
class TestApp(unittest.TestCase):
@classmethod
def setUpClass(cls) -> None:
session = Session()
session.query(User).delete()
session.commit()
def setUp(self) -> None:
self.session = Session()
def tearDown(self) -> None:
pass
def test_creating_user(self, get_user_mock):
new_user = User(username='******')
self.session.add(new_user)
self.session.commit()
user_id = new_user.id
self.assertEqual('Frank', self.session.query(User).get(user_id).username)
def test_creating_user2(self, get_user_mock):
self.session.query(User).delete()
new_user = User(username='******')
self.session.add(new_user)
self.session.commit()
user_id = new_user.id
self.assertEqual('Alex', self.session.query(User).get(user_id).username)
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 upgrade():
# ### commands auto generated by Alembic - please adjust! ###
# Add the default category 'all'
sess = Session(bind=op.get_bind())
sess.add(models_11_94ad0824ffb3.Categories())
sess.commit()
op.create_index('categories_name_uindex',
'categories', ['name'],
unique=True)
op.create_foreign_key('drugsets_categories_id_fk', 'drugsets',
'categories', ['category'], ['id'])
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 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'
}
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,
})
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 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 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 create_house(data: InputValidator, db: Session = Depends(get_db)):
instance = House(**data.dict(exclude_unset=True))
db.add(instance)
db.commit()
return instance
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
async def create_sensor(data: InputValidator, db: Session = Depends(get_db)):
instance = Sensor(**data.dict(exclude_unset=True))
db.add(instance)
db.commit()
asyncio.create_task(patch_pair_sensor(db, instance))
return instance
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()
