diff options
Diffstat (limited to 'wqflask')
| -rw-r--r-- | wqflask/base/data_set.py | 45 | ||||
| -rw-r--r-- | wqflask/db/call.py | 8 | ||||
| -rw-r--r-- | wqflask/utility/logger.py | 4 |
3 files changed, 33 insertions, 24 deletions
diff --git a/wqflask/base/data_set.py b/wqflask/base/data_set.py index a75b517f..a50f5235 100644 --- a/wqflask/base/data_set.py +++ b/wqflask/base/data_set.py @@ -51,7 +51,7 @@ from maintenance import get_group_samplelists from MySQLdb import escape_string as escape from pprint import pformat as pf from db.gn_server import menu_main -from db.call import fetchall +from db.call import fetchall,fetchone,fetch1 from utility.tools import USE_GN_SERVER, USE_REDIS from utility.logger import getLogger @@ -133,7 +133,6 @@ def create_datasets_list(): for dataset_type in type_dict: query = "SELECT Name FROM {}".format(type_dict[dataset_type]) - raise Exception("HELL") for result in fetchall(query): #The query at the beginning of this function isn't #necessary here, but still would rather just reuse @@ -264,7 +263,7 @@ class DatasetGroup(object): def __init__(self, dataset): """This sets self.group and self.group_id""" #logger.debug("DATASET NAME2:", dataset.name) - self.name, self.id = g.db.execute(dataset.query_for_group).fetchone() + self.name, self.id = fetchone(dataset.query_for_group) if self.name == 'BXD300': self.name = "BXD" @@ -297,17 +296,17 @@ class DatasetGroup(object): dataset_menu = [] logger.debug("[tape4] webqtlConfig.PUBLICTHRESH:", webqtlConfig.PUBLICTHRESH) logger.debug("[tape4] type webqtlConfig.PUBLICTHRESH:", type(webqtlConfig.PUBLICTHRESH)) - results = g.db.execute(''' + the_results = fetchall(''' (SELECT '#PublishFreeze',PublishFreeze.FullName,PublishFreeze.Name FROM PublishFreeze,InbredSet WHERE PublishFreeze.InbredSetId = InbredSet.Id - and InbredSet.Name = %s + and InbredSet.Name = '%s' and PublishFreeze.public > %s) UNION (SELECT '#GenoFreeze',GenoFreeze.FullName,GenoFreeze.Name FROM GenoFreeze, InbredSet WHERE GenoFreeze.InbredSetId = InbredSet.Id - and InbredSet.Name = %s + and InbredSet.Name = '%s' and GenoFreeze.public > %s) UNION (SELECT Tissue.Name, ProbeSetFreeze.FullName,ProbeSetFreeze.Name @@ -318,11 +317,9 @@ class DatasetGroup(object): and InbredSet.Name like %s and ProbeSetFreeze.public > %s ORDER BY Tissue.Name, ProbeSetFreeze.CreateTime desc, ProbeSetFreeze.AvgId) - ''', (self.name, webqtlConfig.PUBLICTHRESH, + ''' % (self.name, webqtlConfig.PUBLICTHRESH, self.name, webqtlConfig.PUBLICTHRESH, - "%" + self.name + "%", webqtlConfig.PUBLICTHRESH)) - - the_results = results.fetchall() + "'" + self.name + "'", webqtlConfig.PUBLICTHRESH)) #for tissue_name, dataset in itertools.groupby(the_results, itemgetter(0)): for dataset_item in the_results: @@ -485,14 +482,14 @@ class DataSet(object): self.name, self.name)) - self.id, self.name, self.fullname, self.shortname, self.data_scale, self.tissue = g.db.execute(""" + self.id, self.name, self.fullname, self.shortname, self.data_scale, self.tissue = fetchone(""" SELECT ProbeSetFreeze.Id, ProbeSetFreeze.Name, ProbeSetFreeze.FullName, ProbeSetFreeze.ShortName, ProbeSetFreeze.DataScale, Tissue.Name FROM ProbeSetFreeze, ProbeFreeze, Tissue WHERE ProbeSetFreeze.public > %s AND ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id AND ProbeFreeze.TissueId = Tissue.Id AND (ProbeSetFreeze.Name = '%s' OR ProbeSetFreeze.FullName = '%s' OR ProbeSetFreeze.ShortName = '%s') - """ % (query_args)).fetchone() + """ % (query_args)) else: query_args = tuple(escape(x) for x in ( (self.type + "Freeze"), @@ -502,12 +499,12 @@ class DataSet(object): self.name)) self.tissue = "N/A" - self.id, self.name, self.fullname, self.shortname = g.db.execute(""" + self.id, self.name, self.fullname, self.shortname = fetchone(""" SELECT Id, Name, FullName, ShortName FROM %s WHERE public > %s AND (Name = '%s' OR FullName = '%s' OR ShortName = '%s') - """ % (query_args)).fetchone() + """ % (query_args)) except TypeError: logger.debug("Dataset {} is not yet available in GeneNetwork.".format(self.name)) @@ -529,6 +526,7 @@ class DataSet(object): and Strain.SpeciesId=Species.Id and Species.name = '{}' """.format(create_in_clause(self.samplelist), *mescape(self.group.species)) + logger.sql(query) results = dict(g.db.execute(query).fetchall()) sample_ids = [results[item] for item in self.samplelist] @@ -579,6 +577,7 @@ class DataSet(object): #logger.debug("trait data query: ", query) + logger.sql(query) results = g.db.execute(query).fetchall() #logger.debug("query results:", results) trait_sample_data.append(results) @@ -663,6 +662,7 @@ class PhenotypeDataSet(DataSet): where PublishFreeze.InbredSetId=PublishXRef.InbredSetId and PublishFreeze.Id = {} """.format(escape(str(self.id))) + logger.sql(query) results = g.db.execute(query).fetchall() trait_data = {} for trait in results: @@ -754,6 +754,7 @@ class PhenotypeDataSet(DataSet): Order BY Strain.Name """ + logger.sql(query) results = g.db.execute(query, (trait, self.id)).fetchall() return results @@ -801,6 +802,7 @@ class GenotypeDataSet(DataSet): where GenoXRef.GenoId = Geno.Id and GenoFreezeId = {} """.format(escape(str(self.id))) + logger.sql(query) results = g.db.execute(query).fetchall() trait_data = {} for trait in results: @@ -845,6 +847,7 @@ class GenotypeDataSet(DataSet): Order BY Strain.Name """ + logger.sql(query) results = g.db.execute(query, (webqtlDatabaseFunction.retrieve_species_id(self.group.name), trait, self.name)).fetchall() @@ -927,6 +930,7 @@ class MrnaAssayDataSet(DataSet): where ProbeSetXRef.ProbeSetId = ProbeSet.Id and ProbeSetFreezeId = {} """.format(escape(str(self.id))) + logger.sql(query) results = g.db.execute(query).fetchall() trait_data = {} for trait in results: @@ -996,7 +1000,7 @@ class MrnaAssayDataSet(DataSet): escape(this_trait.name))) #logger.debug("query is:", pf(query)) - + logger.sql(query) result = g.db.execute(query).fetchone() mean = result[0] if result else 0 @@ -1018,6 +1022,7 @@ class MrnaAssayDataSet(DataSet): Geno.Name = '{}' and Geno.SpeciesId = Species.Id """.format(species, this_trait.locus) + logger.sql(query) result = g.db.execute(query).fetchone() if result: @@ -1053,6 +1058,7 @@ class MrnaAssayDataSet(DataSet): ProbeSet.Name = %s ProbeSetFreeze.Name = %s """ % (escape(self.name), escape(self.dataset.name)) + logger.sql(query) results = g.db.execute(query).fetchone() return results[0] @@ -1073,6 +1079,7 @@ class MrnaAssayDataSet(DataSet): Order BY Strain.Name """ % (escape(trait), escape(self.name)) + logger.sql(query) results = g.db.execute(query).fetchall() #logger.debug("RETRIEVED RESULTS HERE:", results) return results @@ -1085,6 +1092,7 @@ class MrnaAssayDataSet(DataSet): where ProbeSetXRef.ProbeSetFreezeId = %s and ProbeSetXRef.ProbeSetId=ProbeSet.Id; """ % (column_name, escape(str(self.id))) + logger.sql(query) results = g.db.execute(query).fetchall() return dict(results) @@ -1121,7 +1129,9 @@ class TempDataSet(DataSet): return desc def get_desc(self): - g.db.execute('SELECT description FROM Temp WHERE Name=%s', self.name) + query = 'SELECT description FROM Temp WHERE Name=%s' % self.name + logger.sql(query) + g.db.execute(query) desc = g.db.fetchone()[0] desc = self.handle_pca(desc) return desc @@ -1153,6 +1163,7 @@ class TempDataSet(DataSet): Strain.Name """ % escape(trait.name) + logger.sql(query) results = g.db.execute(query).fetchall() @@ -1162,7 +1173,7 @@ def geno_mrna_confidentiality(ob): query = '''SELECT Id, Name, FullName, confidentiality, AuthorisedUsers FROM %s WHERE Name = %%s''' % (dataset_table) - + logger.sql(query) result = g.db.execute(query, ob.name) (dataset_id, diff --git a/wqflask/db/call.py b/wqflask/db/call.py index 4cea7e66..194a7650 100644 --- a/wqflask/db/call.py +++ b/wqflask/db/call.py @@ -11,7 +11,7 @@ from utility.benchmark import Bench from utility.logger import getLogger logger = getLogger(__name__ ) -from inspect import stack +# from inspect import stack def fetch1(query, path=None, func=None): """Fetch one result using either a SQL query or the URI path to @@ -37,8 +37,7 @@ original fetchone, but with logging) def helper(query): res = g.db.execute(query) return res.fetchone() - callername = stack()[2][3] - return logger.sql(callername, query, helper) + return logger.sql(query, helper) def fetchall(query): """Return row iterator by calling SQL directly (the @@ -49,8 +48,7 @@ original fetchall, but with logging) def helper(query): res = g.db.execute(query) return res.fetchall() - callername = stack()[2][3] - return logger.sql(callername, query, helper) + return logger.sql(query, helper) def gn_server(path): """Return JSON record by calling GN_SERVER diff --git a/wqflask/utility/logger.py b/wqflask/utility/logger.py index ff53977e..c62ea2fe 100644 --- a/wqflask/utility/logger.py +++ b/wqflask/utility/logger.py @@ -77,10 +77,10 @@ class GNLogger: if self.logger.getEffectiveLevel() < 20: self.collectf(self.logger.debug,*args) - def sql(self, description, sqlcommand, fun = None): + def sql(self, sqlcommand, fun = None): """Log SQL command, optionally invoking a timed fun""" if LOG_SQL: - self.info(description,sqlcommand) + self.info(stack()[1][3],sqlcommand) if fun: result = fun(sqlcommand) if LOG_SQL: |