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
|
"""This module contains db functions that get data related to species or
groups. Particularly useful when generating the menu
"""
from typing import Any, Optional, Tuple
from MySQLdb import escape_string
def get_all_species(conn: Any) -> Optional[Tuple]:
"""Return a list of all species"""
with conn.cursor() as cursor:
cursor.execute("SELECT Name, MenuName FROM Species "
"ORDER BY OrderId")
return cursor.fetchall()
def get_chromosome(name: str, is_species: bool, conn: Any) -> Optional[Tuple]:
"""Given either a group or a species Name, return all the species"""
_sql = ("SELECT Chr_Length.Name, Chr_Length.OrderId, "
"Length FROM Chr_Length, Species WHERE "
"Chr_Length.SpeciesId = Species.SpeciesId AND "
"Species.Name = "
f"'{escape_string(name).decode('UTF-8')}' ORDER BY OrderId")
if not is_species:
_sql = ("SELECT Chr_Length.Name, Chr_Length.OrderId, "
"Length FROM Chr_Length, InbredSet WHERE "
"Chr_Length.SpeciesId = InbredSet.SpeciesId AND "
"InbredSet.Name = "
f"'{escape_string(name).decode('UTF-8')}' ORDER BY OrderId")
with conn.cursor() as cursor:
cursor.execute(_sql)
return cursor.fetchall()
def translate_to_mouse_gene_id(species: str, geneid: int, conn: Any) -> int:
"""
Translate rat or human geneid to mouse geneid
This is a migration of the
`web.webqtl.correlation/CorrelationPage.translateToMouseGeneID` function in
GN1
"""
assert species in ("rat", "mouse", "human"), "Invalid species"
if geneid is None:
return 0
if species == "mouse":
return geneid
with conn.cursor as cursor:
if species == "rat":
cursor.execute(
"SELECT mouse FROM GeneIDXRef WHERE rat = %s", geneid)
rat_geneid = cursor.fetchone()
if rat_geneid:
return rat_geneid[0]
cursor.execute(
"SELECT mouse FROM GeneIDXRef WHERE human = %s", geneid)
human_geneid = cursor.fetchone()
if human_geneid:
return human_geneid[0]
return 0 # default if all else fails
|
