Started switching to using basic sqlalchemy to handle db connection/queries

Began fixing bugs related to this fix, still in progress

8 files changed, 106 insertions, 69 deletions

diff --git a/wqflask/base/data_set.py b/wqflask/base/data_set.py
index 633f7545..015b2623 100755
--- a/wqflask/base/data_set.py
+++ b/wqflask/base/data_set.py
@@ -22,6 +22,8 @@
 
 from __future__ import print_function, division
 
+from flask import Flask, g
+
 from htmlgen import HTMLgen2 as HT
 
 import webqtlConfig
@@ -31,25 +33,28 @@ from pprint import pformat as pf
 # Used by create_database to instantiate objects
 DS_NAME_MAP = {}
 
-def create_dataset(db_conn, dataset_name):
-    cursor = db_conn.cursor()
-    cursor.execute("""
+def create_dataset(dataset_name):
+    #cursor = db_conn.cursor()
+    print("dataset_name:", dataset_name)
+    
+    dataset_type = g.db.execute("""
         SELECT DBType.Name
         FROM DBList, DBType
         WHERE DBList.Name = %s and
               DBType.Id = DBList.DBTypeId
-        """, (dataset_name))
-    print("dataset_name:", dataset_name)
-    dataset_type = cursor.fetchone()[0]
-    print("dataset_type:", pf(dataset_type))
+        """, (dataset_name)).fetchone().Name
+   
+    #dataset_type = cursor.fetchone()[0]
+    print("[blubber] dataset_type:", pf(dataset_type))
     
     dataset_ob = DS_NAME_MAP[dataset_type]
     #dataset_class = getattr(data_set, dataset_ob)
-    
+    print("dataset_ob:", dataset_ob)
     print("DS_NAME_MAP:", pf(DS_NAME_MAP))
     
     dataset_class = globals()[dataset_ob]
-    return dataset_class(dataset_name, db_conn)
+    return dataset_class(dataset_name)
+
 
 class DataSet(object):
     """
@@ -58,12 +63,12 @@ class DataSet(object):
 
     """
 
-    def __init__(self, name, db_conn):
+    def __init__(self, name):
 
         assert name
         self.name = name
-        self.db_conn = db_conn
-        self.cursor = self.db_conn.cursor()
+        #self.db_conn = db_conn
+        #self.cursor = self.db_conn.cursor()
         self.id = None
         self.type = None
         self.group = None
@@ -271,7 +276,7 @@ class GenotypeDataSet(DataSet):
     def check_confidentiality(self):
         return geno_mrna_confidentiality(self)
     
-    def get_trait_info(self, trait_list):
+    def get_trait_info(self, trait_list, species=None):
         for this_trait in trait_list:
             if not this_trait.haveinfo:
                 this_trait.retrieveInfo()
@@ -355,7 +360,7 @@ class MrnaAssayDataSet(DataSet):
                                 ProbeFreeze.InbredSetId = InbredSet.Id AND
                                 ProbeFreeze.Id = ProbeSetFreeze.ProbeFreezeId AND
                                 ProbeSetFreeze.Name = "%s"
-                ''' % self.db_conn.escape_string(self.name)
+                ''' % g.db.escape_string(self.name)
 
 
     def check_confidentiality(self):
diff --git a/wqflask/base/webqtlConfigLocal.py b/wqflask/base/webqtlConfigLocal.py
index 5aab48ac..84686234 100755
--- a/wqflask/base/webqtlConfigLocal.py
+++ b/wqflask/base/webqtlConfigLocal.py
@@ -4,12 +4,12 @@
 
 MYSQL_SERVER = 'localhost'
 DB_NAME = 'db_webqtl_zas1024'
-DB_USER = 'webqtlupd'
+DB_USER = 'webqtl'
 DB_PASSWD = 'webqtl'
 
 MYSQL_UPDSERVER = 'localhost'
 DB_UPDNAME = 'db_webqtl_zas1024'
-DB_UPDUSER = 'webqtlupd'
+DB_UPDUSER = 'webqtl'
 DB_UPDPASSWD = 'webqtl'
 
 GNROOT = '/home/zas1024/gn/'
diff --git a/wqflask/base/webqtlTrait.py b/wqflask/base/webqtlTrait.py
index cc0e2321..1dceba08 100755
--- a/wqflask/base/webqtlTrait.py
+++ b/wqflask/base/webqtlTrait.py
@@ -12,6 +12,7 @@ from utility import webqtlUtil
 
 from pprint import pformat as pf
 
+from flask import Flask, g
 
 class webqtlTrait:
     """
@@ -20,38 +21,46 @@ class webqtlTrait:
 
     """
 
-    def __init__(self, db_conn, **kw):
+    def __init__(self, **kw):
         print("in webqtlTrait")
