Began changing references to cursor/db_conn to use sqlalchemy

Wrote function for phenotype author searches

4 files changed, 103 insertions, 49 deletions

diff --git a/wqflask/base/data_set.py b/wqflask/base/data_set.py
index 34e5eaa1..cd9e810e 100755
--- a/wqflask/base/data_set.py
+++ b/wqflask/base/data_set.py
@@ -90,9 +90,7 @@ class DataSet(object):
 
 
     def get_group(self):
-        assert self.cursor
-        self.cursor.execute(self.query)
-        self.group, self.group_id = self.cursor.fetchone()
+        self.group, self.group_id = g.db.execute(self.query).fetchone()
         if self.group == 'BXD300':
             self.group = "BXD"
         #return group
@@ -107,7 +105,7 @@ class DataSet(object):
 
         """
 
-        query_args = tuple(self.db_conn.escape_string(x) for x in (
+        query_args = tuple(escape(x) for x in (
             (self.type + "Freeze"),
             str(webqtlConfig.PUBLICTHRESH),
             self.name,
@@ -115,18 +113,22 @@ class DataSet(object):
             self.name))
         print("query_args are:", query_args)
 
-        query = '''
-                SELECT
-                        Id, Name, FullName, ShortName
-                FROM
-                        %s
-                WHERE
-                        public > %s AND
-                        (Name = "%s" OR FullName = "%s" OR ShortName = "%s")
-          ''' % (query_args)
+        print("""
+                SELECT Id, Name, FullName, ShortName
+                FROM %s
+                WHERE public > %s AND
+                     (Name = '%s' OR FullName = '%s' OR ShortName = '%s')
+          """ % (query_args))
+
+        self.id, self.name, self.fullname, self.shortname = g.db.execute("""
+                SELECT Id, Name, FullName, ShortName
+                FROM %s
+                WHERE public > %s AND
+                     (Name = '%s' OR FullName = '%s' OR ShortName = '%s')
+          """ % (query_args)).fetchone()
 
-        self.cursor.execute(query)
-        self.id, self.name, self.fullname, self.shortname = self.cursor.fetchone()
+        #self.cursor.execute(query)
+        #self.id, self.name, self.fullname, self.shortname = self.cursor.fetchone()
 
 
     #def genHTML(self, Class='c0dd'):
@@ -185,7 +187,7 @@ class PhenotypeDataSet(DataSet):
                             WHERE
                                     PublishFreeze.InbredSetId = InbredSet.Id AND
                                     PublishFreeze.Name = "%s"
-                    ''' % self.db_conn.escape_string(self.name)
+                    ''' % escape(self.name)
 
     def check_confidentiality(self):
         # (Urgently?) Need to write this
diff --git a/wqflask/base/webqtlTrait.py b/wqflask/base/webqtlTrait.py
index 1dceba08..9763e441 100755
--- a/wqflask/base/webqtlTrait.py
+++ b/wqflask/base/webqtlTrait.py
@@ -30,9 +30,9 @@ class webqtlTrait:
         self.cellid = kw.get('cellid', None)
         self.identification = kw.get('identification', 'un-named trait')
         self.group = kw.get('group', None)
-        self.haveinfo = kw.get(haveinfo, False)
-        self.sequence = kw.get(sequence, None)              # Blat sequence, available for ProbeSet
-        self.data = kw.get(data, {})
+        self.haveinfo = kw.get('haveinfo', False)
+        self.sequence = kw.get('sequence', None)              # Blat sequence, available for ProbeSet
+        self.data = kw.get('data', {})
         
         if kw.get('fullname'):
             name2 = value.split("::")
@@ -381,7 +381,7 @@ class webqtlTrait:
     #    return self.__dict__.items()
 
     def retrieveInfo(self, QTL = None):
-        assert self.dataset and self.cursor
+        assert self.dataset
         if self.dataset.type == 'Publish':
             #self.dataset.DisField = ['Name','PubMed_ID','Phenotype','Abbreviation','Authors','Title',\
             #       'Abstract', 'Journal','Volume','Pages','Month','Year','Sequence',\
@@ -434,10 +434,11 @@ class webqtlTrait:
                             Geno.Name = '%s'
                     """ % (display_fields_string, self.dataset.name, self.name)
         else: #Temp type
