aboutsummaryrefslogtreecommitdiff
path: root/wqflask/base
diff options
context:
space:
mode:
Diffstat (limited to 'wqflask/base')
-rw-r--r--wqflask/base/mrna_assay_tissue_data.py15
-rw-r--r--wqflask/base/species.py14
-rw-r--r--wqflask/base/trait.py421
3 files changed, 225 insertions, 225 deletions
diff --git a/wqflask/base/mrna_assay_tissue_data.py b/wqflask/base/mrna_assay_tissue_data.py
index d7e747aa..b371e39f 100644
--- a/wqflask/base/mrna_assay_tissue_data.py
+++ b/wqflask/base/mrna_assay_tissue_data.py
@@ -1,6 +1,6 @@
import collections
-from flask import g
+from wqflask.database import database_connection
from utility import db_tools
from utility import Bunch
@@ -49,7 +49,11 @@ class MrnaAssayTissueData:
# lower_symbols[gene_symbol.lower()] = True
if gene_symbol != None:
lower_symbols[gene_symbol.lower()] = True
- results = list(g.db.execute(query).fetchall())
+
+ results = None
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(query)
+ results = cursor.fetchall()
for result in results:
symbol = result[0]
if symbol is not None and lower_symbols.get(symbol.lower()):
@@ -81,9 +85,10 @@ class MrnaAssayTissueData:
FROM TissueProbeSetXRef, TissueProbeSetData
WHERE TissueProbeSetData.Id IN {} and
TissueProbeSetXRef.DataId = TissueProbeSetData.Id""".format(db_tools.create_in_clause(id_list))
-
-
- results = g.db.execute(query).fetchall()
+ results = []
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(query)
+ results = cursor.fetchall()
for result in results:
if result.Symbol.lower() not in symbol_values_dict:
symbol_values_dict[result.Symbol.lower()] = [result.value]
diff --git a/wqflask/base/species.py b/wqflask/base/species.py
index f303aabb..0a13315c 100644
--- a/wqflask/base/species.py
+++ b/wqflask/base/species.py
@@ -2,7 +2,7 @@ from collections import OrderedDict
from dataclasses import dataclass
from dataclasses import InitVar
from typing import Optional, Dict
-from flask import g
+from wqflask.database import database_connection
@dataclass
@@ -46,20 +46,20 @@ class Chromosomes:
"""Lazily fetch the chromosomes"""
chromosomes = OrderedDict()
if self.species is not None:
- query = (
+ cursor.execute(
"SELECT Chr_Length.Name, Chr_Length.OrderId, Length "
"FROM Chr_Length, Species WHERE "
"Chr_Length.SpeciesId = Species.SpeciesId AND "
- "Species.Name = "
- "'%s' ORDER BY OrderId" % self.species.capitalize())
+ "Species.Name = %s "
+ "ORDER BY OrderId", (self.species.capitalize(),))
else:
- query = (
+ cursor.execute(
"SELECT Chr_Length.Name, Chr_Length.OrderId, "
"Length FROM Chr_Length, InbredSet WHERE "
"Chr_Length.SpeciesId = InbredSet.SpeciesId AND "
"InbredSet.Name = "
- "'%s' ORDER BY OrderId" % self.dataset.group.name)
- results = g.db.execute(query).fetchall()
+ "%s ORDER BY OrderId", (self.dataset.group.name,))
+ results = cursor.fetchall()
for item in results:
chromosomes[item.OrderId] = IndChromosome(
item.Name, item.Length)
diff --git a/wqflask/base/trait.py b/wqflask/base/trait.py
index b02c6033..57cc8a9b 100644
--- a/wqflask/base/trait.py
+++ b/wqflask/base/trait.py
@@ -13,8 +13,9 @@ from utility.redis_tools import get_redis_conn, get_resource_id
from utility.db_tools import escape
from flask import g, request, url_for
-
from utility.logger import getLogger
+from wqflask.database import database_connection
+
logger = getLogger(__name__)
@@ -386,226 +387,220 @@ def retrieve_trait_info(trait, dataset, get_qtl_info=False):
else:
the_url = GN_PROXY_URL + "run-action?resource={}&user={}&branch=data&action=view&trait={}".format(
resource_id, g.user_session.user_id, trait.name)
+ with database_connection() as conn, conn.cursor() as cursor:
+ try:
+ response = requests.get(the_url).content
+ trait_info = json.loads(response)
+ except: # ZS: I'm assuming the trait is viewable if the try fails for some reason; it should never reach this point unless the user has privileges, since that's dealt with in create_trait
+ trait_info = ()
+ if dataset.type == 'Publish':
+ cursor.execute(
+ "SELECT PublishXRef.Id, InbredSet.InbredSetCode, "
+ "Publication.PubMed_ID, "
+ "CAST(Phenotype.Pre_publication_description AS BINARY), "
+ "CAST(Phenotype.Post_publication_description AS BINARY), "
+ "CAST(Phenotype.Original_description AS BINARY), "
+ "CAST(Phenotype.Pre_publication_abbreviation AS BINARY), "
+ "CAST(Phenotype.Post_publication_abbreviation AS BINARY), "
+ "PublishXRef.mean, Phenotype.Lab_code, "
+ "Phenotype.Submitter, Phenotype.Owner, "
+ "Phenotype.Authorized_Users, "
+ "CAST(Publication.Authors AS BINARY), "
+ "CAST(Publication.Title AS BINARY), "
+ "CAST(Publication.Abstract AS BINARY), "
+ "CAST(Publication.Journal AS BINARY), "
+ "Publication.Volume, Publication.Pages, "
+ "Publication.Month, Publication.Year, "
+ "PublishXRef.Sequence, Phenotype.Units, "
+ "PublishXRef.comments FROM PublishXRef, Publication, "
+ "Phenotype, PublishFreeze, InbredSet WHERE "
+ "PublishXRef.Id = %s AND "
+ "Phenotype.Id = PublishXRef.PhenotypeId "
+ "AND Publication.Id = PublishXRef.PublicationId "
+ "AND PublishXRef.InbredSetId = PublishFreeze.InbredSetId "
+ "AND PublishXRef.InbredSetId = InbredSet.Id AND "
+ "PublishFreeze.Id = %s",
+ (trait.name, dataset.id,)
+ )
+ trait_info = cursor.fetchone()
+
+ # XZ, 05/08/2009: Xiaodong add this block to use ProbeSet.Id to find the probeset instead of just using ProbeSet.Name
+ # XZ, 05/08/2009: to avoid the problem of same probeset name from different platforms.
+ elif dataset.type == 'ProbeSet':
+ display_fields_string = ', ProbeSet.'.join(dataset.display_fields)
+ display_fields_string = f'ProbeSet.{display_fields_string}'
+ cursor.execute(
+ "SELECT %s FROM ProbeSet, ProbeSetFreeze, "
+ "ProbeSetXRef WHERE "
+ "ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id "
+ "AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND "
+ "ProbeSetFreeze.Name = %s AND "
+ "ProbeSet.Name = %s",
+ (display_fields_string, dataset.name,
+ str(trait.name),)
+ )
+ trait_info = cursor.fetchone()
+ # XZ, 05/08/2009: We also should use Geno.Id to find marker instead of just using Geno.Name
+ # to avoid the problem of same marker name from different species.
+ elif dataset.type == 'Geno':
+ display_fields_string = ',Geno.'.join(dataset.display_fields)
+ display_fields_string = f'Geno.{display_fields_string}'
+ cursor.execute(
+ "SELECT %s FROM Geno, GenoFreeze, "
+ "GenoXRef WHERE "
+ "GenoXRef.GenoFreezeId = GenoFreeze.Id "
+ "AND GenoXRef.GenoId = Geno.Id "
+ "AND GenoFreeze.Name = %s "
+ "AND Geno.Name = %s",
+ (display_fields_string, dataset.name, trait.name)
+ )
+ trait_info = cursor.fetchone()
+ else: # Temp type
+ cursor.execute(
+ "SELECT %s FROM %s WHERE Name = %s",
+ (','.join(dataset.display_fields),
+ dataset.type, trait.name,)
+ )
+ trait_info = cursor.fetchone()
+
+ if trait_info:
+ trait.haveinfo = True
+ for i, field in enumerate(dataset.display_fields):
+ holder = trait_info[i]
+ if isinstance(holder, bytes):
+ holder = holder.decode("utf-8", errors="ignore")
+ setattr(trait, field, holder)
- try:
- response = requests.get(the_url).content
- trait_info = json.loads(response)
- except: # ZS: I'm assuming the trait is viewable if the try fails for some reason; it should never reach this point unless the user has privileges, since that's dealt with in create_trait
- if dataset.type == 'Publish':
- query = """
- SELECT
- PublishXRef.Id, InbredSet.InbredSetCode, Publication.PubMed_ID,
- CAST(Phenotype.Pre_publication_description AS BINARY),
- CAST(Phenotype.Post_publication_description AS BINARY),
- CAST(Phenotype.Original_description AS BINARY),
- CAST(Phenotype.Pre_publication_abbreviation AS BINARY),
- CAST(Phenotype.Post_publication_abbreviation AS BINARY), PublishXRef.mean,
- Phenotype.Lab_code, Phenotype.Submitter, Phenotype.Owner, Phenotype.Authorized_Users,
- CAST(Publication.Authors AS BINARY), CAST(Publication.Title AS BINARY), CAST(Publication.Abstract AS BINARY),
- CAST(Publication.Journal AS BINARY), Publication.Volume, Publication.Pages,
- Publication.Month, Publication.Year, PublishXRef.Sequence,
- Phenotype.Units, PublishXRef.comments
- FROM
- PublishXRef, Publication, Phenotype, PublishFreeze, InbredSet
- WHERE
- PublishXRef.Id = %s AND
- Phenotype.Id = PublishXRef.PhenotypeId AND
- Publication.Id = PublishXRef.PublicationId AND
- PublishXRef.InbredSetId = PublishFreeze.InbredSetId AND
- PublishXRef.InbredSetId = InbredSet.Id AND
- PublishFreeze.Id = %s
- """ % (trait.name, dataset.id)
-
- logger.sql(query)
- trait_info = g.db.execute(query).fetchone()
-
- # XZ, 05/08/2009: Xiaodong add this block to use ProbeSet.Id to find the probeset instead of just using ProbeSet.Name
- # XZ, 05/08/2009: to avoid the problem of same probeset name from different platforms.
- elif dataset.type == 'ProbeSet':
- display_fields_string = ', ProbeSet.'.join(dataset.display_fields)
- display_fields_string = 'ProbeSet.' + display_fields_string
- query = """
- SELECT %s
- FROM ProbeSet, ProbeSetFreeze, ProbeSetXRef
- WHERE
- ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND
- ProbeSetXRef.ProbeSetId = ProbeSet.Id AND
- ProbeSetFreeze.Name = '%s' AND
- ProbeSet.Name = '%s'
- """ % (escape(display_fields_string),
- escape(dataset.name),
- escape(str(trait.name)))
- logger.sql(query)
- trait_info = g.db.execute(query).fetchone()
- # XZ, 05/08/2009: We also should use Geno.Id to find marker instead of just using Geno.Name
- # to avoid the problem of same marker name from different species.
- elif dataset.type == 'Geno':
- display_fields_string = ',Geno.'.join(dataset.display_fields)
- display_fields_string = 'Geno.' + display_fields_string
- query = """
- SELECT %s
- FROM Geno, GenoFreeze, GenoXRef
- WHERE
- GenoXRef.GenoFreezeId = GenoFreeze.Id AND
- GenoXRef.GenoId = Geno.Id AND
- GenoFreeze.Name = '%s' AND
- Geno.Name = '%s'
- """ % (escape(display_fields_string),
- escape(dataset.name),
- escape(trait.name))
- logger.sql(query)
- trait_info = g.db.execute(query).fetchone()
- else: # Temp type
- query = """SELECT %s FROM %s WHERE Name = %s"""
- logger.sql(query)
- trait_info = g.db.execute(query,
- ','.join(dataset.display_fields),
- dataset.type, trait.name).fetchone()
-
- if trait_info:
- trait.haveinfo = True
- for i, field in enumerate(dataset.display_fields):
- holder = trait_info[i]
- if isinstance(holder, bytes):
- holder = holder.decode("utf-8", errors="ignore")
- setattr(trait, field, holder)
-
- if dataset.type == 'Publish':
- if trait.group_code:
- trait.display_name = trait.group_code + "_" + str(trait.name)
-
- trait.confidential = 0
- if trait.pre_publication_description and not trait.pubmed_id:
- trait.confidential = 1
-
- description = trait.post_publication_description
-
- # If the dataset is confidential and the user has access to confidential
- # phenotype traits, then display the pre-publication description instead
- # of the post-publication description
- trait.description_display = "N/A"
- if not trait.pubmed_id:
- trait.abbreviation = trait.pre_publication_abbreviation
- if trait.pre_publication_description:
- trait.description_display = trait.pre_publication_description
- else:
- trait.abbreviation = trait.post_publication_abbreviation
- if description:
- trait.description_display = description.strip()
+ if dataset.type == 'Publish':
+ if trait.group_code:
+ trait.display_name = trait.group_code + "_" + str(trait.name)
+
+ trait.confidential = 0
+ if trait.pre_publication_description and not trait.pubmed_id:
+ trait.confidential = 1
+
+ description = trait.post_publication_description
+
+ # If the dataset is confidential and the user has access to confidential
+ # phenotype traits, then display the pre-publication description instead
+ # of the post-publication description
+ trait.description_display = "N/A"
+ if not trait.pubmed_id:
+ trait.abbreviation = trait.pre_publication_abbreviation
+ if trait.pre_publication_description:
+ trait.description_display = trait.pre_publication_description
+ else:
+ trait.abbreviation = trait.post_publication_abbreviation
+ if description:
+ trait.description_display = description.strip()
- if not trait.year.isdigit():
- trait.pubmed_text = "N/A"
- else:
- trait.pubmed_text = trait.year
+ if not trait.year.isdigit():
+ trait.pubmed_text = "N/A"
+ else:
+ trait.pubmed_text = trait.year
- if trait.pubmed_id:
- trait.pubmed_link = webqtlConfig.PUBMEDLINK_URL % trait.pubmed_id
+ if trait.pubmed_id:
+ trait.pubmed_link = webqtlConfig.PUBMEDLINK_URL % trait.pubmed_id
- if dataset.type == 'ProbeSet' and dataset.group:
- description_string = trait.description
- target_string = trait.probe_target_description
+ if dataset.type == 'ProbeSet' and dataset.group:
+ description_string = trait.description
+ target_string = trait.probe_target_description
- if str(description_string or "") != "" and description_string != 'None':
- description_display = description_string
- else:
- description_display = trait.symbol
-
- if (str(description_display or "") != ""
- and description_display != 'N/A'
- and str(target_string or "") != "" and target_string != 'None'):
- description_display = description_display + '; ' + target_string.strip()
-
- # Save it for the jinja2 template
- trait.description_display = description_display
-
- trait.location_repr = 'N/A'
- if trait.chr and trait.mb:
- trait.location_repr = 'Chr%s: %.6f' % (
- trait.chr, float(trait.mb))
-
- elif dataset.type == "Geno":
- trait.location_repr = 'N/A'
- if trait.chr and trait.mb:
- trait.location_repr = 'Chr%s: %.6f' % (
- trait.chr, float(trait.mb))
-
- if get_qtl_info:
- # LRS and its location
- trait.LRS_score_repr = "N/A"
- trait.LRS_location_repr = "N/A"
- trait.locus = trait.locus_chr = trait.locus_mb = trait.lrs = trait.pvalue = trait.additive = ""
- if dataset.type == 'ProbeSet' and not trait.cellid:
- trait.mean = ""
- query = """
- SELECT
- ProbeSetXRef.Locus, ProbeSetXRef.LRS, ProbeSetXRef.pValue, ProbeSetXRef.mean, ProbeSetXRef.additive
- FROM
- ProbeSetXRef, ProbeSet
- WHERE
- ProbeSetXRef.ProbeSetId = ProbeSet.Id AND
- ProbeSet.Name = "{}" AND
- ProbeSetXRef.ProbeSetFreezeId ={}
- """.format(trait.name, dataset.id)
- logger.sql(query)
- trait_qtl = g.db.execute(query).fetchone()
- if trait_qtl:
- trait.locus, trait.lrs, trait.pvalue, trait.mean, trait.additive = trait_qtl
- if trait.locus:
- query = """
- select Geno.Chr, Geno.Mb from Geno, Species
- where Species.Name = '{}' and
- Geno.Name = '{}' and
- Geno.SpeciesId = Species.Id
- """.format(dataset.group.species, trait.locus)
- logger.sql(query)
- result = g.db.execute(query).fetchone()
- if result:
- trait.locus_chr = result[0]
- trait.locus_mb = result[1]
+ if str(description_string or "") != "" and description_string != 'None':
+ description_display = description_string
+ else:
+ description_display = trait.symbol
+
+ if (str(description_display or "") != ""
+ and description_display != 'N/A'
+ and str(target_string or "") != "" and target_string != 'None'):
+ description_display = description_display + '; ' + target_string.strip()
+
+ # Save it for the jinja2 template
+ trait.description_display = description_display
+
+ trait.location_repr = 'N/A'
+ if trait.chr and trait.mb:
+ trait.location_repr = 'Chr%s: %.6f' % (
+ trait.chr, float(trait.mb))
+
+ elif dataset.type == "Geno":
+ trait.location_repr = 'N/A'
+ if trait.chr and trait.mb:
+ trait.location_repr = 'Chr%s: %.6f' % (
+ trait.chr, float(trait.mb))
+
+ if get_qtl_info:
+ # LRS and its location
+ trait.LRS_score_repr = "N/A"
+ trait.LRS_location_repr = "N/A"
+ trait.locus = trait.locus_chr = trait.locus_mb = trait.lrs = trait.pvalue = trait.additive = ""
+ if dataset.type == 'ProbeSet' and not trait.cellid:
+ trait.mean = ""
+ cursor.execute(
+ "SELECT ProbeSetXRef.Locus, ProbeSetXRef.LRS, "
+ "ProbeSetXRef.pValue, ProbeSetXRef.mean, "
+ "ProbeSetXRef.additive FROM ProbeSetXRef, "
+ "ProbeSet WHERE "
+ "ProbeSetXRef.ProbeSetId = ProbeSet.Id "
+ "AND ProbeSet.Name = %s AND "
+ "ProbeSetXRef.ProbeSetFreezeId = %s",
+ (trait.name, dataset.id,)
+ )
+ trait_qtl = cursor.fetchone()
+ if trait_qtl:
+ trait.locus, trait.lrs, trait.pvalue, trait.mean, trait.additive = trait_qtl
+ if trait.locus:
+ cursor.execute(
+ "SELECT Geno.Chr, Geno.Mb FROM "
+ "Geno, Species WHERE "
+ "Species.Name = %s AND "
+ "Geno.Name = %s AND "
+ "Geno.SpeciesId = Species.Id",
+ (dataset.group.species, trait.locus,)
+ )
+ if result := cursor.fetchone() :
+ trait.locus_chr = result[0]
+ trait.locus_mb = result[1]
+ else:
+ trait.locus = trait.locus_chr = trait.locus_mb = trait.additive = ""
else:
trait.locus = trait.locus_chr = trait.locus_mb = trait.additive = ""
- else:
- trait.locus = trait.locus_chr = trait.locus_mb = trait.additive = ""
- if dataset.type == 'Publish':
- query = """
- SELECT
- PublishXRef.Locus, PublishXRef.LRS, PublishXRef.additive
- FROM
- PublishXRef, PublishFreeze
- WHERE
- PublishXRef.Id = %s AND
- PublishXRef.InbredSetId = PublishFreeze.InbredSetId AND
- PublishFreeze.Id =%s
- """ % (trait.name, dataset.id)
- logger.sql(query)
- trait_qtl = g.db.execute(query).fetchone()
- if trait_qtl:
- trait.locus, trait.lrs, trait.additive = trait_qtl
- if trait.locus:
- query = """
- select Geno.Chr, Geno.Mb from Geno, Species
- where Species.Name = '{}' and
- Geno.Name = '{}' and
- Geno.SpeciesId = Species.Id
- """.format(dataset.group.species, trait.locus)
- logger.sql(query)
- result = g.db.execute(query).fetchone()
- if result:
- trait.locus_chr = result[0]
- trait.locus_mb = result[1]
+ if dataset.type == 'Publish':
+ cursor.execute(
+ "SELECT PublishXRef.Locus, PublishXRef.LRS, "
+ "PublishXRef.additive FROM "
+ "PublishXRef, PublishFreeze WHERE "
+ "PublishXRef.Id = %s AND "
+ "PublishXRef.InbredSetId = PublishFreeze.InbredSetId "
+ "AND PublishFreeze.Id = %s", (trait.name, dataset.id,)
+ )
+ if trait_qtl := cursor.fetchone():
+ trait.locus, trait.lrs, trait.additive = trait_qtl
+ if trait.locus:
+ cursor.execute(
+ "SELECT Geno.Chr, Geno.Mb FROM Geno, "
+ "Species WHERE Species.Name = %s "
+ "AND Geno.Name = %s AND "
+ "Geno.SpeciesId = Species.Id",
+ (dataset.group.species, trait.locus,)
+ )
+ if result := cursor.fetchone():
+ trait.locus_chr = result[0]
+ trait.locus_mb = result[1]
+ else:
+ trait.locus = trait.locus_chr = trait.locus_mb = trait.additive = ""
else:
trait.locus = trait.locus_chr = trait.locus_mb = trait.additive = ""
else:
- trait.locus = trait.locus_chr = trait.locus_mb = trait.additive = ""
- else:
- trait.locus = trait.lrs = trait.additive = ""
- if (dataset.type == 'Publish' or dataset.type == "ProbeSet") and str(trait.locus_chr or "") != "" and str(trait.locus_mb or "") != "":
- trait.LRS_location_repr = LRS_location_repr = 'Chr%s: %.6f' % (
- trait.locus_chr, float(trait.locus_mb))
- if str(trait.lrs or "") != "":
- trait.LRS_score_repr = LRS_score_repr = '%3.1f' % trait.lrs
- else:
- raise KeyError(repr(trait.name)
- + ' information is not found in the database.')
- return trait
+ trait.locus = trait.lrs = trait.additive = ""
+ if (dataset.type == 'Publish' or dataset.type == "ProbeSet") and str(trait.locus_chr or "") != "" and str(trait.locus_mb or "") != "":
+ trait.LRS_location_repr = LRS_location_repr = 'Chr%s: %.6f' % (
+ trait.locus_chr, float(trait.locus_mb))
+ if str(trait.lrs or "") != "":
+ trait.LRS_score_repr = LRS_score_repr = '%3.1f' % trait.lrs
+ else:
+ raise KeyError(repr(trait.name)
+ + ' information is not found in the database.')
+ return trait