diff options
| -rw-r--r-- | wqflask/wqflask/api/gen_menu.py | 251 |
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 |