-        self.db_conn = db_conn
-        self.cursor = self.db_conn.cursor()
-        self.dataset = None                  # database object
-        self.name = ''                  # Trait ID, ProbeSet ID, Published ID, etc.
-        self.cellid = ''
-        self.identification = 'un-named trait'
-        self.group = ''
-        self.haveinfo = 0
-        self.sequence = ''              # Blat sequence, available for ProbeSet
-        self.data = {}
-        print("foo")
-        print("kw in webqtlTrait are:", pf(kw))
-        print("printed\n\n")
-        for name, value in kw.items():
-            if self.__dict__.has_key(name):
-                setattr(self, name, value)
-            elif name == 'fullname':
-                name2 = value.split("::")
-                if len(name2) == 2:
-                    self.dataset, self.name = name2
-                elif len(name2) == 3:
-                    self.dataset, self.name, self.cellid = name2
-                else:
-                    raise KeyError, repr(value) + ' parameter format error.'
-            else:
-                raise KeyError, repr(name) + ' not a valid parameter for this class.'
+        #self.db_conn = db_conn
+        #self.cursor = self.db_conn.cursor()
+        self.dataset = kw.get('dataset', None)                  # database object
+        self.name = kw.get('name', None)                 # Trait ID, ProbeSet ID, Published ID, etc.
+        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, {})
+        
+        if kw.get('fullname'):
+            name2 = value.split("::")
+            if len(name2) == 2:
+                self.dataset, self.name = name2
+            elif len(name2) == 3:
+                self.dataset, self.name, self.cellid = name2
+                
+        #print("foo")
+        #print("kw in webqtlTrait are:", pf(kw))
+        #print("printed\n\n")
+        #for name, value in kw.items():
+        #    if self.__dict__.has_key(name):
+        #        setattr(self, name, value)
+        #    elif name == 'fullname':
+        #        name2 = value.split("::")
+        #        if len(name2) == 2:
+        #            self.dataset, self.name = name2
+        #        elif len(name2) == 3:
+        #            self.dataset, self.name, self.cellid = name2
+        #        else:
+        #            raise KeyError, repr(value) + ' parameter format error.'
+        #    else:
+        #        raise KeyError, repr(name) + ' not a valid parameter for this class.'
 
         if self.dataset and isinstance(self.dataset, basestring):
-            assert self.cursor, "Don't have a cursor"
-            self.dataset = create_dataset(self.db_conn, self.dataset)
+            #assert self.cursor, "Don't have a cursor"
+            self.dataset = create_dataset(self.dataset)
 
         #if self.dataset == None, not from a database
         print("self.dataset is:", self.dataset, type(self.dataset))
@@ -432,7 +441,7 @@ class webqtlTrait:
         self.cursor.execute(query)
         traitInfo = self.cursor.fetchone()
         if traitInfo:
-            self.haveinfo = 1
+            self.haveinfo = True
 
             #XZ: assign SQL query result to trait attributes.
             for i, field in enumerate(self.dataset.display_fields):
diff --git a/wqflask/cfg/zach_settings.py b/wqflask/cfg/zach_settings.py
index ed97f222..8d3bf4ab 100644
--- a/wqflask/cfg/zach_settings.py
+++ b/wqflask/cfg/zach_settings.py
@@ -1,2 +1,5 @@
 LOGFILE = """/tmp/flask_gn_log"""
+
 TRAP_BAD_REQUEST_ERRORS = True
+
+DB_URI = """mysql://webqtl:webqtl@localhost/db_webqtl_zas1024"""
\ No newline at end of file
diff --git a/wqflask/wqflask/do_search.py b/wqflask/wqflask/do_search.py
index 17078802..bae3df08 100644
--- a/wqflask/wqflask/do_search.py
+++ b/wqflask/wqflask/do_search.py
@@ -144,14 +144,22 @@ class PhenotypeSearch(DoSearch):
                     'Publication.Title',
                     'Publication.Authors',
                     'PublishXRef.Id')
+    
+    header_fields = ['',
+                     'Record ID',
+                     'Description',
+                     'Authors',
+                     'Year',
+                     'Max LRS',
+                     'Max LRS Location']
 
     def get_where_clause(self):
         """Generate clause for WHERE portion of query"""
 
         #Todo: Zach will figure out exactly what both these lines mean
         #and comment here
-        if "'" not in self.search_term:
-            search_term = "[[:<:]]" + self.search_term + "[[:>:]]"
+        if "'" not in self.search_term[0]:
+            search_term = "[[:<:]]" + self.search_term[0] + "[[:>:]]"
 
         # This adds a clause to the query that matches the search term
         # against each field in the search_fields tuple
