From 4534daa6fb07c23b90e024560ca64091fc330eed Mon Sep 17 00:00:00 2001 From: BonfaceKilz Date: Mon, 19 Apr 2021 17:46:38 +0300 Subject: Move looped sql query into one statement in "get_species_groups" It's in-efficient to have a sql query executed in a loop. As data grows, the query becomes slower. It's better to let sql handle such queries. --- wqflask/utility/helper_functions.py | 29 ++++++++++++++--------------- 1 file changed, 14 insertions(+), 15 deletions(-) diff --git a/wqflask/utility/helper_functions.py b/wqflask/utility/helper_functions.py index 15d5b3ab..4ba92ed5 100644 --- a/wqflask/utility/helper_functions.py +++ b/wqflask/utility/helper_functions.py @@ -47,19 +47,18 @@ def get_trait_db_obs(self, trait_db_list): if trait_ob: self.trait_list.append((trait_ob, dataset_ob)) -def get_species_groups(): - - species_query = "SELECT SpeciesId, MenuName FROM Species" - species_ids_and_names = g.db.execute(species_query).fetchall() - - species_and_groups = [] - for species_id, species_name in species_ids_and_names: - this_species_groups = {} - this_species_groups['species'] = species_name - groups_query = "SELECT InbredSetName FROM InbredSet WHERE SpeciesId = %s" % (species_id) - groups = [group[0] for group in g.db.execute(groups_query).fetchall()] - this_species_groups['groups'] = groups - species_and_groups.append(this_species_groups) - - return species_and_groups +def get_species_groups(): + """Group each species into a group""" + _menu = {} + for species, group_name in g.db.execute( + "SELECT s.MenuName, i.InbredSetName FROM InbredSet i " + "INNER JOIN Species s ON s.SpeciesId = i.SpeciesId " + "ORDER BY i.SpeciesId ASC, i.Name ASC").fetchall(): + if _menu.get(species): + _menu = _menu[species].append(group_name) + else: + _menu[species] = [group_name] + return [{"species": key, + "groups": value} for key, value in + list(_menu.items())] -- cgit v1.2.3