def retrieveMarkerNameForGroupByRange(cursor, InbredSetName, Chr, MbStart, MbEnd): MarkerName = [] SpeciesId = webqtlDatabaseFunction.retrieveSpeciesId(cursor, InbredSetName) GenoFreezeId = retrieveGenoFreezeId(cursor, InbredSetName) MbStartClause = '' MbEndClause = '' try: MbStartClause = 'and Mb >= %s ' % float(MbStart) except: pass try: MbEndClause = 'and Mb <= %s' % float(MbEnd) except: pass cmd = "SELECT Geno.Name FROM Geno, GenoXRef WHERE Geno.SpeciesId=%s and Chr='%s' " % (SpeciesId, Chr) + MbStartClause + MbEndClause + " and GenoXRef.GenoFreezeId=%s and GenoXRef.GenoId=Geno.Id and GenoXRef.Used_for_mapping='Y' order by Mb" % (GenoFreezeId) cursor.execute(cmd) results = cursor.fetchall() for one_result in results: MarkerName.append( one_result[0] ) return MarkerName
def addGeno(cursor, GenoId, InbredSetName, MarkerWebID, fd): SpeciesId = webqtlDatabaseFunction.retrieveSpeciesId(cursor, InbredSetName) Name = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Name' ) Chr = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Chr' ) Mb = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Mb' ) Sequence = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Sequence' ) Source = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Source' ) chr_num = get_chr_num (cursor, Chr, SpeciesId) cmd = "INSERT INTO Geno (Id, SpeciesId, Name, Marker_Name, Chr, Mb, Sequence, Source, chr_num) VALUES (%s, %s, '%s', '%s', '%s', %s, '%s', '%s', %s )" % (GenoId, SpeciesId, Name, Name, Chr, Mb, Sequence, Source, chr_num) cursor.execute(cmd)
def updateGeno(cursor, GenoId, InbredSetName, MarkerWebID, fd): SpeciesId = webqtlDatabaseFunction.retrieveSpeciesId(cursor, InbredSetName) Chr = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Chr' ) cmd = "UPDATE Geno SET Chr='%s' WHERE Id=%s" % (Chr, GenoId) cursor.execute(cmd) chr_num = get_chr_num (cursor, Chr, SpeciesId) cmd = "UPDATE Geno SET chr_num=%s WHERE Id=%s" % (chr_num, GenoId) cursor.execute(cmd) Mb = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Mb' ) cmd = "UPDATE Geno SET Mb=%s WHERE Id=%s" % (Mb, GenoId) cursor.execute(cmd) Sequence = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Sequence' ) cmd = "UPDATE Geno SET Sequence='%s' WHERE Id=%s" % (Sequence, GenoId) cursor.execute(cmd) Source = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Source' ) cmd = "UPDATE Geno SET Source='%s' WHERE Id=%s" % (Source, GenoId) cursor.execute(cmd)
def retrieveMarkerInfoForGroup(cursor, MarkerName, InbredSetName): """ @type cursor: MySQLdb.connect.cursor @type MarkerName: string @rtype: list @return: the Marker's Id, Name, Chr, cM, Mb, Sequence, Source """ SpeciesId = webqtlDatabaseFunction.retrieveSpeciesId(cursor, InbredSetName) GenoFreezeId = retrieveGenoFreezeId(cursor, InbredSetName) cmd = ','.join( MarkerInfoField ) cmd = "SELECT Geno.Id," + cmd + " FROM Geno, GenoXRef WHERE Geno.SpeciesId=%s and Geno.Name='%s' and GenoXRef.GenoFreezeId=%s and GenoXRef.GenoId=Geno.Id" % (SpeciesId, MarkerName, GenoFreezeId) cursor.execute(cmd) result = cursor.fetchone() if result: return result else: return None
def getCollectionTableBody(self, traitList=None, formName=None, species=""): tblobj_body = [] className = "fs12 fwn b1 c222" for thisTrait in traitList: tr = [] if not thisTrait.haveinfo: thisTrait.retrieveInfo(QTL=1) trId = str(thisTrait) oneRadioName = thisTrait.getName() tr.append(TDCell(HT.TD(" ", align="center", valign="center", Class=className))) tr.append( TDCell( HT.TD( HT.Input(type="radio", name=oneRadioName, value="primary"), align="center", valign="center", Class=className, ) ) ) tr.append( TDCell( HT.TD( HT.Input(type="radio", name=oneRadioName, value="control"), align="center", valign="center", Class=className, ) ) ) tr.append( TDCell( HT.TD( HT.Input(type="radio", name=oneRadioName, value="target", checked="true"), align="center", valign="center", Class=className, ) ) ) tr.append( TDCell( HT.TD( HT.Input(type="radio", name=oneRadioName, value="ignored"), align="center", valign="center", Class=className, ) ) ) tr.append( TDCell( HT.TD(thisTrait.db.displayname, Class="fs12 fwn b1 c222"), thisTrait.db.displayname, thisTrait.db.displayname.upper(), ) ) tr.append( TDCell( HT.TD( HT.Href( text=thisTrait.name, url="javascript:showDatabase3('%s','%s','%s','')" % (formName, thisTrait.db.name, thisTrait.name), Class="fs12 fwn", ), nowrap="yes", align="left", Class=className, ), str(thisTrait.name), thisTrait.name, ) ) # description column if thisTrait.db.type == "Publish": PhenotypeString = thisTrait.post_publication_description if thisTrait.confidential: if not webqtlUtil.hasAccessToConfidentialPhenotypeTrait( privilege=self.privilege, userName=self.userName, authorized_users=thisTrait.authorized_users ): PhenotypeString = thisTrait.pre_publication_description tr.append(TDCell(HT.TD(PhenotypeString, Class=className), PhenotypeString, PhenotypeString.upper())) elif thisTrait.db.type == "ProbeSet" or thisTrait.db.type == "Temp": description_string = str(thisTrait.description).strip() if thisTrait.db.type == "ProbeSet": target_string = str(thisTrait.probe_target_description).strip() description_display = "" if len(description_string) > 1 and description_string != "None": description_display = description_string else: description_display = thisTrait.symbol if ( len(description_display) > 1 and description_display != "N/A" and len(target_string) > 1 and target_string != "None" ): description_display = description_display + "; " + target_string.strip() description_string = description_display tr.append(TDCell(HT.TD(description_string, Class=className), description_string, description_string)) else: tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", "Zz")) # location column if thisTrait.db.type == "Publish": tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", "Zz")) else: # ZS: trait_location_value is used for sorting trait_location_repr = "N/A" trait_location_value = 1000000 if hasattr(thisTrait, "chr") and hasattr(thisTrait, "mb") and thisTrait.chr and thisTrait.mb: try: trait_location_value = int(thisTrait.chr) * 1000 + thisTrait.mb except: if thisTrait.chr.upper() == "X": trait_location_value = 20 * 1000 + thisTrait.mb else: trait_location_value = ord(str(thisTrait.chr).upper()[0]) * 1000 + thisTrait.mb trait_location_repr = "Chr%s: %.6f" % (thisTrait.chr, float(thisTrait.mb)) tr.append( TDCell( HT.TD(trait_location_repr, nowrap="yes", Class=className), trait_location_repr, trait_location_value, ) ) if thisTrait.db.type == "ProbeSet": self.cursor.execute( """ select ProbeSetXRef.mean from ProbeSetXRef, ProbeSet where ProbeSetXRef.ProbeSetFreezeId = %d and ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSet.Name = '%s' """ % (thisTrait.db.id, thisTrait.name) ) result = self.cursor.fetchone() if result: if result[0]: mean = result[0] else: mean = 0 else: mean = 0 # XZ, 06/05/2009: It is neccessary to turn on nowrap repr = "%2.3f" % mean tr.append(TDCell(HT.TD(repr, Class=className, align="right", nowrap="ON"), repr, mean)) elif thisTrait.db.type == "Publish": self.cursor.execute( """ select count(PublishData.value), sum(PublishData.value) from PublishData, PublishXRef, PublishFreeze where PublishData.Id = PublishXRef.DataId and PublishXRef.Id = %s and PublishXRef.InbredSetId = PublishFreeze.InbredSetId and PublishFreeze.Id = %d """ % (thisTrait.name, thisTrait.db.id) ) result = self.cursor.fetchone() if result: if result[0] and result[1]: mean = result[1] / result[0] else: mean = 0 else: mean = 0 repr = "%2.3f" % mean tr.append(TDCell(HT.TD(repr, Class=className, align="right", nowrap="ON"), repr, mean)) else: tr.append(TDCell(HT.TD("--", Class=className, align="left", nowrap="ON"), "--", 0)) # Number of cases n_cases_value = 0 n_cases_repr = "--" if thisTrait.db.type == "Publish": self.cursor.execute( """ select count(PublishData.value) from PublishData, PublishXRef, PublishFreeze where PublishData.Id = PublishXRef.DataId and PublishXRef.Id = %s and PublishXRef.InbredSetId = PublishFreeze.InbredSetId and PublishFreeze.Id = %d """ % (thisTrait.name, thisTrait.db.id) ) result = self.cursor.fetchone() if result: if result[0]: n_cases_value = result[0] n_cases_repr = result[0] if n_cases_value == "--": tr.append( TDCell( HT.TD(n_cases_repr, Class=className, align="left", nowrap="on"), n_cases_repr, n_cases_value ) ) else: tr.append( TDCell( HT.TD(n_cases_repr, Class=className, align="right", nowrap="on"), n_cases_repr, n_cases_value, ) ) elif thisTrait.db.type == "ProbeSet": self.cursor.execute( """ select count(ProbeSetData.value) from ProbeSet, ProbeSetXRef, ProbeSetData, ProbeSetFreeze where ProbeSet.Name='%s' and ProbeSetXRef.ProbeSetId = ProbeSet.Id and ProbeSetXRef.DataId = ProbeSetData.Id and ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id and ProbeSetFreeze.Name = '%s' """ % (thisTrait.name, thisTrait.db.name) ) result = self.cursor.fetchone() if result: if result[0]: n_cases_value = result[0] n_cases_repr = result[0] if n_cases_value == "--": tr.append( TDCell( HT.TD(n_cases_repr, Class=className, align="left", nowrap="on"), n_cases_repr, n_cases_value ) ) else: tr.append( TDCell( HT.TD(n_cases_repr, Class=className, align="right", nowrap="on"), n_cases_repr, n_cases_value, ) ) elif thisTrait.db.type == "Geno": self.cursor.execute( """ select count(GenoData.value) from GenoData, GenoXRef, GenoFreeze, Geno, Strain where Geno.SpeciesId = %s and Geno.Name='%s' and GenoXRef.GenoId = Geno.Id and GenoXRef.DataId = GenoData.Id and GenoXRef.GenoFreezeId = GenoFreeze.Id and GenoData.StrainId = Strain.Id and GenoFreeze.Name = '%s' """ % ( webqtlDatabaseFunction.retrieveSpeciesId(self.cursor, thisTrait.db.riset), thisTrait.name, thisTrait.db.name, ) ) result = self.cursor.fetchone() if result: if result[0]: n_cases_value = result[0] n_cases_repr = result[0] if n_cases_value == "--": tr.append( TDCell( HT.TD(n_cases_repr, Class=className, align="left", nowrap="on"), n_cases_repr, n_cases_value ) ) else: tr.append( TDCell( HT.TD(n_cases_repr, Class=className, align="right", nowrap="on"), n_cases_repr, n_cases_value, ) ) else: tr.append( TDCell(HT.TD(n_cases_repr, Class=className, align="left", nowrap="on"), n_cases_repr, n_cases_value) ) if thisTrait.db.type != "Geno": # LRS and its location LRS_score_repr = "--" LRS_score_value = 0 LRS_location_repr = "--" LRS_location_value = 1000000 LRS_flag = 1 # Max LRS and its Locus location if hasattr(thisTrait, "lrs") and hasattr(thisTrait, "locus") and thisTrait.lrs and thisTrait.locus: self.cursor.execute( """ select Geno.Chr, Geno.Mb from Geno, Species where Species.Name = '%s' and Geno.Name = '%s' and Geno.SpeciesId = Species.Id """ % (species, thisTrait.locus) ) result = self.cursor.fetchone() if result: if result[0] and result[1]: LRS_Chr = result[0] LRS_Mb = result[1] # XZ: LRS_location_value is used for sorting try: LRS_location_value = int(LRS_Chr) * 1000 + float(LRS_Mb) except: if LRS_Chr.upper() == "X": LRS_location_value = 20 * 1000 + float(LRS_Mb) else: LRS_location_value = ord(str(LRS_chr).upper()[0]) * 1000 + float(LRS_Mb) LRS_score_repr = "%3.1f" % thisTrait.lrs LRS_score_value = thisTrait.lrs LRS_location_repr = "Chr%s: %.6f" % (LRS_Chr, float(LRS_Mb)) LRS_flag = 0 tr.append( TDCell( HT.TD(LRS_score_repr, Class=className, align="right", nowrap="on"), LRS_score_repr, LRS_score_value, ) ) tr.append( TDCell(HT.TD(LRS_location_repr, Class=className), LRS_location_repr, LRS_location_value) ) if LRS_flag: tr.append(TDCell(HT.TD(LRS_score_repr, Class=className), LRS_score_repr, LRS_score_value)) tr.append(TDCell(HT.TD(LRS_location_repr, Class=className), LRS_location_repr, LRS_location_value)) else: tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", 0)) tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", 1000000)) tblobj_body.append(tr) return tblobj_body
def __init__(self, fd): templatePage.__init__(self, fd) if not self.openMysql(): return fd.readGenotype() TD_LR = HT.TD(height=200,width="100%",bgColor='#eeeeee') self.database = fd.formdata.getfirst('database') self.ProbeSetID = fd.formdata.getfirst('ProbeSetID') self.CellID = fd.formdata.getfirst('CellID') self.db = webqtlDataset(self.database, self.cursor) thisTrait = webqtlTrait(db= self.db, cursor=self.cursor, name=self.ProbeSetID) #, cellid=CellID) thisTrait.retrieveInfo() try: self.cursor.execute('SELECT ProbeFreeze.Name FROM ProbeFreeze,ProbeSetFreeze WHERE ProbeFreeze.Id = ProbeSetFreeze.ProbeFreezeId and ProbeSetFreeze.Name = "%s"' % self.db.name) self.probeDatabase = self.cursor.fetchall()[0][0] self.probeInfoDatabase = 'Probe' except: heading = 'Probe Information' intro = ['Trying to retrieve the probe information for ProbeSet ',HT.Span('%s' % self.ProbeSetID, Class="fwb cdg"),' in Database ',HT.Href(text='%s' % self.db.fullname,url=webqtlConfig.infopagehref % self.database)] detail = ['The information you just requested is not available at this time.'] self.error(heading=heading,intro=intro,detail=detail) return form = HT.Form(cgi= os.path.join(webqtlConfig.CGIDIR, webqtlConfig.SCRIPTFILE), enctype='multipart/form-data', name='showDatabase', submit=HT.Input(type='hidden')) hddn = {'FormID':'showDatabase','ProbeSetID':'_','database':'_','CellID':'_','RISet':fd.RISet, 'incparentsf1':'on'} if fd.RISet == 'BXD': hddn['parentsf1']='ON' for key in hddn.keys(): form.append(HT.Input(name=key, value=hddn[key], type='hidden')) #Buttons on search page linkinfo ="%s/probeInfo.html" % webqtlConfig.PORTADDR mintmap = "" probeinfo = HT.Input(type='button' ,name='mintmap',value='Info', onClick="openNewWin('%s');" % linkinfo, Class="button") cormatrix = HT.Href(url="#redirect", onClick="databaseFunc(document.getElementsByName('showDatabase')[0], 'corMatrix');") cormatrix_img = HT.Image("/images/correlation_matrix1_final.jpg", alt="Correlation Matrix and PCA", title="Correlation Matrix and PCA", style="border:none;") cormatrix.append(cormatrix_img) heatmap = HT.Href(url="#redirect", onClick="databaseFunc(document.getElementsByName('showDatabase')[0], 'heatmap');") heatmap_img = HT.Image("/images/heatmap2_final.jpg", name='mintmap', alt="QTL Heat Map and Clustering", title="QTL Heatmap and Clustering", style="border:none;") heatmap.append(heatmap_img) if self.ProbeSetID[-2:] in ('_A', '_B'): thisProbeSetID = self.ProbeSetID[:-2] else: thisProbeSetID = self.ProbeSetID thisurl = 'http://www.ensembl.org/Mus_musculus/featureview?type=AffyProbe&id=%s' % thisProbeSetID verifyButton = HT.Input(type="button",value="Verify Ensembl",onClick= "openNewWin('%s')" % thisurl, Class="button") addselect = HT.Input(type='button' ,name='addselect',value='Add to Collection', onClick="addRmvSelection('%s', this.form, 'addToSelection');" % fd.RISet,Class="button") selectall = HT.Input(type='button' ,name='selectall',value='Select All', onClick="checkAll(this.form);",Class="button") selectpm = HT.Input(type='button' ,name='selectall',value='Select PM', onClick="checkPM(this.form);",Class="button") selectmm = HT.Input(type='button' ,name='selectall',value='Select MM', onClick="checkMM(this.form);",Class="button") selectinvert = HT.Input(type='button' ,name='selectinvert',value='Select Invert', onClick="checkInvert(this.form);",Class="button") reset = HT.Input(type='reset',name='',value='Select None',Class="button") chrMenu = HT.Input(type='hidden',name='chromosomes',value='all') probedata = HT.Input(type='hidden',name='probedata',value='all') url_rudi_track = self.getProbeTrackURL(self.probeDatabase, self.ProbeSetID) if url_rudi_track: rudi_track = HT.Input(type='button', name='ruditrack', value='Probe Track', onClick="openNewWin('%s')"%url_rudi_track, Class="button") else: rudi_track = None pinfopage = "/probeInfo.html" #updated by NL: 07-22-2011 get chosenStrains _f1, _f12, _mat, _pat = webqtlUtil.ParInfo[fd.RISet] chosenStrains="%s,%s"%(_mat,_pat) tblobj = {} tblobj['header']=[] tblobj['header'].append([ THCell(HT.TD("", Class="cbrb cw fwb fs13 b1", rowspan=2,nowrap='ON'), sort=0), THCell(HT.TD(HT.Href(target="_PROBEINFO", url=pinfopage+"#probe", text=HT.Span('Probe', Class="cw fwb fs13")), HT.Sup(HT.Italic('1')), Class="cbrb cw fwb fs13 b1",align='center',rowspan=2,nowrap='ON'), text="probe", idx=1), THCell(HT.TD(HT.Href(text=HT.Span('Sequence', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#Sequence"),HT.Sup(HT.Italic('2')), Class="cbrb cw fwb fs13 b1", align='center',rowspan=2,nowrap='ON'), text="seq", idx=2), THCell(HT.TD(HT.Href(text=HT.Span('bl2seq', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#bl2seq"),HT.Sup(HT.Italic('3')), Class="cbrb cw fwb fs13 b1", align='center',rowspan=2,nowrap='ON'), sort=0), THCell(HT.TD(HT.Href(text=HT.Span('Exons', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#Exon"),HT.Sup(HT.Italic('4')), Class="cbrb cw fwb fs13 b1",align='center',rowspan=2,nowrap='ON'), sort=0), THCell(HT.TD(HT.Href(text=HT.Span('Tm °C', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#Tm"),HT.Sup(HT.Italic('5')), Class="cbrb cw fwb fs13 b1",align='center',rowspan=2,nowrap='ON'), text="tm", idx=5), THCell(HT.TD(HT.Href(text=HT.Span('Stacking Energy K', HT.Sub('B'),'T', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#KBT"),HT.Sup(HT.Italic('6')), Class="cbrb cw fwb fs13 b1",align='center',colspan=2,NOWRAP="yes",nowrap='ON'), sort=0), THCell(HT.TD(HT.Href(text=HT.Span('Mean', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#Mean"),HT.Sup(HT.Italic('7')), Class="cbrb cw fwb fs13 b1",align='center',rowspan=2,nowrap='ON'), text="mean", idx=8), THCell(HT.TD(HT.Href(text=HT.Span('Stdev', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#Stdev"),HT.Sup(HT.Italic('8')), Class="cbrb cw fwb fs13 b1",align='center',rowspan=2,nowrap='ON'), text="std", idx=9), THCell(HT.TD(HT.Href(text=HT.Span('Probe h2', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#h2"),HT.Sup(HT.Italic('9')), Class="cbrb cw fwb fs13 b1",align='center',rowspan=2,NOWRAP="yes"), text="h2", idx=10), THCell(HT.TD(HT.Href(text=HT.Span('Probe Location', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#location"), HT.Sup(HT.Italic('10')),Class="cbrb cw fwb fs13 b1",align='center',colspan=3)), THCell(HT.TD(HT.Href(text=HT.Span('SNPs', HT.BR(), '(Across all strains)', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#snps"), HT.Sup(HT.Italic('11')),Class="cbrb cw fwb fs13 b1",align='center',rowspan=2,NOWRAP="yes")), THCell(HT.TD(HT.Href(text=HT.Span('SNPs', HT.BR(),'(Different alleles only between %s and %s)'%(_mat,_pat), Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#snps"), HT.Sup(HT.Italic('11')),Class="cbrb cw fwb fs13 b1",align='center',rowspan=2,NOWRAP="yes")) ]) tblobj['header'].append([ THCell(HT.TD(HT.Span('GSB', Class="cw fwb fs13"),align='center', Class="cbrb ffl fwb fs13 b1",), text="gsb", idx=6), THCell(HT.TD(HT.Span('NSB', Class="cw fwb fs13"),align='center', Class="cbrb ffl fwb fs13 b1",), text="nsb", idx=7), THCell(HT.TD(HT.Span('Chr', Class="cw fwb fs13"), align='center', Class="cbrb ffl2 fwb fs13 b1",)), THCell(HT.TD(HT.Span('Start', Class="cw fwb fs13"),align='center', Class="cbrb ffl fwb fs13 b1",)), THCell(HT.TD(HT.Span('End', Class="cw fwb fs13"),align='center', Class="cbrb ffl fwb fs13 b1",)), ]) tblobj['body'] = [] blatbutton = '' fetchField = ['Probe.Name','Probe.Sequence','Probe.ExonNo','Probe.Tm', 'Probe.E_GSB','Probe.E_NSB', 'ProbeH2.h2', 'ProbeH2.weight'] query = "SELECT %s FROM (Probe, ProbeSet, ProbeFreeze) left join ProbeH2 on ProbeH2.ProbeId = Probe.Id and ProbeH2.ProbeFreezeId = ProbeFreeze.Id WHERE ProbeSet.Name = '%s' and Probe.ProbeSetId = ProbeSet.Id and ProbeFreeze.Name = '%s' order by Probe.SerialOrder" % (string.join(fetchField,','), self.ProbeSetID, self.probeDatabase) self.cursor.execute(query) results = self.cursor.fetchall() blatsequence = "" # add by NL: get strains' name in SnpPattern database table strainsInSnpPatternDBtable=self.getStrainNameIndexPair() # after snpBrowserPage.py change to MVC, this function can be removed in this class and called from other class; allStrainNameList=[v[0] for v in strainsInSnpPatternDBtable] speciesid = webqtlDatabaseFunction.retrieveSpeciesId(cursor=self.cursor,RISet=fd.RISet) for result in results: """ ProbeId, CellID,Sequence,ExonNo,Tm, E_GSB,E_NSB = map(self.nullRecord,result) h2 = '' query = "SELECT h2 FROM ProbeH2 WHERE ProbeFreezeId = '%s' and ProbeId=%s" % (self.probeDatabase, ProbeId) self.cursor.execute(query) results = self.cursor.fetchall() """ CellID,Sequence,ExonNo,Tm, E_GSB,E_NSB,h2, weight = map(self.nullRecord,result) Average = '' STDEV = '' mean = -10000.0 stdev = -10000.0 try: thisTrait.cellid = CellID thisTrait.retrieveData() mean, median, var, stdev, sem, N = reaper.anova(thisTrait.exportInformative()[1]) if mean: Average = '%2.2f' % mean if stdev: STDEV = '%2.2f' % stdev except: pass if CellID == self.CellID: bkColor = "cbrdull fs11 b1" else: bkColor = "fs11 b1" seqcolor= '' if thisTrait.blatseq: blatsequence = thisTrait.blatseq if int(CellID[-1]) % 2 == 1: seqcolor= 'cdg' else: if int(CellID[-1]) % 2 == 1: seqcolor= 'cdg' blatsequence += string.strip(Sequence) if thisTrait.genbankid and (int(CellID[-1]) % 2 == 1): probeurl = 'http://www.ncbi.nlm.nih.gov/blast/bl2seq/wblast2.cgi?one=%s&sseq=%s' % (thisTrait.genbankid, Sequence) probefy1 = HT.Input(type="button",value="Blast",onClick= "openNewWin('%s')" % probeurl, Class="buttonsmaller") else: probefy1 = '' traitName = str(thisTrait) #XZ, Aug 08, 2011: Note that probesets on some affy chips are not name as "xxx_at" (i.e., Affy Mouse Gene 1.0 ST (GPL6246)). #EnsemblProbeSetID = self.ProbeSetID[0:self.ProbeSetID.index('_at')+3] EnsemblProbeSetID = self.ProbeSetID if '_at' in self.ProbeSetID: EnsemblProbeSetID = self.ProbeSetID[0:self.ProbeSetID.index('_at')+3] self.cursor.execute(''' SELECT EnsemblProbeLocation.* FROM EnsemblProbeLocation, EnsemblProbe, EnsemblChip, GeneChipEnsemblXRef, ProbeFreeze WHERE EnsemblProbeLocation.ProbeId=EnsemblProbe.Id and EnsemblProbe.ChipId=GeneChipEnsemblXRef.EnsemblChipId and GeneChipEnsemblXRef.GeneChipId=ProbeFreeze.ChipId and EnsemblProbe.Name=%s and EnsemblProbe.ProbeSet=%s and ProbeFreeze.Name=%s group by Chr, Start, End''' ,(CellID, EnsemblProbeSetID, self.probeDatabase)) LocationFields = self.cursor.fetchall() Chr='' Start='' End='' if (len(LocationFields)>=1): Chr,Start,End,Strand,MisMatch,ProbeId = map(self.nullRecord,LocationFields[0]) Start /= 1000000.0 End /= 1000000.0 if (len(LocationFields)>1): self.cursor.execute(''' SELECT ProbeSet.Chr, ProbeSet.Mb FROM ProbeSet, ProbeFreeze WHERE ProbeSet.ChipId=ProbeFreeze.ChipId and ProbeSet.Name=%s and ProbeFreeze.Name=%s''' ,(self.ProbeSetID, self.probeDatabase)) ProbeSetChr, ProbeSetMb = map(self.nullRecord,self.cursor.fetchall()[0]) self.cursor.execute(''' SELECT EnsemblProbeLocation.*, ABS(EnsemblProbeLocation.Start/1000000-%s) as Mb FROM EnsemblProbeLocation, EnsemblProbe, EnsemblChip, GeneChipEnsemblXRef, ProbeFreeze WHERE EnsemblProbeLocation.ProbeId=EnsemblProbe.Id and EnsemblProbe.ChipId=GeneChipEnsemblXRef.EnsemblChipId and GeneChipEnsemblXRef.GeneChipId=ProbeFreeze.ChipId and EnsemblProbe.Name=%s and EnsemblProbe.ProbeSet=%s and EnsemblProbeLocation.Chr=%s and ProbeFreeze.Name=%s order by Mb limit 1''' ,(ProbeSetMb, CellID, EnsemblProbeSetID, ProbeSetChr, self.probeDatabase)) NewLocationFields = self.cursor.fetchall() if (len(NewLocationFields)>0): Chr,Start,End,Strand,MisMatch,ProbeId,Mb = map(self.nullRecord,NewLocationFields[0]) Start /= 1000000.0 End /= 1000000.0 snp_collection = [] snpDiff_collection=[] startIndex=3 if Chr != '' and Start != '' and End != '' and speciesid != None: self.cursor.execute(''' SELECT a.SnpName, a.Id, b.* FROM SnpAll a, SnpPattern b WHERE a.Chromosome=%s and a.Position>=%s and a.Position<=%s and a.SpeciesId=%s and a.Id=b.SnpId''' ,(Chr, Start, End, speciesid)) #chr,Start, End, 1)) snpresults = self.cursor.fetchall() index1=allStrainNameList.index(_mat) #_mat index in results index2=allStrainNameList.index(_pat) #_pat index in results for v in snpresults: #updated by NL: 07-22-2011 check 'limit to' to get snpBrowser snpresults snp_collection.append(HT.Href(text=v[0], url=os.path.join(webqtlConfig.CGIDIR, "main.py?FormID=SnpBrowserResultPage&submitStatus=1&customStrain=1")+ "&geneName=%s" % v[0], Class="fs12 fwn", target="_blank")) snp_collection.append(HT.BR()) #updated by NL: 07-27-2011 link snp info for different allele only strain1_allele=v[startIndex+index1] strain2_allele=v[startIndex+index2] if strain1_allele!=strain2_allele: snpDiff_collection.append(HT.Href(text=v[0], url=os.path.join(webqtlConfig.CGIDIR, "main.py?FormID=SnpBrowserResultPage&submitStatus=1&customStrain=1&diffAlleles=1&chosenStrains=%s"%chosenStrains)+ "&geneName=%s" % v[0], Class="fs12 fwn", target="_blank")) snpDiff_collection.append(HT.BR()) tr = [] tr.append(TDCell(HT.TD(HT.Input(type="checkbox", Class='checkbox', name="searchResult",value=traitName, onClick="highlight(this)"), align="right", Class=bkColor, nowrap="on"), text=traitName)) tr.append(TDCell(HT.TD(HT.Href(text=CellID, url = "javascript:showDatabase2('%s','%s','%s');" % (self.database,self.ProbeSetID,CellID),Class="fs12 fwn"),Class=bkColor), traitName, traitName.upper())) tr.append(TDCell(HT.TD(Sequence, Class=bkColor + " %s ffmono fs14" % seqcolor),Sequence,Sequence.upper())) tr.append(TDCell(HT.TD(probefy1,align='center',Class=bkColor))) tr.append(TDCell(HT.TD(ExonNo,align='center',Class=bkColor))) try: TmValue = float(Tm) except: TmValue = 0.0 tr.append(TDCell(HT.TD(Tm,align='center',Class=bkColor), Tm, TmValue)) try: E_GSBValue = float(E_GSB) except: E_GSBValue = -10000.0 tr.append(TDCell(HT.TD(E_GSB,align='center',Class=bkColor), E_GSB, E_GSBValue)) try: E_NSBValue = float(E_NSB) except: E_NSBValue = -10000.0 tr.append(TDCell(HT.TD(E_NSB,align='center',Class=bkColor), E_NSB, E_NSBValue)) tr.append(TDCell(HT.TD(Average,align='center',Class=bkColor), Average, mean)) tr.append(TDCell(HT.TD(STDEV,align='center',Class=bkColor), STDEV, stdev)) try: h2Value = float(h2) except: h2Value = -10000.0 tr.append(TDCell(HT.TD(h2,align='center',Class=bkColor), h2, h2Value)) tr.append(TDCell(HT.TD(Chr,align='left',Class=bkColor))) tr.append(TDCell(HT.TD(Start,align='left',Class=bkColor))) tr.append(TDCell(HT.TD(End,align='left',Class=bkColor))) snp_td = HT.TD(align='left',Class=bkColor) for one_snp_href in snp_collection: snp_td.append(one_snp_href) tr.append(TDCell(snp_td)) #07-27-2011:add by NL: show SNP results for different allele only snpDiff_td= HT.TD(align='left', valign='top', Class=bkColor) for one_snpDiff_href in snpDiff_collection: snpDiff_td.append(one_snpDiff_href) tr.append(TDCell(snpDiff_td)) tblobj['body'].append(tr) # import cPickle filename = webqtlUtil.genRandStr("Probe_") objfile = open('%s.obj' % (webqtlConfig.TMPDIR+filename), 'wb') cPickle.dump(tblobj, objfile) objfile.close() # NL, 07/27/2010. genTableObj function has been moved from templatePage.py to webqtlUtil.py; div = HT.Div(webqtlUtil.genTableObj(tblobj=tblobj, file=filename, sortby=("", ""), tableID = "sortable", addIndex = "1"), Id="sortable") #UCSC _Species = webqtlDatabaseFunction.retrieveSpecies(cursor=self.cursor, RISet=fd.RISet) if _Species == "rat": thisurl = webqtlConfig.UCSC_BLAT % ('rat', 'rn3', blatsequence) elif _Species == "mouse": thisurl = webqtlConfig.UCSC_BLAT % ('mouse', 'mm9', blatsequence) else: thisurl = "" if thisurl: blatbutton = HT.Input(type='button' ,name='blatPM',value='Verify UCSC', onClick="window.open('%s','_blank')" % thisurl,Class="button") else: blatbutton = "" #GenBank genbankSeq = "" if thisTrait.genbankid: self.cursor.execute("SELECT Sequence FROM Genbank WHERE Id = '%s'" % thisTrait.genbankid ) genbankSeq = self.cursor.fetchone() if genbankSeq: genbankSeq = genbankSeq[0] if genbankSeq: if _Species == "rat": thisurl2 = webqtlConfig.UCSC_BLAT % ('rat', 'rn3', genbankSeq) if _Species == "mouse": thisurl2 = webqtlConfig.UCSC_BLAT % ('mouse', 'mm9', genbankSeq) else: thisurl2 = '' if thisurl2: blatbutton2 = HT.Input(type='button' ,name='blatPM',value='Verify GenBank', onClick="window.open('%s','_blank')" % thisurl2,Class="button") else: blatbutton2 = "" #Snp snpBrowser = "" if thisTrait.symbol and _Species == 'mouse': self.cursor.execute("select geneSymbol from GeneList where geneSymbol = %s", thisTrait.symbol) geneName = self.cursor.fetchone() if geneName: snpurl = os.path.join(webqtlConfig.CGIDIR, "main.py?FormID=snpBrowser") + "&geneName=%s" % geneName[0] else: if thisTrait.chr and thisTrait.mb: snpurl = os.path.join(webqtlConfig.CGIDIR, "main.py?FormID=snpBrowser") + \ "&chr=%s&start=%2.6f&end=%2.6f" % (thisTrait.chr, thisTrait.mb-0.002, thisTrait.mb+0.002) else: snpurl = "" if snpurl: snpBrowser = HT.Input(type="button",value="SNP Browser",onClick= \ "openNewWin('%s')" % snpurl, Class="button") else: snpBrowser = "" #end if heading = HT.Paragraph('Probe Information', Class="title") intro = HT.Paragraph('The table below lists information of all probes of probe set ',HT.Span(self.ProbeSetID, Class="fwb fs13"),' from database ', HT.Span(self.probeDatabase, Class="fwb fs13"), ".") buttons = HT.Paragraph(probedata,probeinfo,heatmap,cormatrix,blatbutton,blatbutton2,verifyButton,snpBrowser, HT.P(),selectall,selectpm,selectmm,selectinvert,reset,addselect) if rudi_track: buttons.append(rudi_track) form.append(buttons,div,HT.P()) TD_LR.append(heading,intro,form, HT.P()) self.dict['basehref'] = '' self.dict['body'] = str(TD_LR) self.dict['title'] = self.db.shortname + ' : ' + self.ProbeSetID +' / Probe Information' # updated by NL, javascript function xmlhttpPost(strURL, div, querystring) and function updatepage(Id, str) # have been moved to dhtml.js self.dict['js1'] = ''
def retrieveData(self, strainlist=[]): assert self.db and self.cursor if self.db.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.db.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.db.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.db.name) #XZ, 03/02/2009: Xiaodong added this block for ProbeSetData and ProbeSetSE elif self.db.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.db.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.db.riset), self.name, self.db.name) self.cursor.execute(query) results = self.cursor.fetchall() self.data.clear() if results: self.mysqlid = results[0][-1] if strainlist: for item in results: if item[0] in strainlist: val = item[1] if val != None: var = item[2] ndata = None if self.db.type in ('Publish', 'Temp'): ndata = item[3] self.data[item[0]] = webqtlCaseData(val, var, ndata) #end for else: for item in results: val = item[1] if val != None: var = item[2] ndata = None if self.db.type in ('Publish', 'Temp'): ndata = item[3] self.data[item[0]] = webqtlCaseData(val, var, ndata) #end for #end if else: pass
def updateResultPage(self, fd, thisTrait): comments = [] ctime = time.ctime() ##Start Updating dataID = -1 if thisTrait.db.type == 'Publish': self.cursor.execute("SelecT PublishXRef.InbredSetId, PublishXRef.DataId, PublishXRef.PublicationId, PublishXRef.PhenotypeId, PublishXRef.Sequence from PublishXRef, PublishFreeze where PublishXRef.InbredSetId= PublishFreeze.InbredSetId and PublishFreeze.Name = '%s' and PublishXRef.Id = %s" % (thisTrait.db.name, thisTrait.name)) PInbredSetId, dataID, PublicationId, PhenotypeId, Sequence = self.cursor.fetchall()[0] modifyField = self.formdata.getvalue('modifiedField') ###Modify Trait Informations if modifyField: modifyField = string.split(modifyField, '::') comments += modifyField updateHomologeneid = False if thisTrait.db.type == 'Publish': PhenotypeItemUpdate = [] PhenotypeItemValues = [] PublicationItemUpdate = [] PublicationItemValues = [] for item in modifyField: itemvalue = self.formdata.getvalue(item) #XZ: identify Phenotype items if item in ['pre_publication_description', 'post_publication_description', 'original_description', 'pre_publication_abbreviation', 'post_publication_abbreviation', 'lab_code', 'submitter', 'owner', 'authorized_users', 'units']: if itemvalue != None: #XZ: the problem is that the item value can not be deleted PhenotypeItemUpdate.append('%s=%%s' % item) PhenotypeItemValues.append(itemvalue) continue #XZ: this is important to distinguish Phenotype item and Publication item elif item == "pubmed_id": #Only integer allowed in this field try: itemvalue = int(itemvalue) except: itemvalue = None #whether old PMID exists self.cursor.execute("SelecT PubMed_ID from Publication where Id = %d" % PublicationId) oldPMID = self.cursor.fetchone() if oldPMID: oldPMID = oldPMID[0] #whether new PMID already exists newPMID = None self.cursor.execute("SelecT Id from Publication where PubMed_ID = %d" % itemvalue) newPMID = self.cursor.fetchone() if newPMID: newPMID = newPMID[0] ##the logic is still not very clear here if newPMID: #new PMID in record self.cursor.execute("Update PublishXRef set PublicationId = %d where InbredSetId=%d and PhenotypeId=%d and PublicationId=%d and Sequence=%d" % (newPMID, PInbredSetId, PhenotypeId, PublicationId, Sequence)) #no need to update other fields PublicationItemUpdate = [] break elif itemvalue: #have new PMID, but not in record or need to change self.cursor.execute("Update Publication set pubmed_id=%d where Id = %s" % (itemvalue,PublicationId)) else: #no new PMID if oldPMID: #remove a pubmed_id, don't know if this ever gonna happen self.cursor.execute("SelecT max(Id) from Publication") maxId = self.cursor.fetchone()[0] + 1 self.cursor.execute("SelecT * from Publication where Id = %d" % PublicationId) oldRecs = list(self.cursor.fetchone()) oldRecs[0] = maxId oldRecs[1] = None NFields = ['%s'] * len(oldRecs) query = "insert into Publication Values (%s)" % string.join(NFields, ',') self.cursor.execute(query, tuple(oldRecs)) self.cursor.execute("Update PublishXRef set PublicationId = %d where InbredSetId=%d and PhenotypeId=%d and PublicationId=%d and Sequence=%d" % (maxId, PInbredSetId, PhenotypeId, PublicationId, Sequence)) PublicationId = maxId pass else: pass continue else: pass if itemvalue: PublicationItemUpdate.append('%s=%%s' % item) PublicationItemValues.append(itemvalue) if PhenotypeItemUpdate: updateStr= string.join(PhenotypeItemUpdate,',') query = "Update Phenotype set %s where Id = %s" % (updateStr, PhenotypeId) self.cursor.execute(query,tuple(PhenotypeItemValues)) if PublicationItemUpdate: updateStr= string.join(PublicationItemUpdate,',') query = "Update Publication set %s where Id = %s" % (updateStr, PublicationId) self.cursor.execute(query,tuple(PublicationItemValues)) else: #ProbeSet or Genotype Data itemValues = [] itemUpdate = [] for item in modifyField: itemvalue = self.formdata.getvalue(item) if itemvalue != None: itemvalue = string.strip(itemvalue) else: pass if item == 'homologeneid': updateHomologeneid = True new_homologeneid = 0 if itemvalue and len(itemvalue) > 0: try: new_homologeneid = int(itemvalue) except: heading = "Record Updating Result" detail = ["Can't update database. Homologeneid must be integer!"] self.error(heading=heading,detail=detail,error="Error") return else: itemUpdate.append('%s=%%s' % item) #XZ: Use %% to put a % in the output string itemValues.append(itemvalue) if itemUpdate: updateStr= string.join(itemUpdate,', ') comments = "%s modified %s at %s\n" % (self.userName, string.join(comments, ', '), ctime) if thisTrait.db.type == "ProbeSet":#XZ, June 29, 2010: The algorithm is not good. Need to fix it later. if thisTrait.chipid in (2,4): if thisTrait.name[-2:] == '_A': thisTrait.name = string.replace(thisTrait.name, '_A', '') elif thisTrait.name[-2:] == '_B': thisTrait.name = string.replace(thisTrait.name, '_B', '') else: pass query = "Update %s set %s where Name like '%s%%%%'" % (thisTrait.db.type,updateStr,thisTrait.name) self.cursor.execute(query,tuple(itemValues)) self.cursor.execute("Update %s set comments = CONCAT(comments,'%s') where Name like '%s%%%%'" % (thisTrait.db.type, comments, thisTrait.name)) elif thisTrait.sequence: query = "Update %s set %s where BlatSeq='%s'" % (thisTrait.db.type,updateStr,thisTrait.sequence) self.cursor.execute(query,tuple(itemValues)) self.cursor.execute("Update %s set comments = CONCAT(comments,'%s') where BlatSeq='%s'" % (thisTrait.db.type, comments, thisTrait.sequence)) else: query = "Update %s set %s where Name='%s'" % (thisTrait.db.type,updateStr,thisTrait.name) self.cursor.execute(query,tuple(itemValues)) self.cursor.execute("Update %s set comments = CONCAT(comments,'%s') where Name='%s'" % (thisTrait.db.type, comments, thisTrait.name)) else: #XZ: Genotype query = "Update %s set %s where SpeciesId=%s and Name='%s'" % (thisTrait.db.type,updateStr, webqtlDatabaseFunction.retrieveSpeciesId(self.cursor, thisTrait.db.riset), thisTrait.name) self.cursor.execute(query,tuple(itemValues)) if updateHomologeneid: #XZ: to update homologene id must be after updating geneid. #XZ: In one species, one homologeneid can have multiple geneid. One geneid only can have one homologeneid. #XZ: In Homologene table, GeneId is unique. #XZ: Geneid might just being updated. thisTrait = webqtlTrait(fullname=self.formdata.getvalue('fullname'), cursor=self.cursor) thisTrait.retrieveInfo() if not thisTrait.geneid: heading = "Record Updating Result" detail = ["There is no geneid associated with this trait. Can't update homologeneid info"] self.error(heading=heading,detail=detail,error="Error") return else: query = """ SELECT Species.TaxonomyId FROM Species, InbredSet WHERE InbredSet.Name = '%s' and InbredSet.SpeciesId = Species.Id """ % thisTrait.db.riset self.cursor.execute(query) taxonomyId = self.cursor.fetchone()[0] if not new_homologeneid: query = """DELETE FROM Homologene WHERE GeneId=%s""" % thisTrait.geneid self.cursor.execute(query) else: query = """SELECT GeneId FROM Homologene WHERE GeneId=%s""" % thisTrait.geneid self.cursor.execute(query) result = self.cursor.fetchone() if not result: query = """INSERT into Homologene (HomologeneId, GeneId, TaxonomyId) VALUES (%s, %s, %s)""" % (new_homologeneid, thisTrait.geneid, taxonomyId) self.cursor.execute(query) else: query = """UPDATE Homologene SET HomologeneId=%s WHERE GeneId=%s""" % (new_homologeneid, thisTrait.geneid) self.cursor.execute(query) #XZ: It's critical to get lasted info first, then update gene level info across traits by geneid. #XZ: Need to build index on GeneId. Otherwise, it's too slow. if thisTrait.db.type == 'ProbeSet': thisTrait = webqtlTrait(fullname=self.formdata.getvalue('fullname'), cursor=self.cursor) thisTrait.retrieveInfo() if thisTrait.geneid: if 'symbol' in modifyField: if thisTrait.symbol: query = """UPDATE ProbeSet SET Symbol='%s' WHERE GeneId=%s""" % (thisTrait.symbol, thisTrait.geneid) else: query = """UPDATE ProbeSet SET Symbol=NULL WHERE GeneId=%s""" % (thisTrait.geneid) self.cursor.execute(query) if 'alias' in modifyField: if thisTrait.alias: newalias = [] for alias in re.split('[,;|\s]+', thisTrait.alias): if alias and 0<len(alias): newalias.append(alias) thisTrait.alias = '; '.join(newalias) query = """UPDATE ProbeSet SET alias='%s' WHERE GeneId=%s""" % (thisTrait.alias, thisTrait.geneid) else: query = """UPDATE ProbeSet SET alias=NULL WHERE GeneId=%s""" % (thisTrait.geneid) self.cursor.execute(query) if 'description' in modifyField: if thisTrait.description: #XZ: Attention, we must use "%s" instead of '%s'. Otherwise, to insert 3'UTR will generate error. query = """UPDATE ProbeSet SET description="%s" WHERE GeneId=%s""" % (thisTrait.description, thisTrait.geneid) else: query = """UPDATE ProbeSet SET description=NULL WHERE GeneId=%s""" % (thisTrait.geneid) self.cursor.execute(query) if 'strand_gene' in modifyField: if thisTrait.strand_gene: query = """UPDATE ProbeSet SET Strand_Gene='%s' WHERE GeneId=%s""" % (thisTrait.strand_gene, thisTrait.geneid) else: query = """UPDATE ProbeSet SET Strand_Gene=NULL WHERE GeneId=%s""" % (thisTrait.geneid) self.cursor.execute(query) if 'unigeneid' in modifyField: if thisTrait.unigeneid: query = """UPDATE ProbeSet SET UniGeneId='%s' WHERE GeneId=%s""" % (thisTrait.unigeneid, thisTrait.geneid) else: query = """UPDATE ProbeSet SET UniGeneId=NULL WHERE GeneId=%s""" % (thisTrait.geneid) self.cursor.execute(query) if 'refseq_transcriptid' in modifyField: if thisTrait.refseq_transcriptid: query = """UPDATE ProbeSet SET RefSeq_TranscriptId='%s' WHERE GeneId=%s""" % (thisTrait.refseq_transcriptid, thisTrait.geneid) else: query = """UPDATE ProbeSet SET RefSeq_TranscriptId=NULL WHERE GeneId=%s""" % (thisTrait.geneid) self.cursor.execute(query) if 'genbankid' in modifyField: if thisTrait.genbankid: query = """UPDATE ProbeSet SET GenbankId='%s' WHERE GeneId=%s""" % (thisTrait.genbankid, thisTrait.geneid) else: query = """UPDATE ProbeSet SET GenbankId=NULL WHERE GeneId=%s""" % (thisTrait.geneid) self.cursor.execute(query) if 'omim' in modifyField: if thisTrait.omim: query = """UPDATE ProbeSet SET OMIM='%s' WHERE GeneId=%s""" % (thisTrait.omim, thisTrait.geneid) else: query = """UPDATE ProbeSet SET OMIM=NULL WHERE GeneId=%s""" % (thisTrait.geneid) self.cursor.execute(query) ###Modify Trait Data if thisTrait.db.type == 'Publish' and dataID > 0 and fd.formdata.getvalue("modifiedDataField"): StrainIds = [] for item in fd.strainlist: self.cursor.execute('SelecT Id from Strain where Name = "%s"' % item) StrainId = self.cursor.fetchone() if not StrainId: raise ValueError else: StrainIds.append(StrainId[0]) comments.append('Trait Value') #XZ, 03/05/2009: Xiaodong changed Data to PublishData, SE to PublishSE self.cursor.execute('delete from PublishData where Id = %d' % dataID) self.cursor.execute('delete from PublishSE where DataId = %d' % dataID) self.cursor.execute('delete from NStrain where DataId = %d' % dataID) for i, strain in enumerate(fd.strainlist): sId = StrainIds[i] if fd.allTraitData.has_key(strain): tdata = fd.allTraitData[strain] _val, _var, _N = tdata.val, tdata.var, tdata.N if _val != None: #XZ, 03/05/2009: Xiaodong changed Data to PublishData, SE to PublishSE self.cursor.execute('insert into PublishData values(%d, %d, %s)' % (dataID, sId, _val)) if _var != None: self.cursor.execute('insert into PublishSE values(%d, %d, %s)' % (dataID, sId, _var)) if _N != None: self.cursor.execute('insert into NStrain values(%d, %d, %s)' % (dataID, sId, _N)) else: pass #end for else: pass TD_LR = HT.TD(valign="top", bgcolor="#eeeeee",height=200,width="100%") main_title = HT.Paragraph(" Record Updating Result", Class="title") TD_LR.append(main_title,HT.Blockquote('Successfully updated record %s in database ' % thisTrait.name, thisTrait.db.genHTML(), '.')) if thisTrait.db.type == 'Publish': comments = "%s modified %s at %s\n" % (self.userName, string.join(comments, ', '), ctime) self.cursor.execute("Update PublishXRef set comments = CONCAT(comments,'%s') where InbredSetId=%d and PhenotypeId=%d and PublicationId=%d and Sequence=%d" % (comments, PInbredSetId, PhenotypeId, PublicationId, Sequence)) if 0: heading = "Record Updating Result" detail = ["Can't update database. The server may be down at this time or you don't have the permission"] self.error(heading=heading,detail=detail,error="Error") return self.dict['body'] = str(TD_LR)
def getCollectionTableBody(self, RISet=None, traitList=None, formName=None, species=''): tblobj_body = [] className = "fs12 fwn b1 c222" for thisTrait in traitList: tr = [] if not thisTrait.haveinfo: thisTrait.retrieveInfo(QTL=1) if thisTrait.riset != RISet: continue trId = str(thisTrait) #XZ: check box column tr.append(TDCell(HT.TD(HT.Input(type="checkbox", Class="checkallbox", name="searchResult",value=trId, onClick="highlight(this)"), nowrap="on", Class=className), text=trId)) #XZ: Dataset column tr.append(TDCell(HT.TD(thisTrait.db.displayname, Class="fs12 fwn b1 c222"), thisTrait.db.displayname, thisTrait.db.displayname.upper())) #XZ: Trait ID column if thisTrait.cellid: tr.append(TDCell(HT.TD(HT.Href(text=thisTrait.cellid,url="javascript:showDatabase3('%s','%s','%s','%s')" % (formName, thisTrait.db.name, thisTrait.name, thisTrait.cellid), Class="fs12 fwn"), nowrap="yes",align="left", Class=className),str(thisTrait.cellid), thisTrait.cellid)) else: tr.append(TDCell(HT.TD(HT.Href(text=thisTrait.getGivenName(),url="javascript:showDatabase3('%s','%s','%s','')" % (formName, thisTrait.db.name, thisTrait.name), Class="fs12 fwn"), nowrap="yes",align="left", Class=className),str(thisTrait.name), thisTrait.name)) #XZ: Symbol column and Description column if (thisTrait.db.type == "Publish"): AbbreviationString = "--" if (thisTrait.post_publication_abbreviation != None): AbbreviationString = thisTrait.post_publication_abbreviation PhenotypeString = thisTrait.post_publication_description if thisTrait.confidential: if not webqtlUtil.hasAccessToConfidentialPhenotypeTrait(privilege=self.privilege, userName=self.userName, authorized_users=thisTrait.authorized_users): if thisTrait.pre_publication_abbreviation: AbbreviationString = thisTrait.pre_publication_abbreviation else: AbbreviationString = "--" PhenotypeString = thisTrait.pre_publication_description if AbbreviationString == "--": tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", "Zz")) else: tr.append(TDCell(HT.TD(AbbreviationString, Class=className), AbbreviationString, AbbreviationString.upper())) if PhenotypeString: PhenotypeString_upper = PhenotypeString.upper() else: PhenotypeString_upper = PhenotypeString tr.append(TDCell(HT.TD(PhenotypeString, Class=className), PhenotypeString, PhenotypeString_upper)) elif (thisTrait.db.type == "ProbeSet" or thisTrait.db.type == "Temp"): description_string = str(thisTrait.description).strip() if (thisTrait.db.type == "ProbeSet"): if (thisTrait.symbol != None): if thisTrait.geneid: symbolurl = HT.Href(text=thisTrait.symbol,target='_blank',url="http://www.ncbi.nlm.nih.gov/entrez/query.fcgi?db=gene&cmd=Retrieve&dopt=Graphics&list_uids=%s" % thisTrait.geneid, Class="font_black fs12 fwn") else: symbolurl = HT.Href(text=thisTrait.symbol,target='_blank',url="http://www.ncbi.nlm.nih.gov/entrez/query.fcgi?CMD=search&DB=gene&term=%s" % thisTrait.symbol, Class="font_black fs12 fwn") tr.append(TDCell(HT.TD(symbolurl, align="left", Class="fs12 fwn b1 c222 fsI"), thisTrait.symbol, thisTrait.symbol)) else: tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", "Zz")) target_string = str(thisTrait.probe_target_description).strip() description_display = '' if len(description_string) > 1 and description_string != 'None': description_display = description_string else: description_display = thisTrait.symbol if len(description_display) > 1 and description_display != 'N/A' and len(target_string) > 1 and target_string != 'None': description_display = description_display + '; ' + target_string.strip() description_string = description_display else: tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", "Zz")) tr.append(TDCell(HT.TD(description_string, Class=className), description_string, description_string)) else: if (thisTrait.name != None): tr.append(TDCell(HT.TD(thisTrait.name, Class="fs12 fwn b1 c222"), thisTrait.name, thisTrait.name)) else: tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", "Zz")) tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", "Zz")) #XZ: Location column if (thisTrait.db.type == "Publish"): tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", "Zz")) else: if thisTrait.db.type == "ProbeSet" and thisTrait.cellid: EnsemblProbeSetID = thisTrait.name if '_at' in thisTrait.name: EnsemblProbeSetID = thisTrait.name[0:thisTrait.name.index('_at')+3] #These tables (Ensembl) were created by Xusheng Wang in 2010 and are mm9 (so they'll need to be changed at some point to be mm10. self.cursor.execute(''' SELECT EnsemblProbeLocation.* FROM EnsemblProbeLocation, EnsemblProbe, EnsemblChip, GeneChipEnsemblXRef, ProbeFreeze, ProbeSetFreeze WHERE EnsemblProbeLocation.ProbeId=EnsemblProbe.Id and EnsemblProbe.ChipId=GeneChipEnsemblXRef.EnsemblChipId and GeneChipEnsemblXRef.GeneChipId=ProbeFreeze.ChipId and EnsemblProbe.Name=%s and EnsemblProbe.ProbeSet=%s and ProbeSetFreeze.Id=%s and ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id group by Chr, Start, End''' ,(thisTrait.cellid, EnsemblProbeSetID, thisTrait.db.id)) LocationFields = self.cursor.fetchall() Chr='' Mb='' Start='' End='' if (len(LocationFields)>=1): Chr,Start,Start_2016,End,End_2016,Strand,MisMatch,ProbeId = map(self.nullRecord,LocationFields[0]) Start /= 1000000.0 End /= 1000000.0 Mb = Start if (len(LocationFields)>1): self.cursor.execute(''' SELECT ProbeSet.Chr, ProbeSet.Mb FROM ProbeSet, ProbeFreeze, ProbeSetFreeze WHERE ProbeSet.ChipId=ProbeFreeze.ChipId and ProbeSet.Name=%s and ProbeSetFreeze.Id=%s and ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id''' ,(thisTrait.name, thisTrait.db.id)) ProbeSetChr, ProbeSetMb = map(self.nullRecord,self.cursor.fetchall()[0]) self.cursor.execute(''' SELECT EnsemblProbeLocation.*, ABS(EnsemblProbeLocation.Start/1000000-%s) as Mb FROM EnsemblProbeLocation, EnsemblProbe, EnsemblChip, GeneChipEnsemblXRef, ProbeFreeze, ProbeSetFreeze WHERE EnsemblProbeLocation.ProbeId=EnsemblProbe.Id and EnsemblProbe.ChipId=GeneChipEnsemblXRef.EnsemblChipId and GeneChipEnsemblXRef.GeneChipId=ProbeFreeze.ChipId and EnsemblProbe.Name=%s and EnsemblProbe.ProbeSet=%s and EnsemblProbeLocation.Chr=%s and ProbeSetFreeze.Id=%s and ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id order by Mb limit 1''' ,(ProbeSetMb, thisTrait.cellid, EnsemblProbeSetID, ProbeSetChr, thisTrait.db.id)) NewLocationFields = self.cursor.fetchall() if (len(NewLocationFields)>0): Chr,Start,Start_2016,End,End_2016,Strand,MisMatch,ProbeId,Mb = map(self.nullRecord,NewLocationFields[0]) Start /= 1000000.0 End /= 1000000.0 Mb = Start #ZS: trait_location_value is used for sorting trait_location_repr = "--" trait_location_value = 1000000 if Chr and Mb: try: trait_location_value = int(Chr)*1000 + Mb except: if Chr.upper() == "X": trait_location_value = 20*1000 + Mb else: trait_location_value = ord(str(Chr).upper()[0])*1000 + Mb trait_location_repr = "Chr%s: %.6f" % (Chr, float(Mb) ) tr.append(TDCell(HT.TD(trait_location_repr, nowrap='ON', Class=className), trait_location_repr, trait_location_value)) else: #ZS: trait_location_value is used for sorting trait_location_repr = "--" trait_location_value = 1000000 if hasattr(thisTrait, 'chr') and hasattr(thisTrait, 'mb') and thisTrait.chr and thisTrait.mb: try: trait_location_value = int(thisTrait.chr)*1000 + thisTrait.mb except: if thisTrait.chr.upper() == "X": trait_location_value = 20*1000 + thisTrait.mb else: trait_location_value = ord(str(thisTrait.chr).upper()[0])*1000 + thisTrait.mb trait_location_repr = "Chr%s: %.6f" % (thisTrait.chr, float(thisTrait.mb) ) tr.append(TDCell(HT.TD(trait_location_repr, nowrap='ON', Class=className), trait_location_repr, trait_location_value)) #XZ: Mean column if (thisTrait.db.type == "ProbeSet"): if thisTrait.cellid: mean = -10000.0 try: thisTrait.retrieveData() mean, median, var, stdev, sem, N = reaper.anova(thisTrait.exportInformative()[1]) except: pass repr = '%2.3f' % mean mean = '%2.2f' % mean tr.append(TDCell(HT.TD(repr, Class=className, align='right', nowrap='ON'),repr, mean)) else: self.cursor.execute(""" select ProbeSetXRef.mean from ProbeSetXRef, ProbeSet where ProbeSetXRef.ProbeSetFreezeId = %d and ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSet.Name = '%s' """ % (thisTrait.db.id, thisTrait.name)) result = self.cursor.fetchone() if result: if result[0]: mean = result[0] else: mean=0 else: mean = 0 #XZ, 06/05/2009: It is neccessary to turn on nowrap repr = "%2.3f" % mean tr.append(TDCell(HT.TD(repr, Class=className, align='right', nowrap='ON'),repr, mean)) elif (thisTrait.db.type == "Publish"): self.cursor.execute(""" select count(PublishData.value), sum(PublishData.value) from PublishData, PublishXRef, PublishFreeze where PublishData.Id = PublishXRef.DataId and PublishXRef.Id = %s and PublishXRef.InbredSetId = PublishFreeze.InbredSetId and PublishFreeze.Id = %d """ % (thisTrait.name, thisTrait.db.id)) result = self.cursor.fetchone() if result: if result[0] and result[1]: mean = result[1]/result[0] else: mean = 0 else: mean = 0 repr = "%2.3f" % mean tr.append(TDCell(HT.TD(repr, Class=className, align='right', nowrap='ON'),repr, mean)) else: tr.append(TDCell(HT.TD("--", Class=className, align='left', nowrap='ON'),"--", 0)) #Number of cases n_cases_value = 0 n_cases_repr = "--" if (thisTrait.db.type == "Publish"): self.cursor.execute(""" select count(PublishData.value) from PublishData, PublishXRef, PublishFreeze where PublishData.Id = PublishXRef.DataId and PublishXRef.Id = %s and PublishXRef.InbredSetId = PublishFreeze.InbredSetId and PublishFreeze.Id = %d """ % (thisTrait.name, thisTrait.db.id)) result = self.cursor.fetchone() if result: if result[0]: n_cases_value = result[0] n_cases_repr = result[0] if (n_cases_value == "--"): tr.append(TDCell(HT.TD(n_cases_repr, Class=className, align='left', nowrap="on"), n_cases_repr, n_cases_value)) else: tr.append(TDCell(HT.TD(n_cases_repr, Class=className, align='right', nowrap="on"), n_cases_repr, n_cases_value)) elif (thisTrait.db.type == "ProbeSet"): self.cursor.execute(""" select count(ProbeSetData.value) from ProbeSet, ProbeSetXRef, ProbeSetData, ProbeSetFreeze where ProbeSet.Name='%s' and ProbeSetXRef.ProbeSetId = ProbeSet.Id and ProbeSetXRef.DataId = ProbeSetData.Id and ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id and ProbeSetFreeze.Name = '%s' """ % (thisTrait.name, thisTrait.db.name)) result = self.cursor.fetchone() if result: if result[0]: n_cases_value = result[0] n_cases_repr = result[0] if (n_cases_value == "--"): tr.append(TDCell(HT.TD(n_cases_repr, Class=className, align='left', nowrap="on"), n_cases_repr, n_cases_value)) else: tr.append(TDCell(HT.TD(n_cases_repr, Class=className, align='right', nowrap="on"), n_cases_repr, n_cases_value)) elif (thisTrait.db.type == "Geno"): self.cursor.execute(""" select count(GenoData.value) from GenoData, GenoXRef, GenoFreeze, Geno, Strain where Geno.SpeciesId = %s and Geno.Name='%s' and GenoXRef.GenoId = Geno.Id and GenoXRef.DataId = GenoData.Id and GenoXRef.GenoFreezeId = GenoFreeze.Id and GenoData.StrainId = Strain.Id and GenoFreeze.Name = '%s' """ % (webqtlDatabaseFunction.retrieveSpeciesId(self.cursor, thisTrait.db.riset), thisTrait.name, thisTrait.db.name)) result = self.cursor.fetchone() if result: if result[0]: n_cases_value = result[0] n_cases_repr = result[0] if (n_cases_value == "--"): tr.append(TDCell(HT.TD(n_cases_repr, Class=className, align='left', nowrap="on"), n_cases_repr, n_cases_value)) else: tr.append(TDCell(HT.TD(n_cases_repr, Class=className, align='right', nowrap="on"), n_cases_repr, n_cases_value)) else: tr.append(TDCell(HT.TD(n_cases_repr, Class=className, align='left', nowrap="on"), n_cases_repr, n_cases_value)) #XZ: Max LRS column and Max LRS Location column if (thisTrait.db.type != "Geno"): #LRS value LRS_score_repr = '--' LRS_score_value = 0 if hasattr(thisTrait, 'lrs') and thisTrait.lrs: LRS_score_repr = '%3.1f' % thisTrait.lrs LRS_score_value = thisTrait.lrs tr.append(TDCell(HT.TD(LRS_score_repr, Class=className, align='right', nowrap="on"), LRS_score_repr, LRS_score_value)) #LRS location LRS_location_repr = '--' LRS_location_value = 1000000 LRS_flag = 1 #Max LRS and its Locus location if hasattr(thisTrait, 'lrs') and hasattr(thisTrait, 'locus') and thisTrait.lrs and thisTrait.locus: self.cursor.execute(""" select Geno.Chr, Geno.Mb from Geno, Species where Species.Name = '%s' and Geno.Name = '%s' and Geno.SpeciesId = Species.Id """ % (species, thisTrait.locus)) result = self.cursor.fetchone() if result: if result[0] and result[1]: LRS_Chr = result[0] LRS_Mb = result[1] #XZ: LRS_location_value is used for sorting try: LRS_location_value = int(LRS_Chr)*1000 + float(LRS_Mb) except: if LRS_Chr.upper() == 'X': LRS_location_value = 20*1000 + float(LRS_Mb) else: LRS_location_value = ord(str(LRS_chr).upper()[0])*1000 + float(LRS_Mb) LRS_location_repr = 'Chr%s: %.6f' % (LRS_Chr, float(LRS_Mb)) LRS_flag = 0 tr.append(TDCell(HT.TD(LRS_location_repr, Class=className), LRS_location_repr, LRS_location_value)) if LRS_flag: tr.append(TDCell(HT.TD(LRS_location_repr, Class=className), LRS_location_repr, LRS_location_value)) else: tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", 0)) tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", 1000000)) try: additive = '%3.3f' % thisTrait.additive except Exception: additive = '' tr.append(TDCell(HT.TD(additive, Class=className, align="right", nowrap="on"), additive, additive)) tblobj_body.append(tr) return tblobj_body