aboutsummaryrefslogtreecommitdiff
path: root/wqflask/dbFunction/webqtlDatabaseFunction.py
diff options
context:
space:
mode:
Diffstat (limited to 'wqflask/dbFunction/webqtlDatabaseFunction.py')
-rwxr-xr-xwqflask/dbFunction/webqtlDatabaseFunction.py293
1 files changed, 146 insertions, 147 deletions
diff --git a/wqflask/dbFunction/webqtlDatabaseFunction.py b/wqflask/dbFunction/webqtlDatabaseFunction.py
index 772e0526..7e33da3f 100755
--- a/wqflask/dbFunction/webqtlDatabaseFunction.py
+++ b/wqflask/dbFunction/webqtlDatabaseFunction.py
@@ -26,7 +26,7 @@
#webqtlDatabaseFunction.py
#
-#This file consists of various database related functions; the names are generally self-explanatory.
+#This file consists of various database related functions; the names are generally self-explanatory.
import MySQLdb
import string
@@ -49,63 +49,63 @@ def getCursor():
###########################################################################
#input: cursor, groupName (string)
#output: mappingMethodId (int) info, value will be Null or else
-#function: retrieve mappingMethodId info from InbredSet table
-###########################################################################
+#function: retrieve mappingMethodId info from InbredSet table
+###########################################################################
def getMappingMethod(cursor=None, groupName=None):
- cursor.execute("select MappingMethodId from InbredSet where Name= '%s'" % groupName)
- mappingMethodId = cursor.fetchone()[0]
- return mappingMethodId
+ cursor.execute("select MappingMethodId from InbredSet where Name= '%s'" % groupName)
+ mappingMethodId = cursor.fetchone()[0]
+ return mappingMethodId
###########################################################################
#input: cursor, inbredSetId (int), strainId (int)
-#output: isMappingId (bull) info, value will be 0,1,2 or else, 0 or Null means
-# "can not do mapping", >0 means "can do mapping", >1 means "there exsists
+#output: isMappingId (bull) info, value will be 0,1,2 or else, 0 or Null means
+# "can not do mapping", >0 means "can do mapping", >1 means "there exsists
# redundant data, user needs to choose one to do mapping function"
-#function: retrieve isMappingId info from StrainXRef table
-###########################################################################
-
-def isMapping(cursor=None, inbredSetId=None, strainId=None):
- cursor.execute("select IsMapping from StrainXRef where InbredSetId='%d' and StrainId = '%d'" %(inbredSetId, strainId))
- isMappingId = cursor.fetchone()[0]
- return isMappingId
+#function: retrieve isMappingId info from StrainXRef table
+###########################################################################
+
+def isMapping(cursor=None, inbredSetId=None, strainId=None):
+ cursor.execute("select IsMapping from StrainXRef where InbredSetId='%d' and StrainId = '%d'" %(inbredSetId, strainId))
+ isMappingId = cursor.fetchone()[0]
+ return isMappingId
###########################################################################
#input: cursor, groupName (string)
#output: all species data info (array), value will be Null or else
-#function: retrieve all species info from Species table
-###########################################################################
+#function: retrieve all species info from Species table
+###########################################################################
def getAllSpecies(cursor=None):
- cursor.execute("select Id, Name, MenuName, FullName, TaxonomyId,OrderId from Species Order by OrderId")
- allSpecies = cursor.fetchall()
- return allSpecies
+ cursor.execute("select Id, Name, MenuName, FullName, TaxonomyId,OrderId from Species Order by OrderId")
+ allSpecies = cursor.fetchall()
+ return allSpecies
###########################################################################
#input: cursor, RISet (string)
#output: specie's name (string), value will be None or else
-#function: retrieve specie's name info based on RISet
-###########################################################################
+#function: retrieve specie's name info based on RISet
+###########################################################################
def retrieveSpecies(cursor=None, RISet=None):
- try:
- cursor.execute("select Species.Name from Species, InbredSet where InbredSet.Name = '%s' and InbredSet.SpeciesId = Species.Id" % RISet)
- return cursor.fetchone()[0]
- except:
- return None
+ try:
+ cursor.execute("select Species.Name from Species, InbredSet where InbredSet.Name = '%s' and InbredSet.SpeciesId = Species.Id" % RISet)
+ return cursor.fetchone()[0]
+ except:
+ return None
###########################################################################
#input: cursor, RISet (string)
#output: specie's Id (string), value will be None or else
-#function: retrieve specie's Id info based on RISet
-###########################################################################
-
+#function: retrieve specie's Id info based on RISet
+###########################################################################
+
def retrieveSpeciesId(cursor=None, RISet=None):
- try:
- cursor.execute("select SpeciesId from InbredSet where Name = '%s'" % RISet)
- return cursor.fetchone()[0]
- except:
- return None
+ try:
+ cursor.execute("select SpeciesId from InbredSet where Name = '%s'" % RISet)
+ return cursor.fetchone()[0]
+ except:
+ return None
###########################################################################
# input: cursor
@@ -118,24 +118,24 @@ def retrieveSpeciesId(cursor=None, RISet=None):
###########################################################################
def getTissueDataSet(cursor=None):
- tissProbeSetFreezeIdList=[]
- nameList =[]
- fullNameList = []
-
- query = "select Id,Name,FullName from TissueProbeSetFreeze; "
- try:
- cursor.execute(query)
- result = cursor.fetchall()
-
- for row in result:
- tissProbeSetFreezeIdList.append(row[0])
- nameList.append(row[1])
- fullNameList.append(row[2])
- except:
- return None
-
- return tissProbeSetFreezeIdList,nameList,fullNameList
-
+ tissProbeSetFreezeIdList=[]
+ nameList =[]
+ fullNameList = []
+
+ query = "select Id,Name,FullName from TissueProbeSetFreeze; "
+ try:
+ cursor.execute(query)
+ result = cursor.fetchall()
+
+ for row in result:
+ tissProbeSetFreezeIdList.append(row[0])
+ nameList.append(row[1])
+ fullNameList.append(row[2])
+ except:
+ return None
+
+ return tissProbeSetFreezeIdList,nameList,fullNameList
+
###########################################################################
# input: cursor,GeneSymbol (string), and TissueProbeSetFreezeId (string)
# output: geneId (string), dataId (string)
@@ -143,42 +143,42 @@ def getTissueDataSet(cursor=None):
###########################################################################
def getGeneIdDataIdForTissueBySymbol(cursor=None, GeneSymbol=None, TissueProbeSetFreezeId= 0):
- query ="select GeneId, DataId from TissueProbeSetXRef where Symbol = '%s' and TissueProbeSetFreezeId=%s order by Mean desc" %(GeneSymbol,TissueProbeSetFreezeId)
- try:
- cursor.execute(query)
- result = cursor.fetchone()
- geneId = result[0]
- dataId = result[1]
- except:
- geneId = 0
- dataId = 0
-
- return geneId,dataId
+ query ="select GeneId, DataId from TissueProbeSetXRef where Symbol = '%s' and TissueProbeSetFreezeId=%s order by Mean desc" %(GeneSymbol,TissueProbeSetFreezeId)
+ try:
+ cursor.execute(query)
+ result = cursor.fetchone()
+ geneId = result[0]
+ dataId = result[1]
+ except:
+ geneId = 0
+ dataId = 0
+
+ return geneId,dataId
###########################################################################
# input: cursor, TissueProbeSetFreezeId (int)
# output: chipId (int)
-# function: retrieve chipId from TissueProbeFreeze table
+# function: retrieve chipId from TissueProbeFreeze table
###########################################################################
def getChipIdByTissueProbeSetFreezeId(cursor=None, TissueProbeSetFreezeId=None):
- query = "select TissueProbeFreezeId from TissueProbeSetFreeze where Id =%s" % TissueProbeSetFreezeId
- try:
- cursor.execute(query)
- result = cursor.fetchone()
- TissueProbeFreezeId = result[0]
- except:
- TissueProbeFreezeId =0
-
- query1 = "select ChipId from TissueProbeFreeze where Id =%s" % TissueProbeFreezeId
- try:
- cursor.execute(query1)
- result1 = cursor.fetchone()
- chipId = result1[0]
- except:
- chipId =0
-
- return chipId
+ query = "select TissueProbeFreezeId from TissueProbeSetFreeze where Id =%s" % TissueProbeSetFreezeId
+ try:
+ cursor.execute(query)
+ result = cursor.fetchone()
+ TissueProbeFreezeId = result[0]
+ except:
+ TissueProbeFreezeId =0
+
+ query1 = "select ChipId from TissueProbeFreeze where Id =%s" % TissueProbeFreezeId
+ try:
+ cursor.execute(query1)
+ result1 = cursor.fetchone()
+ chipId = result1[0]
+ except:
+ chipId =0
+
+ return chipId
###########################################################################
# input: cursor, TissueProbeSetFreezeId (int)
@@ -186,80 +186,79 @@ def getChipIdByTissueProbeSetFreezeId(cursor=None, TissueProbeSetFreezeId=None):
# function: retrieve how many tissue used in the specific dataset based on TissueProbeSetFreezeId
###########################################################################
def getTissueCountByTissueProbeSetFreezeId(cursor=None, TissueProbeSetFreezeId=None):
- query1 ="select DataId from TissueProbeSetXRef where TissueProbeSetFreezeId =%s limit 1" % TissueProbeSetFreezeId
- try:
- cursor.execute(query1)
- result1 = cursor.fetchone()
- DataId = result1[0]
-
- query2 =" select count(*) from TissueProbeSetData where Id=%s" % DataId
- try:
- cursor.execute(query2)
- result2 = cursor.fetchone()
- TissueCount = result2[0]
- except:
- TissueCount =0
- except:
- TissueCount =0
-
- return TissueCount
-
+ query1 ="select DataId from TissueProbeSetXRef where TissueProbeSetFreezeId =%s limit 1" % TissueProbeSetFreezeId
+ try:
+ cursor.execute(query1)
+ result1 = cursor.fetchone()
+ DataId = result1[0]
+
+ query2 =" select count(*) from TissueProbeSetData where Id=%s" % DataId
+ try:
+ cursor.execute(query2)
+ result2 = cursor.fetchone()
+ TissueCount = result2[0]
+ except:
+ TissueCount =0
+ except:
+ TissueCount =0
+
+ return TissueCount
+
###########################################################################
# input: cursor, TissueProbeSetFreezeId (int)
# output: DatasetName(string),DatasetFullName(string)
# function: retrieve DatasetName, DatasetFullName based on TissueProbeSetFreezeId
###########################################################################
def getDatasetNamesByTissueProbeSetFreezeId(cursor=None, TissueProbeSetFreezeId=None):
- query ="select Name, FullName from TissueProbeSetFreeze where Id=%s" % TissueProbeSetFreezeId
- try:
- cursor.execute(query)
- result = cursor.fetchone()
- DatasetName = result[0]
- DatasetFullName =result[1]
- except:
- DatasetName =None
- DatasetFullName =None
-
- return DatasetName, DatasetFullName
-
+ query ="select Name, FullName from TissueProbeSetFreeze where Id=%s" % TissueProbeSetFreezeId
+ try:
+ cursor.execute(query)
+ result = cursor.fetchone()
+ DatasetName = result[0]
+ DatasetFullName =result[1]
+ except:
+ DatasetName =None
+ DatasetFullName =None
+
+ return DatasetName, DatasetFullName
+
###########################################################################
# input: cursor, geneIdLst (list)
# output: geneIdSymbolPair(dict),key is geneId, value is geneSymbol
# function: retrieve GeneId, GeneSymbol based on geneId List
-###########################################################################
+###########################################################################
def getGeneIdSymbolPairByGeneId(cursor=None, geneIdLst =None):
- geneIdSymbolPair={}
- for geneId in geneIdLst:
- geneIdSymbolPair[geneId]=None
-
- query ="select GeneId,GeneSymbol from GeneList where GeneId in (%s)" % string.join(geneIdLst, ", ")
- try:
- cursor.execute(query)
- results = cursor.fetchall()
- for item in results:
- geneId =item[0]
- geneSymbol =item[1]
- geneIdSymbolPair[geneId]=geneSymbol
- except:
- geneIdSymbolPair=None
-
- return geneIdSymbolPair
-
-
+ geneIdSymbolPair={}
+ for geneId in geneIdLst:
+ geneIdSymbolPair[geneId]=None
+
+ query ="select GeneId,GeneSymbol from GeneList where GeneId in (%s)" % string.join(geneIdLst, ", ")
+ try:
+ cursor.execute(query)
+ results = cursor.fetchall()
+ for item in results:
+ geneId =item[0]
+ geneSymbol =item[1]
+ geneIdSymbolPair[geneId]=geneSymbol
+ except:
+ geneIdSymbolPair=None
+
+ return geneIdSymbolPair
+
+
def updateTissueProbesetXRefByProbesetId(cursor=None, probesetId=None):
- query ="select Symbol,GeneId,Chr,Mb,description, Probe_Target_Description from ProbeSet where Id =%s"%probesetId
- try:
- cursor.execute(query)
- result =cursor.fetchone()
-
- updateQuery ='''
- Update TissueProbeSetXRef
- Set Symbol='%s',GeneId='%s', Chr='%s', Mb='%s', description ='%s',Probe_Target_Description='%s'
- where ProbesetId=%s
- '''%(result[0],result[1],result[2],result[3],result[4],result[5],probesetId)
-
- cursor.execute(updateQuery)
-
- except:
- return None
- \ No newline at end of file
+ query ="select Symbol,GeneId,Chr,Mb,description, Probe_Target_Description from ProbeSet where Id =%s"%probesetId
+ try:
+ cursor.execute(query)
+ result =cursor.fetchone()
+
+ updateQuery ='''
+ Update TissueProbeSetXRef
+ Set Symbol='%s',GeneId='%s', Chr='%s', Mb='%s', description ='%s',Probe_Target_Description='%s'
+ where ProbesetId=%s
+ '''%(result[0],result[1],result[2],result[3],result[4],result[5],probesetId)
+
+ cursor.execute(updateQuery)
+
+ except:
+ return None