Continued to make changes related to getting rid of cursor/db_conn

and using simple sqlalchemy

Got Pheno/MrnaAssay dataset searches working again

9 files changed, 38 insertions, 40 deletions

diff --git a/misc/new_variable_names.txt b/misc/new_variable_names.txt
index 2b10c07e..c11c160e 100644
--- a/misc/new_variable_names.txt
+++ b/misc/new_variable_names.txt
@@ -3,3 +3,4 @@ webqtlDataset.py -> data_set.py
 webqtlDataset (class object) -> DataSet
 database/db -> dataset/data_set
 DataEditingPage -> show_trait.py/show_trait.html
+webqtlTrait -> GeneralTrait
\ No newline at end of file
diff --git a/wqflask/base/data_set.py b/wqflask/base/data_set.py
index 7833f5c1..70b33014 100755
--- a/wqflask/base/data_set.py
+++ b/wqflask/base/data_set.py
@@ -272,7 +272,7 @@ class GenotypeDataSet(DataSet):
                 WHERE
                         GenoFreeze.InbredSetId = InbredSet.Id AND
                         GenoFreeze.Name = "%s"
-                ''' % self.db_conn.escape_string(self.name)
+                ''' % escape(self.name)
 
     def check_confidentiality(self):
         return geno_mrna_confidentiality(self)
@@ -425,13 +425,12 @@ class MrnaAssayDataSet(DataSet):
     where ProbeSetXRef.ProbeSetFreezeId = %s and
     ProbeSet.Id = ProbeSetXRef.ProbeSetId and
     ProbeSet.Name = '%s'
-            """ % (self.db_conn.escape_string(str(this_trait.dataset.id)),
-                   self.db_conn.escape_string(this_trait.name)))
+            """ % (escape(str(this_trait.dataset.id)),
+                   escape(this_trait.name)))
 
             print("query is:", pf(query))
 
-            self.cursor.execute(query)
-            result = self.cursor.fetchone()
+            result = g.db.execute(query).fetchone()
 
             if result:
                 if result[0]:
diff --git a/wqflask/base/webqtlTrait.py b/wqflask/base/webqtlTrait.py
index dec5fa00..5367b41f 100755
--- a/wqflask/base/webqtlTrait.py
+++ b/wqflask/base/webqtlTrait.py
@@ -10,6 +10,7 @@ from data_set import create_dataset
 from dbFunction import webqtlDatabaseFunction
 from utility import webqtlUtil
 
+from MySQLdb import escape_string as escape
 from pprint import pformat as pf
 
 from flask import Flask, g
@@ -40,9 +41,7 @@ class GeneralTrait:
                 self.dataset, self.name, self.cellid = name2
                 
         #if self.dataset and isinstance(self.dataset, basestring):
-        self.dataset = create_dataset(self.dataset)
-        
-        
+        self.dataset = create_dataset(self.dataset.name)
 
         print("self.dataset is:", self.dataset, type(self.dataset))
         #if self.dataset:
@@ -367,7 +366,7 @@ class GeneralTrait:
     def retrieve_info(self, QTL=False):
         assert self.dataset, "Dataset doesn't exist"
         if self.dataset.type == 'Publish':
