Samples: Read an save data to db.

3 files changed, 115 insertions, 2 deletions

diff --git a/qc_app/files.py b/qc_app/files.py
new file mode 100644
index 0000000..6485b27
--- /dev/null
+++ b/qc_app/files.py
@@ -0,0 +1,20 @@
+"""Utilities to deal with uploaded files."""
+from pathlib import Path
+from typing import Union
+
+from werkzeug.utils import secure_filename
+from flask import (
+    request,
+    current_app as app)
+
+def save_file(key: str, upload_dir: Path) -> Union[Path, bool]:
+    """Save the uploaded file and return the path."""
+    if not bool(request.files.get(key)):
+        return False
+    filename = Path(secure_filename(request.files[key].filename))
+    if not upload_dir.exists():
+        upload_dir.mkdir()
+
+    filepath = Path(upload_dir, filename)
+    request.files["samples_file"].save(filepath)
+    return filepath
diff --git a/qc_app/samples.py b/qc_app/samples.py
index cc745ca..27fdad3 100644
--- a/qc_app/samples.py
+++ b/qc_app/samples.py
@@ -1,9 +1,22 @@
 """Code regarding samples"""
+import csv
+from pathlib import Path
+from typing import Iterator
+
 import MySQLdb as mdb
 from MySQLdb.cursors import DictCursor
 from flask import (
-    flash, request, url_for, redirect, Blueprint, render_template, current_app as app)
+    flash,
+    request,
+    url_for,
+    redirect,
+    Blueprint,
+    render_template,
+    current_app as app)
+
+from quality_control.parsing import take
 
+from .files import save_file
 from .db_utils import with_db_connection
 from .dbinsert import species_by_id, groups_by_species
 
@@ -106,3 +119,83 @@ def select_population():
     return render_template("samples/upload-samples.html",
                            species=species,
                            population=population)
+
+def read_samples_file(filepath, separator: str, **kwargs) -> Iterator[dict]:
+    """Read the samples file."""
+    with open(filepath, "r", encoding="utf-8") as inputfile:
+        reader = csv.DictReader(
+            inputfile,
+            fieldnames=("Name", "Name2", "Symbol", "Alias"),
+            delimiter=separator,
+            quotechar=kwargs.get("quotechar"))
+        for row in reader:
+            yield row
+
+def save_samples_data(conn: mdb.Connection, file_data: Iterator[dict]):
+    """Save the samples to DB."""
+    with conn.cursor() as cursor:
+        while True:
+            cursor.executemany(
+                "INSERT INTO Strain(Name, Name2, SpeciesId, Symbol, Alias) "
+                "VALUES("
+                "    %(Name)s, %(Name2)s, %(SpeciesId)s, %(Symbol)s, %(Alias)s"
+                ")",
+                tuple(take(file_data, 10000)))
+
+def cross_reference_samples(conn: mdb.Connection,
+                            population_id: int,
+                            strain_names: tuple[str, ...]):
+    """Link samples to their population."""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        params_str = ", ".join(["%s"] * len(strain_names))
+        cursor.execute(
+            "SELECT Id FROM Strain WHERE (Name, SpeciesId) IN "
+            f"{params_str}",
+            tuple((name, species["SpeciesId"]) for name in strain_names))
+        strain_ids = (sid for sid in cursor.fetchall())
+        cursor.execute(
+            "SELECT MAX(OrderId) AS loid FROM StrainXRef WHERE InbredSetId=%s",
+            (population_id,))
+        last_order_id = cursor.fetchone()["loid"]
+        cursor.executemany(
+            "INSERT INTO StrainXRef(InbredSetId, StrainId, OrderId) "
+            "VALUES (%(pop_id)s, %(strain_id)s, %(order_id)s)",
+            tuple({
+                "pop_id": population_id,
+                "strain_id": strain_id,
+                "order_id": order_id
+            } for order_id, strain_id in
+                  enumerate(strain_ids, start=(last_order_id+10))))
+
+@samples.route("/upload/samples", methods=["POST"])
+def upload_samples():
+    """Upload the samples."""
+    samples_uploads_page = redirect(url_for("samples.select_population"),
+                                    code=307)
+
+    species = species_by_id(request.form.get("species_id"))
+    if not bool(species):
+        flash("Invalid species!", "alert-error")
+        return samples_uploads_page
+
+    population = with_db_connection(
+        lambda conn: population_by_id(
+            conn, int(request.form.get("inbredset_id"))))
+    if not bool(population):
+        flash("Invalid grouping/population!", "alert-error")
+        return samples_uploads_page
+
+    samples_file = save_file("samples_file", Path(app.config["UPLOAD_FOLDER"]))
+    if not bool(samples_file):
+        flash("You need to provide a file with the samples data.")
+        return samples_uploads_page
+
+    def __insert_samples__(conn: mdb.Connection):
+        save_samples_data(conn, read_samples_file(samples_file))
+        cross_reference_samples(
+            conn,
+            population["InbredSetId"],
+            tuple(row["Name"] for row in read_samples_file(samples_file)))
+
+    with_db_connection(__insert_samples__)
+    return "SUCCESS: Respond with a better UI than this."
diff --git a/qc_app/templates/samples/upload-samples.html b/qc_app/templates/samples/upload-samples.html
index 23dc8a8..5d1ec4c 100644
--- a/qc_app/templates/samples/upload-samples.html
+++ b/qc_app/templates/samples/upload-samples.html
@@ -40,7 +40,7 @@
 
 <form id="form-samples"
       method="POST"
-      action="#"
+      action="{{url_for('samples.upload_samples')}}"
       enctype="multipart/form-data">
   <legend class="heading">upload samples</legend>
   <fieldset>