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
|
import json
from gn2.base.data_set import create_dataset
from gn2.base.trait import GeneralTrait
from gn2.db import webqtlDatabaseFunction
from gn2.wqflask.database import database_connection
from gn2.utility.tools import get_setting
class GSearch:
def __init__(self, kw):
self.type = kw['type']
self.terms = kw['terms']
#self.row_range = kw['row_range']
if self.type == "gene":
results = None
with database_connection(get_setting("SQL_URI")) as conn, conn.cursor() as cursor:
cursor.execute("""
SELECT Species.`Name` AS species_name, InbredSet.`Name` AS inbredset_name,
Tissue.`Name` AS tissue_name, ProbeSetFreeze.Name AS probesetfreeze_name,
ProbeSet.Name AS probeset_name, ProbeSet.Symbol AS probeset_symbol,
ProbeSet.`description` AS probeset_description, ProbeSet.Chr AS chr, ProbeSet.Mb AS mb,
ProbeSetXRef.Mean AS mean, ProbeSetXRef.LRS AS lrs, ProbeSetXRef.`Locus` AS locus,
ProbeSetXRef.`pValue` AS pvalue, ProbeSetXRef.`additive` AS additive
FROM Species, InbredSet, ProbeSetXRef, ProbeSet, ProbeFreeze, ProbeSetFreeze, Tissue
WHERE InbredSet.`SpeciesId`=Species.`Id` AND ProbeFreeze.InbredSetId=InbredSet.`Id`
AND ProbeFreeze.`TissueId`=Tissue.`Id` AND ProbeSetFreeze.ProbeFreezeId=ProbeFreeze.Id
AND ( MATCH (ProbeSet.Name,ProbeSet.description,ProbeSet.symbol,alias,GenbankId, UniGeneId,
Probe_Target_Description) AGAINST (%s IN BOOLEAN MODE) )
AND ProbeSet.Id = ProbeSetXRef.ProbeSetId AND ProbeSetXRef.ProbeSetFreezeId=ProbeSetFreeze.Id
AND ProbeSetFreeze.public > 0 ORDER BY species_name, inbredset_name, tissue_name,
probesetfreeze_name, probeset_name LIMIT 6000""",
(self.terms,))
results = cursor.fetchall()
self.trait_list = []
for line in results:
dataset = create_dataset(
line[3], "ProbeSet", get_samplelist=False)
trait_id = line[4]
this_trait = GeneralTrait(
dataset=dataset, name=trait_id, get_qtl_info=True, get_sample_info=False)
self.trait_list.append(this_trait)
elif self.type == "phenotype":
with database_connection(get_setting("SQL_URI")) as conn, conn.cursor() as cursor:
results = None
cursor.execute("""
SELECT Species.`Name`, InbredSet.`Name`, PublishFreeze.`Name`, PublishXRef.`Id`,
Phenotype.`Post_publication_description`, Publication.`Authors`, Publication.`Year`,
PublishXRef.`LRS`, PublishXRef.`Locus`, PublishXRef.`additive` FROM Species, InbredSet,
PublishFreeze, PublishXRef, Phenotype, Publication WHERE PublishXRef.`InbredSetId`=InbredSet.`Id`
AND PublishFreeze.`InbredSetId`=InbredSet.`Id` AND InbredSet.`SpeciesId`=Species.`Id`
AND PublishXRef.`PhenotypeId`=Phenotype.`Id` AND PublishXRef.`PublicationId`=Publication.`Id`
AND (Phenotype.Post_publication_description REGEXP "[[:<:]]%s[[:>:]]"
OR Phenotype.Pre_publication_description REGEXP "[[:<:]]%s[[:>:]]"
OR Phenotype.Pre_publication_abbreviation REGEXP "[[:<:]]%s[[:>:]]"
OR Phenotype.Post_publication_abbreviation REGEXP "[[:<:]]%s[[:>:]]"
OR Phenotype.Lab_code REGEXP "[[:<:]]%s[[:>:]]"
OR Publication.PubMed_ID REGEXP "[[:<:]]%s[[:>:]]"
OR Publication.Abstract REGEXP "[[:<:]]%s[[:>:]]"
OR Publication.Title REGEXP "[[:<:]]%s[[:>:]]"
OR Publication.Authors REGEXP "[[:<:]]%s[[:>:]]"
OR PublishXRef.Id REGEXP "[[:<:]]%s[[:>:]]")
ORDER BY Species.`Name`, InbredSet.`Name`, PublishXRef.`Id` LIMIT 6000""",
((self.terms, ) * 10))
results = cursor.fetchall()
self.trait_list = []
for line in results:
dataset = create_dataset(line[2], "Publish")
trait_id = line[3]
this_trait = GeneralTrait(
dataset=dataset, name=trait_id, get_qtl_info=True, get_sample_info=False)
self.trait_list.append(this_trait)
self.results = self.convert_to_json()
def convert_to_json(self):
json_dict = {}
#json_dict['draw'] = self.draw,
json_dict['recordsTotal'] = len(self.trait_list),
json_dict['data'] = []
for i, trait in enumerate(self.trait_list):
trait_row = {"checkbox": "<INPUT TYPE=\"checkbox\" NAME=\"searchResult\" class=\"checkbox trait_checkbox\" style=\"transform: scale(1.5);\" VALUE=\"{}:{}\">".format(trait.name, trait.dataset.name),
"index": i + 1,
"species": trait.dataset.group.species,
"group": trait.dataset.group.name,
"tissue": trait.dataset.tissue,
"dataset": trait.dataset.fullname,
"record": "<a href=\"/show_trait?trait_id=" + trait.name + "&dataset=" + trait.dataset.name + "\" target=\"_blank\">" + trait.name + "</a>",
"symbol": trait.symbol,
"description": trait.description_display,
"location": trait.location_repr,
"mean": trait.mean,
"max_lrs": trait.LRS_score_repr,
"max_lrs_location": trait.LRS_location_repr,
"additive_effect": trait.additive}
json_dict['data'].append(trait_row)
json_results = json.dumps(json_dict)
return json_results
|
