Logger: SQL

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
-
-
-
-