From 85644d74643f4fef3641b1ce3c7c437b08b9ee70 Mon Sep 17 00:00:00 2001 From: Pjotr Prins Date: Thu, 23 Jun 2016 12:18:42 +0000 Subject: Logger: SQL --- wqflask/base/mrna_assay_tissue_data.py | 7 +++++-- wqflask/base/species.py | 16 ++++++++++------ wqflask/base/trait.py | 18 +++++++++++++++--- wqflask/wqflask/do_search.py | 21 ++------------------- wqflask/wqflask/gsearch.py | 5 +++++ wqflask/wqflask/show_trait/show_trait.py | 3 +++ wqflask/wqflask/update_search_results.py | 9 +++++---- 7 files changed, 45 insertions(+), 34 deletions(-) diff --git a/wqflask/base/mrna_assay_tissue_data.py b/wqflask/base/mrna_assay_tissue_data.py index 039f5d1f..5511af06 100644 --- a/wqflask/base/mrna_assay_tissue_data.py +++ b/wqflask/base/mrna_assay_tissue_data.py @@ -11,6 +11,9 @@ from MySQLdb import escape_string as escape from pprint import pformat as pf +from utility.logger import getLogger +logger = getLogger(__name__ ) + class MrnaAssayTissueData(object): def __init__(self, gene_symbols=None): @@ -54,7 +57,7 @@ class MrnaAssayTissueData(object): as x inner join TissueProbeSetXRef as t on t.Symbol = x.Symbol and t.Mean = x.maxmean; '''.format(in_clause) - + logger.sql(query) results = g.db.execute(query).fetchall() lower_symbols = [] @@ -99,6 +102,7 @@ class MrnaAssayTissueData(object): TissueProbeSetXRef.DataId = TissueProbeSetData.Id""".format(db_tools.create_in_clause(id_list)) print("TISSUE QUERY:", query) + logger.sql(query) results = g.db.execute(query).fetchall() for result in results: @@ -157,4 +161,3 @@ class MrnaAssayTissueData(object): # # if len(tissue_data.gene_symbols): # return get_symbol_values_pairs(tissue_data) - diff --git a/wqflask/base/species.py b/wqflask/base/species.py index 79d867c8..ce763fc3 100644 --- a/wqflask/base/species.py +++ b/wqflask/base/species.py @@ -10,6 +10,9 @@ from utility import Bunch from pprint import pformat as pf +from utility.logger import getLogger +logger = getLogger(__name__ ) + class TheSpecies(object): def __init__(self, dataset): self.dataset = dataset @@ -48,16 +51,17 @@ class Chromosomes(object): self.dataset = dataset self.chromosomes = collections.OrderedDict() - results = g.db.execute(""" + + query = """ Select Chr_Length.Name, Chr_Length.OrderId, Length from Chr_Length, InbredSet where Chr_Length.SpeciesId = InbredSet.SpeciesId AND - InbredSet.Name = %s + InbredSet.Name = '%s' Order by OrderId - """, self.dataset.group.name).fetchall() - #print("group: ", self.dataset.group.name) - #print("bike:", results) + """ % self.dataset.group.name + logger.sql(query) + results = g.db.execute(query).fetchall() for item in results: self.chromosomes[item.OrderId] = IndChromosome(item.Name, item.Length) @@ -120,4 +124,4 @@ class Chromosomes(object): # Testing #if __name__ == '__main__': -# foo = dict(bar=dict(length)) \ No newline at end of file +# foo = dict(bar=dict(length)) diff --git a/wqflask/base/trait.py b/wqflask/base/trait.py index 3dc4ddda..4d4d1a42 100644 --- a/wqflask/base/trait.py +++ b/wqflask/base/trait.py @@ -20,6 +20,9 @@ from pprint import pformat as pf from flask import Flask, g, request +from utility.logger import getLogger +logger = getLogger(__name__ ) + def print_mem(stage=""): mem = resource.getrusage(resource.RUSAGE_SELF).ru_maxrss print("{}: {}".format(stage, mem/1024)) @@ -259,6 +262,7 @@ class GeneralTrait(object): PublishFreeze.Id = %s """ % (self.name, self.dataset.id) + logger.sql(query) trait_info = g.db.execute(query).fetchone() @@ -278,6 +282,7 @@ class GeneralTrait(object): """ % (escape(display_fields_string), escape(self.dataset.name), escape(str(self.name))) + logger.sql(query) trait_info = g.db.execute(query).fetchone() #XZ, 05/08/2009: We also should use Geno.Id to find marker instead of just using Geno.Name # to avoid the problem of same marker name from different species. @@ -295,9 +300,11 @@ class GeneralTrait(object): """ % (escape(display_fields_string), escape(self.dataset.name), escape(self.name)) + logger.sql(query) trait_info = g.db.execute(query).fetchone() else: #Temp type query = """SELECT %s FROM %s WHERE Name = %s""" + logger.sql(query) trait_info = g.db.execute(query, (string.join(self.dataset.display_fields,','), self.dataset.type, self.name)).fetchone() @@ -368,6 +375,7 @@ class GeneralTrait(object): InbredSet.SpeciesId = Species.Id AND Species.TaxonomyId = Homologene.TaxonomyId """ % (escape(str(self.geneid)), escape(self.dataset.group.name)) + logger.sql(query) result = g.db.execute(query).fetchone() #else: # result = None @@ -429,6 +437,7 @@ class GeneralTrait(object): ProbeSet.Name = "{}" AND ProbeSetXRef.ProbeSetFreezeId ={} """.format(self.name, self.dataset.id) + logger.sql(query) trait_qtl = g.db.execute(query).fetchone() if trait_qtl: self.locus, self.lrs, self.pvalue, self.mean, self.additive= trait_qtl @@ -439,6 +448,7 @@ class GeneralTrait(object): Geno.Name = '{}' and Geno.SpeciesId = Species.Id """.format(self.dataset.group.species, self.locus) + logger.sql(query) result = g.db.execute(query).fetchone() if result: self.locus_chr = result[0] @@ -452,7 +462,7 @@ class GeneralTrait(object): if self.dataset.type == 'Publish': - trait_qtl = g.db.execute(""" + query = """ SELECT PublishXRef.Locus, PublishXRef.LRS, PublishXRef.additive FROM @@ -461,7 +471,9 @@ class GeneralTrait(object): PublishXRef.Id = %s AND PublishXRef.InbredSetId = PublishFreeze.InbredSetId AND PublishFreeze.Id =%s - """, (self.name, self.dataset.id)).fetchone() + """ % (self.name, self.dataset.id) + logger.sql(query) + trait_qtl = g.db.execute(query).fetchone() if trait_qtl: self.locus, self.lrs, self.additive = trait_qtl if self.locus: @@ -471,6 +483,7 @@ class GeneralTrait(object): Geno.Name = '{}' and Geno.SpeciesId = Species.Id """.format(self.dataset.group.species, self.locus) + logger.sql(query) result = g.db.execute(query).fetchone() if result: self.locus_chr = result[0] @@ -730,4 +743,3 @@ def get_sample_data(): # jsonable_sample_data[sample] = trait_ob.data[sample].value # #return jsonable_sample_data - diff --git a/wqflask/wqflask/do_search.py b/wqflask/wqflask/do_search.py index ff64b518..ad3eab79 100644 --- a/wqflask/wqflask/do_search.py +++ b/wqflask/wqflask/do_search.py @@ -38,7 +38,7 @@ class DoSearch(object): def execute(self, query): """Executes query and returns results""" query = self.normalize_spaces(query) - logger.debug("execute:", pf(query)) + logger.sql(query) results = g.db.execute(query, no_parameters=True).fetchall() return results @@ -108,8 +108,6 @@ class QuickMrnaAssaySearch(DoSearch): AGAINST ('%s' IN BOOLEAN MODE)) """ % (escape(self.search_term[0])) - logger.debug("final query is:", pf(query)) - return self.execute(query) @@ -175,9 +173,6 @@ class MrnaAssaySearch(DoSearch): """ % (escape(from_clause), where_clause, escape(str(self.dataset.id)))) - - #logger.debug("query is:", pf(query)) - return query def run_combined(self, from_clause = '', where_clause = ''): @@ -198,8 +193,6 @@ class MrnaAssaySearch(DoSearch): where_clause, escape(str(self.dataset.id)))) - logger.debug("final query is:", pf(query)) - return self.execute(query) def run(self): @@ -209,8 +202,6 @@ class MrnaAssaySearch(DoSearch): where_clause = self.get_where_clause() query = self.base_query + "WHERE " + where_clause + "ORDER BY ProbeSet.symbol ASC" - #logger.debug("final query is:", pf(query)) - return self.execute(query) @@ -290,8 +281,6 @@ class PhenotypeSearch(DoSearch): escape(str(self.dataset.group.id)), escape(str(self.dataset.id)))) - logger.debug("query is:", pf(query)) - return query def run_combined(self, from_clause, where_clause): @@ -313,9 +302,6 @@ class PhenotypeSearch(DoSearch): escape(str(self.dataset.group.id)), escape(str(self.dataset.id)))) - logger.debug("final query is:", pf(query)) - - return self.execute(query) def run(self): @@ -364,8 +350,6 @@ class QuickPhenotypeSearch(PhenotypeSearch): PublishXRef.InbredSetId = InbredSet.Id and InbredSet.SpeciesId = Species.Id""" % where_clause) - logger.debug("query is:", pf(query)) - return query def run(self): @@ -432,8 +416,6 @@ class GenotypeSearch(DoSearch): and GenoFreeze.Id = %s"""% (where_clause, escape(str(self.dataset.id)))) - logger.debug("query is:", pf(query)) - return query def run(self): @@ -931,6 +913,7 @@ class PvalueSearch(MrnaAssaySearch): self.query = self.compile_final_query(where_clause = self.where_clause) + logger.sql(self.query) return self.execute(self.query) class AuthorSearch(PhenotypeSearch): diff --git a/wqflask/wqflask/gsearch.py b/wqflask/wqflask/gsearch.py index 5072f931..64d638b2 100644 --- a/wqflask/wqflask/gsearch.py +++ b/wqflask/wqflask/gsearch.py @@ -7,6 +7,9 @@ from db import webqtlDatabaseFunction from utility.benchmark import Bench +from utility.logger import getLogger +logger = getLogger(__name__) + class GSearch(object): def __init__(self, kw): @@ -42,6 +45,7 @@ class GSearch(object): LIMIT 6000 """ % (self.terms) with Bench("Running query"): + logger.sql(sql) re = g.db.execute(sql).fetchall() self.trait_list = [] with Bench("Creating trait objects"): @@ -84,6 +88,7 @@ class GSearch(object): ORDER BY Species.`Name`, InbredSet.`Name`, PublishXRef.`Id` LIMIT 6000 """ % (self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms) + logger.sql(sql) re = g.db.execute(sql).fetchall() self.trait_list = [] with Bench("Creating trait objects"): diff --git a/wqflask/wqflask/show_trait/show_trait.py b/wqflask/wqflask/show_trait/show_trait.py index 7c961091..5340301a 100644 --- a/wqflask/wqflask/show_trait/show_trait.py +++ b/wqflask/wqflask/show_trait/show_trait.py @@ -406,6 +406,7 @@ class ShowTrait(object): ProbeSet.Name = '{}' AND Probe.ProbeSetId = ProbeSet.Id order by Probe.SerialOrder""".format(this_trait.dataset.name, this_trait.name) + logger.sql(query) seqs = g.db.execute(query).fetchall() for seqt in seqs: @@ -450,6 +451,7 @@ class ShowTrait(object): FROM Probe, ProbeSet WHERE ProbeSet.Name = '%s' AND Probe.ProbeSetId = ProbeSet.Id""" % (this_trait.name) #query database for number of probes associated with trait; if count > 0, set probe tool button and text + logger.sql(query) probeResult = g.db.execute(query).fetchone() if probeResult[0] > 0: self.show_probes = "True" @@ -1250,6 +1252,7 @@ def get_nearest_marker(this_trait, this_db): ORDER BY ABS( Geno.Mb - {}) LIMIT 1""".format(this_chr, this_db.group.name+"Geno", this_mb) logger.debug("query:", query) + logger.sql(query) result = g.db.execute(query).fetchall() logger.debug("result:", result) diff --git a/wqflask/wqflask/update_search_results.py b/wqflask/wqflask/update_search_results.py index 7681e7fb..68bea9d6 100644 --- a/wqflask/wqflask/update_search_results.py +++ b/wqflask/wqflask/update_search_results.py @@ -9,6 +9,9 @@ from db import webqtlDatabaseFunction from utility.benchmark import Bench +from utility.logger import getLogger +logger = getLogger(__name__) + class GSearch(object): def __init__(self, kw): @@ -45,6 +48,7 @@ class GSearch(object): LIMIT 6000 """ % (self.terms) with Bench("Running query"): + logger.sql(sql) re = g.db.execute(sql).fetchall() self.trait_list = [] with Bench("Creating trait objects"): @@ -87,6 +91,7 @@ class GSearch(object): ORDER BY Species.`Name`, InbredSet.`Name`, PublishXRef.`Id` LIMIT 6000 """ % (self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms) + logger.sql(sql) re = g.db.execute(sql).fetchall() self.trait_list = [] with Bench("Creating trait objects"): @@ -123,7 +128,3 @@ class GSearch(object): json_results = json.dumps(json_dict) return json_results - - - - -- cgit v1.2.3