diff options
Diffstat (limited to 'wqflask/base')
| -rwxr-xr-x | wqflask/base/data_set.py | 221 | ||||
| -rwxr-xr-x | wqflask/base/trait.py (renamed from wqflask/base/webqtlTrait.py) | 445 |
2 files changed, 441 insertions, 225 deletions
diff --git a/wqflask/base/data_set.py b/wqflask/base/data_set.py index 70b33014..68f5e5ed 100755 --- a/wqflask/base/data_set.py +++ b/wqflask/base/data_set.py @@ -21,12 +21,16 @@ # This module is used by GeneNetwork project (www.genenetwork.org) from __future__ import print_function, division +import os from flask import Flask, g from htmlgen import HTMLgen2 as HT +import reaper + import webqtlConfig +from utility import webqtlUtil from MySQLdb import escape_string as escape from pprint import pformat as pf @@ -57,6 +61,74 @@ def create_dataset(dataset_name): return dataset_class(dataset_name) +class DatasetGroup(object): + """ + Each group has multiple datasets; each species has multiple groups. + + For example, Mouse has multiple groups (BXD, BXA, etc), and each group + has multiple datasets associated with it. + + """ + def __init__(self, dataset): + """This sets self.group and self.group_id""" + self.name, self.group_id = g.db.execute(dataset.query).fetchone() + if self.name == 'BXD300': + self.name = "BXD" + + self.incparentsf1 = False + + + #def read_genotype(self): + # self.read_genotype_file() + # + # if not self.genotype: # Didn'd succeed, so we try method 2 + # self.read_genotype_data() + + def read_genotype_file(self): + '''read genotype from .geno file instead of database''' + #if self.group == 'BXD300': + # self.group = 'BXD' + # + #assert self.group, "self.group needs to be set" + + #genotype_1 is Dataset Object without parents and f1 + #genotype_2 is Dataset Object with parents and f1 (not for intercross) + + self.genotype_1 = reaper.Dataset() + + # reaper barfs on unicode filenames, so here we ensure it's a string + full_filename = str(os.path.join(webqtlConfig.GENODIR, self.name + '.geno')) + self.genotype_1.read(full_filename) + + print("Got to after read") + + try: + # NL, 07/27/2010. ParInfo has been moved from webqtlForm.py to webqtlUtil.py; + _f1, _f12, _mat, _pat = webqtlUtil.ParInfo[self.name] + except KeyError: + _f1 = _f12 = _mat = _pat = None + + self.genotype_2 = self.genotype_1 + if self.genotype_1.type == "group" and _mat and _pat: + self.genotype_2 = self.genotype_1.add(Mat=_mat, Pat=_pat) #, F1=_f1) + + #determine default genotype object + if self.incparentsf1 and self.genotype_1.type != "intercross": + self.genotype = self.genotype_2 + else: + self.incparentsf1 = 0 + self.genotype = self.genotype_1 + + self.samplelist = list(self.genotype.prgy) + self.f1list = [] + self.parlist = [] + + if _f1 and _f12: + self.f1list = [_f1, _f12] + if _mat and _pat: + self.parlist = [_mat, _pat] + + class DataSet(object): """ DataSet class defines a dataset in webqtl, can be either Microarray, @@ -70,27 +142,35 @@ class DataSet(object): self.name = name self.id = None self.type = None - self.group = None self.setup() self.check_confidentiality() self.retrieve_name() - self.get_group() + self.group = DatasetGroup(self) # sets self.group and self.group_id + + + def get_desc(self): + """Gets overridden later, at least for Temp...used by trait's get_given_name""" + return None # Delete this eventually @property def riset(): Weve_Renamed_This_As_Group + + + #@property + #def group(self): + # if not self._group: + # self.get_group() + # + # return self._group + - def get_group(self): - self.group, self.group_id = g.db.execute(self.query).fetchone() - if self.group == 'BXD300': - self.group = "BXD" - #return group def retrieve_name(self): @@ -176,7 +256,7 @@ class PhenotypeDataSet(DataSet): self.type = 'Publish' - self.query = ''' + self.query_for_group = ''' SELECT InbredSet.Name, InbredSet.Id FROM @@ -239,7 +319,29 @@ class PhenotypeDataSet(DataSet): this_trait.LRS_score_repr = LRS_score_repr = '%3.1f' % this_trait.lrs this_trait.LRS_score_value = LRS_score_value = this_trait.lrs - this_trait.LRS_location_repr = LRS_location_repr = 'Chr %s: %.4f Mb' % (LRS_Chr, float(LRS_Mb) ) + this_trait.LRS_location_repr = LRS_location_repr = 'Chr %s: %.4f Mb' % (LRS_Chr, float(LRS_Mb)) + + def retrieve_sample_data(self, trait): + query = """ + SELECT + Strain.Name, PublishData.value, PublishSE.error, NStrain.count, PublishData.Id + FROM + (PublishData, Strain, PublishXRef, PublishFreeze) + left join PublishSE on + (PublishSE.DataId = PublishData.Id AND PublishSE.StrainId = PublishData.StrainId) + left join NStrain on + (NStrain.DataId = PublishData.Id AND + NStrain.StrainId = PublishData.StrainId) + WHERE + PublishXRef.InbredSetId = PublishFreeze.InbredSetId AND + PublishData.Id = PublishXRef.DataId AND PublishXRef.Id = %s AND + PublishFreeze.Id = %d AND PublishData.StrainId = Strain.Id + Order BY + Strain.Name + """ % (self.trait.name, self.id) + results = g.db.execute(query).fetchall() + return results + class GenotypeDataSet(DataSet): DS_NAME_MAP['Geno'] = 'GenotypeDataSet' @@ -297,6 +399,26 @@ class GenotypeDataSet(DataSet): this_trait.location_repr = 'Chr%s: %.4f' % (this_trait.chr, float(this_trait.mb) ) this_trait.location_value = trait_location_value + + def retrieve_sample_data(self, trait): + query = """ + SELECT + Strain.Name, GenoData.value, GenoSE.error, GenoData.Id + FROM + (GenoData, GenoFreeze, Strain, Geno, GenoXRef) + left join GenoSE on + (GenoSE.DataId = GenoData.Id AND GenoSE.StrainId = GenoData.StrainId) + WHERE + Geno.SpeciesId = %s AND Geno.Name = '%s' AND GenoXRef.GenoId = Geno.Id AND + GenoXRef.GenoFreezeId = GenoFreeze.Id AND + GenoFreeze.Name = '%s' AND + GenoXRef.DataId = GenoData.Id AND + GenoData.StrainId = Strain.Id + Order BY + Strain.Name + """ % (webqtlDatabaseFunction.retrieve_species_id(self.group), trait.name, self.name) + results = g.db.execute(query).fetchall() + return results class MrnaAssayDataSet(DataSet): @@ -476,6 +598,42 @@ class MrnaAssayDataSet(DataSet): this_trait.LRS_score_repr = LRS_score_repr = '%3.1f' % this_trait.lrs this_trait.LRS_score_value = LRS_score_value = this_trait.lrs this_trait.LRS_location_repr = LRS_location_repr = 'Chr %s: %.4f Mb' % (LRS_Chr, float(LRS_Mb) ) + + def get_sequence(self): + query = """ + SELECT + ProbeSet.BlatSeq + FROM + ProbeSet, ProbeSetFreeze, ProbeSetXRef + WHERE + ProbeSet.Id=ProbeSetXRef.ProbeSetId and + ProbeSetFreeze.Id = ProbeSetXRef.ProbSetFreezeId and + ProbeSet.Name = %s + ProbeSetFreeze.Name = %s + """ % (escape(self.name), escape(self.dataset.name)) + results = g.db.execute(query).fetchone() + + return results[0] + + def retrieve_sample_data(self, trait): + query = """ + SELECT + Strain.Name, ProbeSetData.value, ProbeSetSE.error, ProbeSetData.Id + FROM + (ProbeSetData, ProbeSetFreeze, Strain, ProbeSet, ProbeSetXRef) + left join ProbeSetSE on + (ProbeSetSE.DataId = ProbeSetData.Id AND ProbeSetSE.StrainId = ProbeSetData.StrainId) + WHERE + ProbeSet.Name = '%s' AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND + ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND + ProbeSetFreeze.Name = '%s' AND + ProbeSetXRef.DataId = ProbeSetData.Id AND + ProbeSetData.StrainId = Strain.Id + Order BY + Strain.Name + """ % (escape(trait.name), escape(self.name)) + results = g.db.execute(query).fetchall() + return results class TempDataSet(DataSet): @@ -497,6 +655,51 @@ class TempDataSet(DataSet): self.id = 1 self.fullname = 'Temporary Storage' self.shortname = 'Temp' + + + @staticmethod + def handle_pca(desc): + if 'PCA' in desc: + # Todo: Modernize below lines + desc = desc[desc.rindex(':')+1:].strip() + else: + desc = desc[:desc.index('entered')].strip() + return desc + + def get_desc(self): + g.db.execute('SELECT description FROM Temp WHERE Name=%s', self.name) + desc = g.db.fetchone()[0] + desc = self.handle_pca(desc) + return desc + + def get_group(self): + self.cursor.execute(""" + SELECT + InbredSet.Name, InbredSet.Id + FROM + InbredSet, Temp + WHERE + Temp.InbredSetId = InbredSet.Id AND + Temp.Name = "%s" + """, self.name) + self.group, self.group_id = self.cursor.fetchone() + #return self.group + + def retrieve_sample_data(self, trait): + query = """ + SELECT + Strain.Name, TempData.value, TempData.SE, TempData.NStrain, TempData.Id + FROM + TempData, Temp, Strain + WHERE + TempData.StrainId = Strain.Id AND + TempData.Id = Temp.DataId AND + Temp.name = '%s' + Order BY + Strain.Name + """ % escape(trait.name) + + results = g.db.execute(query).fetchall() def geno_mrna_confidentiality(ob): diff --git a/wqflask/base/webqtlTrait.py b/wqflask/base/trait.py index 5367b41f..d3753fc1 100755 --- a/wqflask/base/webqtlTrait.py +++ b/wqflask/base/trait.py @@ -28,7 +28,7 @@ class GeneralTrait: 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.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', {}) @@ -41,28 +41,28 @@ class GeneralTrait: self.dataset, self.name, self.cellid = name2 #if self.dataset and isinstance(self.dataset, basestring): - self.dataset = create_dataset(self.dataset.name) + self.dataset = create_dataset(self.dataset) print("self.dataset is:", self.dataset, type(self.dataset)) #if self.dataset: - self.dataset.get_group() + #self.dataset.get_group() - if self.dataset.type == "Temp": - self.cursor.execute(''' - SELECT - InbredSet.Name - FROM - InbredSet, Temp - WHERE - Temp.InbredSetId = InbredSet.Id AND - Temp.Name = "%s" - ''', self.name) - self.group = self.cursor.fetchone()[0] - else: - self.group = self.dataset.get_group() + #if self.dataset.type == "Temp": + # self.cursor.execute(''' + # SELECT + # InbredSet.Name + # FROM + # InbredSet, Temp + # WHERE + # Temp.InbredSetId = InbredSet.Id AND + # Temp.Name = "%s" + # ''', self.name) + # self.group = self.cursor.fetchone()[0] + #else: + # self.group = self.dataset.get_group() - print("trinity, self.group is:", self.group) + #print("trinity, self.group is:", self.group) # # In ProbeSet, there are maybe several annotations match one sequence @@ -77,76 +77,80 @@ class GeneralTrait: # It also should be changed in other places where it are used. #if self.dataset: - if self.dataset.type == 'ProbeSet': - print("Doing ProbeSet Query") - query = ''' - SELECT - ProbeSet.BlatSeq - FROM - ProbeSet, ProbeSetFreeze, ProbeSetXRef - WHERE - ProbeSet.Id=ProbeSetXRef.ProbeSetId and - ProbeSetFreeze.Id = ProbeSetXRef.ProbeSetFreezeId and - ProbeSet.Name = %s and - ProbeSetFreeze.Name = %s - ''', (self.name, self.dataset.name) - print("query is:", query) - self.sequence = g.db.execute(*query).fetchone()[0] - #self.sequence = self.cursor.fetchone()[0] - print("self.sequence is:", self.sequence) - - - def getName(self): - str = "" + #if self.dataset.type == 'ProbeSet': + # print("Doing ProbeSet Query") + # query = ''' + # SELECT + # ProbeSet.BlatSeq + # FROM + # ProbeSet, ProbeSetFreeze, ProbeSetXRef + # WHERE + # ProbeSet.Id=ProbeSetXRef.ProbeSetId and + # ProbeSetFreeze.Id = ProbeSetXRef.ProbeSetFreezeId and + # ProbeSet.Name = %s and + # ProbeSetFreeze.Name = %s + # ''', (self.name, self.dataset.name) + # print("query is:", query) + # self.sequence = g.db.execute(*query).fetchone()[0] + # #self.sequence = self.cursor.fetchone()[0] + # print("self.sequence is:", self.sequence) + + + def get_name(self): + stringy = "" if self.dataset and self.name: - str = "%s::%s" % (self.dataset, self.name) + stringy = "%s::%s" % (self.dataset, self.name) if self.cellid: - str += "::" + self.cellid + stringy += "::" + self.cellid else: - str = self.description - return str - - # - # when user enter a trait or GN generate a trait, user want show the name - # not the name that generated by GN randomly, the two follow function are - # used to give the real name and the database. displayName() will show the - # database also, getGivenName() just show the name. - # For other trait, displayName() as same as getName(), getGivenName() as - # same as self.name - # - # Hongqiang 11/29/07 - # - def getGivenName(self): - str = self.name + stringy = self.description + return stringy + + + def get_given_name(self): + """ + when user enter a trait or GN generate a trait, user want show the name + not the name that generated by GN randomly, the two follow function are + used to give the real name and the database. displayName() will show the + database also, getGivenName() just show the name. + For other trait, displayName() as same as getName(), getGivenName() as + same as self.name + + Hongqiang 11/29/07 + + """ + stringy = self.name if self.dataset and self.name: - if self.dataset.type=='Temp': - self.cursor.execute('SELECT description FROM Temp WHERE Name=%s', self.name) - desc = self.cursor.fetchone()[0] - if desc.__contains__('PCA'): - desc = desc[desc.rindex(':')+1:].strip() - else: - desc = desc[:desc.index('entered')].strip() - str = desc - return str + desc = self.dataset.get_desc() + if desc: + #desc = self.handle_pca(desc) + stringy = desc + return stringy + + - def displayName(self): - str = "" + def display_name(self): + stringy = "" if self.dataset and self.name: - if self.dataset.type=='Temp': - desc = self.description - if desc.__contains__('PCA'): - desc = desc[desc.rindex(':')+1:].strip() - else: - desc = desc[:desc.index('entered')].strip() - str = "%s::%s" % (self.dataset, desc) + desc = self.dataset.get_desc() + #desc = self.handle_pca(desc) + if desc: + #desc = self.handle_pca(desc) + #stringy = desc + #if desc.__contains__('PCA'): + # desc = desc[desc.rindex(':')+1:].strip() + #else: + # desc = desc[:desc.index('entered')].strip() + #desc = self.handle_pca(desc) + stringy = "%s::%s" % (self.dataset, desc) else: - str = "%s::%s" % (self.dataset, self.name) + stringy = "%s::%s" % (self.dataset, self.name) if self.cellid: - str += "::" + self.cellid + stringy += "::" + self.cellid else: - str = self.description + stringy = self.description - return str + return stringy #def __str__(self): @@ -155,41 +159,43 @@ class GeneralTrait: #__str__ = getName #__repr__ = __str__ - def exportData(self, samplelist, type="val"): + def export_data(self, samplelist, the_type="val"): """ - export data according to samplelist - mostly used in calculating correlation + export data according to samplelist + mostly used in calculating correlation + """ result = [] for sample in samplelist: if self.data.has_key(sample): - if type=='val': + if the_type=='val': result.append(self.data[sample].val) - elif type=='var': + elif the_type=='var': result.append(self.data[sample].var) - elif type=='N': + elif the_type=='N': result.append(self.data[sample].N) else: - raise KeyError, `type`+' type is incorrect.' + raise KeyError, `the_type`+' the_type is incorrect.' else: result.append(None) return result - def exportInformative(self, incVar=0): + def export_informative(self, incVar=0): """ - export informative sample - mostly used in qtl regression + export informative sample + mostly used in qtl regression + """ samples = [] vals = [] - vars = [] + the_vars = [] for sample, value in self.data.items(): if value.val != None: if not incVar or value.var != None: samples.append(sample) vals.append(value.val) - vars.append(value.var) - return samples, vals, vars + the_vars.append(value.var) + return samples, vals, the_vars # @@ -199,136 +205,144 @@ class GeneralTrait: # # Hongqiang Li, 3/3/2008 # - def getSequence(self): - assert self.cursor - if self.dataset.type == 'ProbeSet': - self.cursor.execute(''' - SELECT - ProbeSet.BlatSeq - FROM - ProbeSet, ProbeSetFreeze, ProbeSetXRef - WHERE - ProbeSet.Id=ProbeSetXRef.ProbeSetId and - ProbeSetFreeze.Id = ProbeSetXRef.ProbSetFreezeId and - ProbeSet.Name = %s - ProbeSetFreeze.Name = %s - ''', self.name, self.dataset.name) - #self.cursor.execute(query) - results = self.fetchone() - - return results[0] - + #def getSequence(self): + # assert self.cursor + # if self.dataset.type == 'ProbeSet': + # self.cursor.execute(''' + # SELECT + # ProbeSet.BlatSeq + # FROM + # ProbeSet, ProbeSetFreeze, ProbeSetXRef + # WHERE + # ProbeSet.Id=ProbeSetXRef.ProbeSetId and + # ProbeSetFreeze.Id = ProbeSetXRef.ProbSetFreezeId and + # ProbeSet.Name = %s + # ProbeSetFreeze.Name = %s + # ''', self.name, self.dataset.name) + # #self.cursor.execute(query) + # results = self.fetchone() + # + # return results[0] - def retrieveData(self, samplelist=None): + def retrieve_sample_data(self, samplelist=None): if samplelist == None: samplelist = [] - assert self.dataset and self.cursor - - if self.dataset.type == 'Temp': - query = ''' - SELECT - Strain.Name, TempData.value, TempData.SE, TempData.NStrain, TempData.Id - FROM - TempData, Temp, Strain - WHERE - TempData.StrainId = Strain.Id AND - TempData.Id = Temp.DataId AND - Temp.name = '%s' - Order BY - Strain.Name - ''' % self.name - #XZ, 03/02/2009: Xiaodong changed Data to PublishData, SE to PublishSE - elif self.dataset.type == 'Publish': - query = ''' - SELECT - Strain.Name, PublishData.value, PublishSE.error, NStrain.count, PublishData.Id - FROM - (PublishData, Strain, PublishXRef, PublishFreeze) - left join PublishSE on - (PublishSE.DataId = PublishData.Id AND PublishSE.StrainId = PublishData.StrainId) - left join NStrain on - (NStrain.DataId = PublishData.Id AND - NStrain.StrainId = PublishData.StrainId) - WHERE - PublishXRef.InbredSetId = PublishFreeze.InbredSetId AND - PublishData.Id = PublishXRef.DataId AND PublishXRef.Id = %s AND - PublishFreeze.Id = %d AND PublishData.StrainId = Strain.Id - Order BY - Strain.Name - ''' % (self.name, self.dataset.id) + + assert self.dataset + + #if self.cellid: + # #Probe Data + # query = ''' + # SELECT + # Strain.Name, ProbeData.value, ProbeSE.error, ProbeData.Id + # FROM + # (ProbeData, ProbeFreeze, ProbeSetFreeze, ProbeXRef, + # Strain, Probe, ProbeSet) + # left join ProbeSE on + # (ProbeSE.DataId = ProbeData.Id AND ProbeSE.StrainId = ProbeData.StrainId) + # WHERE + # Probe.Name = '%s' AND ProbeSet.Name = '%s' AND + # Probe.ProbeSetId = ProbeSet.Id AND + # ProbeXRef.ProbeId = Probe.Id AND + # ProbeXRef.ProbeFreezeId = ProbeFreeze.Id AND + # ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id AND + # ProbeSetFreeze.Name = '%s' AND + # ProbeXRef.DataId = ProbeData.Id AND + # ProbeData.StrainId = Strain.Id + # Order BY + # Strain.Name + # ''' % (self.cellid, self.name, self.dataset.name) + # + #else: + results = self.dataset.retrieve_sample_data(self) + + #if self.dataset.type == 'Temp': + # query = ''' + # SELECT + # Strain.Name, TempData.value, TempData.SE, TempData.NStrain, TempData.Id + # FROM + # TempData, Temp, Strain + # WHERE + # TempData.StrainId = Strain.Id AND + # TempData.Id = Temp.DataId AND + # Temp.name = '%s' + # Order BY + # Strain.Name + # ''' % self.name + ##XZ, 03/02/2009: Xiaodong changed Data to PublishData, SE to PublishSE + #elif self.dataset.type == 'Publish': + # query = ''' + # SELECT + # Strain.Name, PublishData.value, PublishSE.error, NStrain.count, PublishData.Id + # FROM + # (PublishData, Strain, PublishXRef, PublishFreeze) + # left join PublishSE on + # (PublishSE.DataId = PublishData.Id AND PublishSE.StrainId = PublishData.StrainId) + # left join NStrain on + # (NStrain.DataId = PublishData.Id AND + # NStrain.StrainId = PublishData.StrainId) + # WHERE + # PublishXRef.InbredSetId = PublishFreeze.InbredSetId AND + # PublishData.Id = PublishXRef.DataId AND PublishXRef.Id = %s AND + # PublishFreeze.Id = %d AND PublishData.StrainId = Strain.Id + # Order BY + # Strain.Name + # ''' % (self.name, self.dataset.id) #XZ, 03/02/2009: Xiaodong changed Data to ProbeData, SE to ProbeSE - elif self.cellid: - #Probe Data - query = ''' - SELECT - Strain.Name, ProbeData.value, ProbeSE.error, ProbeData.Id - FROM - (ProbeData, ProbeFreeze, ProbeSetFreeze, ProbeXRef, - Strain, Probe, ProbeSet) - left join ProbeSE on - (ProbeSE.DataId = ProbeData.Id AND ProbeSE.StrainId = ProbeData.StrainId) - WHERE - Probe.Name = '%s' AND ProbeSet.Name = '%s' AND - Probe.ProbeSetId = ProbeSet.Id AND - ProbeXRef.ProbeId = Probe.Id AND - ProbeXRef.ProbeFreezeId = ProbeFreeze.Id AND - ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id AND - ProbeSetFreeze.Name = '%s' AND - ProbeXRef.DataId = ProbeData.Id AND - ProbeData.StrainId = Strain.Id - Order BY - Strain.Name - ''' % (self.cellid, self.name, self.dataset.name) + #elif self.cellid: + #XZ, 03/02/2009: Xiaodong added this block for ProbeSetData and ProbeSetSE - elif self.dataset.type == 'ProbeSet': - #ProbeSet Data - query = ''' - SELECT - Strain.Name, ProbeSetData.value, ProbeSetSE.error, ProbeSetData.Id - FROM - (ProbeSetData, ProbeSetFreeze, Strain, ProbeSet, ProbeSetXRef) - left join ProbeSetSE on - (ProbeSetSE.DataId = ProbeSetData.Id AND ProbeSetSE.StrainId = ProbeSetData.StrainId) - WHERE - ProbeSet.Name = '%s' AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND - ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND - ProbeSetFreeze.Name = '%s' AND - ProbeSetXRef.DataId = ProbeSetData.Id AND - ProbeSetData.StrainId = Strain.Id - Order BY - Strain.Name - ''' % (self.name, self.dataset.name) - #XZ, 03/02/2009: Xiaodong changeded Data to GenoData, SE to GenoSE - else: - #Geno Data - #XZ: The SpeciesId is not necessary, but it's nice to keep it to speed up database search. - query = ''' - SELECT - Strain.Name, GenoData.value, GenoSE.error, GenoData.Id - FROM - (GenoData, GenoFreeze, Strain, Geno, GenoXRef) - left join GenoSE on - (GenoSE.DataId = GenoData.Id AND GenoSE.StrainId = GenoData.StrainId) - WHERE - Geno.SpeciesId = %s AND Geno.Name = '%s' AND GenoXRef.GenoId = Geno.Id AND - GenoXRef.GenoFreezeId = GenoFreeze.Id AND - GenoFreeze.Name = '%s' AND - GenoXRef.DataId = GenoData.Id AND - GenoData.StrainId = Strain.Id - Order BY - Strain.Name - ''' % (webqtlDatabaseFunction.retrieveSpeciesId(self.cursor, self.dataset.group), self.name, self.dataset.name) + #elif self.dataset.type == 'ProbeSet': + # #ProbeSet Data + # query = ''' + # SELECT + # Strain.Name, ProbeSetData.value, ProbeSetSE.error, ProbeSetData.Id + # FROM + # (ProbeSetData, ProbeSetFreeze, Strain, ProbeSet, ProbeSetXRef) + # left join ProbeSetSE on + # (ProbeSetSE.DataId = ProbeSetData.Id AND ProbeSetSE.StrainId = ProbeSetData.StrainId) + # WHERE + # ProbeSet.Name = '%s' AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND + # ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND + # ProbeSetFreeze.Name = '%s' AND + # ProbeSetXRef.DataId = ProbeSetData.Id AND + # ProbeSetData.StrainId = Strain.Id + # Order BY + # Strain.Name + # ''' % (self.name, self.dataset.name) + ##XZ, 03/02/2009: Xiaodong changeded Data to GenoData, SE to GenoSE + #else: + # #Geno Data + # #XZ: The SpeciesId is not necessary, but it's nice to keep it to speed up database search. + # query = ''' + # SELECT + # Strain.Name, GenoData.value, GenoSE.error, GenoData.Id + # FROM + # (GenoData, GenoFreeze, Strain, Geno, GenoXRef) + # left join GenoSE on + # (GenoSE.DataId = GenoData.Id AND GenoSE.StrainId = GenoData.StrainId) + # WHERE + # Geno.SpeciesId = %s AND Geno.Name = '%s' AND GenoXRef.GenoId = Geno.Id AND + # GenoXRef.GenoFreezeId = GenoFreeze.Id AND + # GenoFreeze.Name = '%s' AND + # GenoXRef.DataId = GenoData.Id AND + # GenoData.StrainId = Strain.Id + # Order BY + # Strain.Name + # ''' % (webqtlDatabaseFunction.retrieveSpeciesId(self.cursor, self.dataset.group), self.name, self.dataset.name) - self.cursor.execute(query) - results = self.cursor.fetchall() + #self.cursor.execute(query) + #results = self.cursor.fetchall() + + # Todo: is this necessary? If not remove self.data.clear() if results: - self.mysqlid = results[0][-1] + #self.mysqlid = results[0][-1] #if samplelist: for item in results: #name, value, variance, num_cases = item @@ -351,8 +365,6 @@ class GeneralTrait: # self.data[item[0]] = webqtlCaseData(val, var, ndata) # #end for # #end if - #else: - # pass #def keys(self): # return self.__dict__.keys() @@ -399,7 +411,9 @@ class GeneralTrait: ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSetFreeze.Name = '%s' AND ProbeSet.Name = '%s' - """ % (display_fields_string, self.dataset.name, self.name) + """ % (escape(display_fields_string), + escape(self.dataset.name), + escape(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 @@ -415,7 +429,7 @@ class GeneralTrait: GenoXRef.GenoId = Geno.Id AND GenoFreeze.Name = '%s' AND Geno.Name = '%s' - """ % (display_fields_string, self.dataset.name, self.name) + """ % (escape(display_fields_string), escape(self.dataset.name), escape(self.name)) traitInfo = g.db.execute(query).fetchone() print("traitInfo is: ", pf(traitInfo)) else: #Temp type @@ -440,7 +454,7 @@ class GeneralTrait: self.confidential = 1 self.homologeneid = None - if self.dataset.type == 'ProbeSet' and self.group and self.geneid: + if self.dataset.type == 'ProbeSet' and self.dataset.group and self.geneid: #XZ, 05/26/2010: From time to time, this query get error message because some geneid values in database are not number. #XZ: So I have to test if geneid is number before execute the query. #XZ: The geneid values in database should be cleaned up. @@ -451,7 +465,7 @@ class GeneralTrait: geneidIsNumber = 0 if geneidIsNumber: - result = g.db.execute(""" + query = """ SELECT HomologeneId FROM @@ -461,9 +475,8 @@ class GeneralTrait: InbredSet.Name = '%s' AND InbredSet.SpeciesId = Species.Id AND Species.TaxonomyId = Homologene.TaxonomyId - """, (self.geneid, self.group)).fetchone() - #self.cursor.execute(query) - #result = self.cursor.fetchone() + """ % (escape(str(self.geneid)), escape(self.dataset.group.name)) + result = g.db.execute(query).fetchone() else: result = None |