From 292d177f768e8f949bc50f8896b560879aaae178 Mon Sep 17 00:00:00 2001 From: Zachary Sloan Date: Wed, 5 Dec 2012 14:33:02 -0600 Subject: Continued to make changes related to getting rid of cursor/db_conn and using simple sqlalchemy Got Pheno/MrnaAssay dataset searches working again --- misc/new_variable_names.txt | 1 + wqflask/base/data_set.py | 9 ++--- wqflask/base/webqtlTrait.py | 46 +++++++++++----------- wqflask/wqflask/correlation/CorrelationPage.py | 2 +- wqflask/wqflask/correlation/correlationFunction.py | 2 +- wqflask/wqflask/do_search.py | 10 ++--- wqflask/wqflask/search_results.py | 4 +- wqflask/wqflask/show_trait/SampleList.py | 2 +- wqflask/wqflask/show_trait/show_trait.py | 2 +- 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 -- cgit v1.2.3