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