From 33e106334a212853842c30f8c595da1099c3d84e Mon Sep 17 00:00:00 2001
From: Frederick Muriuki Muriithi
Date: Wed, 17 Jan 2024 11:41:43 +0300
Subject: UI: Select ProbeSet study.

---
 qc_app/db/__init__.py |  2 +-
 qc_app/db/datasets.py | 30 +++++++++++++++++++++++++++++-
 2 files changed, 30 insertions(+), 2 deletions(-)

(limited to 'qc_app/db')

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())
-- 
cgit v1.2.3