1 files changed, 36 insertions, 6 deletions
diff --git a/uploader/publications/models.py b/uploader/publications/models.py
index 3fc9542..8dd62f3 100644
--- a/uploader/publications/models.py
+++ b/uploader/publications/models.py
@@ -1,5 +1,6 @@
 """Module to handle persistence and retrieval of publication to/from MariaDB"""
 import logging
+from typing import Iterable
 
 from MySQLdb.cursors import DictCursor
 
@@ -41,15 +42,10 @@ def create_new_publications(
                  "%(pubmed_id)s, %(abstract)s, %(authors)s, %(title)s, "
                  "%(journal)s, %(volume)s, %(pages)s, %(month)s, %(year)s"
                  ") "
-                 "ON DUPLICATE KEY UPDATE "
-                 "Abstract=VALUES(Abstract), Authors=VALUES(Authors), "
-                 "Title=VALUES(Title), Journal=VALUES(Journal), "
-                 "Volume=VALUES(Volume), Pages=VALUES(pages), "
-                 "Month=VALUES(Month), Year=VALUES(Year) "
                  "RETURNING *"),
                 publications)
             return tuple({
-                **row, "PublicationId": row["Id"]
+                **row, "publication_id": row["Id"]
             } for row in cursor.fetchall())
         return tuple()
 
@@ -71,3 +67,37 @@ def update_publications(conn: Connection , publications: tuple[dict, ...]) -> tu
             return publications
         return tuple()
     return tuple()
+
+
+def fetch_publications(conn: Connection) -> Iterable[dict]:
+    """Fetch publications from the database."""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute("SELECT * FROM Publication")
+        for row in cursor.fetchall():
+            yield dict(row)
+
+
+def fetch_publication_by_id(conn: Connection, publication_id: int) -> dict:
+    """Fetch a specific publication from the database."""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute("SELECT * FROM Publication WHERE Id=%s",
+                       (publication_id,))
+        return dict(cursor.fetchone())
+
+
+def fetch_publication_phenotypes(
+        conn: Connection, publication_id: int) -> Iterable[dict]:
+    """Fetch all phenotypes linked to this publication."""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute(
+            "SELECT pxr.Id AS xref_id, pxr.PublicationId, phe.* "
+            "FROM PublishXRef AS pxr INNER JOIN Phenotype AS phe "
+            "ON pxr.PhenotypeId=phe.Id "
+            "WHERE pxr.PublicationId=%s",
+            (publication_id,))
+        while True:
+            row = cursor.fetchone()
+            if row:
+                yield row
+            else:
+                break