aboutsummaryrefslogtreecommitdiff
path: root/wqflask
diff options
context:
space:
mode:
Diffstat (limited to 'wqflask')
-rw-r--r--wqflask/wqflask/api/gen_menu.py251
1 files changed, 132 insertions, 119 deletions
diff --git a/wqflask/wqflask/api/gen_menu.py b/wqflask/wqflask/api/gen_menu.py
index 7f4ec1bc..0c1120ab 100644
--- a/wqflask/wqflask/api/gen_menu.py
+++ b/wqflask/wqflask/api/gen_menu.py
@@ -1,15 +1,12 @@
-from flask import g
-
-
-def gen_dropdown_json():
+def gen_dropdown_json(conn):
"""Generates and outputs (as json file) the data for the main dropdown menus on
the home page
"""
- species = get_species()
- groups = get_groups(species)
- types = get_types(groups)
- datasets = get_datasets(types)
+ species = get_species(conn)
+ groups = get_groups(species, conn)
+ types = get_types(groups, conn)
+ datasets = get_datasets(types, conn)
data = dict(species=species,
groups=groups,
@@ -19,48 +16,50 @@ def gen_dropdown_json():
return data
-def get_species():
+def get_species(conn):
"""Build species list"""
- results = g.db.execute(
- "SELECT Name, MenuName FROM Species ORDER BY OrderId").fetchall()
- return [[name, menu_name] for name, menu_name in results]
+ with conn.cursor() as cursor:
+ cursor.execute("SELECT Name, MenuName FROM Species "
+ "ORDER BY OrderId")
+ results = cursor.fetchall()
+ return [[name, menu_name] for name, menu_name in results]
-def get_groups(species):
+def get_groups(species, conn):
"""Build groups list"""
groups = {}
- for species_name, _species_full_name in species:
- groups[species_name] = []
-
- results = g.db.execute(
- ("SELECT InbredSet.Name, InbredSet.FullName, "
- "IFNULL(InbredSet.Family, 'None') "
- "FROM InbredSet, Species WHERE Species.Name = '{}' "
- "AND InbredSet.SpeciesId = Species.Id GROUP by InbredSet.Name "
- "ORDER BY IFNULL(InbredSet.FamilyOrder, InbredSet.FullName) "
- "ASC, IFNULL(InbredSet.Family, InbredSet.FullName) ASC, "
- "InbredSet.FullName ASC, InbredSet.MenuOrderId ASC")
- .format(species_name)).fetchall()
-
- for result in results:
- family_name = "Family:" + str(result[2])
- groups[species_name].append(
- [str(result[0]), str(result[1]), family_name])
-
+ with conn.cursor() as cursor:
+ for species_name, _species_full_name in species:
+ groups[species_name] = []
+ cursor.execute(
+ ("SELECT InbredSet.Name, InbredSet.FullName, "
+ "IFNULL(InbredSet.Family, 'None') "
+ "FROM InbredSet, Species WHERE Species.Name = '{}' "
+ "AND InbredSet.SpeciesId = Species.Id GROUP by "
+ "InbredSet.Name ORDER BY IFNULL(InbredSet.FamilyOrder, "
+ "InbredSet.FullName) ASC, IFNULL(InbredSet.Family, "
+ "InbredSet.FullName) ASC, InbredSet.FullName ASC, "
+ "InbredSet.MenuOrderId ASC")
+ .format(species_name))
+ results = cursor.fetchall()
+ for result in results:
+ family_name = "Family:" + str(result[2])
+ groups[species_name].append(
+ [str(result[0]), str(result[1]), family_name])
return groups
-def get_types(groups):
+def get_types(groups, conn):
"""Build types list"""
types = {}
for species, group_dict in list(groups.items()):
types[species] = {}
for group_name, _group_full_name, _family_name in group_dict:
- if phenotypes_exist(group_name):
+ if phenotypes_exist(group_name, conn):
types[species][group_name] = [
("Phenotypes", "Traits and Cofactors", "Phenotypes")]
- if genotypes_exist(group_name):
+ if genotypes_exist(group_name, conn):
if group_name in types[species]:
types[species][group_name] += [
("Genotypes", "DNA Markers and SNPs", "Genotypes")]
@@ -68,11 +67,11 @@ def get_types(groups):
types[species][group_name] = [
("Genotypes", "DNA Markers and SNPs", "Genotypes")]
if group_name in types[species]:
- types_list = build_types(species, group_name)
+ types_list = build_types(species, group_name, conn)
if len(types_list) > 0:
types[species][group_name] += types_list
else:
- types_list = build_types(species, group_name)
+ types_list = build_types(species, group_name, conn)
if len(types_list) > 0:
types[species][group_name] = types_list
else:
@@ -83,22 +82,28 @@ def get_types(groups):
return types
-def phenotypes_exist(group_name):
- results = g.db.execute(
- ("SELECT Name FROM PublishFreeze "
- "WHERE PublishFreeze.Name = "
- "'{}'").format(group_name + "Publish")).fetchone()
+def phenotypes_exist(group_name, conn):
+ results = []
+ with conn.cursor() as cursor:
+ cursor.execute(
+ ("SELECT Name FROM PublishFreeze "
+ "WHERE PublishFreeze.Name = "
+ "'{}'").format(group_name + "Publish"))
+ results = cursor.fetchone()
return bool(results)
-def genotypes_exist(group_name):
- results = g.db.execute(
- ("SELECT Name FROM GenoFreeze " +
- "WHERE GenoFreeze.Name = '{}'").format(group_name + "Geno")).fetchone()
- return bool(results)
+def genotypes_exist(group_name, conn):
+ with conn.cursor() as cursor:
+ cursor.execute(
+ ("SELECT Name FROM GenoFreeze " +
+ "WHERE GenoFreeze.Name = '{}'").format(
+ group_name + "Geno"))
+ results = cursor.fetchone()
+ return bool(results)
-def build_types(species, group):
+def build_types(species, group, conn):
"""Fetches tissues
Gets the tissues with data for this species/group
@@ -116,17 +121,19 @@ def build_types(species, group):
"ORDER BY Tissue.Name").format(species, group)
results = []
- for result in g.db.execute(query).fetchall():
- if bool(result):
- these_datasets = build_datasets(species, group, result[0])
- if len(these_datasets) > 0:
- results.append([str(result[0]), str(result[0]),
- "Molecular Traits"])
-
+ with conn.cursor() as cursor:
+ cursor.execute(query)
+ for result in cursor.fetchall():
+ if bool(result):
+ these_datasets = build_datasets(species,
+ group, result[0], conn)
+ if len(these_datasets) > 0:
+ results.append([str(result[0]), str(result[0]),
+ "Molecular Traits"])
return results
-def get_datasets(types):
+def get_datasets(types, conn):
"""Build datasets list"""
datasets = {}
for species, group_dict in list(types.items()):
@@ -134,83 +141,89 @@ def get_datasets(types):
for group, type_list in list(group_dict.items()):
datasets[species][group] = {}
for type_name in type_list:
- these_datasets = build_datasets(species, group, type_name[0])
+ these_datasets = build_datasets(species, group,
+ type_name[0], conn)
if bool(these_datasets):
datasets[species][group][type_name[0]] = these_datasets
return datasets
-def build_datasets(species, group, type_name):
+def build_datasets(species, group, type_name, conn):
"""Gets dataset names from database"""
dataset_text = dataset_value = None
datasets = []
- if type_name == "Phenotypes":
- results = g.db.execute(
- ("SELECT InfoFiles.GN_AccesionId, PublishFreeze.Name, "
- "PublishFreeze.FullName FROM InfoFiles, PublishFreeze, "
- "InbredSet WHERE InbredSet.Name = '{}' AND "
- "PublishFreeze.InbredSetId = InbredSet.Id AND "
- "InfoFiles.InfoPageName = PublishFreeze.Name "
- "ORDER BY PublishFreeze.CreateTime ASC").format(group)).fetchall()
- if bool(results):
- for result in results:
- dataset_id = str(result[0])
- dataset_value = str(result[1])
- dataset_text = str(result[2])
- if group == 'MDP':
- dataset_text = "Mouse Phenome Database"
-
+ with conn.cursor() as cursor:
+ if type_name == "Phenotypes":
+ cursor.execute(
+ ("SELECT InfoFiles.GN_AccesionId, PublishFreeze.Name, "
+ "PublishFreeze.FullName FROM InfoFiles, PublishFreeze, "
+ "InbredSet WHERE InbredSet.Name = '{}' AND "
+ "PublishFreeze.InbredSetId = InbredSet.Id AND "
+ "InfoFiles.InfoPageName = PublishFreeze.Name "
+ "ORDER BY PublishFreeze.CreateTime ASC").format(group))
+ results = cursor.fetchall()
+ if bool(results):
+ for result in results:
+ dataset_id = str(result[0])
+ dataset_value = str(result[1])
+ dataset_text = str(result[2])
+ if group == 'MDP':
+ dataset_text = "Mouse Phenome Database"
+
+ datasets.append([dataset_id, dataset_value, dataset_text])
+ else:
+ cursor.execute(
+ ("SELECT PublishFreeze.Name, PublishFreeze.FullName "
+ "FROM PublishFreeze, InbredSet "
+ "WHERE InbredSet.Name = '{}' AND "
+ "PublishFreeze.InbredSetId = InbredSet.Id "
+ "ORDER BY PublishFreeze.CreateTime ASC")
+ .format(group))
+ result = cursor.fetchone()
+ dataset_id = "None"
+ dataset_value = str(result[0])
+ dataset_text = str(result[1])
datasets.append([dataset_id, dataset_value, dataset_text])
- else:
- result = g.db.execute(
- ("SELECT PublishFreeze.Name, PublishFreeze.FullName "
- "FROM PublishFreeze, InbredSet "
- "WHERE InbredSet.Name = '{}' AND "
- "PublishFreeze.InbredSetId = InbredSet.Id "
- "ORDER BY PublishFreeze.CreateTime ASC")
- .format(group)).fetchone()
+ elif type_name == "Genotypes":
+ cursor.execute(
+ ("SELECT InfoFiles.GN_AccesionId "
+ "FROM InfoFiles, GenoFreeze, InbredSet "
+ "WHERE InbredSet.Name = '{}' AND "
+ "GenoFreeze.InbredSetId = InbredSet.Id AND "
+ "InfoFiles.InfoPageName = GenoFreeze.ShortName "
+ "ORDER BY GenoFreeze.CreateTime "
+ "DESC").format(group))
+ results = cursor.fetchone()
dataset_id = "None"
- dataset_value = str(result[0])
- dataset_text = str(result[1])
+ if bool(results):
+ dataset_id = str(results[0])
+
+ dataset_value = "%sGeno" % group
+ dataset_text = "%s Genotypes" % group
datasets.append([dataset_id, dataset_value, dataset_text])
- elif type_name == "Genotypes":
- results = g.db.execute(
- ("SELECT InfoFiles.GN_AccesionId "
- "FROM InfoFiles, GenoFreeze, InbredSet "
- "WHERE InbredSet.Name = '{}' AND "
- "GenoFreeze.InbredSetId = InbredSet.Id AND "
- "InfoFiles.InfoPageName = GenoFreeze.ShortName "
- "ORDER BY GenoFreeze.CreateTime DESC").format(group)).fetchone()
-
- dataset_id = "None"
- if bool(results):
- dataset_id = str(results[0])
-
- dataset_value = "%sGeno" % group
- dataset_text = "%s Genotypes" % group
- datasets.append([dataset_id, dataset_value, dataset_text])
-
- else: # for mRNA expression/ProbeSet
- results = g.db.execute(
- ("SELECT ProbeSetFreeze.Id, ProbeSetFreeze.Name, "
- "ProbeSetFreeze.FullName FROM ProbeSetFreeze, "
- "ProbeFreeze, InbredSet, Tissue, Species WHERE "
- "Species.Name = '{0}' AND Species.Id = "
- "InbredSet.SpeciesId AND InbredSet.Name = '{1}' "
- "AND ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id "
- "AND Tissue.Name = '{2}' AND ProbeFreeze.TissueId = "
- "Tissue.Id AND ProbeFreeze.InbredSetId = InbredSet.Id "
- "AND ProbeSetFreeze.public > 0 "
- "ORDER BY -ProbeSetFreeze.OrderList DESC, ProbeSetFreeze.CreateTime DESC").format(species, group, type_name)).fetchall()
-
- datasets = []
- for dataset_info in results:
- this_dataset_info = []
- for info in dataset_info:
- this_dataset_info.append(str(info))
- datasets.append(this_dataset_info)
+ else: # for mRNA expression/ProbeSet
+ cursor.execute(
+ ("SELECT ProbeSetFreeze.Id, ProbeSetFreeze.Name, "
+ "ProbeSetFreeze.FullName FROM ProbeSetFreeze, "
+ "ProbeFreeze, InbredSet, Tissue, Species WHERE "
+ "Species.Name = '{0}' AND Species.Id = "
+ "InbredSet.SpeciesId AND InbredSet.Name = '{1}' "
+ "AND ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id "
+ "AND Tissue.Name = '{2}' AND ProbeFreeze.TissueId = "
+ "Tissue.Id AND ProbeFreeze.InbredSetId = InbredSet.Id "
+ "AND ProbeSetFreeze.public > 0 "
+ "ORDER BY -ProbeSetFreeze.OrderList DESC, "
+ "ProbeSetFreeze.CreateTime "
+ "DESC").format(species, group, type_name))
+ results = cursor.fetchall()
+ datasets = []
+ for dataset_info in results:
+ this_dataset_info = []
+ for info in dataset_info:
+ this_dataset_info.append(str(info))
+ datasets.append(this_dataset_info)
return datasets
generated by cgit v1.2.3 (git 2.44.0) at 2024-11-23 09:24:14 +0000