aboutsummaryrefslogtreecommitdiff
path: root/wqflask
diff options
context:
space:
mode:
Diffstat (limited to 'wqflask')
-rw-r--r--wqflask/base/data_set.py711
-rw-r--r--wqflask/wqflask/api/router.py902
2 files changed, 810 insertions, 803 deletions
diff --git a/wqflask/base/data_set.py b/wqflask/base/data_set.py
index 2f4c1154..ade38131 100644
--- a/wqflask/base/data_set.py
+++ b/wqflask/base/data_set.py
@@ -141,18 +141,18 @@ class DatasetType:
sql_query_mapping = {
'mrna_expr': ("SELECT ProbeSetFreeze.Id FROM "
"ProbeSetFreeze WHERE "
- "ProbeSetFreeze.Name = \"%s\" "),
+ "ProbeSetFreeze.Name = %s "),
'pheno': ("SELECT InfoFiles.GN_AccesionId "
"FROM InfoFiles, PublishFreeze, InbredSet "
- "WHERE InbredSet.Name = '%s' AND "
+ "WHERE InbredSet.Name = %s AND "
"PublishFreeze.InbredSetId = InbredSet.Id AND "
"InfoFiles.InfoPageName = PublishFreeze.Name"),
'other_pheno': ("SELECT PublishFreeze.Name "
"FROM PublishFreeze, InbredSet "
- "WHERE InbredSet.Name = '%s' AND "
+ "WHERE InbredSet.Name = %s AND "
"PublishFreeze.InbredSetId = InbredSet.Id"),
'geno': ("SELECT GenoFreeze.Id FROM GenoFreeze WHERE "
- "GenoFreeze.Name = \"%s\" ")
+ "GenoFreeze.Name = %s ")
}
dataset_name_mapping = {
@@ -166,13 +166,14 @@ class DatasetType:
if t in ['pheno', 'other_pheno']:
group_name = name.replace("Publish", "")
- results = g.db.execute(sql_query_mapping[t] % group_name).fetchone()
- if results:
- self.datasets[name] = dataset_name_mapping[t]
- self.redis_instance.set(
- "dataset_structure", json.dumps(self.datasets))
- return True
- return None
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(sql_query_mapping[t], (group_name,))
+ if cursor.fetchone():
+ self.datasets[name] = dataset_name_mapping[t]
+ self.redis_instance.set(
+ "dataset_structure", json.dumps(self.datasets))
+ return True
+
def __call__(self, name):
if name not in self.datasets:
@@ -348,9 +349,13 @@ class DatasetGroup:
self.genofile = None
def get_mapping_methods(self):
-
- mapping_id = g.db.execute(
- "select MappingMethodId from InbredSet where Name= '%s'" % self.name).fetchone()[0]
+ mapping_id = ()
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(
+ "SELECT MappingMethodId FROM "
+ "InbredSet WHERE Name= %s"
+ (self.name,))
+ mapping_id = cursor.fetchone()[0]
if mapping_id == "1":
mapping_names = ["GEMMA", "QTLReaper", "R/qtl"]
elif mapping_id == "2":
@@ -589,29 +594,38 @@ class DataSet:
}
def get_accession_id(self):
- if self.type == "Publish":
- results = g.db.execute("""select InfoFiles.GN_AccesionId from InfoFiles, PublishFreeze, InbredSet where
- InbredSet.Name = %s and
- PublishFreeze.InbredSetId = InbredSet.Id and
- InfoFiles.InfoPageName = PublishFreeze.Name and
- PublishFreeze.public > 0 and
- PublishFreeze.confidentiality < 1 order by
- PublishFreeze.CreateTime desc""", (self.group.name)).fetchone()
- elif self.type == "Geno":
- results = g.db.execute("""select InfoFiles.GN_AccesionId from InfoFiles, GenoFreeze, InbredSet where
- InbredSet.Name = %s and
- GenoFreeze.InbredSetId = InbredSet.Id and
- InfoFiles.InfoPageName = GenoFreeze.ShortName and
- GenoFreeze.public > 0 and
- GenoFreeze.confidentiality < 1 order by
- GenoFreeze.CreateTime desc""", (self.group.name)).fetchone()
- else:
- results = None
+ results = None
+ with database_connection() as conn, conn.cursor() as cursor:
+ if self.type == "Publish":
+ cursor.execute(
+ "SELECT InfoFiles.GN_AccesionId FROM "
+ "InfoFiles, PublishFreeze, InbredSet "
+ "WHERE InbredSet.Name = %s AND "
+ "PublishFreeze.InbredSetId = InbredSet.Id "
+ "AND InfoFiles.InfoPageName = PublishFreeze.Name "
+ "AND PublishFreeze.public > 0 AND "
+ "PublishFreeze.confidentiality < 1 "
+ "ORDER BY PublishFreeze.CreateTime DESC",
+ (self.group.name,)
+ )
+ results = cursor.fetchone()
+ elif self.type == "Geno":
+ cursor.execute(
+ "SELECT InfoFiles.GN_AccesionId FROM "
+ "InfoFiles, GenoFreeze, InbredSet "
+ "WHERE InbredSet.Name = %s AND "
+ "GenoFreeze.InbredSetId = InbredSet.Id "
+ "AND InfoFiles.InfoPageName = GenoFreeze.ShortName "
+ "AND GenoFreeze.public > 0 AND "
+ "GenoFreeze.confidentiality < 1 "
+ "ORDER BY GenoFreeze.CreateTime DESC",
+ (self.group.name,)
+ )
+ results = cursor.fetchone()
- if results != None:
+ if results:
return str(results[0])
- else:
- return "None"
+ return "None"
def retrieve_other_names(self):
"""This method fetches the the dataset names in search_result.
@@ -624,52 +638,49 @@ class DataSet:
all is passed.
"""
-
- try:
- query = ""
- _vars = None
- query_args = (self.name, self.name, self.name)
- if self.type == "ProbeSet":
- query = (
- "SELECT ProbeSetFreeze.Id, ProbeSetFreeze.Name, "
- "ProbeSetFreeze.FullName, ProbeSetFreeze.ShortName, "
- "ProbeSetFreeze.DataScale, Tissue.Name "
- "FROM ProbeSetFreeze, ProbeFreeze, Tissue "
- "WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id "
- "AND ProbeFreeze.TissueId = Tissue.Id "
- "AND (ProbeSetFreeze.Name = %s OR ProbeSetFreeze.FullName = %s OR ProbeSetFreeze.ShortName = %s)")
- else:
- query = (
- "SELECT Id, Name, FullName, ShortName "
- f"FROM {self.type}Freeze "
- "WHERE (Name = %s OR FullName = %s OR ShortName = %s)")
- self.tissue = "N/A"
- with database_connection() as conn, conn.cursor() as cursor:
- cursor.execute(query, query_args)
- _vars = cursor.fetchone()
+ with database_connection() as conn, conn.cursor() as cursor:
+ try:
if self.type == "ProbeSet":
+ cursor.execute(
+ "SELECT ProbeSetFreeze.Id, ProbeSetFreeze.Name, "
+ "ProbeSetFreeze.FullName, ProbeSetFreeze.ShortName, "
+ "ProbeSetFreeze.DataScale, Tissue.Name "
+ "FROM ProbeSetFreeze, ProbeFreeze, Tissue "
+ "WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id "
+ "AND ProbeFreeze.TissueId = Tissue.Id "
+ "AND (ProbeSetFreeze.Name = %s OR "
+ "ProbeSetFreeze.FullName = %s "
+ "OR ProbeSetFreeze.ShortName = %s)",
+ (self.name,)*3)
(self.id, self.name, self.fullname, self.shortname,
- self.data_scale, self.tissue) = _vars
+ self.data_scale, self.tissue) = cursor.fetchone()
else:
- self.id, self.name, self.fullname, self.shortname = _vars
- except TypeError:
- pass
+ cursor.execute(
+ "SELECT Id, Name, FullName, ShortName "
+ f"FROM {self.type}Freeze "
+ "WHERE (Name = %s OR FullName = "
+ "%s OR ShortName = %s)",
+ (self.name,)*3)
+ (self.id, self.name, self.fullname, self.shortname,
+ self.data_scale, self.tissue) = cursor.fetchone()
+ self.tissue = "N/A"
+ except TypeError:
+ pass
def chunk_dataset(self, dataset, n):
results = {}
-
- query = """
- SELECT ProbeSetXRef.DataId,ProbeSet.Name
- FROM ProbeSet, ProbeSetXRef, ProbeSetFreeze
- WHERE ProbeSetFreeze.Name = '{}' AND
- ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND
- ProbeSetXRef.ProbeSetId = ProbeSet.Id
- """.format(self.name)
-
- # should cache this
-
- traits_name_dict = dict(g.db.execute(query).fetchall())
+ traits_name_dict = ()
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(
+ "SELECT ProbeSetXRef.DataId,ProbeSet.Name "
+ "FROM ProbeSet, ProbeSetXRef, ProbeSetFreeze "
+ "WHERE ProbeSetFreeze.Name = %s AND "
+ "ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id "
+ "AND ProbeSetXRef.ProbeSetId = ProbeSet.Id",
+ (self.name,))
+ # should cache this
+ traits_name_dict = dict(cursor.fetchall())
for i in range(0, len(dataset), n):
matrix = list(dataset[i:i + n])
@@ -691,31 +702,35 @@ class DataSet:
if self.group.parlist != None and self.group.f1list != None:
if (self.group.parlist + self.group.f1list) in self.samplelist:
self.samplelist += self.group.parlist + self.group.f1list
-
- query = """
- SELECT Strain.Name, Strain.Id FROM Strain, Species
- WHERE Strain.Name IN {}
- and Strain.SpeciesId=Species.Id
- and Species.name = '{}'
- """.format(create_in_clause(self.samplelist), *mescape(self.group.species))
- results = dict(g.db.execute(query).fetchall())
- sample_ids = [results[item] for item in self.samplelist]
-
- sorted_samplelist = [strain_name for strain_name, strain_id in sorted(
- results.items(), key=lambda item: item[1])]
-
- query = """SELECT * from ProbeSetData
- where StrainID in {}
- and id in (SELECT ProbeSetXRef.DataId
- FROM (ProbeSet, ProbeSetXRef, ProbeSetFreeze)
- WHERE ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id
- and ProbeSetFreeze.Name = '{}'
- and ProbeSet.Id = ProbeSetXRef.ProbeSetId)""".format(create_in_clause(sample_ids), self.name)
-
- query_results = list(g.db.execute(query).fetchall())
- data_results = self.chunk_dataset(query_results, len(sample_ids))
- self.samplelist = sorted_samplelist
- self.trait_data = data_results
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(
+ "SELECT Strain.Name, Strain.Id FROM "
+ "Strain, Species WHERE Strain.Name IN "
+ f"{create_in_clause(self.samplelist)} "
+ "AND Strain.SpeciesId=Species.Id AND "
+ "Species.name = %s", (self.group.species,)
+ )
+ results = dict(cursor.fetchall())
+ sample_ids = [results[item] for item in self.samplelist]
+
+ sorted_samplelist = [strain_name for strain_name, strain_id in sorted(
+ results.items(), key=lambda item: item[1])]
+
+ cursor.execute(
+ "SELECT * from ProbeSetData WHERE StrainID IN "
+ f"{create_in_clause(sample_ids)} AND id IN "
+ "(SELECT ProbeSetXRef.DataId FROM "
+ "(ProbeSet, ProbeSetXRef, ProbeSetFreeze) "
+ "WHERE ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id "
+ "AND ProbeSetFreeze.Name = %s AND "
+ "ProbeSet.Id = ProbeSetXRef.ProbeSetId)",
+ (self.name,)
+ )
+
+ query_results = list(cursor.fetchall())
+ data_results = self.chunk_dataset(query_results, len(sample_ids))
+ self.samplelist = sorted_samplelist
+ self.trait_data = data_results
def get_trait_data(self, sample_list=None):
if sample_list:
@@ -727,81 +742,84 @@ class DataSet:
if (self.group.parlist + self.group.f1list) in self.samplelist:
self.samplelist += self.group.parlist + self.group.f1list
- query = """
- SELECT Strain.Name, Strain.Id FROM Strain, Species
- WHERE Strain.Name IN {}
- and Strain.SpeciesId=Species.Id
- and Species.name = '{}'
- """.format(create_in_clause(self.samplelist), *mescape(self.group.species))
- results = dict(g.db.execute(query).fetchall())
- sample_ids = [results.get(item)
- for item in self.samplelist if item is not None]
-
- # MySQL limits the number of tables that can be used in a join to 61,
- # so we break the sample ids into smaller chunks
- # Postgres doesn't have that limit, so we can get rid of this after we transition
- chunk_size = 50
- number_chunks = int(math.ceil(len(sample_ids) / chunk_size))
-
- cached_results = fetch_cached_results(self.name, self.type, self.samplelist)
- if cached_results is None:
- trait_sample_data = []
- for sample_ids_step in chunks.divide_into_chunks(sample_ids, number_chunks):
- if self.type == "Publish":
- dataset_type = "Phenotype"
- else:
- dataset_type = self.type
- temp = ['T%s.value' % item for item in sample_ids_step]
- if self.type == "Publish":
- query = "SELECT {}XRef.Id,".format(escape(self.type))
- else:
- query = "SELECT {}.Name,".format(escape(dataset_type))
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(
+ "SELECT Strain.Name, Strain.Id FROM Strain, Species "
+ f"WHERE Strain.Name IN {create_in_clause(self.samplelist)} "
+ "AND Strain.SpeciesId=Species.Id "
+ "AND Species.name = %s",
+ (self.group.species,)
+ )
+ results = dict(cursor.fetchall())
+ sample_ids = [results.get(item)
+ for item in self.samplelist if item is not None]
+
+ # MySQL limits the number of tables that can be used in a join to 61,
+ # so we break the sample ids into smaller chunks
+ # Postgres doesn't have that limit, so we can get rid of this after we transition
+ chunk_size = 50
+ number_chunks = int(math.ceil(len(sample_ids) / chunk_size))
+
+ cached_results = fetch_cached_results(self.name, self.type, self.samplelist)
+
+ if cached_results is None:
+ trait_sample_data = []
+ for sample_ids_step in chunks.divide_into_chunks(sample_ids, number_chunks):
+ if self.type == "Publish":
+ dataset_type = "Phenotype"
+ else:
+ dataset_type = self.type
+ temp = ['T%s.value' % item for item in sample_ids_step]
+ if self.type == "Publish":
+ query = "SELECT {}XRef.Id,".format(escape(self.type))
+ else:
+ query = "SELECT {}.Name,".format(escape(dataset_type))
+ data_start_pos = 1
+ query += ', '.join(temp)
+ query += ' FROM ({}, {}XRef, {}Freeze) '.format(*mescape(dataset_type,
+ self.type,
+ self.type))
+
+ for item in sample_ids_step:
+ query += """
+ left join {}Data as T{} on T{}.Id = {}XRef.DataId
+ and T{}.StrainId={}\n
+ """.format(*mescape(self.type, item, item, self.type, item, item))
+
+ if self.type == "Publish":
+ query += """
+ WHERE {}XRef.InbredSetId = {}Freeze.InbredSetId
+ and {}Freeze.Name = '{}'
+ and {}.Id = {}XRef.{}Id
+ order by {}.Id
+ """.format(*mescape(self.type, self.type, self.type, self.name,
+ dataset_type, self.type, dataset_type, dataset_type))
+ else:
+ query += """
+ WHERE {}XRef.{}FreezeId = {}Freeze.Id
+ and {}Freeze.Name = '{}'
+ and {}.Id = {}XRef.{}Id
+ order by {}.Id
+ """.format(*mescape(self.type, self.type, self.type, self.type,
+ self.name, dataset_type, self.type, self.type, dataset_type))
+ cursor.execute(query)
+ results = cursor.fetchall()
+ trait_sample_data.append([list(result) for result in results])
+
+ trait_count = len(trait_sample_data[0])
+ self.trait_data = collections.defaultdict(list)
+
data_start_pos = 1
- query += ', '.join(temp)
- query += ' FROM ({}, {}XRef, {}Freeze) '.format(*mescape(dataset_type,
- self.type,
- self.type))
-
- for item in sample_ids_step:
- query += """
- left join {}Data as T{} on T{}.Id = {}XRef.DataId
- and T{}.StrainId={}\n
- """.format(*mescape(self.type, item, item, self.type, item, item))
-
- if self.type == "Publish":
- query += """
- WHERE {}XRef.InbredSetId = {}Freeze.InbredSetId
- and {}Freeze.Name = '{}'
- and {}.Id = {}XRef.{}Id
- order by {}.Id
- """.format(*mescape(self.type, self.type, self.type, self.name,
- dataset_type, self.type, dataset_type, dataset_type))
- else:
- query += """
- WHERE {}XRef.{}FreezeId = {}Freeze.Id
- and {}Freeze.Name = '{}'
- and {}.Id = {}XRef.{}Id
- order by {}.Id
- """.format(*mescape(self.type, self.type, self.type, self.type,
- self.name, dataset_type, self.type, self.type, dataset_type))
-
- results = g.db.execute(query).fetchall()
- trait_sample_data.append([list(result) for result in results])
-
- trait_count = len(trait_sample_data[0])
- self.trait_data = collections.defaultdict(list)
-
- data_start_pos = 1
- for trait_counter in range(trait_count):
- trait_name = trait_sample_data[0][trait_counter][0]
- for chunk_counter in range(int(number_chunks)):
- self.trait_data[trait_name] += (
- trait_sample_data[chunk_counter][trait_counter][data_start_pos:])
-
- cache_dataset_results(
- self.name, self.type, self.samplelist, self.trait_data)
- else:
- self.trait_data = cached_results
+ for trait_counter in range(trait_count):
+ trait_name = trait_sample_data[0][trait_counter][0]
+ for chunk_counter in range(int(number_chunks)):
+ self.trait_data[trait_name] += (
+ trait_sample_data[chunk_counter][trait_counter][data_start_pos:])
+
+ cache_dataset_results(
+ self.name, self.type, self.samplelist, self.trait_data)
+ else:
+ self.trait_data = cached_results
class PhenotypeDataSet(DataSet):
@@ -897,43 +915,41 @@ SELECT InbredSet.Name, InbredSet.Id, InbredSet.GeneticType, InbredSet.InbredSetC
this_trait.LRS_location_repr = "N/A"
if this_trait.lrs:
- query = """
- select Geno.Chr, Geno.Mb from Geno, Species
- where Species.Name = '%s' and
- Geno.Name = '%s' and
- Geno.SpeciesId = Species.Id
- """ % (species, this_trait.locus)
- result = g.db.execute(query).fetchone()
-
- if result:
- if result[0] and result[1]:
- LRS_Chr = result[0]
- LRS_Mb = result[1]
-
- this_trait.LRS_score_repr = LRS_score_repr = '%3.1f' % this_trait.lrs
- this_trait.LRS_location_repr = LRS_location_repr = 'Chr%s: %.6f' % (
- LRS_Chr, float(LRS_Mb))
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(
+ "SELECT Geno.Chr, Geno.Mb FROM "
+ "Geno, Species WHERE "
+ "Species.Name = %s AND "
+ "Geno.Name = %s AND "
+ "Geno.SpeciesId = Species.Id",
+ (species, this_trait.locus,)
+ )
+ if result := cursor.fetchone():
+ if result[0] and result[1]:
+ LRS_Chr, LRS_Mb = result[0], result[1]
+ this_trait.LRS_score_repr = LRS_score_repr = '%3.1f' % this_trait.lrs
+ this_trait.LRS_location_repr = LRS_location_repr = 'Chr%s: %.6f' % (
+ LRS_Chr, float(LRS_Mb))
def retrieve_sample_data(self, trait):
- query = """
- SELECT
- Strain.Name, PublishData.value, PublishSE.error, NStrain.count, Strain.Name2
- FROM
- (PublishData, Strain, PublishXRef, PublishFreeze)
- left join PublishSE on
- (PublishSE.DataId = PublishData.Id AND PublishSE.StrainId = PublishData.StrainId)
- left join NStrain on
- (NStrain.DataId = PublishData.Id AND
- NStrain.StrainId = PublishData.StrainId)
- WHERE
- PublishXRef.InbredSetId = PublishFreeze.InbredSetId AND
- PublishData.Id = PublishXRef.DataId AND PublishXRef.Id = %s AND
- PublishFreeze.Id = %s AND PublishData.StrainId = Strain.Id
- Order BY
- Strain.Name
- """
- results = g.db.execute(query, (trait, self.id)).fetchall()
- return results
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(
+ "SELECT Strain.Name, PublishData.value, "
+ "PublishSE.error, NStrain.count, "
+ "Strain.Name2 FROM (PublishData, Strain, "
+ "PublishXRef, PublishFreeze) LEFT JOIN "
+ "PublishSE ON "
+ "(PublishSE.DataId = PublishData.Id "
+ "AND PublishSE.StrainId = PublishData.StrainId) "
+ "LEFT JOIN NStrain ON "
+ "(NStrain.DataId = PublishData.Id AND "
+ "NStrain.StrainId = PublishData.StrainId) "
+ "WHERE PublishXRef.InbredSetId = PublishFreeze.InbredSetId "
+ "AND PublishData.Id = PublishXRef.DataId AND "
+ "PublishXRef.Id = %s AND PublishFreeze.Id = %s "
+ "AND PublishData.StrainId = Strain.Id "
+ "ORDER BY Strain.Name", (trait, self.id))
+ return cursor.fetchall()
class GenotypeDataSet(DataSet):
@@ -976,25 +992,25 @@ GenoFreeze.Name = %s"""
this_trait.chr, float(this_trait.mb))
def retrieve_sample_data(self, trait):
- query = """
- SELECT
- Strain.Name, GenoData.value, GenoSE.error, "N/A", Strain.Name2
- FROM
- (GenoData, GenoFreeze, Strain, Geno, GenoXRef)
- left join GenoSE on
- (GenoSE.DataId = GenoData.Id AND GenoSE.StrainId = GenoData.StrainId)
- WHERE
- Geno.SpeciesId = %s AND Geno.Name = %s AND GenoXRef.GenoId = Geno.Id AND
- GenoXRef.GenoFreezeId = GenoFreeze.Id AND
- GenoFreeze.Name = %s AND
- GenoXRef.DataId = GenoData.Id AND
- GenoData.StrainId = Strain.Id
- Order BY
- Strain.Name
- """
- results = g.db.execute(query,
- (webqtlDatabaseFunction.retrieve_species_id(self.group.name),
- trait, self.name)).fetchall()
+ results = []
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(
+ "SELECT Strain.Name, GenoData.value, "
+ "GenoSE.error, 'N/A', Strain.Name2 "
+ "FROM (GenoData, GenoFreeze, Strain, Geno, "
+ "GenoXRef) LEFT JOIN GenoSE ON "
+ "(GenoSE.DataId = GenoData.Id AND "
+ "GenoSE.StrainId = GenoData.StrainId) "
+ "WHERE Geno.SpeciesId = %s AND "
+ "Geno.Name = %s AND GenoXRef.GenoId = Geno.Id "
+ "AND GenoXRef.GenoFreezeId = GenoFreeze.Id "
+ "AND GenoFreeze.Name = %s AND "
+ "GenoXRef.DataId = GenoData.Id "
+ "AND GenoData.StrainId = Strain.Id "
+ "ORDER BY Strain.Name",
+ (webqtlDatabaseFunction.retrieve_species_id(self.group.name),
+ trait, self.name,))
+ results = list(cursor.fetchall())
if self.group.name in webqtlUtil.ParInfo:
f1_1, f1_2, ref, nonref = webqtlUtil.ParInfo[self.group.name]
@@ -1073,114 +1089,113 @@ ProbeFreeze.Id = ProbeSetFreeze.ProbeFreezeId AND ProbeSetFreeze.Name = %s"""
# Note: setting trait_list to [] is probably not a great idea.
if not trait_list:
trait_list = []
+ with database_connection() as conn, conn.cursor() as cursor:
+ for this_trait in trait_list:
- for this_trait in trait_list:
-
- if not this_trait.haveinfo:
- this_trait.retrieveInfo(QTL=1)
-
- if not this_trait.symbol:
- this_trait.symbol = "N/A"
-
- # XZ, 12/08/2008: description
- # XZ, 06/05/2009: Rob asked to add probe target description
- description_string = str(
- str(this_trait.description).strip(codecs.BOM_UTF8), 'utf-8')
- target_string = str(
- str(this_trait.probe_target_description).strip(codecs.BOM_UTF8), 'utf-8')
-
- if len(description_string) > 1 and description_string != 'None':
- description_display = description_string
- else:
- description_display = this_trait.symbol
-
- if (len(description_display) > 1 and description_display != 'N/A'
- and len(target_string) > 1 and target_string != 'None'):
- description_display = description_display + '; ' + target_string.strip()
-
- # Save it for the jinja2 template
- this_trait.description_display = description_display
-
- if this_trait.chr and this_trait.mb:
- this_trait.location_repr = 'Chr%s: %.6f' % (
- this_trait.chr, float(this_trait.mb))
-
- # Get mean expression value
- query = (
- """select ProbeSetXRef.mean from ProbeSetXRef, ProbeSet
- where ProbeSetXRef.ProbeSetFreezeId = %s and
- ProbeSet.Id = ProbeSetXRef.ProbeSetId and
- ProbeSet.Name = '%s'
- """ % (escape(str(this_trait.dataset.id)),
- escape(this_trait.name)))
- result = g.db.execute(query).fetchone()
+ if not this_trait.haveinfo:
+ this_trait.retrieveInfo(QTL=1)
- mean = result[0] if result else 0
+ if not this_trait.symbol:
+ this_trait.symbol = "N/A"
- if mean:
- this_trait.mean = "%2.3f" % mean
+ # XZ, 12/08/2008: description
+ # XZ, 06/05/2009: Rob asked to add probe target description
+ description_string = str(
+ str(this_trait.description).strip(codecs.BOM_UTF8), 'utf-8')
+ target_string = str(
+ str(this_trait.probe_target_description).strip(codecs.BOM_UTF8), 'utf-8')
- # LRS and its location
- this_trait.LRS_score_repr = 'N/A'
- this_trait.LRS_location_repr = 'N/A'
-
- # Max LRS and its Locus location
- if this_trait.lrs and this_trait.locus:
- query = """
- select Geno.Chr, Geno.Mb from Geno, Species
- where Species.Name = '{}' and
- Geno.Name = '{}' and
- Geno.SpeciesId = Species.Id
- """.format(species, this_trait.locus)
- result = g.db.execute(query).fetchone()
-
- if result:
- lrs_chr, lrs_mb = result
- this_trait.LRS_score_repr = '%3.1f' % this_trait.lrs
- this_trait.LRS_location_repr = 'Chr%s: %.6f' % (
- lrs_chr, float(lrs_mb))
+ if len(description_string) > 1 and description_string != 'None':
+ description_display = description_string
+ else:
+ description_display = this_trait.symbol
+
+ if (len(description_display) > 1 and description_display != 'N/A'
+ and len(target_string) > 1 and target_string != 'None'):
+ description_display = description_display + '; ' + target_string.strip()
+
+ # Save it for the jinja2 template
+ this_trait.description_display = description_display
+
+ if this_trait.chr and this_trait.mb:
+ this_trait.location_repr = 'Chr%s: %.6f' % (
+ this_trait.chr, float(this_trait.mb))
+
+ # Get mean expression value
+ cursor.execute(
+ "SELECT ProbeSetXRef.mean FROM "
+ "ProbeSetXRef, ProbeSet WHERE "
+ "ProbeSetXRef.ProbeSetFreezeId = %s "
+ "AND ProbeSet.Id = ProbeSetXRef.ProbeSetId "
+ "AND ProbeSet.Name = %s",
+ (str(this_trait.dataset.id), this_trait.name,)
+ )
+ result = cursor.fetchone()
+
+ mean = result[0] if result else 0
+
+ if mean:
+ this_trait.mean = "%2.3f" % mean
+
+ # LRS and its location
+ this_trait.LRS_score_repr = 'N/A'
+ this_trait.LRS_location_repr = 'N/A'
+
+ # Max LRS and its Locus location
+ if this_trait.lrs and this_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",
+ (species, this_trait.locus,)
+ )
+ if result := cursor.fetchone():
+ lrs_chr, lrs_mb = result
+ this_trait.LRS_score_repr = '%3.1f' % this_trait.lrs
+ this_trait.LRS_location_repr = 'Chr%s: %.6f' % (
+ lrs_chr, float(lrs_mb))
return trait_list
def retrieve_sample_data(self, trait):
- query = """
- SELECT
- Strain.Name, ProbeSetData.value, ProbeSetSE.error, NStrain.count, Strain.Name2
- FROM
- (ProbeSetData, ProbeSetFreeze,
- Strain, ProbeSet, ProbeSetXRef)
- left join ProbeSetSE on
- (ProbeSetSE.DataId = ProbeSetData.Id AND ProbeSetSE.StrainId = ProbeSetData.StrainId)
- left join NStrain on
- (NStrain.DataId = ProbeSetData.Id AND
- NStrain.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()
- return results
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(
+ "SELECT Strain.Name, ProbeSetData.value, "
+ "ProbeSetSE.error, NStrain.count, "
+ "Strain.Name2 FROM (ProbeSetData, "
+ "ProbeSetFreeze, Strain, ProbeSet, "
+ "ProbeSetXRef) LEFT JOIN ProbeSetSE ON "
+ "(ProbeSetSE.DataId = ProbeSetData.Id AND "
+ "ProbeSetSE.StrainId = ProbeSetData.StrainId) "
+ "LEFT JOIN NStrain ON "
+ "(NStrain.DataId = ProbeSetData.Id AND "
+ "NStrain.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",
+ (trait, self.name)
+ )
+ return cursor.fetchall()
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)
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(
+ f"SELECT ProbeSet.Name, ProbeSet.{column_name} "
+ "FROM ProbeSet,ProbeSetXRef WHERE "
+ "ProbeSetXRef.ProbeSetFreezeId = %s "
+ "AND ProbeSetXRef.ProbeSetId=ProbeSet.Id",
+ (str(self.id),))
+ return dict(cursor.fetchall())
class TempDataSet(DataSet):
- '''Temporary user-generated data set'''
-
+ """Temporary user-generated data set"""
DS_NAME_MAP['Temp'] = 'TempDataSet'
def setup(self):
@@ -1202,30 +1217,15 @@ class TempDataSet(DataSet):
def geno_mrna_confidentiality(ob):
- dataset_table = ob.type + "Freeze"
-
- query = '''SELECT Id, Name, FullName, confidentiality,
- AuthorisedUsers FROM %s WHERE Name = "%s"''' % (dataset_table, ob.name)
- result = g.db.execute(query)
-
- (dataset_id,
- name,
- full_name,
- confidential,
- authorized_users) = result.fetchall()[0]
-
- if confidential:
- return True
- result = g.db.execute(query)
-
- (dataset_id,
- name,
- full_name,
- confidential,
- authorized_users) = result.fetchall()[0]
-
- if confidential:
- return True
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(
+ "SELECT confidentiality, "
+ "AuthorisedUsers FROM %s WHERE Name = %s",
+ (f"{ob.type}Freeze", ob.name,)
+ )
+ result = cursor.fetchall()[0]
+ if result:
+ return True
def parse_db_url():
@@ -1239,16 +1239,15 @@ def query_table_timestamp(dataset_type: str):
"""function to query the update timestamp of a given dataset_type"""
# computation data and actions
-
- fetch_db_name = parse_db_url()
- query_update_time = f"""
- SELECT UPDATE_TIME FROM information_schema.tables
- WHERE TABLE_SCHEMA = '{fetch_db_name[-1]}'
- AND TABLE_NAME = '{dataset_type}Data'
- """
-
- date_time_obj = g.db.execute(query_update_time).fetchone()[0]
- return date_time_obj.strftime("%Y-%m-%d %H:%M:%S")
+ with database_connection() as conn, conn.cursor() as cursor:
+ fetch_db_name = parse_db_url()
+ cursor.execute(
+ "SELECT UPDATE_TIME FROM "
+ "information_schema.tables "
+ f"WHERE TABLE_SCHEMA = '{fetch_db_name[-1]}' "
+ f"AND TABLE_NAME = '{dataset_type}Data'")
+ date_time_obj = cursor.fetchone()[0]
+ return date_time_obj.strftime("%Y-%m-%d %H:%M:%S")
def generate_hash_file(dataset_name: str, dataset_type: str, dataset_timestamp: str, samplelist: str):
diff --git a/wqflask/wqflask/api/router.py b/wqflask/wqflask/api/router.py
index ca700083..7335ec4f 100644
--- a/wqflask/wqflask/api/router.py
+++ b/wqflask/wqflask/api/router.py
@@ -12,7 +12,7 @@ from zipfile import ZipFile, ZIP_DEFLATED
import flask
from flask import current_app
-from flask import g
+from wqflask.database import database_connection
from flask import request
from flask import make_response
from flask import send_file
@@ -36,62 +36,72 @@ def hello_world():
@app.route("/api/v_{}/species".format(version))
def get_species_list():
- results = g.db.execute(
- "SELECT SpeciesId, Name, FullName, TaxonomyId FROM Species;")
- the_species = results.fetchall()
species_list = []
- for species in the_species:
- species_dict = {
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(
+ "SELECT SpeciesId, Name, FullName, TaxonomyId FROM Species"
+ )
+ for species in cursor.fetchall():
+ species_dict = {
"Id": species[0],
"Name": species[1],
"FullName": species[2],
"TaxonomyId": species[3]
}
species_list.append(species_dict)
-
return flask.jsonify(species_list)
@app.route("/api/v_{}/species/<path:species_name>".format(version))
@app.route("/api/v_{}/species/<path:species_name>.<path:file_format>".format(version))
def get_species_info(species_name, file_format="json"):
- results = g.db.execute("""SELECT SpeciesId, Name, FullName, TaxonomyId
- FROM Species
- WHERE (Name="{0}" OR FullName="{0}" OR SpeciesName="{0}");""".format(species_name))
-
- the_species = results.fetchone()
- species_dict = {
- "Id": the_species[0],
- "Name": the_species[1],
- "FullName": the_species[2],
- "TaxonomyId": the_species[3]
- }
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(
+ "SELECT SpeciesId, Name, FullName, TaxonomyId "
+ "FROM Species WHERE (Name=%s OR FullName=%s "
+ "OR SpeciesName=%s)", ((species_name,)*3))
+ _species = cursor.fetchone()
+ species_dict = {
+ "Id": _species[0],
+ "Name": _species[1],
+ "FullName": _species[2],
+ "TaxonomyId": _species[3]
+ }
- return flask.jsonify(species_dict)
+ return flask.jsonify(species_dict)
@app.route("/api/v_{}/groups".format(version))
@app.route("/api/v_{}/groups/<path:species_name>".format(version))
def get_groups_list(species_name=None):
- if species_name:
- results = g.db.execute("""SELECT InbredSet.InbredSetId, InbredSet.SpeciesId, InbredSet.InbredSetName,
- InbredSet.Name, InbredSet.FullName, InbredSet.public,
- IFNULL(InbredSet.MappingMethodId, "None"), IFNULL(InbredSet.GeneticType, "None")
- FROM InbredSet, Species
- WHERE InbredSet.SpeciesId = Species.Id AND
- (Species.Name = "{0}" OR
- Species.FullName="{0}" OR
- Species.SpeciesName="{0}");""".format(species_name))
- else:
- results = g.db.execute("""SELECT InbredSet.InbredSetId, InbredSet.SpeciesId, InbredSet.InbredSetName,
- InbredSet.Name, InbredSet.FullName, InbredSet.public,
- IFNULL(InbredSet.MappingMethodId, "None"), IFNULL(InbredSet.GeneticType, "None")
- FROM InbredSet;""")
-
- the_groups = results.fetchall()
- if the_groups:
+ _groups = ()
+ with database_connection() as conn, conn.cursor() as cursor:
+ if species_name:
+ cursor.execute(
+ "SELECT InbredSet.InbredSetId, "
+ "InbredSet.SpeciesId, InbredSet.InbredSetName, "
+ "InbredSet.Name, InbredSet.FullName, "
+ "InbredSet.public, IFNULL(InbredSet.MappingMethodId, "
+ "'None'), IFNULL(InbredSet.GeneticType, 'None') "
+ "FROM InbredSet, Species WHERE "
+ "InbredSet.SpeciesId = Species.Id AND "
+ "(Species.Name = %s OR Species.FullName=%s "
+ "OR Species.SpeciesName=%s)", ((species_name,) * 3)
+ )
+ else:
+ cursor.execute(
+ "SELECT InbredSet.InbredSetId, "
+ "InbredSet.SpeciesId, InbredSet.InbredSetName, "
+ "InbredSet.Name, InbredSet.FullName, "
+ "InbredSet.public, IFNULL(InbredSet.MappingMethodId, "
+ "'None'), IFNULL(InbredSet.GeneticType, 'None') "
+ "FROM InbredSet"
+ )
+ _groups = cursor.fetchall()
+
+ if _groups:
groups_list = []
- for group in the_groups:
+ for group in _groups:
group_dict = {
"Id": group[0],
"SpeciesId": group[1],
@@ -103,10 +113,8 @@ def get_groups_list(species_name=None):
"GeneticType": group[7]
}
groups_list.append(group_dict)
-
return flask.jsonify(groups_list)
- else:
- return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")
+ return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")
@app.route("/api/v_{}/group/<path:group_name>".format(version))
@@ -114,26 +122,37 @@ def get_groups_list(species_name=None):
@app.route("/api/v_{}/group/<path:species_name>/<path:group_name>".format(version))
@app.route("/api/v_{}/group/<path:species_name>/<path:group_name>.<path:file_format>".format(version))
def get_group_info(group_name, species_name=None, file_format="json"):
- if species_name:
- results = g.db.execute("""SELECT InbredSet.InbredSetId, InbredSet.SpeciesId, InbredSet.InbredSetName,
- InbredSet.Name, InbredSet.FullName, InbredSet.public,
- IFNULL(InbredSet.MappingMethodId, "None"), IFNULL(InbredSet.GeneticType, "None")
- FROM InbredSet, Species
- WHERE InbredSet.SpeciesId = Species.Id AND
- (InbredSet.InbredSetName = "{0}" OR
- InbredSet.Name = "{0}" OR
- InbredSet.FullName = "{0}") AND
- (Species.Name = "{1}" OR
- Species.FullName="{1}" OR
- Species.SpeciesName="{1}");""".format(group_name, species_name))
- else:
- results = g.db.execute("""SELECT InbredSet.InbredSetId, InbredSet.SpeciesId, InbredSet.InbredSetName,
- InbredSet.Name, InbredSet.FullName, InbredSet.public,
- IFNULL(InbredSet.MappingMethodId, "None"), IFNULL(InbredSet.GeneticType, "None")
- FROM InbredSet
- WHERE (InbredSet.InbredSetName = "{0}" OR
- InbredSet.Name = "{0}" OR
- InbredSet.FullName = "{0}");""".format(group_name))
+ group = ()
+ with database_connection() as conn, conn.cursor() as cursor:
+ if species_name:
+ cursor.execute(
+ "SELECT InbredSet.InbredSetId, InbredSet.SpeciesId, "
+ "InbredSet.InbredSetName, InbredSet.Name, "
+ "InbredSet.FullName, InbredSet.public, "
+ "IFNULL(InbredSet.MappingMethodId, 'None'), "
+ "IFNULL(InbredSet.GeneticType, 'None') "
+ "FROM InbredSet, Species WHERE "
+ "InbredSet.SpeciesId = Species.Id "
+ "AND (InbredSet.InbredSetName = %s OR "
+ "InbredSet.Name = %s OR InbredSet.FullName = %s) "
+ "AND (Species.Name = %s OR "
+ "Species.FullName = %s OR Species.SpeciesName = %s)",
+ (*(group_name,)*3, *(species_name,)*3)
+ )
+ else:
+ cursor.execute(
+ "SELECT InbredSet.InbredSetId, InbredSet.SpeciesId, "
+ "InbredSet.InbredSetName, InbredSet.Name, "
+ "InbredSet.FullName, InbredSet.public, "
+ "IFNULL(InbredSet.MappingMethodId, 'None'), "
+ "IFNULL(InbredSet.GeneticType, 'None') "
+ "FROM InbredSet WHERE "
+ "(InbredSet.InbredSetName = %s OR "
+ "InbredSet.Name = %s OR "
+ "InbredSet.FullName = %s)",
+ ((group_name,)*3)
+ )
+ results = cursor.fetchone()
group = results.fetchone()
if group:
@@ -156,36 +175,48 @@ def get_group_info(group_name, species_name=None, file_format="json"):
@app.route("/api/v_{}/datasets/<path:group_name>".format(version))
@app.route("/api/v_{}/datasets/<path:species_name>/<path:group_name>".format(version))
def get_datasets_for_group(group_name, species_name=None):
- if species_name:
- results = g.db.execute("""
- SELECT ProbeSetFreeze.Id, ProbeSetFreeze.ProbeFreezeId, ProbeSetFreeze.AvgID,
- ProbeSetFreeze.Name, ProbeSetFreeze.Name2, ProbeSetFreeze.FullName,
- ProbeSetFreeze.ShortName, ProbeSetFreeze.CreateTime, ProbeSetFreeze.public,
- ProbeSetFreeze.confidentiality, ProbeSetFreeze.DataScale
- FROM ProbeSetFreeze, ProbeFreeze, InbredSet, Species
- WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id AND
- ProbeFreeze.InbredSetId = InbredSet.Id AND
- (InbredSet.Name = "{0}" OR InbredSet.InbredSetName = "{0}" OR InbredSet.FullName = "{0}") AND
- InbredSet.SpeciesId = Species.Id AND
- (Species.SpeciesName = "{1}" OR Species.MenuName = "{1}" OR Species.FullName = "{1}");
- """.format(group_name, species_name))
- else:
- results = g.db.execute("""
- SELECT ProbeSetFreeze.Id, ProbeSetFreeze.ProbeFreezeId, ProbeSetFreeze.AvgID,
- ProbeSetFreeze.Name, ProbeSetFreeze.Name2, ProbeSetFreeze.FullName,
- ProbeSetFreeze.ShortName, ProbeSetFreeze.CreateTime, ProbeSetFreeze.public,
- ProbeSetFreeze.confidentiality, ProbeSetFreeze.DataScale
- FROM ProbeSetFreeze, ProbeFreeze, InbredSet
- WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id AND
- ProbeFreeze.InbredSetId = InbredSet.Id AND
- (InbredSet.Name = "{0}" OR InbredSet.InbredSetName = "{0}" OR InbredSet.FullName = "{0}");
- """.format(group_name))
-
- the_datasets = results.fetchall()
-
- if the_datasets:
+ _datasets = ()
+ with database_connection() as conn, conn.cursor() as cursor:
+ if species_name:
+ cursor.execute(
+ "SELECT ProbeSetFreeze.Id, ProbeSetFreeze.ProbeFreezeId, "
+ "ProbeSetFreeze.AvgID, ProbeSetFreeze.Name, "
+ "ProbeSetFreeze.Name2, "
+ "ProbeSetFreeze.FullName, ProbeSetFreeze.ShortName, "
+ "ProbeSetFreeze.CreateTime, ProbeSetFreeze.public, "
+ "ProbeSetFreeze.confidentiality, ProbeSetFreeze.DataScale "
+ "FROM ProbeSetFreeze, ProbeFreeze, InbredSet, Species "
+ "WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id "
+ "AND ProbeFreeze.InbredSetId = InbredSet.Id "
+ "AND (InbredSet.Name = %s OR "
+ "InbredSet.InbredSetName = %s OR "
+ "InbredSet.FullName = %s) AND "
+ "InbredSet.SpeciesId = Species.Id AND "
+ "(Species.SpeciesName = %s OR "
+ "Species.MenuName = %s OR Species.FullName = %s);",
+ (*(group_name,)*3, *(species_name)*3)
+ )
+ else:
+ cursor.execute(
+ "SELECT ProbeSetFreeze.Id, ProbeSetFreeze.ProbeFreezeId, "
+ "ProbeSetFreeze.AvgID, ProbeSetFreeze.Name, "
+ "ProbeSetFreeze.Name2, ProbeSetFreeze.FullName, "
+ "ProbeSetFreeze.ShortName, ProbeSetFreeze.CreateTime, "
+ "ProbeSetFreeze.public, ProbeSetFreeze.confidentiality, "
+ "ProbeSetFreeze.DataScale FROM ProbeSetFreeze, "
+ "ProbeFreeze, InbredSet WHERE "
+ "ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id "
+ "AND ProbeFreeze.InbredSetId = InbredSet.Id "
+ "AND (InbredSet.Name = %s OR "
+ "InbredSet.InbredSetName = %s OR "
+ "InbredSet.FullName = %s)",
+ ((group_name,) * 3)
+ )
+ _datasets = cursor.fetchall()
+
+ if _datasets:
datasets_list = []
- for dataset in the_datasets:
+ for dataset in _datasets:
dataset_dict = {
"Id": dataset[0],
"ProbeFreezeId": dataset[1],
@@ -213,8 +244,10 @@ def get_datasets_for_group(group_name, species_name=None):
def get_dataset_info(dataset_name, group_name=None, file_format="json"):
# ZS: First get ProbeSet (mRNA expression) datasets and then get Phenotype datasets
- datasets_list = [] # ZS: I figure I might as well return a list if there are multiple matches, though I don"t know if this will actually happen in practice
-
+ # ZS: I figure I might as well return a list if there are multiple
+ # matches, though I don"t know if this will actually happen in
+ # practice
+ datasets_list, dataset_dict = [], {}
probeset_query = """
SELECT ProbeSetFreeze.Id, ProbeSetFreeze.Name, ProbeSetFreeze.FullName,
ProbeSetFreeze.ShortName, ProbeSetFreeze.DataScale, ProbeFreeze.TissueId,
@@ -237,71 +270,71 @@ def get_dataset_info(dataset_name, group_name=None, file_format="json"):
(ProbeSetFreeze.Name = "{0}" OR ProbeSetFreeze.Name2 = "{0}" OR
ProbeSetFreeze.FullName = "{0}" OR ProbeSetFreeze.ShortName = "{0}")
""".format(dataset_name)
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(f"{probeset_query}{where_statement}")
- probeset_query += where_statement
- probeset_results = g.db.execute(probeset_query)
- dataset = probeset_results.fetchone()
-
- if dataset:
- dataset_dict = {
- "dataset_type": "mRNA expression",
- "id": dataset[0],
- "name": dataset[1],
- "full_name": dataset[2],
- "short_name": dataset[3],
- "data_scale": dataset[4],
- "tissue_id": dataset[5],
- "tissue": dataset[6],
- "public": dataset[7],
- "confidential": dataset[8]
- }
-
- datasets_list.append(dataset_dict)
-
- if group_name:
- pheno_query = """
- SELECT PublishXRef.Id, Phenotype.Post_publication_abbreviation, Phenotype.Post_publication_description,
- Phenotype.Pre_publication_abbreviation, Phenotype.Pre_publication_description,
- Publication.PubMed_ID, Publication.Title, Publication.Year
- FROM PublishXRef, Phenotype, Publication, InbredSet, PublishFreeze
- WHERE PublishXRef.InbredSetId = InbredSet.Id AND
- PublishXRef.PhenotypeId = Phenotype.Id AND
- PublishXRef.PublicationId = Publication.Id AND
- PublishFreeze.InbredSetId = InbredSet.Id AND
- PublishFreeze.public > 0 AND
- PublishFreeze.confidentiality < 1 AND
- InbredSet.Name = "{0}" AND PublishXRef.Id = "{1}"
- """.format(group_name, dataset_name)
-
- pheno_results = g.db.execute(pheno_query)
- dataset = pheno_results.fetchone()
-
- if dataset:
- if dataset[5]:
- dataset_dict = {
- "dataset_type": "phenotype",
- "id": dataset[0],
- "name": dataset[1],
- "description": dataset[2],
- "pubmed_id": dataset[5],
- "title": dataset[6],
- "year": dataset[7]
- }
- elif dataset[4]:
- dataset_dict = {
- "dataset_type": "phenotype",
- "id": dataset[0],
- "name": dataset[3],
- "description": dataset[4]
- }
- else:
- dataset_dict = {
- "dataset_type": "phenotype",
- "id": dataset[0]
- }
+ if dataset := cursor.fetchone():
+ dataset_dict = {
+ "dataset_type": "mRNA expression",
+ "id": dataset[0],
+ "name": dataset[1],
+ "full_name": dataset[2],
+ "short_name": dataset[3],
+ "data_scale": dataset[4],
+ "tissue_id": dataset[5],
+ "tissue": dataset[6],
+ "public": dataset[7],
+ "confidential": dataset[8]
+ }
datasets_list.append(dataset_dict)
+ if group_name:
+ cursor.execute(
+ "SELECT PublishXRef.Id, "
+ "Phenotype.Post_publication_abbreviation, "
+ "Phenotype.Post_publication_description, "
+ "Phenotype.Pre_publication_abbreviation, "
+ "Phenotype.Pre_publication_description, "
+ "Publication.PubMed_ID, Publication.Title, "
+ "Publication.Year FROM PublishXRef, Phenotype, "
+ "Publication, InbredSet, PublishFreeze WHERE "
+ "PublishXRef.InbredSetId = InbredSet.Id "
+ "AND PublishXRef.PhenotypeId = Phenotype.Id "
+ "AND PublishXRef.PublicationId = Publication.Id "
+ "AND PublishFreeze.InbredSetId = InbredSet.Id "
+ "AND PublishFreeze.public > 0 AND "
+ "PublishFreeze.confidentiality < 1 "
+ "AND InbredSet.Name = %s AND PublishXRef.Id = %s",
+ (group_name, dataset_name,)
+ )
+
+ if dataset := cursor.fetchone():
+ if dataset[5]:
+ dataset_dict = {
+ "dataset_type": "phenotype",
+ "id": dataset[0],
+ "name": dataset[1],
+ "description": dataset[2],
+ "pubmed_id": dataset[5],
+ "title": dataset[6],
+ "year": dataset[7]
+ }
+ elif dataset[4]:
+ dataset_dict = {
+ "dataset_type": "phenotype",
+ "id": dataset[0],
+ "name": dataset[3],
+ "description": dataset[4]
+ }
+ else:
+ dataset_dict = {
+ "dataset_type": "phenotype",
+ "id": dataset[0]
+ }
+
+ datasets_list.append(dataset_dict)
+
if len(datasets_list) > 1:
return flask.jsonify(datasets_list)
elif len(datasets_list) == 1:
@@ -403,44 +436,48 @@ def fetch_traits(dataset_name, file_format="json"):
if 'limit_to' in request.args:
limit_number = request.args['limit_to']
query += "LIMIT " + str(limit_number)
-
- if file_format == "json":
- filename = dataset_name + "_traits.json"
-
- final_query = query.format(dataset_id)
-
- result_list = []
- for result in g.db.execute(final_query).fetchall():
- trait_dict = {}
- for i, field in enumerate(field_list):
- if result[i]:
- trait_dict[field] = result[i]
- result_list.append(trait_dict)
-
- return flask.jsonify(result_list)
- elif file_format == "csv":
- filename = dataset_name + "_traits.csv"
-
- results_list = []
- header_list = []
- header_list += field_list
- results_list.append(header_list)
-
- final_query = query.format(dataset_id)
- for result in g.db.execute(final_query).fetchall():
- results_list.append(result)
-
- si = io.StringIO()
- csv_writer = csv.writer(si)
- csv_writer.writerows(results_list)
- output = make_response(si.getvalue())
- output.headers["Content-Disposition"] = "attachment; filename=" + filename
- output.headers["Content-type"] = "text/csv"
- return output
- else:
- return return_error(code=400, source=request.url_rule.rule, title="Invalid Output Format", details="Current formats available are JSON and CSV, with CSV as default")
+ with database_connection() as conn, conn.cursor() as cursor:
+ if file_format == "json":
+ filename = dataset_name + "_traits.json"
+ cursor.execute(query.format(dataset_id))
+ for result in cursor.fetchall():
+ trait_dict = {}
+ for i, field in enumerate(field_list):
+ if result[i]:
+ trait_dict[field] = result[i]
+ result_list.append(trait_dict)
+ return flask.jsonify(result_list)
+ elif file_format == "csv":
+ filename = dataset_name + "_traits.csv"
+
+ results_list = []
+ header_list = []
+ header_list += field_list
+ results_list.append(header_list)
+ cursor.execute(query.format(dataset_id))
+ for result in cursor.fetchall():
+ results_list.append(result)
+
+ si = io.StringIO()
+ csv_writer = csv.writer(si)
+ csv_writer.writerows(results_list)
+ output = make_response(si.getvalue())
+ output.headers["Content-Disposition"] = "attachment; filename=" + filename
+ output.headers["Content-type"] = "text/csv"
+ return output
+ else:
+ return return_error(
+ code=400,
+ source=request.url_rule.rule,
+ title="Invalid Output Format",
+ details="Current formats available are JSON and CSV, with CSV as default"
+ )
else:
- return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")
+ return return_error(
+ code=204,
+ source=request.url_rule.rule,
+ title="No Results",
+ details="")
@app.route("/api/v_{}/sample_data/<path:dataset_name>".format(version))
@@ -521,20 +558,21 @@ def all_sample_data(dataset_name, file_format="csv"):
header_list.append("id")
header_list += sample_list
results_list.append(header_list)
- for i, trait_id in enumerate(trait_ids):
- line_list = []
- line_list.append(str(trait_names[i]))
- final_query = query.format(dataset_id, trait_id)
- results = g.db.execute(final_query).fetchall()
- results_dict = {}
- for item in results:
- results_dict[item[0]] = item[2]
- for sample in sample_list:
- if sample in results_dict:
- line_list.append(results_dict[sample])
- else:
- line_list.append("x")
- results_list.append(line_list)
+ with database_connection() as conn, conn.cursor() as cursor:
+ for i, trait_id in enumerate(trait_ids):
+ line_list = []
+ line_list.append(str(trait_names[i]))
+ cursor.execute(query.format(dataset_id, trait_id))
+ results = cursor.fetchall()
+ results_dict = {}
+ for item in results:
+ results_dict[item[0]] = item[2]
+ for sample in sample_list:
+ if sample in results_dict:
+ line_list.append(results_dict[sample])
+ else:
+ line_list.append("x")
+ results_list.append(line_list)
results_list = list(map(list, zip(*results_list)))
@@ -554,73 +592,26 @@ def all_sample_data(dataset_name, file_format="csv"):
@app.route("/api/v_{}/sample_data/<path:dataset_name>/<path:trait_name>".format(version))
@app.route("/api/v_{}/sample_data/<path:dataset_name>/<path:trait_name>.<path:file_format>".format(version))
def trait_sample_data(dataset_name, trait_name, file_format="json"):
- probeset_query = """
- SELECT
- Strain.Name, Strain.Name2, ProbeSetData.value, ProbeSetData.Id, ProbeSetSE.error
- FROM
- (ProbeSetData, ProbeSetFreeze, Strain, ProbeSet, ProbeSetXRef)
- LEFT JOIN ProbeSetSE ON
- (ProbeSetSE.DataId = ProbeSetData.Id AND ProbeSetSE.StrainId = ProbeSetData.StrainId)
- WHERE
- ProbeSet.Name = "{0}" AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND
- ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND
- ProbeSetFreeze.Name = "{1}" AND
- ProbeSetXRef.DataId = ProbeSetData.Id AND
- ProbeSetData.StrainId = Strain.Id
- ORDER BY
- Strain.Name
- """.format(trait_name, dataset_name)
-
- probeset_results = g.db.execute(probeset_query)
-
- sample_data = probeset_results.fetchall()
- if len(sample_data) > 0:
- sample_list = []
- for sample in sample_data:
- sample_dict = {
- "sample_name": sample[0],
- "sample_name_2": sample[1],
- "value": sample[2],
- "data_id": sample[3],
- }
- if sample[4]:
- sample_dict["se"] = sample[4]
- sample_list.append(sample_dict)
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(
+ "SELECT Strain.Name, Strain.Name2, "
+ "ProbeSetData.value, ProbeSetData.Id, "
+ "ProbeSetSE.error 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",
+ (trait_name, dataset_name,)
+ )
- return flask.jsonify(sample_list)
- else:
- if not dataset_name.isdigit():
- group_id = get_group_id(dataset_name)
- if group_id:
- dataset_or_group = group_id
- else:
- dataset_or_group = dataset_name
- else:
- dataset_or_group = dataset_name
-
- pheno_query = """
- SELECT DISTINCT
- Strain.Name, Strain.Name2, PublishData.value, PublishData.Id, PublishSE.error, NStrain.count
- FROM
- (PublishData, Strain, PublishXRef, PublishFreeze)
- LEFT JOIN PublishSE ON
- (PublishSE.DataId = PublishData.Id AND PublishSE.StrainId = PublishData.StrainId)
- LEFT JOIN NStrain ON
- (NStrain.DataId = PublishData.Id AND
- NStrain.StrainId = PublishData.StrainId)
- WHERE
- PublishXRef.InbredSetId = PublishFreeze.InbredSetId AND
- PublishData.Id = PublishXRef.DataId AND PublishXRef.Id = "{1}" AND
- (PublishFreeze.Id = "{0}" OR PublishFreeze.Name = "{0}" OR
- PublishFreeze.ShortName = "{0}" OR PublishXRef.InbredSetId = "{0}") AND
- PublishData.StrainId = Strain.Id
- ORDER BY
- Strain.Name
- """.format(dataset_or_group, trait_name)
-
- pheno_results = g.db.execute(pheno_query)
-
- sample_data = pheno_results.fetchall()
+ sample_data = cursor.fetchall()
if len(sample_data) > 0:
sample_list = []
for sample in sample_data:
@@ -628,17 +619,61 @@ def trait_sample_data(dataset_name, trait_name, file_format="json"):
"sample_name": sample[0],
"sample_name_2": sample[1],
"value": sample[2],
- "data_id": sample[3]
+ "data_id": sample[3],
}
if sample[4]:
sample_dict["se"] = sample[4]
- if sample[5]:
- sample_dict["n_cases"] = sample[5]
sample_list.append(sample_dict)
return flask.jsonify(sample_list)
else:
- return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")
+ if not dataset_name.isdigit():
+ group_id = get_group_id(dataset_name)
+ if group_id:
+ dataset_or_group = group_id
+ else:
+ dataset_or_group = dataset_name
+ else:
+ dataset_or_group = dataset_name
+
+ cursor.execute(
+ "SELECT DISTINCT Strain.Name, Strain.Name2, "
+ "PublishData.value, PublishData.Id, PublishSE.error, "
+ "NStrain.count FROM (PublishData, Strain, "
+ "PublishXRef, PublishFreeze) LEFT JOIN "
+ "PublishSE ON (PublishSE.DataId = PublishData.Id "
+ "AND PublishSE.StrainId = PublishData.StrainId) "
+ "LEFT JOIN NStrain ON "
+ "(NStrain.DataId = PublishData.Id AND "
+ "NStrain.StrainId = PublishData.StrainId) "
+ "WHERE PublishXRef.InbredSetId = PublishFreeze.InbredSetId "
+ "AND PublishData.Id = PublishXRef.DataId AND "
+ "PublishXRef.Id = %s AND (PublishFreeze.Id = %s "
+ "OR PublishFreeze.Name = %s OR "
+ "PublishFreeze.ShortName = %s OR "
+ "PublishXRef.InbredSetId = %s) AND "
+ "PublishData.StrainId = Strain.Id "
+ "ORDER BY Strain.Name",
+ (trait_name, *(dataset_or_group,)*4)
+ )
+ if len(sample_data := cursor.fetchall()) > 0:
+ sample_list = []
+ for sample in sample_data:
+ sample_dict = {
+ "sample_name": sample[0],
+ "sample_name_2": sample[1],
+ "value": sample[2],
+ "data_id": sample[3]
+ }
+ if sample[4]:
+ sample_dict["se"] = sample[4]
+ if sample[5]:
+ sample_dict["n_cases"] = sample[5]
+ sample_list.append(sample_dict)
+
+ return flask.jsonify(sample_list)
+ else:
+ return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")
@app.route("/api/v_{}/trait/<path:dataset_name>/<path:trait_name>".format(version))
@@ -646,70 +681,64 @@ def trait_sample_data(dataset_name, trait_name, file_format="json"):
@app.route("/api/v_{}/trait_info/<path:dataset_name>/<path:trait_name>".format(version))
@app.route("/api/v_{}/trait_info/<path:dataset_name>/<path:trait_name>.<path:file_format>".format(version))
def get_trait_info(dataset_name, trait_name, file_format="json"):
- probeset_query = """
- SELECT
- ProbeSet.Id, ProbeSet.Name, ProbeSet.Symbol, ProbeSet.description, ProbeSet.Chr, ProbeSet.Mb, ProbeSet.alias,
- ProbeSetXRef.mean, ProbeSetXRef.se, ProbeSetXRef.Locus, ProbeSetXRef.LRS, ProbeSetXRef.pValue, ProbeSetXRef.additive
- FROM
- ProbeSet, ProbeSetXRef, ProbeSetFreeze
- WHERE
- ProbeSet.Name = "{0}" AND
- ProbeSetXRef.ProbeSetId = ProbeSet.Id AND
- ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND
- ProbeSetFreeze.Name = "{1}"
- """.format(trait_name, dataset_name)
-
- probeset_results = g.db.execute(probeset_query)
-
- trait_info = probeset_results.fetchone()
- if trait_info:
- trait_dict = {
- "id": trait_info[0],
- "name": trait_info[1],
- "symbol": trait_info[2],
- "description": trait_info[3],
- "chr": trait_info[4],
- "mb": trait_info[5],
- "alias": trait_info[6],
- "mean": trait_info[7],
- "se": trait_info[8],
- "locus": trait_info[9],
- "lrs": trait_info[10],
- "p_value": trait_info[11],
- "additive": trait_info[12]
- }
-
- return flask.jsonify(trait_dict)
- else:
- # ZS: Check if the user input the dataset_name as BXDPublish, etc (which is always going to be the group name + "Publish"
- if "Publish" in dataset_name:
- dataset_name = dataset_name.replace("Publish", "")
-
- group_id = get_group_id(dataset_name)
- pheno_query = """
- SELECT
- PublishXRef.PhenotypeId, PublishXRef.Locus, PublishXRef.LRS, PublishXRef.additive
- FROM
- PublishXRef
- WHERE
- PublishXRef.Id = "{0}" AND
- PublishXRef.InbredSetId = "{1}"
- """.format(trait_name, group_id)
-
- pheno_results = g.db.execute(pheno_query)
-
- trait_info = pheno_results.fetchone()
- if trait_info:
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(
+ "SELECT ProbeSet.Id, ProbeSet.Name, ProbeSet.Symbol, "
+ "ProbeSet.description, ProbeSet.Chr, ProbeSet.Mb, "
+ "ProbeSet.alias, ProbeSetXRef.mean, ProbeSetXRef.se, "
+ "ProbeSetXRef.Locus, ProbeSetXRef.LRS, "
+ "ProbeSetXRef.pValue, ProbeSetXRef.additive "
+ "FROM ProbeSet, ProbeSetXRef, ProbeSetFreeze "
+ "WHERE ProbeSet.Name = %s AND "
+ "ProbeSetXRef.ProbeSetId = ProbeSet.Id AND "
+ "ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id "
+ "AND ProbeSetFreeze.Name = %s",
+ (trait_name, dataset_name,)
+ )
+ if trait_info := cursor.fetchone():
trait_dict = {
"id": trait_info[0],
- "locus": trait_info[1],
- "lrs": trait_info[2],
- "additive": trait_info[3]
+ "name": trait_info[1],
+ "symbol": trait_info[2],
+ "description": trait_info[3],
+ "chr": trait_info[4],
+ "mb": trait_info[5],
+ "alias": trait_info[6],
+ "mean": trait_info[7],
+ "se": trait_info[8],
+ "locus": trait_info[9],
+ "lrs": trait_info[10],
+ "p_value": trait_info[11],
+ "additive": trait_info[12]
}
return flask.jsonify(trait_dict)
else:
- return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")
+ # ZS: Check if the user input the dataset_name as BXDPublish, etc (which is always going to be the group name + "Publish"
+ if "Publish" in dataset_name:
+ dataset_name = dataset_name.replace("Publish", "")
+
+ group_id = get_group_id(dataset_name)
+ cursor.execute(
+ "SELECT PublishXRef.PhenotypeId, "
+ "PublishXRef.Locus, PublishXRef.LRS, "
+ "PublishXRef.additive FROM "
+ "PublishXRef WHERE "
+ "PublishXRef.Id = %s AND "
+ "PublishXRef.InbredSetId = %s",
+ (trait_name, group_id,)
+ )
+ if trait_info := cursor.fetchone():
+ trait_dict = {
+ "id": trait_info[0],
+ "locus": trait_info[1],
+ "lrs": trait_info[2],
+ "additive": trait_info[3]
+ }
+
+ return flask.jsonify(trait_dict)
+ else:
+ return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")
@app.route("/api/v_{}/correlation".format(version), methods=("GET",))
@@ -872,129 +901,108 @@ def get_dataset_trait_ids(dataset_name, start_vars):
limit_string = "LIMIT " + str(start_vars['limit_to'])
else:
limit_string = ""
+ with database_connection() as conn, conn.cursor() as cursor:
+ if "Geno" in dataset_name:
+ data_type = "Geno" # ZS: Need to pass back the dataset type
+ cursor.execute(
+ "SELECT GenoXRef.GenoId, Geno.Name, "
+ "GenoXRef.GenoFreezeId FROM Geno, "
+ "GenoXRef, GenoFreeze WHERE "
+ "Geno.Id = GenoXRef.GenoId AND "
+ "GenoXRef.GenoFreezeId = GenoFreeze.Id "
+ f"AND GenoFreeze.Name = %s {limit_string}",
+ (dataset_name,))
+
+ results = cursor.fetchall()
+
+ trait_ids = [result[0] for result in results]
+ trait_names = [result[1] for result in results]
+ dataset_id = results[0][2]
+ return trait_ids, trait_names, data_type, dataset_id
+
+ elif "Publish" in dataset_name or get_group_id(dataset_name):
+ data_type = "Publish"
+ dataset_name = dataset_name.replace("Publish", "")
+ dataset_id = get_group_id(dataset_name)
+ cursor.execute(
+ "SELECT PublishXRef.PhenotypeId, "
+ "PublishXRef.Id, InbredSet.InbredSetCode "
+ "FROM PublishXRef, InbredSet WHERE "
+ "PublishXRef.InbredSetId = %s AND "
+ "InbredSet.Id = PublishXRef.InbredSetId "
+ f"{limit_string}",
+ (dataset_id,)
+ )
+ results = cursor.fetchall()
+
+ trait_ids = [result[0] for result in results]
+ trait_names = [str(result[2]) + "_" + str(result[1])
+ for result in results]
+
+ return trait_ids, trait_names, data_type, dataset_id
- if "Geno" in dataset_name:
- data_type = "Geno" # ZS: Need to pass back the dataset type
- query = """
- SELECT
- GenoXRef.GenoId, Geno.Name, GenoXRef.GenoFreezeId
- FROM
- Geno, GenoXRef, GenoFreeze
- WHERE
- Geno.Id = GenoXRef.GenoId AND
- GenoXRef.GenoFreezeId = GenoFreeze.Id AND
- GenoFreeze.Name = "{0}"
- {1}
- """.format(dataset_name, limit_string)
-
- results = g.db.execute(query).fetchall()
-
- trait_ids = [result[0] for result in results]
- trait_names = [result[1] for result in results]
- dataset_id = results[0][2]
- return trait_ids, trait_names, data_type, dataset_id
-
- elif "Publish" in dataset_name or get_group_id(dataset_name):
- data_type = "Publish"
- dataset_name = dataset_name.replace("Publish", "")
- dataset_id = get_group_id(dataset_name)
-
- query = """
- SELECT
- PublishXRef.PhenotypeId, PublishXRef.Id, InbredSet.InbredSetCode
- FROM
- PublishXRef, InbredSet
- WHERE
- PublishXRef.InbredSetId = "{0}" AND
- InbredSet.Id = PublishXRef.InbredSetId
- {1}
- """.format(dataset_id, limit_string)
-
- results = g.db.execute(query).fetchall()
-
- trait_ids = [result[0] for result in results]
- trait_names = [str(result[2]) + "_" + str(result[1])
- for result in results]
-
- return trait_ids, trait_names, data_type, dataset_id
-
- else:
- data_type = "ProbeSet"
- query = """
- SELECT
- ProbeSetXRef.ProbeSetId, ProbeSet.Name, ProbeSetXRef.ProbeSetFreezeId
- FROM
- ProbeSet, ProbeSetXRef, ProbeSetFreeze
- WHERE
- ProbeSet.Id = ProbeSetXRef.ProbeSetId AND
- ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND
- ProbeSetFreeze.Name = "{0}"
- {1}
- """.format(dataset_name, limit_string)
-
- results = g.db.execute(query).fetchall()
-
- trait_ids = [result[0] for result in results]
- trait_names = [result[1] for result in results]
- dataset_id = results[0][2]
- return trait_ids, trait_names, data_type, dataset_id
+ else:
+ data_type = "ProbeSet"
+ cursor.execute(
+ "SELECT ProbeSetXRef.ProbeSetId, "
+ "ProbeSet.Name, ProbeSetXRef.ProbeSetFreezeId "
+ "FROM ProbeSet, ProbeSetXRef, "
+ "ProbeSetFreeze WHERE "
+ "ProbeSet.Id = ProbeSetXRef.ProbeSetId AND "
+ "ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id "
+ f"AND ProbeSetFreeze.Name = %s {limit_string}",
+ (dataset_name,)
+ )
+ results = cursor.fetchall()
+ trait_ids = [result[0] for result in results]
+ trait_names = [result[1] for result in results]
+ dataset_id = results[0][2]
+ return trait_ids, trait_names, data_type, dataset_id
def get_samplelist(dataset_name):
- group_id = get_group_id_from_dataset(dataset_name)
-
- query = """
- SELECT Strain.Name
- FROM Strain, StrainXRef
- WHERE StrainXRef.StrainId = Strain.Id AND
- StrainXRef.InbredSetId = {}
- """.format(group_id)
-
- results = g.db.execute(query).fetchall()
-
- samplelist = [result[0] for result in results]
-
- return samplelist
+ with database_connection() as conn, conn.cursor() as cursor:
+ cursor.execute(
+ "SELECT Strain.Name FROM Strain, StrainXRef "
+ "WHERE StrainXRef.StrainId = Strain.Id AND "
+ "StrainXRef.InbredSetId = %s",
+ (dataset_name,)
+ )
+ # sample list
+ return [result[0] for result in cursor.fetchall()]
def get_group_id_from_dataset(dataset_name):
- if "Publish" in dataset_name:
- query = """
- SELECT
- InbredSet.Id
- FROM
- InbredSet, PublishFreeze
- WHERE
- PublishFreeze.InbredSetId = InbredSet.Id AND
- PublishFreeze.Name = "{}"
- """.format(dataset_name)
- elif "Geno" in dataset_name:
- query = """
- SELECT
- InbredSet.Id
- FROM
- InbredSet, GenoFreeze
- WHERE
- GenoFreeze.InbredSetId = InbredSet.Id AND
- GenoFreeze.Name = "{}"
- """.format(dataset_name)
- else:
- query = """
- SELECT
- InbredSet.Id
- FROM
- InbredSet, ProbeSetFreeze, ProbeFreeze
- WHERE
- ProbeFreeze.InbredSetId = InbredSet.Id AND
- ProbeFreeze.Id = ProbeSetFreeze.ProbeFreezeId AND
- ProbeSetFreeze.Name = "{}"
- """.format(dataset_name)
-
- result = g.db.execute(query).fetchone()
-
- if len(result) > 0:
- return result[0]
- else:
+ result = ()
+ with database_connection() as conn, conn.cursor() as cursor:
+ if "Publish" in dataset_name:
+ cursor.execute(
+ "SELECT InbredSet.Id FROM "
+ "InbredSet, PublishFreeze "
+ "WHERE PublishFreeze.InbredSetId = InbredSet.Id "
+ "AND PublishFreeze.Name = %s",
+ (dataset_name,)
+ )
+ elif "Geno" in dataset_name:
+ cursor.execute(
+ "SELECT InbredSet.Id FROM "
+ "InbredSet, GenoFreeze WHERE "
+ "GenoFreeze.InbredSetId = InbredSet.Id "
+ "AND GenoFreeze.Name = %s",
+ (dataset_name,)
+ )
+ else:
+ cursor.execute(
+ "SELECT InbredSet.Id FROM "
+ "InbredSet, ProbeSetFreeze, "
+ "ProbeFreeze WHERE "
+ "ProbeFreeze.InbredSetId = InbredSet.Id "
+ "AND ProbeFreeze.Id = ProbeSetFreeze.ProbeFreezeId "
+ "AND ProbeSetFreeze.Name = %s",
+ (dataset_name,)
+ )
+ if result := cursor.fetchone():
+ return result[0]
return None