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 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_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 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 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 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 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 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 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 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_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