-            traitInfo = g.db.execute("""
+            query = """
                     SELECT
                             PublishXRef.Id, Publication.PubMed_ID,
                             Phenotype.Pre_publication_description, Phenotype.Post_publication_description, Phenotype.Original_description,
@@ -384,47 +383,46 @@ class GeneralTrait:
                             Phenotype.Id = PublishXRef.PhenotypeId AND
                             Publication.Id = PublishXRef.PublicationId AND
                             PublishXRef.InbredSetId = PublishFreeze.InbredSetId AND
-                            PublishFreeze.Id =%s
-                    """, (self.name, self.dataset.id)).fetchone()
+                            PublishFreeze.Id = %s
+                    """ % (self.name, self.dataset.id)
+            traitInfo = g.db.execute(query).fetchone()
         #XZ, 05/08/2009: Xiaodong add this block to use ProbeSet.Id to find the probeset instead of just using ProbeSet.Name
         #XZ, 05/08/2009: to avoid the problem of same probeset name from different platforms.
         elif self.dataset.type == 'ProbeSet':
-            display_fields_string = ',ProbeSet.'.join(self.dataset.display_fields)
+            display_fields_string = ', ProbeSet.'.join(self.dataset.display_fields)
             display_fields_string = 'ProbeSet.' + display_fields_string
-            traitInfo = g.db.execute("""
+            query = """
                     SELECT %s
                     FROM ProbeSet, ProbeSetFreeze, ProbeSetXRef
                     WHERE
                             ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND
                             ProbeSetXRef.ProbeSetId = ProbeSet.Id AND
-                            ProbeSetFreeze.Name = %s AND
-                            ProbeSet.Name = %s
-                    """, (display_fields_string, self.dataset.name, self.name)).fetchone()
+                            ProbeSetFreeze.Name = '%s' AND
+                            ProbeSet.Name = '%s'
+                    """ % (display_fields_string, self.dataset.name, self.name)
+            traitInfo = g.db.execute(query).fetchone()
+            print("traitInfo is: ", pf(traitInfo))
         #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.
         elif self.dataset.type == 'Geno':
             display_fields_string = string.join(self.dataset.display_fields,',Geno.')
             display_fields_string = 'Geno.' + display_fields_string
-            traitInfo = g.db.execute("""
+            query = """
                     SELECT %s
                     FROM Geno, GenoFreeze, GenoXRef
                     WHERE
                             GenoXRef.GenoFreezeId = GenoFreeze.Id AND
                             GenoXRef.GenoId = Geno.Id AND
-                            GenoFreeze.Name = %s AND
-                            Geno.Name = %s
-                    """, (display_fields_string, self.dataset.name, self.name)).fetchone()
+                            GenoFreeze.Name = '%s' AND
+                            Geno.Name = '%s'
+                    """ % (display_fields_string, self.dataset.name, self.name)
+            traitInfo = g.db.execute(query).fetchone()
+            print("traitInfo is: ", pf(traitInfo))
         else: #Temp type
-            traitInfo = g.db.execute("""SELECT %s FROM %s WHERE Name = %s
-                                     """, (string.join(self.dataset.display_fields,','),
-                                             self.dataset.type, self.name)).fetchone()
-            
-        query = """SELECT %s FROM %s WHERE Name = %s
+            query = """SELECT %s FROM %s WHERE Name = %s
                                      """ % (string.join(self.dataset.display_fields,','),
-                                             self.dataset.type, self.name)
-            
-        print("query is:", pf(query))
-        print("traitInfo is: ", pf(traitInfo))
+                                            self.dataset.type, self.name)
+            traitInfo = g.db.execute(query).fetchone()
 
 
         #self.cursor.execute(query)
diff --git a/wqflask/wqflask/correlation/CorrelationPage.py b/wqflask/wqflask/correlation/CorrelationPage.py
index 8af30d1e..f1dd96ef 100644
--- a/wqflask/wqflask/correlation/CorrelationPage.py
+++ b/wqflask/wqflask/correlation/CorrelationPage.py
@@ -46,7 +46,7 @@ import reaper
 from base import webqtlConfig
 from utility.THCell import THCell
 from utility.TDCell import TDCell
-from base.webqtlTrait import webqtlTrait
+from base.webqtlTrait import GeneralTrait
 from base.data_set import create_dataset
 from base.templatePage import templatePage
 from utility import webqtlUtil
diff --git a/wqflask/wqflask/correlation/correlationFunction.py b/wqflask/wqflask/correlation/correlationFunction.py
index 4d62a468..8638cb1e 100644
--- a/wqflask/wqflask/correlation/correlationFunction.py
+++ b/wqflask/wqflask/correlation/correlationFunction.py
@@ -31,7 +31,7 @@ import pp
 import string
 
 from utility import webqtlUtil
-from base.webqtlTrait import webqtlTrait
+from base.webqtlTrait import GeneralTrait
 from dbFunction import webqtlDatabaseFunction
 
 
diff --git a/wqflask/wqflask/do_search.py b/wqflask/wqflask/do_search.py
index 2094ed14..4301fb50 100644
--- a/wqflask/wqflask/do_search.py
+++ b/wqflask/wqflask/do_search.py
@@ -35,7 +35,7 @@ class DoSearch(object):
         """Executes query and returns results"""
         query = self.normalize_spaces(query)
         print("in do_search query is:", pf(query))
-        results = g.db.execute(query).fetchall()
+        results = g.db.execute(query, no_parameters=True).fetchall()
         #results = self.cursor.fetchall()
         return results
 
@@ -167,7 +167,7 @@ class PhenotypeSearch(DoSearch):
         fields_clause = []
         for field in self.search_fields:
             fields_clause.append('''%s REGEXP "%s"''' % (field, search_term))
-        fields_clause = "(%s)" % ' OR '.join(fields_clause)
+        fields_clause = "(%s) and " % ' OR '.join(fields_clause)
 
         return fields_clause
 
@@ -198,7 +198,7 @@ class PhenotypeSearch(DoSearch):
     def run(self):
         """Generates and runs a simple search of a phenotype dataset"""
 
-        self.query = self.compile_final_query(where_clause = self.get_fields_clause())
+        query = self.compile_final_query(where_clause = self.get_fields_clause())
 
 #        self.query = """SELECT PublishXRef.Id,
 #PublishFreeze.createtime as thistable,
@@ -220,7 +220,7 @@ class PhenotypeSearch(DoSearch):
 #and PublishFreeze.Id = 1;"""
 
 
-        results = g.db.execute(self.query, no_parameters=True).fetchall()
+        results = self.execute(query)
         print("in [df] run results are:", results)
         return results
 
@@ -272,7 +272,7 @@ class GenotypeSearch(DoSearch):
                     Geno.Id = GenoXRef.GenoId and
                     GenoXRef.GenoFreezeId = GenoFreeze.Id and
                     GenoFreeze.Id = %s"""% (where_clause,
-                                            escape(self.dataset.id)))
+                                            escape(str(self.dataset.id))))
 
         print("query is:", pf(query))
 
diff --git a/wqflask/wqflask/search_results.py b/wqflask/wqflask/search_results.py
index efa1c5cc..cd478110 100644
--- a/wqflask/wqflask/search_results.py
+++ b/wqflask/wqflask/search_results.py
@@ -30,7 +30,7 @@ from base import webqtlConfig
 from utility.THCell import THCell
 from utility.TDCell import TDCell
 from base.data_set import create_dataset
-from base.webqtlTrait import webqtlTrait
+from base.webqtlTrait import GeneralTrait
 from base.templatePage import templatePage
 from wqflask import parser
 from wqflask import do_search
@@ -113,7 +113,7 @@ class SearchResultPage(templatePage):
 
             print("foo locals are:", locals())
             trait_id = result[0]
-            this_trait = webqtlTrait(dataset=self.dataset, name=trait_id)
+            this_trait = GeneralTrait(dataset=self.dataset, name=trait_id)
             this_trait.retrieve_info(QTL=True)
             self.trait_list.append(this_trait)
 
diff --git a/wqflask/wqflask/show_trait/SampleList.py b/wqflask/wqflask/show_trait/SampleList.py
index df0dc61e..25877521 100644
--- a/wqflask/wqflask/show_trait/SampleList.py
+++ b/wqflask/wqflask/show_trait/SampleList.py
@@ -2,7 +2,7 @@ from __future__ import absolute_import, print_function, division
 
 from base import webqtlCaseData
 from utility import webqtlUtil, Plot, Bunch
-from base.webqtlTrait import webqtlTrait
+from base.webqtlTrait import GeneralTrait
 
 from pprint import pformat as pf
 
diff --git a/wqflask/wqflask/show_trait/show_trait.py b/wqflask/wqflask/show_trait/show_trait.py
index 7060f2ea..aef9219f 100755
--- a/wqflask/wqflask/show_trait/show_trait.py
+++ b/wqflask/wqflask/show_trait/show_trait.py
@@ -13,7 +13,7 @@ from base import webqtlConfig
 from base import webqtlCaseData
 from wqflask.show_trait.SampleList import SampleList
 from utility import webqtlUtil, Plot, Bunch
-from base.webqtlTrait import webqtlTrait
+from base.webqtlTrait import GeneralTrait
 from dbFunction import webqtlDatabaseFunction
 from base.templatePage import templatePage
 from basicStatistics import BasicStatisticsFunctions