-            query = 'SELECT %s FROM %s WHERE Name = "%s"' % \
-                    (string.join(self.dataset.display_fields,','), self.dataset.type, self.name)
-
+            traitInfo = g.db.execute("""SELECT %s FROM %s WHERE Name = '%s'
+                                     """, (string.join(self.dataset.display_fields,','),
+                                             self.dataset.type, self.name)).fetchone()
 
+        
         self.cursor.execute(query)
         traitInfo = self.cursor.fetchone()
         if traitInfo:
diff --git a/wqflask/wqflask/do_search.py b/wqflask/wqflask/do_search.py
index bae3df08..802cbea5 100644
--- a/wqflask/wqflask/do_search.py
+++ b/wqflask/wqflask/do_search.py
@@ -3,6 +3,9 @@
 
 from __future__ import print_function, division
 
+from flask import Flask, g
+
+from MySQLdb import escape_string as escape
 from pprint import pformat as pf
 
 import sys
@@ -34,13 +37,13 @@ class DoSearch(object):
         """Executes query and returns results"""
         query = self.normalize_spaces(query)
         print("in do_search query is:", pf(query))
-        self.cursor.execute(query)
+        g.db.execute(query)
         results = self.cursor.fetchall()
         return results
 
     def escape(self, stringy):
         """Shorter name than self.db_conn.escape_string"""
-        return self.db_conn.escape_string(str(stringy))
+        return escape(str(stringy))
     
     def mescape(self, *items):
         """Multiple escape"""
@@ -153,7 +156,7 @@ class PhenotypeSearch(DoSearch):
                      'Max LRS',
                      'Max LRS Location']
 
-    def get_where_clause(self):
+    def get_fields_clause(self):
         """Generate clause for WHERE portion of query"""
 
         #Todo: Zach will figure out exactly what both these lines mean
@@ -163,15 +166,17 @@ class PhenotypeSearch(DoSearch):
 
         # This adds a clause to the query that matches the search term
         # against each field in the search_fields tuple
-        where_clause = []
+        fields_clause = []
         for field in self.search_fields:
-            where_clause.append('''%s REGEXP "%s"''' % (field, search_term))
-        where_clause = "(%s)" % ' OR '.join(where_clause)
+            fields_clause.append('''%s REGEXP "%s"''' % (field, search_term))
+        fields_clause = "(%s)" % ' OR '.join(fields_clause)
 
-        return where_clause
+        return fields_clause
 
-    def run(self):
-        """Generates and runs a simple search of a phenotype dataset"""
+    def compile_final_query(self, from_clause = '', where_clause = ''):
+        """Generates the final query string"""
+
+        from_clause = self.normalize_spaces(from_clause)
 
         #Get group information for dataset
         self.dataset.get_group()
@@ -182,12 +187,42 @@ class PhenotypeSearch(DoSearch):
                     PublishXRef.PhenotypeId = Phenotype.Id and
                     PublishXRef.PublicationId = Publication.Id and
                     PublishFreeze.Id = %s""" % (
-                        self.get_where_clause(),
+                        self.get_fields_clause(),
                         self.escape(self.dataset.group_id),
                         self.escape(self.dataset.id)))
 
-        return self.execute(query)
+        print("query is:", pf(query))
 
+        return query
+
+    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())
+
+#        self.query = """SELECT PublishXRef.Id,
+#PublishFreeze.createtime as thistable,
+#Publication.PubMed_ID as Publication_PubMed_ID,
+#Phenotype.Post_publication_description as Phenotype_Name FROM Phenotype,
+#PublishFreeze, Publication, PublishXRef WHERE (Phenotype.Post_publication_description
+#REGEXP "[[:<:]]brain[[:>:]]" OR Phenotype.Pre_publication_description REGEXP "[[:<:]]brain[[:>:]]"
+#OR Phenotype.Pre_publication_abbreviation REGEXP "[[:<:]]brain[[:>:]]"
+#OR Phenotype.Post_publication_abbreviation REGEXP "[[:<:]]brain[[:>:]]"
+#OR Phenotype.Lab_code REGEXP "[[:<:]]brain[[:>:]]"
+#OR Publication.PubMed_ID REGEXP "[[:<:]]brain[[:>:]]"
+#OR Publication.Abstract REGEXP "[[:<:]]brain[[:>:]]"
+#OR Publication.Title REGEXP "[[:<:]]brain[[:>:]]"
+#OR Publication.Authors REGEXP "[[:<:]]brain[[:>:]]"
+#OR PublishXRef.Id REGEXP "[[:<:]]brain[[:>:]]")
+#and PublishXRef.InbredSetId = 1
+#and PublishXRef.PhenotypeId = Phenotype.Id
+#and PublishXRef.PublicationId = Publication.Id
+#and PublishFreeze.Id = 1;"""
+
+
+        results = g.db.execute(self.query, no_parameters=True).fetchall()
+        print("in [df] run results are:", results)
+        return results
 
 class GenotypeSearch(DoSearch):
     """A search within a genotype dataset"""
