def convert_to_mouse_gene_id(self, species=None, gene_id=None): """If the species is rat or human, translate the gene_id to the mouse geneid If there is no input gene_id or there's no corresponding mouse gene_id, return None """ if not gene_id: return None mouse_gene_id = None if species == 'mouse': mouse_gene_id = gene_id elif species == 'rat': query = """SELECT mouse FROM GeneIDXRef WHERE rat='%s'""" % escape(gene_id) result = g.db.execute(query).fetchone() if result != None: mouse_gene_id = result.mouse elif species == 'human': query = """SELECT mouse FROM GeneIDXRef WHERE human='%s'""" % escape(gene_id) result = g.db.execute(query).fetchone() if result != None: mouse_gene_id = result.mouse return mouse_gene_id
def convert_to_mouse_gene_id(species=None, gene_id=None): """If the species is rat or human, translate the gene_id to the mouse geneid If there is no input gene_id or there's no corresponding mouse gene_id, return None """ if not gene_id: return None mouse_gene_id = None if species == 'mouse': mouse_gene_id = gene_id elif species == 'rat': query = """SELECT mouse FROM GeneIDXRef WHERE rat='%s'""" % escape(gene_id) result = g.db.execute(query).fetchone() if result != None: mouse_gene_id = result.mouse elif species == 'human': query = """SELECT mouse FROM GeneIDXRef WHERE human='%s'""" % escape(gene_id) result = g.db.execute(query).fetchone() if result != None: mouse_gene_id = result.mouse return mouse_gene_id
def do_literature_correlation_for_all_traits(this_trait, target_dataset, trait_geneid_dict, corr_params): input_trait_mouse_gene_id = convert_to_mouse_gene_id( target_dataset.group.species.lower(), this_trait.geneid) lit_corr_data = {} for trait, gene_id in trait_geneid_dict.iteritems(): mouse_gene_id = convert_to_mouse_gene_id( target_dataset.group.species.lower(), gene_id) if mouse_gene_id and str(mouse_gene_id).find(";") == -1: result = g.db.execute("""SELECT value FROM LCorrRamin3 WHERE GeneId1='%s' and GeneId2='%s' """ % (escape(mouse_gene_id), escape(input_trait_mouse_gene_id))).fetchone() if not result: result = g.db.execute("""SELECT value FROM LCorrRamin3 WHERE GeneId2='%s' and GeneId1='%s' """ % (escape(mouse_gene_id), escape(input_trait_mouse_gene_id))).fetchone() if result: lit_corr = result.value lit_corr_data[trait] = [gene_id, lit_corr] else: lit_corr_data[trait] = [gene_id, 0] else: lit_corr_data[trait] = [gene_id, 0] return lit_corr_data
def get_where_clause(self): if self.search_term[0] != "*": match_clause = """(MATCH (ProbeSet.Name, ProbeSet.description, ProbeSet.symbol, alias, GenbankId, UniGeneId, Probe_Target_Description) AGAINST ('%s' IN BOOLEAN MODE)) and """ % ( escape(self.search_term[0]) ) else: match_clause = "" where_clause = ( match_clause + """ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = %s """ % (escape(str(self.dataset.id))) ) return where_clause
def compile_final_query(self, from_clause='', where_clause=''): """Generates the final query string""" from_clause = self.normalize_spaces(from_clause) if self.search_term[0] == "*": query = (self.base_query + """%s WHERE PublishXRef.InbredSetId = %s and PublishXRef.PhenotypeId = Phenotype.Id and PublishXRef.PublicationId = Publication.Id and PublishFreeze.Id = %s""" % (from_clause, escape(str(self.dataset.group.id)), escape(str(self.dataset.id)))) else: query = ( self.base_query + """%s WHERE %s and PublishXRef.InbredSetId = %s and PublishXRef.PhenotypeId = Phenotype.Id and PublishXRef.PublicationId = Publication.Id and PublishFreeze.Id = %s""" % (from_clause, where_clause, escape(str( self.dataset.group.id)), escape(str(self.dataset.id)))) return query
def do_lit_correlation_for_trait_list(self): input_trait_mouse_gene_id = self.convert_to_mouse_gene_id(self.dataset.group.species.lower(), self.this_trait.geneid) for trait in self.correlation_results: if trait.geneid: trait.mouse_gene_id = self.convert_to_mouse_gene_id(self.dataset.group.species.lower(), trait.geneid) else: trait.mouse_gene_id = None if trait.mouse_gene_id and str(trait.mouse_gene_id).find(";") == -1: result = g.db.execute( """SELECT value FROM LCorrRamin3 WHERE GeneId1='%s' and GeneId2='%s' """ % (escape(str(trait.mouse_gene_id)), escape(str(input_trait_mouse_gene_id))) ).fetchone() if not result: result = g.db.execute("""SELECT value FROM LCorrRamin3 WHERE GeneId2='%s' and GeneId1='%s' """ % (escape(str(trait.mouse_gene_id)), escape(str(input_trait_mouse_gene_id))) ).fetchone() if result: lit_corr = result.value trait.lit_corr = lit_corr else: trait.lit_corr = 0 else: trait.lit_corr = 0
def compile_final_query(self, from_clause="", where_clause=""): """Generates the final query string""" from_clause = self.normalize_spaces(from_clause) if self.search_term[0] == "*": query = ( self.base_query + """%s WHERE PublishXRef.InbredSetId = %s and PublishXRef.PhenotypeId = Phenotype.Id and PublishXRef.PublicationId = Publication.Id and PublishFreeze.Id = %s""" % (from_clause, escape(str(self.dataset.group.id)), escape(str(self.dataset.id))) ) else: query = ( self.base_query + """%s WHERE %s and PublishXRef.InbredSetId = %s and PublishXRef.PhenotypeId = Phenotype.Id and PublishXRef.PublicationId = Publication.Id and PublishFreeze.Id = %s""" % (from_clause, where_clause, escape(str(self.dataset.group.id)), escape(str(self.dataset.id))) ) return query
def get_sequence(self): query = """ SELECT ProbeSet.BlatSeq FROM ProbeSet, ProbeSetFreeze, ProbeSetXRef WHERE ProbeSet.Id=ProbeSetXRef.ProbeSetId and ProbeSetFreeze.Id = ProbeSetXRef.ProbSetFreezeId and ProbeSet.Name = %s ProbeSetFreeze.Name = %s """ % (escape(self.name), escape(self.dataset.name)) results = g.db.execute(query).fetchone() return results[0]
def retrieve_other_names(self): """This method fetches the the dataset names in search_result. If the data set name parameter is not found in the 'Name' field of the data set table, check if it is actually the FullName or ShortName instead. This is not meant to retrieve the data set info if no name at all is passed. """ try: if self.type == "ProbeSet": query_args = tuple(escape(x) for x in ( str(webqtlConfig.PUBLICTHRESH), self.name, self.name, self.name)) self.id, self.name, self.fullname, self.shortname, self.data_scale, self.tissue = fetch1(""" SELECT ProbeSetFreeze.Id, ProbeSetFreeze.Name, ProbeSetFreeze.FullName, ProbeSetFreeze.ShortName, ProbeSetFreeze.DataScale, Tissue.Name FROM ProbeSetFreeze, ProbeFreeze, Tissue WHERE ProbeSetFreeze.public > %s AND ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id AND ProbeFreeze.TissueId = Tissue.Id AND (ProbeSetFreeze.Name = '%s' OR ProbeSetFreeze.FullName = '%s' OR ProbeSetFreeze.ShortName = '%s') """ % (query_args),"/dataset/"+self.name+".json", lambda r: (r["id"],r["name"],r["full_name"],r["short_name"],r["data_scale"],r["tissue"]) ) else: query_args = tuple(escape(x) for x in ( (self.type + "Freeze"), str(webqtlConfig.PUBLICTHRESH), self.name, self.name, self.name)) self.tissue = "N/A" self.id, self.name, self.fullname, self.shortname = fetchone(""" SELECT Id, Name, FullName, ShortName FROM %s WHERE public > %s AND (Name = '%s' OR FullName = '%s' OR ShortName = '%s') """ % (query_args)) except TypeError: logger.debug("Dataset {} is not yet available in GeneNetwork.".format(self.name)) pass
def compile_final_query(self, from_clause='', where_clause=''): """Generates the final query string""" from_clause = self.normalize_spaces(from_clause) query = ( self.base_query + """%s WHERE %s and ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = %s ORDER BY ProbeSet.symbol ASC """ % (escape(from_clause), where_clause, escape(str(self.dataset.id)))) return query
def get_alias_where_clause(self): search_string = escape(self.search_term[0]) if self.search_term[0] != "*": match_clause = """((MATCH (ProbeSet.symbol) AGAINST ('%s' IN BOOLEAN MODE))) and """ % ( search_string) else: match_clause = "" where_clause = (match_clause + """ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = %s """ % (escape(str(self.dataset.id)))) return where_clause
def compile_final_query(self, from_clause = '', where_clause = ''): """Generates the final query string""" from_clause = self.normalize_spaces(from_clause) query = (self.base_query + """%s WHERE %s and ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = %s ORDER BY ProbeSet.symbol ASC """ % (escape(from_clause), where_clause, escape(str(self.dataset.id)))) return query
def get_sequence(self): query = """ SELECT ProbeSet.BlatSeq FROM ProbeSet, ProbeSetFreeze, ProbeSetXRef WHERE ProbeSet.Id=ProbeSetXRef.ProbeSetId and ProbeSetFreeze.Id = ProbeSetXRef.ProbSetFreezeId and ProbeSet.Name = %s ProbeSetFreeze.Name = %s """ % (escape(self.name), escape(self.dataset.name)) logger.sql(query) results = g.db.execute(query).fetchone() return results[0]
def quick_search(self): #search_terms = "" #for term in self.search_terms.split(): # search_terms += '+{} '.format(term) search_terms = ' '.join('+{}'.format(escape(term)) for term in self.search_terms.split()) print("search_terms are:", search_terms) query = """ SELECT table_name, the_key, result_fields FROM QuickSearch WHERE MATCH (terms) AGAINST ('{}' IN BOOLEAN MODE) """.format( search_terms) with Bench("Doing QuickSearch Query: "): dbresults = g.db.execute(query, no_parameters=True).fetchall() #print("results: ", pf(results)) self.results = collections.defaultdict(list) type_dict = { 'PublishXRef': 'phenotype', 'ProbeSetXRef': 'mrna_assay', 'GenoXRef': 'genotype' } self.species_groups = {} for dbresult in dbresults: this_result = {} this_result['table_name'] = dbresult.table_name if self.trait_type == type_dict[ dbresult.table_name] or self.trait_type == 'all': this_result['key'] = dbresult.the_key this_result['result_fields'] = json.loads( dbresult.result_fields) this_species = this_result['result_fields']['species'] this_group = this_result['result_fields']['group_name'] if this_species not in self.species_groups: self.species_groups[this_species] = {} if type_dict[dbresult.table_name] not in self.species_groups[ this_species]: self.species_groups[this_species][type_dict[ dbresult.table_name]] = [] if this_group not in self.species_groups[this_species][ type_dict[dbresult.table_name]]: self.species_groups[this_species][type_dict[ dbresult.table_name]].append(this_group) #if type_dict[dbresult.table_name] not in self.species_groups: # self.species_groups[type_dict[dbresult.table_name]] = {} #if this_species not in self.species_groups[type_dict[dbresult.table_name]]: # self.species_groups[type_dict[dbresult.table_name]][this_species] = [] #if this_group not in self.species_groups[type_dict[dbresult.table_name]][this_species]: # self.species_groups[type_dict[dbresult.table_name]][this_species].append(this_group) self.results[type_dict[dbresult.table_name]].append( this_result) import redis Redis = redis.Redis()
def login(): error = '' try: c, conn = connection() if request.method == "POST": data = c.execute("SELECT * FROM entries WHERE username= (%s)", [escape(request.form['username'])]) data = c.fetchone()[2] #if int(data) == 0: # error = "Invalid Username/Password. Try again!" if sha256_crypt.verify(request.form['password'], data): session['logged_in'] = True session['username'] = request.form['username'] flash("You are now logged in!") return redirect(url_for('dashboard')) else: error = "Invalid Username/Password. Try again!" gc.collect() return render_template("login.html", error=error) except Exception as e: #flash (e) error = "Invalid Username/Password. Try again!" return render_template("login.html", error=error)
def retrieve_other_names(self): """ If the data set name parameter is not found in the 'Name' field of the data set table, check if it is actually the FullName or ShortName instead. This is not meant to retrieve the data set info if no name at all is passed. """ query_args = tuple(escape(x) for x in ( (self.type + "Freeze"), str(webqtlConfig.PUBLICTHRESH), self.name, self.name, self.name)) print("query_args are:", query_args) #print(""" # SELECT Id, Name, FullName, ShortName # FROM %s # WHERE public > %s AND # (Name = '%s' OR FullName = '%s' OR ShortName = '%s') # """ % (query_args)) try: self.id, self.name, self.fullname, self.shortname = g.db.execute(""" SELECT Id, Name, FullName, ShortName FROM %s WHERE public > %s AND (Name = '%s' OR FullName = '%s' OR ShortName = '%s') """ % (query_args)).fetchone() except TypeError: print("Dataset {} is not yet available in GeneNetwork.".format(self.name)) pass
def setup(self): # Fields in the database table self.search_fields = ['Name', 'Chr'] # Find out what display_fields is self.display_fields = ['name', 'chr', 'mb', 'source2', 'sequence'] # Fields displayed in the search results table header self.header_fields = ['Index', 'ID', 'Location'] # Todo: Obsolete or rename this field self.type = 'Geno' self.query_for_group = ''' SELECT InbredSet.Name, InbredSet.Id, InbredSet.GeneticType FROM InbredSet, GenoFreeze WHERE GenoFreeze.InbredSetId = InbredSet.Id AND GenoFreeze.Name = "%s" ''' % escape(self.name)
def run(self): self.search_term = [float(value) for value in self.search_term] if self.search_operator == "=": assert isinstance(self.search_term, (list, tuple)) self.range_min, self.range_max = self.search_term[:2] self.where_clause = """ (SELECT Pow(2, max(value) -min(value)) FROM ProbeSetData WHERE ProbeSetData.Id = ProbeSetXRef.dataId) > %s AND (SELECT Pow(2, max(value) -min(value)) FROM ProbeSetData WHERE ProbeSetData.Id = ProbeSetXRef.dataId) < %s """ % self.mescape(min(self.range_min, self.range_max), max(self.range_min, self.range_max)) else: # Deal with >, <, >=, and <= self.where_clause = """ (SELECT Pow(2, max(value) -min(value)) FROM ProbeSetData WHERE ProbeSetData.Id = ProbeSetXRef.dataId) > %s """ % (escape(self.search_term[0])) print("where_clause is:", pf(self.where_clause)) self.query = self.compile_final_query(where_clause = self.where_clause) return self.execute(self.query)
def get_where_clause(self): if self.search_operator == "=": assert isinstance(self.search_term, (list, tuple)) self.range_min, self.range_max = self.search_term[:2] where_clause = """ (SELECT Pow(2, max(value) -min(value)) FROM ProbeSetData WHERE ProbeSetData.Id = ProbeSetXRef.dataId) > %s AND (SELECT Pow(2, max(value) -min(value)) FROM ProbeSetData WHERE ProbeSetData.Id = ProbeSetXRef.dataId) < %s """ % self.mescape( min(self.range_min, self.range_max), max(self.range_min, self.range_max) ) else: # Deal with >, <, >=, and <= where_clause = """ (SELECT Pow(2, max(value) -min(value)) FROM ProbeSetData WHERE ProbeSetData.Id = ProbeSetXRef.dataId) > %s """ % ( escape(self.search_term[0]) ) logger.debug("where_clause is:", pf(where_clause)) return where_clause
def run_combined(self, from_clause, where_clause): """Generates and runs a combined search of an phenotype dataset""" logger.debug("Running PhenotypeSearch") from_clause = self.normalize_spaces(from_clause) query = (self.base_query + """%s WHERE %s PublishXRef.InbredSetId = %s and PublishXRef.PhenotypeId = Phenotype.Id and PublishXRef.PublicationId = Publication.Id and PublishFreeze.Id = %s""" % (from_clause, where_clause, escape(str( self.dataset.group.id)), escape(str(self.dataset.id)))) return self.execute(query)
def build_table_dump(table_name, rows_with_desc, ignore_duplicate_insert): "Build a dump-like output from the given table and rows" table_dump = '' for row in rows_with_desc[0]: table_dump += 'INSERT%s INTO %s VALUES (%s);\n' % \ (ignore_duplicate_insert and ' IGNORE' or '', table_name, ','.join([escape(column, conversions) for column in row])) return table_dump
def compile_final_query(self, from_clause='', where_clause=''): """Generates the final query string""" from_clause = self.normalize_spaces(from_clause) if self.search_term[0] == "*": query = (self.base_query + """WHERE Geno.Id = GenoXRef.GenoId and GenoXRef.GenoFreezeId = GenoFreeze.Id and GenoFreeze.Id = %s""" % (escape(str(self.dataset.id)))) else: query = (self.base_query + """WHERE %s and Geno.Id = GenoXRef.GenoId and GenoXRef.GenoFreezeId = GenoFreeze.Id and GenoFreeze.Id = %s""" % (where_clause, escape(str(self.dataset.id)))) return query
def run_combined(self, from_clause='', where_clause=''): """Generates and runs a combined search of an mRNA expression dataset""" logger.debug("Running ProbeSetSearch") #query = self.base_query + from_clause + " WHERE " + where_clause from_clause = self.normalize_spaces(from_clause) query = ( self.base_query + """%s WHERE %s and ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = %s ORDER BY ProbeSet.symbol ASC """ % (escape(from_clause), where_clause, escape(str(self.dataset.id)))) return self.execute(query)
def retrieve_other_names(self): """ If the data set name parameter is not found in the 'Name' field of the data set table, check if it is actually the FullName or ShortName instead. This is not meant to retrieve the data set info if no name at all is passed. """ try: if self.type == "ProbeSet": query_args = tuple(escape(x) for x in ( str(webqtlConfig.PUBLICTHRESH), self.name, self.name, self.name)) self.id, self.name, self.fullname, self.shortname, self.tissue = g.db.execute(""" SELECT ProbeSetFreeze.Id, ProbeSetFreeze.Name, ProbeSetFreeze.FullName, ProbeSetFreeze.ShortName, Tissue.Name FROM ProbeSetFreeze, ProbeFreeze, Tissue WHERE ProbeSetFreeze.public > %s AND ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id AND ProbeFreeze.TissueId = Tissue.Id AND (ProbeSetFreeze.Name = '%s' OR ProbeSetFreeze.FullName = '%s' OR ProbeSetFreeze.ShortName = '%s') """ % (query_args)).fetchone() else: query_args = tuple(escape(x) for x in ( (self.type + "Freeze"), str(webqtlConfig.PUBLICTHRESH), self.name, self.name, self.name)) self.tissue = "N/A" self.id, self.name, self.fullname, self.shortname = g.db.execute(""" SELECT Id, Name, FullName, ShortName FROM %s WHERE public > %s AND (Name = '%s' OR FullName = '%s' OR ShortName = '%s') """ % (query_args)).fetchone() except TypeError: print("Dataset {} is not yet available in GeneNetwork.".format(self.name)) pass
def run_combined(self, from_clause, where_clause): """Generates and runs a combined search of an phenotype dataset""" logger.debug("Running PhenotypeSearch") from_clause = self.normalize_spaces(from_clause) query = ( self.base_query + """%s WHERE %s PublishXRef.InbredSetId = %s and PublishXRef.PhenotypeId = Phenotype.Id and PublishXRef.PublicationId = Publication.Id and PublishFreeze.Id = %s""" % (from_clause, where_clause, escape(str(self.dataset.group.id)), escape(str(self.dataset.id))) ) return self.execute(query)
def retrieve_genes(self, column_name): query = """ select ProbeSet.Name, ProbeSet.%s from ProbeSet,ProbeSetXRef where ProbeSetXRef.ProbeSetFreezeId = %s and ProbeSetXRef.ProbeSetId=ProbeSet.Id; """ % (column_name, escape(str(self.id))) results = g.db.execute(query).fetchall() return dict(results)
def run_combined(self, from_clause = '', where_clause = ''): """Generates and runs a combined search of an mRNA expression dataset""" logger.debug("Running ProbeSetSearch") #query = self.base_query + from_clause + " WHERE " + where_clause from_clause = self.normalize_spaces(from_clause) query = (self.base_query + """%s WHERE %s and ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = %s ORDER BY ProbeSet.symbol ASC """ % (escape(from_clause), where_clause, escape(str(self.dataset.id)))) return self.execute(query)
def compile_final_query(self, from_clause = '', where_clause = ''): """Generates the final query string""" from_clause = self.normalize_spaces(from_clause) if self.search_term[0] == "*": query = (self.base_query + """WHERE Geno.Id = GenoXRef.GenoId and GenoXRef.GenoFreezeId = GenoFreeze.Id and GenoFreeze.Id = %s"""% (escape(str(self.dataset.id)))) else: query = (self.base_query + """WHERE %s and Geno.Id = GenoXRef.GenoId and GenoXRef.GenoFreezeId = GenoFreeze.Id and GenoFreeze.Id = %s"""% (where_clause, escape(str(self.dataset.id)))) return query
def get_where_clause(self): if self.search_term[0] != "*": match_clause = """(MATCH (ProbeSet.Name, ProbeSet.description, ProbeSet.symbol, alias, GenbankId, UniGeneId, Probe_Target_Description) AGAINST ('%s' IN BOOLEAN MODE)) and """ % (escape(self.search_term[0])) else: match_clause = "" where_clause = (match_clause + """ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = %s """ % (escape(str(self.dataset.id)))) return where_clause
def retrieve_sample_data(self, trait): query = """ SELECT Strain.Name, ProbeSetData.value, ProbeSetSE.error, ProbeSetData.Id FROM (ProbeSetData, ProbeSetFreeze, Strain, ProbeSet, ProbeSetXRef) left join ProbeSetSE on (ProbeSetSE.DataId = ProbeSetData.Id AND ProbeSetSE.StrainId = ProbeSetData.StrainId) WHERE ProbeSet.Name = '%s' AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND ProbeSetFreeze.Name = '%s' AND ProbeSetXRef.DataId = ProbeSetData.Id AND ProbeSetData.StrainId = Strain.Id Order BY Strain.Name """ % (escape(trait), escape(self.name)) results = g.db.execute(query).fetchall() #print("RETRIEVED RESULTS HERE:", results) return results
def setup(self): #logger.debug("IS A PHENOTYPEDATASET") # Fields in the database table self.search_fields = ['Phenotype.Post_publication_description', 'Phenotype.Pre_publication_description', 'Phenotype.Pre_publication_abbreviation', 'Phenotype.Post_publication_abbreviation', 'Phenotype.Lab_code', 'Publication.PubMed_ID', 'Publication.Abstract', 'Publication.Title', 'Publication.Authors', 'PublishXRef.Id'] # Figure out what display_fields is self.display_fields = ['name', 'pubmed_id', 'pre_publication_description', 'post_publication_description', 'original_description', 'pre_publication_abbreviation', 'post_publication_abbreviation', 'lab_code', 'submitter', 'owner', 'authorized_users', 'authors', 'title', 'abstract', 'journal', 'volume', 'pages', 'month', 'year', 'sequence', 'units', 'comments'] # Fields displayed in the search results table header self.header_fields = ['Index', 'Record', 'Description', 'Authors', 'Year', 'Max LRS', 'Max LRS Location', 'Additive Effect'] self.type = 'Publish' self.query_for_group = ''' SELECT InbredSet.Name, InbredSet.Id, InbredSet.GeneticType FROM InbredSet, PublishFreeze WHERE PublishFreeze.InbredSetId = InbredSet.Id AND PublishFreeze.Name = "%s" ''' % escape(self.name)
def retrieve_genes(self, column_name): query = """ select ProbeSet.Name, ProbeSet.%s from ProbeSet,ProbeSetXRef where ProbeSetXRef.ProbeSetFreezeId = %s and ProbeSetXRef.ProbeSetId=ProbeSet.Id; """ % (column_name, escape(str(self.id))) logger.sql(query) results = g.db.execute(query).fetchall() return dict(results)
def setup(self): # Fields in the database table self.search_fields = ['Name', 'Description', 'Probe_Target_Description', 'Symbol', 'Alias', 'GenbankId', 'UniGeneId', 'RefSeq_TranscriptId'] # Find out what display_fields is self.display_fields = ['name', 'symbol', 'description', 'probe_target_description', 'chr', 'mb', 'alias', 'geneid', 'genbankid', 'unigeneid', 'omim', 'refseq_transcriptid', 'blatseq', 'targetseq', 'chipid', 'comments', 'strand_probe', 'strand_gene', 'probe_set_target_region', 'probe_set_specificity', 'probe_set_blat_score', 'probe_set_blat_mb_start', 'probe_set_blat_mb_end', 'probe_set_strand', 'probe_set_note_by_rw', 'flag'] # Fields displayed in the search results table header self.header_fields = ['Index', 'Record', 'Symbol', 'Description', 'Location', 'Mean', 'Max LRS', 'Max LRS Location', 'Additive Effect'] # Todo: Obsolete or rename this field self.type = 'ProbeSet' self.query_for_group = ''' SELECT InbredSet.Name, InbredSet.Id, InbredSet.GeneticType FROM InbredSet, ProbeSetFreeze, ProbeFreeze WHERE ProbeFreeze.InbredSetId = InbredSet.Id AND ProbeFreeze.Id = ProbeSetFreeze.ProbeFreezeId AND ProbeSetFreeze.Name = "%s" ''' % escape(self.name)
def compile_final_query(self, from_clause = '', where_clause = ''): """Generates the final query string""" from_clause = self.normalize_spaces(from_clause) query = (self.base_query + """%s WHERE %s PublishXRef.InbredSetId = %s and PublishXRef.PhenotypeId = Phenotype.Id and PublishXRef.PublicationId = Publication.Id and PublishFreeze.Id = %s""" % ( from_clause, where_clause, escape(str(self.dataset.group.id)), escape(str(self.dataset.id)))) print("query is:", pf(query)) return query
def run(self): """Generates and runs a simple search of an mRNA expression dataset""" print("Running ProbeSetSearch") query = self.base_query + """WHERE (MATCH (ProbeSet.Name, ProbeSet.description, ProbeSet.symbol, alias, GenbankId, UniGeneId, Probe_Target_Description) AGAINST ('%s' IN BOOLEAN MODE)) and ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = %s """ % (escape(self.search_term[0]), escape(str(self.dataset.id))) #print("final query is:", pf(query)) return self.execute(query)
def get_where_clause(self): field = 'GOterm.acc' go_id = 'GO:' + ('0000000' + self.search_term[0])[-7:] statements = ("""%s.symbol=GOgene_product.symbol and GOassociation.gene_product_id=GOgene_product.id and GOterm.id=GOassociation.term_id""" % (escape(self.dataset.type))) where_clause = " %s = '%s' and %s " % (field, go_id, statements) return where_clause
def get_trait_list(self): query = """ select Geno.Name from Geno, GenoXRef where GenoXRef.GenoId = Geno.Id and GenoFreezeId = {} """.format(escape(str(self.id))) results = g.db.execute(query).fetchall() trait_data = {} for trait in results: trait_data[trait[0]] = self.retrieve_sample_data(trait[0]) return trait_data
def retrieve_sample_data(self, trait): query = """ SELECT Strain.Name, ProbeSetData.value, ProbeSetSE.error, ProbeSetData.Id, Strain.Name2 FROM (ProbeSetData, ProbeSetFreeze, Strain, ProbeSet, ProbeSetXRef) left join ProbeSetSE on (ProbeSetSE.DataId = ProbeSetData.Id AND ProbeSetSE.StrainId = ProbeSetData.StrainId) WHERE ProbeSet.Name = '%s' AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND ProbeSetFreeze.Name = '%s' AND ProbeSetXRef.DataId = ProbeSetData.Id AND ProbeSetData.StrainId = Strain.Id Order BY Strain.Name """ % (escape(trait), escape(self.name)) logger.sql(query) results = g.db.execute(query).fetchall() #logger.debug("RETRIEVED RESULTS HERE:", results) return results
def run(self): """Generates and runs a search for assays across all mRNA expression datasets""" logger.debug("Running ProbeSetSearch") query = self.base_query + """WHERE (MATCH (ProbeSet.Name, ProbeSet.description, ProbeSet.symbol, ProbeSet.alias) AGAINST ('%s' IN BOOLEAN MODE)) """ % (escape(self.search_term[0])) return self.execute(query)
def get_trait_list(self): query = """ select PublishXRef.Id from PublishXRef, PublishFreeze where PublishFreeze.InbredSetId=PublishXRef.InbredSetId and PublishFreeze.Id = {} """.format(escape(str(self.id))) results = g.db.execute(query).fetchall() trait_data = {} for trait in results: trait_data[trait[0]] = self.retrieve_sample_data(trait[0]) return trait_data
def get_where_clause(self): field = 'GOterm.acc' go_id = 'GO:' + ('0000000'+self.search_term[0])[-7:] statements = ("""%s.symbol=GOgene_product.symbol and GOassociation.gene_product_id=GOgene_product.id and GOterm.id=GOassociation.term_id""" % ( escape(self.dataset.type))) where_clause = " %s = '%s' and %s " % (field, go_id, statements) return where_clause
def run_combined(self, from_clause, where_clause): """Generates and runs a combined search of an mRNA expression dataset""" print("Running ProbeSetSearch") #query = self.base_query + from_clause + " WHERE " + where_clause from_clause = self.normalize_spaces(from_clause) query = (self.base_query + """%s WHERE %s and ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = %s """ % (escape(from_clause), where_clause, escape(str(self.dataset.id)))) print("final query is:", pf(query)) return self.execute(query)
def get_trait_list_1(self): query = """ select ProbeSet.Name from ProbeSet, ProbeSetXRef where ProbeSetXRef.ProbeSetId = ProbeSet.Id and ProbeSetFreezeId = {} """.format(escape(str(self.id))) logger.sql(query) results = g.db.execute(query).fetchall() trait_data = {} for trait in results: trait_data[trait[0]] = self.retrieve_sample_data(trait[0]) return trait_data
def get_trait_list(self): query = """ select Geno.Name from Geno, GenoXRef where GenoXRef.GenoId = Geno.Id and GenoFreezeId = {} """.format(escape(str(self.id))) logger.sql(query) results = g.db.execute(query).fetchall() trait_data = {} for trait in results: trait_data[trait[0]] = self.retrieve_sample_data(trait[0]) return trait_data
def get_trait_list(self): query = """ select PublishXRef.Id from PublishXRef, PublishFreeze where PublishFreeze.InbredSetId=PublishXRef.InbredSetId and PublishFreeze.Id = {} """.format(escape(str(self.id))) logger.sql(query) results = g.db.execute(query).fetchall() trait_data = {} for trait in results: trait_data[trait[0]] = self.retrieve_sample_data(trait[0]) return trait_data
def do_lit_correlation_for_all_traits(self): input_trait_mouse_gene_id = self.convert_to_mouse_gene_id( self.dataset.group.species.lower(), self.this_trait.geneid) lit_corr_data = {} for trait, gene_id in self.trait_geneid_dict.iteritems(): mouse_gene_id = self.convert_to_mouse_gene_id( self.dataset.group.species.lower(), gene_id) if mouse_gene_id and str(mouse_gene_id).find(";") == -1: #print("gene_symbols:", input_trait_mouse_gene_id + " / " + mouse_gene_id) result = g.db.execute("""SELECT value FROM LCorrRamin3 WHERE GeneId1='%s' and GeneId2='%s' """ % (escape(mouse_gene_id), escape(input_trait_mouse_gene_id))).fetchone() if not result: result = g.db.execute("""SELECT value FROM LCorrRamin3 WHERE GeneId2='%s' and GeneId1='%s' """ % (escape(mouse_gene_id), escape(input_trait_mouse_gene_id))).fetchone() if result: #print("result:", result) lit_corr = result.value lit_corr_data[trait] = [gene_id, lit_corr] else: lit_corr_data[trait] = [gene_id, 0] else: lit_corr_data[trait] = [gene_id, 0] lit_corr_data = collections.OrderedDict( sorted(lit_corr_data.items(), key=lambda t: -abs(t[1][1]))) return lit_corr_data
def quick_search(self): #search_terms = "" #for term in self.search_terms.split(): # search_terms += '+{} '.format(term) search_terms = ' '.join('+{}'.format(escape(term)) for term in self.search_terms.split()) print("search_terms are:", search_terms) query = """ SELECT table_name, the_key, result_fields FROM QuickSearch WHERE MATCH (terms) AGAINST ('{}' IN BOOLEAN MODE) """.format(search_terms) with Bench("Doing QuickSearch Query: "): dbresults = g.db.execute(query, no_parameters=True).fetchall() #print("results: ", pf(results)) self.results = collections.defaultdict(list) type_dict = {'PublishXRef': 'phenotype', 'ProbeSetXRef': 'mrna_assay', 'GenoXRef': 'genotype'} self.species_groups = {} for dbresult in dbresults: this_result = {} this_result['table_name'] = dbresult.table_name if self.trait_type == type_dict[dbresult.table_name] or self.trait_type == 'all': this_result['key'] = dbresult.the_key this_result['result_fields'] = json.loads(dbresult.result_fields) this_species = this_result['result_fields']['species'] this_group = this_result['result_fields']['group_name'] if this_species not in self.species_groups: self.species_groups[this_species] = {} if type_dict[dbresult.table_name] not in self.species_groups[this_species]: self.species_groups[this_species][type_dict[dbresult.table_name]] = [] if this_group not in self.species_groups[this_species][type_dict[dbresult.table_name]]: self.species_groups[this_species][type_dict[dbresult.table_name]].append(this_group) #if type_dict[dbresult.table_name] not in self.species_groups: # self.species_groups[type_dict[dbresult.table_name]] = {} #if this_species not in self.species_groups[type_dict[dbresult.table_name]]: # self.species_groups[type_dict[dbresult.table_name]][this_species] = [] #if this_group not in self.species_groups[type_dict[dbresult.table_name]][this_species]: # self.species_groups[type_dict[dbresult.table_name]][this_species].append(this_group) self.results[type_dict[dbresult.table_name]].append(this_result) import redis Redis = redis.Redis()
def do_lit_correlation_for_trait_list(self): input_trait_mouse_gene_id = self.convert_to_mouse_gene_id( self.dataset.group.species.lower(), self.this_trait.geneid) for trait in self.correlation_results: if trait.geneid: trait.mouse_gene_id = self.convert_to_mouse_gene_id( self.dataset.group.species.lower(), trait.geneid) else: trait.mouse_gene_id = None if trait.mouse_gene_id and str( trait.mouse_gene_id).find(";") == -1: result = g.db.execute("""SELECT value FROM LCorrRamin3 WHERE GeneId1='%s' and GeneId2='%s' """ % (escape(str(trait.mouse_gene_id)), escape(str(input_trait_mouse_gene_id)))).fetchone() if not result: result = g.db.execute("""SELECT value FROM LCorrRamin3 WHERE GeneId2='%s' and GeneId1='%s' """ % (escape(str(trait.mouse_gene_id)), escape(str(input_trait_mouse_gene_id)))).fetchone() if result: lit_corr = result.value trait.lit_corr = lit_corr else: trait.lit_corr = 0 else: trait.lit_corr = 0
def signup(): try: form = RegistrationForm(request.form) if request.method == "POST" and form.validate(): username = form.username.data email = form.email.data card = form.card.data password = sha256_crypt.encrypt((str(form.password.data))) c, conn = connection() u = c.execute("SELECT * FROM entries WHERE username = (%s)", [(escape(username))]) if int(u) > 0: flash("Username already taken, Choose another one") return render_template("signup.html", form=form) else: c.execute( "INSERT INTO entries(username, password, email,card_no) VALUES (%s, %s, %s, %s)", (escape(username), escape(password), escape(email), escape(card))) conn.commit() flash("Thanks for Registering") c.close() conn.close() gc.collect() session['logged_in'] = True session['username'] = username return redirect(url_for('dashboard')) return render_template("signup.html", form=form) except Exception as e: return (str(e))
def compile_final_query(self, from_clause = '', where_clause = ''): """Generates the final query string""" from_clause = self.normalize_spaces(from_clause) query = (self.base_query + """WHERE %s and Geno.Id = GenoXRef.GenoId and GenoXRef.GenoFreezeId = GenoFreeze.Id and GenoFreeze.Id = %s"""% (where_clause, escape(str(self.dataset.id)))) print("query is:", pf(query)) return query
def retrieve_sample_data(self, trait): query = """ SELECT Strain.Name, TempData.value, TempData.SE, TempData.NStrain, TempData.Id FROM TempData, Temp, Strain WHERE TempData.StrainId = Strain.Id AND TempData.Id = Temp.DataId AND Temp.name = '%s' Order BY Strain.Name """ % escape(trait.name) logger.sql(query) results = g.db.execute(query).fetchall()
def transform(self, s): """Return string transformation. """ groups = self._match(s) if groups != False: try: if self.f_preformat: # Execute preformat functions for name, value in groups.iteritems(): if name in self.f_preformat.keys(): groups[name] = self.f_preformat[name](groups) formatted = self.formatter % dict( map( lambda x: (x[0], escape(x[1]) if type(x[1]) == str else x[1]), groups.iteritems())) # Useless functionality? if self.f_postformat: return self.f_postformat(formatted, groups) groups = self._get_udfs_input(groups) # Calc size if self.f_size: size = self.f_size(groups) else: size = None # Calc typesize if self.f_typesize: self.last_type_size = self.f_typesize(groups) return tvalue(s, formatted, self.type_format, size, self.last_type_size) except Exception, e: if self.nullable: return tvalue(s, None, None) # NULL else: raise RuntimeError( 'Error in transformation using %s (%s)' % (self.name, e))
def get_where_clause(self): if self.search_operator == "=": assert isinstance(self.search_term, (list, tuple)) self.range_min, self.range_max = self.search_term[:2] where_clause = """ (SELECT Pow(2, max(value) -min(value)) FROM ProbeSetData WHERE ProbeSetData.Id = ProbeSetXRef.dataId) > %s AND (SELECT Pow(2, max(value) -min(value)) FROM ProbeSetData WHERE ProbeSetData.Id = ProbeSetXRef.dataId) < %s """ % self.mescape(min(self.range_min, self.range_max), max(self.range_min, self.range_max)) else: # Deal with >, <, >=, and <= where_clause = """ (SELECT Pow(2, max(value) -min(value)) FROM ProbeSetData WHERE ProbeSetData.Id = ProbeSetXRef.dataId) > %s """ % (escape(self.search_term[0])) logger.debug("where_clause is:", pf(where_clause)) return where_clause