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
|
import utilities
def get_probesetfreezes(inbredsetid):
cursor, con = utilities.get_cursor()
sql = """
SELECT ProbeSetFreeze.`Id`, ProbeSetFreeze.`Name`, ProbeSetFreeze.`FullName`
FROM ProbeSetFreeze, ProbeFreeze
WHERE ProbeSetFreeze.`ProbeFreezeId`=ProbeFreeze.`Id`
AND ProbeFreeze.`InbredSetId`=%s
"""
cursor.execute(sql, (inbredsetid))
return cursor.fetchall()
def get_probesetfreeze(probesetfreezeid):
cursor, con = utilities.get_cursor()
sql = """
SELECT ProbeSetFreeze.`Id`, ProbeSetFreeze.`Name`, ProbeSetFreeze.`FullName`
FROM ProbeSetFreeze
WHERE ProbeSetFreeze.`Id`=%s
"""
cursor.execute(sql, (probesetfreezeid))
return cursor.fetchone()
def get_strains(inbredsetid):
cursor, con = utilities.get_cursor()
sql = """
SELECT Strain.`Id`, Strain.`Name`
FROM StrainXRef, Strain
WHERE StrainXRef.`InbredSetId`=%s
AND StrainXRef.`StrainId`=Strain.`Id`
ORDER BY StrainXRef.`OrderId`
"""
cursor.execute(sql, (inbredsetid))
return cursor.fetchall()
def get_inbredset(probesetfreezeid):
cursor, con = utilities.get_cursor()
sql = """
SELECT InbredSet.`Id`, InbredSet.`Name`, InbredSet.`FullName`
FROM InbredSet, ProbeFreeze, ProbeSetFreeze
WHERE InbredSet.`Id`=ProbeFreeze.`InbredSetId`
AND ProbeFreeze.`Id`=ProbeSetFreeze.`ProbeFreezeId`
AND ProbeSetFreeze.`Id`=%s
"""
cursor.execute(sql, (probesetfreezeid))
return cursor.fetchone()
def get_species(inbredsetid):
cursor, con = utilities.get_cursor()
sql = """
SELECT Species.`Id`, Species.`Name`, Species.`MenuName`, Species.`FullName`
FROM InbredSet, Species
WHERE InbredSet.`Id`=%s
AND InbredSet.`SpeciesId`=Species.`Id`
"""
cursor.execute(sql, (inbredsetid))
return cursor.fetchone()
def get_genofreeze_byinbredsetid(inbredsetid):
cursor, con = utilities.get_cursor()
sql = """
SELECT GenoFreeze.`Id`, GenoFreeze.`Name`, GenoFreeze.`FullName`, GenoFreeze.`InbredSetId`
FROM GenoFreeze
WHERE GenoFreeze.`InbredSetId`=%s
"""
cursor.execute(sql, (inbredsetid))
return cursor.fetchone()
def get_nextdataid_genotype():
cursor, con = utilities.get_cursor()
sql = """
SELECT GenoData.`Id`
FROM GenoData
ORDER BY GenoData.`Id` DESC
LIMIT 1
"""
cursor.execute(sql)
re = cursor.fetchone()
dataid = re[0]
dataid += 1
return dataid
def get_nextdataid_phenotype():
cursor, con = utilities.get_cursor()
sql = """
SELECT PublishData.`Id`
FROM PublishData
ORDER BY PublishData.`Id` DESC
LIMIT 1
"""
cursor.execute(sql)
re = cursor.fetchone()
dataid = re[0]
dataid += 1
return dataid
def get_nextorderid_strainxref(inbredsetid):
cursor, con = utilities.get_cursor()
sql = """
SELECT StrainXRef.`OrderId`
FROM StrainXRef
WHERE StrainXRef.`InbredSetId`=%s
ORDER BY StrainXRef.`OrderId` DESC
LIMIT 1
"""
cursor.execute(sql, (inbredsetid))
re = cursor.fetchone()
if re:
orderid = re[0] + 1
else:
orderid = 1
return orderid
def insert_strain(inbredsetid, strainname):
speciesid = get_species(inbredsetid)[0]
cursor, con = utilities.get_cursor()
sql = """
INSERT INTO Strain
SET
Strain.`Name`=%s,
Strain.`Name2`=%s,
Strain.`SpeciesId`=%s
"""
cursor.execute(sql, (strainname, strainname, speciesid))
def insert_strainxref(inbredsetid, strainid):
orderid = get_nextorderid_strainxref(inbredsetid)
cursor, con = utilities.get_cursor()
sql = """
INSERT INTO StrainXRef
SET
StrainXRef.`InbredSetId`=%s,
StrainXRef.`StrainId`=%s,
StrainXRef.`OrderId`=%s,
StrainXRef.`Used_for_mapping`=%s,
StrainXRef.`PedigreeStatus`=%s
"""
cursor.execute(sql, (inbredsetid, strainid, orderid, "N", None))
def get_strain(inbredsetid, strainname):
speciesid = get_species(inbredsetid)[0]
cursor, con = utilities.get_cursor()
sql = """
SELECT Strain.`Id`, Strain.`Name`
FROM Strain
WHERE Strain.`SpeciesId`=%s
AND Strain.`Name` LIKE %s
"""
cursor.execute(sql, (speciesid, strainname))
return cursor.fetchone()
def get_strainxref(inbredsetid, strainid):
cursor, con = utilities.get_cursor()
sql = """
SELECT StrainXRef.`StrainId`
FROM StrainXRef
WHERE StrainXRef.`InbredSetId`=%s
AND StrainXRef.`StrainId`=%s
"""
cursor.execute(sql, (inbredsetid, strainid))
return cursor.fetchone()
def get_strain_sure(inbredsetid, strainname, updatestrainxref=None):
strain = get_strain(inbredsetid, strainname)
if not strain:
insert_strain(inbredsetid, strainname)
strain = get_strain(inbredsetid, strainname)
strainxref = get_strainxref(inbredsetid, strain[0])
if not strainxref and updatestrainxref:
insert_strainxref(inbredsetid, strain[0])
return strain
def get_strains_bynames(inbredsetid, strainnames, updatestrainxref=None):
strains = []
for strainname in strainnames:
strains.append(get_strain_sure(inbredsetid, strainname, updatestrainxref))
return strains
|