@@ -195,6 +203,10 @@ class GenotypeSearch(DoSearch):
                 FROM GenoXRef, GenoFreeze, Geno """
 
     search_fields = ('Name', 'Chr')
+    
+    header_fields = ['',
+                     'Record ID',
+                     'Location']    
 
     def get_fields_clause(self):
         """Generate clause for part of the WHERE portion of query"""
@@ -203,13 +215,13 @@ class GenotypeSearch(DoSearch):
         # against each field in search_fields (above)
         fields_clause = []
         
-        if "'" not in self.search_term:
-            self.search_term = "[[:<:]]" + self.search_term + "[[:>:]]"
+        if "'" not in self.search_term[0]:
+            self.search_term = "[[:<:]]" + self.search_term[0] + "[[:>:]]"
 
         for field in self.search_fields:
             fields_clause.append('''%s REGEXP "%s"''' % ("%s.%s" % self.mescape(self.dataset.type,
-                                                                               field,
-                                                                               self.search_term)))
+                                                                               field),
+                                                                               self.search_term))
         print("hello ;where_clause is:", pf(fields_clause))
         fields_clause = "(%s)" % ' OR '.join(fields_clause)
 
diff --git a/wqflask/wqflask/search_results.py b/wqflask/wqflask/search_results.py
index c7bbdaf2..04b14e8f 100644
--- a/wqflask/wqflask/search_results.py
+++ b/wqflask/wqflask/search_results.py
@@ -83,7 +83,8 @@ class SearchResultPage(templatePage):
             self.dataset_group_ids = map(lambda x: x[2], results)
         else:
             print("self.dataset is:", pf(self.dataset))
-            self.dataset = create_dataset(self.db_conn, self.dataset)
+            # Replaces a string with an object
+            self.dataset = create_dataset(self.dataset)
             print("self.dataset is now:", pf(self.dataset))
  
         self.search()
diff --git a/wqflask/wqflask/show_trait/show_trait.py b/wqflask/wqflask/show_trait/show_trait.py
index e8ad0b1d..7060f2ea 100755
--- a/wqflask/wqflask/show_trait/show_trait.py
+++ b/wqflask/wqflask/show_trait/show_trait.py
@@ -31,16 +31,15 @@ from pprint import pformat as pf
 
 class ShowTrait(templatePage):
 
-    def __init__(self, fd):
-        self.fd = fd
+    def __init__(self, args):
+        print("in ShowTrait, args are:", args)
+        self.group = args.group
+        self.trait_id = trait_id
+        self.dataset = dataset
 
-        print("red1 fd.group:", fd.group)
-        templatePage.__init__(self, fd)
+        #assert self.openMysql(), "No database!"
 
-        print("red2 fd.group:", fd.group)
-        assert self.openMysql(), "No database!"
-
-        print("red3 fd.group:", fd.group)
+        #print("red3 fd.group:", fd.group)
         this_trait = self.get_this_trait()
 
         print("red4 fd.group:", fd.group)
@@ -183,11 +182,13 @@ class ShowTrait(templatePage):
         #if traitInfos:
         #    database, ProbeSetID, CellID = traitInfos
         #else:
-        dataset = self.fd['dataset']
-        trait_id = self.fd['trait_id']
-        cell_id = self.fd.get('CellID')
+        #dataset = self.fd['dataset']
+        #trait_id = self.fd['trait_id']
+        #cell_id = self.fd.get('CellID')
 
-        this_trait =  webqtlTrait(self.db_conn, dataset=dataset, name=trait_id, cellid=cell_id)
+        this_trait = webqtlTrait(dataset=dataset,
+                                 name=trait_id,
+                                 cellid=cell_id)
 
         ##identification, etc.
         self.fd.identification = '%s : %s' % (this_trait.dataset.shortname, trait_id)
diff --git a/wqflask/wqflask/views.py b/wqflask/wqflask/views.py
index cdc3379f..17dc42fb 100644
--- a/wqflask/wqflask/views.py
+++ b/wqflask/wqflask/views.py
@@ -7,10 +7,12 @@ import simplejson as json
 import yaml
 
 import flask
+import sqlalchemy
+#import config
 
 from wqflask import app
 
-from flask import render_template, request, make_response, Response
+from flask import render_template, request, make_response, Response, Flask, g, config
 
 from wqflask import search_results
 from wqflask.show_trait import show_trait
@@ -27,6 +29,10 @@ from pprint import pformat as pf
 #logging.basicConfig(filename="/tmp/gn_log", level=logging.INFO)
 #_log = logging.getLogger("correlation")
 
+@app.before_request
+def connect_db():
+    print("blue app.config:", app.config, pf(vars(app.config)))
+    g.db = sqlalchemy.create_engine(app.config['DB_URI'])
 
 @app.route("/")
 def index_page():
@@ -86,9 +92,9 @@ def whats_new_page():
 @app.route("/show_trait")
 def show_trait_page():
     # Here it's currently too complicated not to use an fd that is a webqtlFormData
-    fd = webqtlFormData.webqtlFormData(request.args)
-    print("stp y1:", pf(vars(fd)))
-    template_vars = show_trait.ShowTrait(fd)
+    #fd = webqtlFormData.webqtlFormData(request.args)
+    #print("stp y1:", pf(vars(fd)))
+    template_vars = show_trait.ShowTrait(request.args)
     template_vars.js_data = json.dumps(template_vars.js_data,
                                        default=json_default_handler,
                                        indent="   ",