aboutsummaryrefslogtreecommitdiff
path: root/qc_app/db/populations.py
blob: 4485e52d148402615d2f47b0bc1a4032307290c3 (plain)
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
        }