diff options
Diffstat (limited to 'qc_app/db')
| -rw-r--r-- | qc_app/db/__init__.py | 2 | ||||
| -rw-r--r-- | qc_app/db/datasets.py | 30 |
2 files changed, 30 insertions, 2 deletions
diff --git a/qc_app/db/__init__.py b/qc_app/db/__init__.py index 270f1a0..36e93e8 100644 --- a/qc_app/db/__init__.py +++ b/qc_app/db/__init__.py @@ -5,4 +5,4 @@ from .populations import ( population_by_id, populations_by_species, population_by_species_and_id) -from .datasets import geno_dataset_by_species_and_population +from .datasets import geno_datasets_by_species_and_population diff --git a/qc_app/db/datasets.py b/qc_app/db/datasets.py index 8122cfa..086c103 100644 --- a/qc_app/db/datasets.py +++ b/qc_app/db/datasets.py @@ -2,7 +2,7 @@ import MySQLdb as mdb from MySQLdb.cursors import DictCursor -def geno_dataset_by_species_and_population( +def geno_datasets_by_species_and_population( conn: mdb.Connection, speciesid: int, populationid: int) -> tuple[dict, ...]: @@ -21,3 +21,31 @@ def geno_dataset_by_id(conn: mdb.Connection, dataset_id: int) -> dict: cursor.execute("SELECT * FROM GenoFreeze WHERE Id=%s", (dataset_id,)) return dict(cursor.fetchone()) + +def probeset_studies_by_species_and_population( + conn: mdb.Connection, + speciesid: int, + populationid: int) -> tuple[dict, ...]: + """Retrieve all probesets""" + with conn.cursor(cursorclass=DictCursor) as cursor: + cursor.execute( + "SELECT pf.* FROM InbredSet AS iset INNER JOIN ProbeFreeze AS pf " + "ON iset.InbredSetId=pf.InbredSetId " + "WHERE iset.SpeciesId=%(sid)s AND iset.InbredSetId=%(pid)s", + {"sid": speciesid, "pid": populationid}) + return tuple(dict(row) for row in cursor.fetchall()) + +def probeset_datasets_by_study(conn: mdb.Connection, + studyid: int) -> tuple[dict, ...]: + """Retrieve all probeset databases by study.""" + with conn.cursor(cursorclass=DictCursor) as cursor: + cursor.execute("SELECT * FROM ProbeSetFreeze WHERE ProbeFreezeId=%s", + (studyid,)) + return tuple(dict(row) for row in cursor.fetchall()) + +def probe_study_by_id(conn: mdb.Connection, studyid: int) -> dict: + """Retrieve ProbeSet study by ID""" + with conn.cursor(cursorclass=DictCursor) as cursor: + cursor.execute("SELECT * FROM ProbeFreeze WHERE Id=%s", + (studyid,)) + return dict(cursor.fetchone()) |