@@ -606,6 +641,22 @@ class PvalueSearch(ProbeSetSearch):
 
         return self.execute(self.query)
     
+class AuthorSearch(PhenotypeSearch):
+    """Searches for phenotype traits with specified author(s)"""
+    
+    DoSearch.search_types["NAME"] = "AuthorSearch" 
+    
+    def run(self):
+        
+        self.search_term = [float(value) for value in self.search_term]
+        
+        self.where_clause = """ Publication.Authors LIKE %s and
+                                """ % (self.escape(self.search_term[0]))
+        
+        self.query = self.compile_final_query(where_clause = self.where_clause)
+        
+        return self.execute(self.query)
+    
 
 
 if __name__ == "__main__":
@@ -630,20 +681,20 @@ if __name__ == "__main__":
     dataset_name = "HC_M2_0606_P"
     dataset = create_dataset(db_conn, dataset_name)
     
-    cursor.execute("""
-                SELECT ProbeSet.Name as TNAME, 0 as thistable,
-                ProbeSetXRef.Mean as TMEAN, ProbeSetXRef.LRS as TLRS,
-                ProbeSetXRef.PVALUE as TPVALUE, ProbeSet.Chr_num as TCHR_NUM,
-                ProbeSet.Mb as TMB, ProbeSet.Symbol as TSYMBOL,
-                ProbeSet.name_num as TNAME_NUM
-                FROM ProbeSetXRef, ProbeSet, Geno
-                WHERE ProbeSetXRef.LRS > 99.0 and
-                ABS(ProbeSet.Mb-Geno.Mb) < 5 and
-                ProbeSetXRef.Locus = Geno.name and
-                Geno.SpeciesId = 1 and
-                ProbeSet.Chr = Geno.Chr and
-                ProbeSet.Id = ProbeSetXRef.ProbeSetId and
-                ProbeSetXRef.ProbeSetFreezeId = 112""")
+    #cursor.execute("""
+    #            SELECT ProbeSet.Name as TNAME, 0 as thistable,
+    #            ProbeSetXRef.Mean as TMEAN, ProbeSetXRef.LRS as TLRS,
+    #            ProbeSetXRef.PVALUE as TPVALUE, ProbeSet.Chr_num as TCHR_NUM,
+    #            ProbeSet.Mb as TMB, ProbeSet.Symbol as TSYMBOL,
+    #            ProbeSet.name_num as TNAME_NUM
+    #            FROM ProbeSetXRef, ProbeSet, Geno
+    #            WHERE ProbeSetXRef.LRS > 99.0 and
+    #            ABS(ProbeSet.Mb-Geno.Mb) < 5 and
+    #            ProbeSetXRef.Locus = Geno.name and
+    #            Geno.SpeciesId = 1 and
+    #            ProbeSet.Chr = Geno.Chr and
+    #            ProbeSet.Id = ProbeSetXRef.ProbeSetId and
+    #            ProbeSetXRef.ProbeSetFreezeId = 112""")
 
     #print(pf(cursor.fetchall()))
     #results = ProbeSetSearch("shh", None, dataset, cursor, db_conn).run()
diff --git a/wqflask/wqflask/search_results.py b/wqflask/wqflask/search_results.py
index 04b14e8f..52f628f6 100644
--- a/wqflask/wqflask/search_results.py
+++ b/wqflask/wqflask/search_results.py
@@ -113,7 +113,7 @@ class SearchResultPage(templatePage):
 
             print("foo locals are:", locals())
             trait_id = result[0]
-            this_trait = webqtlTrait(self.db_conn, dataset=self.dataset, name=trait_id)
+            this_trait = webqtlTrait(dataset=self.dataset, name=trait_id)
             this_trait.retrieveInfo(QTL=True)
             self.trait_list.append(this_trait)