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
|
"""Functions for accessing the database relating to species populations."""
import MySQLdb as mdb
from MySQLdb.cursors import DictCursor
def population_by_id(conn: mdb.Connection, population_id) -> dict:
"""Get the grouping/population by id."""
with conn.cursor(cursorclass=DictCursor) as cursor:
cursor.execute("SELECT * FROM InbredSet WHERE InbredSetId=%s",
(population_id,))
return cursor.fetchone()
def population_by_species_and_id(
conn: mdb.Connection, species_id, population_id) -> dict:
"""Retrieve a population by its identifier and species."""
with conn.cursor(cursorclass=DictCursor) as cursor:
cursor.execute("SELECT * FROM InbredSet WHERE SpeciesId=%s AND Id=%s",
(species_id, population_id))
return cursor.fetchone()
def populations_by_species(conn: mdb.Connection, speciesid) -> tuple:
"Retrieve group (InbredSet) information from the database."
with conn.cursor(cursorclass=DictCursor) as cursor:
query = "SELECT * FROM InbredSet WHERE SpeciesId=%s"
cursor.execute(query, (speciesid,))
return tuple(cursor.fetchall())
return tuple()
def save_population(conn: mdb.Connection, population_details: dict) -> dict:
"""Save the population details to the db."""
with conn.cursor(cursorclass=DictCursor) as cursor:
cursor.execute(
"INSERT INTO InbredSet("
"InbredSetId, InbredSetName, Name, SpeciesId, FullName, "
"MenuOrderId, Description"
") "
"VALUES ("
"%(InbredSetId)s, %(InbredSetName)s, %(Name)s, %(SpeciesId)s, "
"%(FullName)s, %(MenuOrderId)s, %(Description)s"
")",
{
"MenuOrderId": 0,
"InbredSetId": 0,
**population_details
})
new_id = cursor.lastrowid
cursor.execute("UPDATE InbredSet SET InbredSetId=%s WHERE Id=%s",
(new_id, new_id))
return {
**population_details,
"Id": new_id,
"InbredSetId": new_id,
"population_id": new_id
}
|
