1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
|
# Copyright (C) University of Tennessee Health Science Center, Memphis, TN.
#
# This program is free software: you can redistribute it and/or modify it
# under the terms of the GNU Affero General Public License
# as published by the Free Software Foundation, either version 3 of the
# License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
# See the GNU Affero General Public License for more details.
#
# This program is available from Source Forge: at GeneNetwork Project
# (sourceforge.net/projects/genenetwork/).
#
# Contact Drs. Robert W. Williams and Xiaodong Zhou (2010)
# at rwilliams@uthsc.edu and xzhou15@uthsc.edu
#
#
#
# This module is used by GeneNetwork project (www.genenetwork.org)
from inspect import stack
from flask import Flask, g
import MySQLdb
import string
import urllib2
import json
from base import webqtlConfig
from utility.tools import USE_GN_SERVER, LOG_SQL
from utility.benchmark import Bench
from utility.logger import getLogger
logger = getLogger(__name__ )
###########################################################################
#output: cursor instance
#function: connect to database and return cursor instance
###########################################################################
def getCursor():
try:
logger.warning("Creating new MySQLdb cursor")
con = MySQLdb.Connect(db=webqtlConfig.DB_NAME, host=webqtlConfig.MYSQL_SERVER, user=webqtlConfig.DB_USER, passwd=webqtlConfig.DB_PASSWD)
cursor = con.cursor()
return cursor
except:
return None
def fetchone(query):
"""Return tuple containing one row by calling SQL directly
"""
with Bench("SQL",LOG_SQL):
def helper(query):
res = g.db.execute(query)
return res.fetchone()
callername = stack()[1][3]
return logger.sql(callername, query, helper)
def gn_server(path):
"""Return JSON record by calling GN_SERVER
"""
with Bench("GN_SERVER",LOG_SQL):
res = urllib2.urlopen("http://localhost:8880/"+path)
rest = res.read()
res2 = json.loads(rest)
logger.info(res2)
return res2
def retrieve_species(group):
"""Get the species of a group (e.g. returns string "mouse" on "BXD"
"""
if USE_GN_SERVER:
result = gn_server("/cross/"+group+".json")
return result["species"]
else:
result = fetchone("select Species.Name from Species, InbredSet where InbredSet.Name = '%s' and InbredSet.SpeciesId = Species.Id" % (group))
return result[0]
def getMappingMethod(cursor=None, groupName=None):
if USE_GN_SERVER:
return gn_server("/cross/"+group+".json")["mapping_method_id"]
else:
return fetchone("select MappingMethodId from InbredSet where Name= '%s'" % groupName)
###########################################################################
#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
# 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
###########################################################################
#input: cursor, groupName (string)
#output: all species data info (array), value will be Null or else
#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
def retrieve_species_id(group):
return g.db.execute("select SpeciesId from InbredSet where Name = %s", (group)).fetchone()[0]
def getTissueDataSet(cursor=None):
"""Retrieve all TissueProbeSetFreezeId,Name,FullName info from
TissueProbeSetFreeze table. These data will listed in the dropdown
menu in the first page of Tissue Correlation
"""
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)
# function: retrieve geneId and DataId from TissueProbeSetXRef table
###########################################################################
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
###########################################################################
# input: cursor, TissueProbeSetFreezeId (int)
# output: chipId (int)
# 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
###########################################################################
# input: cursor, TissueProbeSetFreezeId (int)
# output: TissueCount (int)
# 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
###########################################################################
# 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
###########################################################################
# 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
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
|
