From d9f617afba9c3881f273902447aef94755a6deee Mon Sep 17 00:00:00 2001 From: Artem Tarasov Date: Wed, 13 May 2015 11:13:05 +0300 Subject: cache datasets for each group --- wqflask/base/data_set.py | 47 ++++++++++++++++++++++++++++++++ wqflask/wqflask/database.py | 7 ++++- wqflask/wqflask/show_trait/show_trait.py | 35 +----------------------- 3 files changed, 54 insertions(+), 35 deletions(-) (limited to 'wqflask') diff --git a/wqflask/base/data_set.py b/wqflask/base/data_set.py index 1cd57b4b..40c38fe8 100755 --- a/wqflask/base/data_set.py +++ b/wqflask/base/data_set.py @@ -292,6 +292,7 @@ class DatasetGroup(object): self.incparentsf1 = False self.allsamples = None + self._datasets = None def get_specified_markers(self, markers = []): self.markers = HumanMarkers(self.name, markers) @@ -305,6 +306,52 @@ class DatasetGroup(object): self.markers = marker_class(self.name) + def datasets(self): + key = "group_dataset_menu:v1:" + self.name + print("key is:", key) + with Bench("Loading cache"): + result = Redis.get(key) + if result: + self._datasets = pickle.loads(result) + return self._datasets + + dataset_menu = [] + print("[tape4] webqtlConfig.PUBLICTHRESH:", webqtlConfig.PUBLICTHRESH) + print("[tape4] type webqtlConfig.PUBLICTHRESH:", type(webqtlConfig.PUBLICTHRESH)) + results = g.db.execute("""SELECT PublishFreeze.FullName,PublishFreeze.Name FROM + PublishFreeze,InbredSet WHERE PublishFreeze.InbredSetId = InbredSet.Id + and InbredSet.Name = %s and PublishFreeze.public > %s""", + (self.name, webqtlConfig.PUBLICTHRESH)) + for item in results.fetchall(): + dataset_menu.append(dict(tissue=None, + datasets=[item])) + + results = g.db.execute("""SELECT GenoFreeze.FullName,GenoFreeze.Name FROM GenoFreeze, + InbredSet WHERE GenoFreeze.InbredSetId = InbredSet.Id and InbredSet.Name = + %s and GenoFreeze.public > %s""", + (self.name, webqtlConfig.PUBLICTHRESH)) + for item in results.fetchall(): + dataset_menu.append(dict(tissue=None, + datasets=[item])) + + #03/09/2009: Xiaodong changed the SQL query to order by Name as requested by Rob. + tissues = g.db.execute("SELECT Id, Name FROM Tissue order by Name") + for item in tissues.fetchall(): + tissue_id, tissue_name = item + data_sets = g.db.execute('''SELECT ProbeSetFreeze.FullName,ProbeSetFreeze.Name FROM ProbeSetFreeze, ProbeFreeze, + InbredSet WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id and ProbeFreeze.TissueId = %s and + ProbeSetFreeze.public > %s and ProbeFreeze.InbredSetId = InbredSet.Id and InbredSet.Name like %s + order by ProbeSetFreeze.CreateTime desc, ProbeSetFreeze.AvgId ''', + (tissue_id, webqtlConfig.PUBLICTHRESH, "%" + self.name + "%")) + dataset_sub_menu = [item for item in data_sets.fetchall() if item] + if dataset_sub_menu: + dataset_menu.append(dict(tissue=tissue_name, + datasets=dataset_sub_menu)) + + Redis.set(key, pickle.dumps(dataset_menu, pickle.HIGHEST_PROTOCOL)) + Redis.expire(key, 60*5) + self._datasets = dataset_menu + return self._datasets def get_f1_parent_strains(self): try: diff --git a/wqflask/wqflask/database.py b/wqflask/wqflask/database.py index e55f06a7..16013a0c 100755 --- a/wqflask/wqflask/database.py +++ b/wqflask/wqflask/database.py @@ -13,6 +13,11 @@ db_session = scoped_session(sessionmaker(autocommit=False, Base = declarative_base() Base.query = db_session.query_property() +import logging + +logging.basicConfig() +logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) + def init_db(): # import all modules here that might define models so that # they will be registered properly on the metadata. Otherwise @@ -23,4 +28,4 @@ def init_db(): Base.metadata.create_all(bind=engine) print("Done creating all...") -init_db() \ No newline at end of file +init_db() diff --git a/wqflask/wqflask/show_trait/show_trait.py b/wqflask/wqflask/show_trait/show_trait.py index b28c9b76..9bce69d9 100755 --- a/wqflask/wqflask/show_trait/show_trait.py +++ b/wqflask/wqflask/show_trait/show_trait.py @@ -888,40 +888,7 @@ class ShowTrait(object): this_group = 'BXD' if this_group: - - dataset_menu = [] - print("[tape4] webqtlConfig.PUBLICTHRESH:", webqtlConfig.PUBLICTHRESH) - print("[tape4] type webqtlConfig.PUBLICTHRESH:", type(webqtlConfig.PUBLICTHRESH)) - results = g.db.execute("""SELECT PublishFreeze.FullName,PublishFreeze.Name FROM - PublishFreeze,InbredSet WHERE PublishFreeze.InbredSetId = InbredSet.Id - and InbredSet.Name = %s and PublishFreeze.public > %s""", - (this_group, webqtlConfig.PUBLICTHRESH)) - for item in results.fetchall(): - dataset_menu.append(dict(tissue=None, - datasets=[item])) - - results = g.db.execute("""SELECT GenoFreeze.FullName,GenoFreeze.Name FROM GenoFreeze, - InbredSet WHERE GenoFreeze.InbredSetId = InbredSet.Id and InbredSet.Name = - %s and GenoFreeze.public > %s""", - (this_group, webqtlConfig.PUBLICTHRESH)) - for item in results.fetchall(): - dataset_menu.append(dict(tissue=None, - datasets=[item])) - - #03/09/2009: Xiaodong changed the SQL query to order by Name as requested by Rob. - tissues = g.db.execute("SELECT Id, Name FROM Tissue order by Name") - for item in tissues.fetchall(): - tissue_id, tissue_name = item - data_sets = g.db.execute('''SELECT ProbeSetFreeze.FullName,ProbeSetFreeze.Name FROM ProbeSetFreeze, ProbeFreeze, - InbredSet WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id and ProbeFreeze.TissueId = %s and - ProbeSetFreeze.public > %s and ProbeFreeze.InbredSetId = InbredSet.Id and InbredSet.Name like %s - order by ProbeSetFreeze.CreateTime desc, ProbeSetFreeze.AvgId ''', - (tissue_id, webqtlConfig.PUBLICTHRESH, "%" + this_group + "%")) - dataset_sub_menu = [item for item in data_sets.fetchall() if item] - if dataset_sub_menu: - dataset_menu.append(dict(tissue=tissue_name, - datasets=dataset_sub_menu)) - + dataset_menu = self.dataset.group.datasets() dataset_menu_selected = None if len(dataset_menu): if this_trait and this_trait.dataset: -- cgit v1.2.3 From ee84f921331e7e68df0d57a1bc285b5f91780135 Mon Sep 17 00:00:00 2001 From: Artem Tarasov Date: Wed, 13 May 2015 13:09:38 +0300 Subject: replace multiple tissue queries with a single one --- wqflask/base/data_set.py | 28 ++++++++++++++++------------ 1 file changed, 16 insertions(+), 12 deletions(-) (limited to 'wqflask') diff --git a/wqflask/base/data_set.py b/wqflask/base/data_set.py index 40c38fe8..f5bd5cb8 100755 --- a/wqflask/base/data_set.py +++ b/wqflask/base/data_set.py @@ -29,6 +29,7 @@ import json import gzip import cPickle as pickle import itertools +from operator import itemgetter from redis import Redis Redis = Redis() @@ -335,18 +336,21 @@ class DatasetGroup(object): datasets=[item])) #03/09/2009: Xiaodong changed the SQL query to order by Name as requested by Rob. - tissues = g.db.execute("SELECT Id, Name FROM Tissue order by Name") - for item in tissues.fetchall(): - tissue_id, tissue_name = item - data_sets = g.db.execute('''SELECT ProbeSetFreeze.FullName,ProbeSetFreeze.Name FROM ProbeSetFreeze, ProbeFreeze, - InbredSet WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id and ProbeFreeze.TissueId = %s and - ProbeSetFreeze.public > %s and ProbeFreeze.InbredSetId = InbredSet.Id and InbredSet.Name like %s - order by ProbeSetFreeze.CreateTime desc, ProbeSetFreeze.AvgId ''', - (tissue_id, webqtlConfig.PUBLICTHRESH, "%" + self.name + "%")) - dataset_sub_menu = [item for item in data_sets.fetchall() if item] - if dataset_sub_menu: - dataset_menu.append(dict(tissue=tissue_name, - datasets=dataset_sub_menu)) + results = g.db.execute(''' + SELECT Tissue.Name, ProbeSetFreeze.FullName,ProbeSetFreeze.Name + FROM ProbeSetFreeze, ProbeFreeze, InbredSet, Tissue + WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id and + ProbeFreeze.TissueId = Tissue.Id and + ProbeSetFreeze.public > %s and + ProbeFreeze.InbredSetId = InbredSet.Id and + InbredSet.Name like %s + ORDER BY Tissue.Name, ProbeSetFreeze.CreateTime desc, ProbeSetFreeze.AvgId + ''', (webqtlConfig.PUBLICTHRESH, "%" + self.name + "%")) + + for tissue_name, dataset in itertools.groupby(results.fetchall(), itemgetter(0)): + dataset_sub_menu = [item[1:] for item in dataset] + dataset_menu.append(dict(tissue=tissue_name, + datasets=dataset_sub_menu)) Redis.set(key, pickle.dumps(dataset_menu, pickle.HIGHEST_PROTOCOL)) Redis.expire(key, 60*5) -- cgit v1.2.3 From 73be62c9e5ef5c4b3a87d3fcdc46f338679bf5e1 Mon Sep 17 00:00:00 2001 From: Artem Tarasov Date: Wed, 13 May 2015 14:16:14 +0300 Subject: combine three similar queries into one --- wqflask/base/data_set.py | 59 ++++++++++++++++++++++++------------------------ 1 file changed, 30 insertions(+), 29 deletions(-) (limited to 'wqflask') diff --git a/wqflask/base/data_set.py b/wqflask/base/data_set.py index f5bd5cb8..706b193d 100755 --- a/wqflask/base/data_set.py +++ b/wqflask/base/data_set.py @@ -319,38 +319,39 @@ class DatasetGroup(object): dataset_menu = [] print("[tape4] webqtlConfig.PUBLICTHRESH:", webqtlConfig.PUBLICTHRESH) print("[tape4] type webqtlConfig.PUBLICTHRESH:", type(webqtlConfig.PUBLICTHRESH)) - results = g.db.execute("""SELECT PublishFreeze.FullName,PublishFreeze.Name FROM - PublishFreeze,InbredSet WHERE PublishFreeze.InbredSetId = InbredSet.Id - and InbredSet.Name = %s and PublishFreeze.public > %s""", - (self.name, webqtlConfig.PUBLICTHRESH)) - for item in results.fetchall(): - dataset_menu.append(dict(tissue=None, - datasets=[item])) - - results = g.db.execute("""SELECT GenoFreeze.FullName,GenoFreeze.Name FROM GenoFreeze, - InbredSet WHERE GenoFreeze.InbredSetId = InbredSet.Id and InbredSet.Name = - %s and GenoFreeze.public > %s""", - (self.name, webqtlConfig.PUBLICTHRESH)) - for item in results.fetchall(): - dataset_menu.append(dict(tissue=None, - datasets=[item])) - - #03/09/2009: Xiaodong changed the SQL query to order by Name as requested by Rob. results = g.db.execute(''' - SELECT Tissue.Name, ProbeSetFreeze.FullName,ProbeSetFreeze.Name - FROM ProbeSetFreeze, ProbeFreeze, InbredSet, Tissue - WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id and - ProbeFreeze.TissueId = Tissue.Id and - ProbeSetFreeze.public > %s and - ProbeFreeze.InbredSetId = InbredSet.Id and - InbredSet.Name like %s - ORDER BY Tissue.Name, ProbeSetFreeze.CreateTime desc, ProbeSetFreeze.AvgId - ''', (webqtlConfig.PUBLICTHRESH, "%" + self.name + "%")) + (SELECT '#PublishFreeze',PublishFreeze.FullName,PublishFreeze.Name + FROM PublishFreeze,InbredSet + WHERE PublishFreeze.InbredSetId = InbredSet.Id + and InbredSet.Name = %s + and PublishFreeze.public > %s) + UNION + (SELECT '#GenoFreeze',GenoFreeze.FullName,GenoFreeze.Name + FROM GenoFreeze, InbredSet + WHERE GenoFreeze.InbredSetId = InbredSet.Id + and InbredSet.Name = %s + and GenoFreeze.public > %s) + UNION + (SELECT Tissue.Name, ProbeSetFreeze.FullName,ProbeSetFreeze.Name + FROM ProbeSetFreeze, ProbeFreeze, InbredSet, Tissue + WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id + and ProbeFreeze.TissueId = Tissue.Id + and ProbeFreeze.InbredSetId = InbredSet.Id + and InbredSet.Name like %s + and ProbeSetFreeze.public > %s + ORDER BY Tissue.Name, ProbeSetFreeze.CreateTime desc, ProbeSetFreeze.AvgId) + ''', (self.name, webqtlConfig.PUBLICTHRESH, + self.name, webqtlConfig.PUBLICTHRESH, + "%" + self.name + "%", webqtlConfig.PUBLICTHRESH)) for tissue_name, dataset in itertools.groupby(results.fetchall(), itemgetter(0)): - dataset_sub_menu = [item[1:] for item in dataset] - dataset_menu.append(dict(tissue=tissue_name, - datasets=dataset_sub_menu)) + if tissue_name in ['#PublishFreeze', '#GenoFreeze']: + for item in dataset: + dataset_menu.append(dict(tissue=None, datasets=[item[1:]])) + else: + dataset_sub_menu = [item[1:] for item in dataset] + dataset_menu.append(dict(tissue=tissue_name, + datasets=dataset_sub_menu)) Redis.set(key, pickle.dumps(dataset_menu, pickle.HIGHEST_PROTOCOL)) Redis.expire(key, 60*5) -- cgit v1.2.3 From 14a48d480c8d2cf8c2fab134d40a981813af9264 Mon Sep 17 00:00:00 2001 From: Artem Tarasov Date: Wed, 13 May 2015 15:06:09 +0300 Subject: comment out sql query logging --- wqflask/wqflask/database.py | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'wqflask') diff --git a/wqflask/wqflask/database.py b/wqflask/wqflask/database.py index 16013a0c..159c5d6c 100755 --- a/wqflask/wqflask/database.py +++ b/wqflask/wqflask/database.py @@ -13,10 +13,10 @@ db_session = scoped_session(sessionmaker(autocommit=False, Base = declarative_base() Base.query = db_session.query_property() -import logging - -logging.basicConfig() -logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) +#import logging +# +#logging.basicConfig() +#logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) def init_db(): # import all modules here that might define models so that -- cgit v1.2.3