aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorFrederick Muriuki Muriithi2023-12-07 16:29:05 +0300
committerFrederick Muriuki Muriithi2023-12-07 16:29:05 +0300
commit49b67d306147ab21f91aaf00831d5b36e28585f6 (patch)
treea6c423ea3a13528ba995f9d791968dd901654b7e
parent8760d188faebe552028f3142a821d0851df4e1e9 (diff)
downloadgn-uploader-49b67d306147ab21f91aaf00831d5b36e28585f6.tar.gz
Samples: Read an save data to db.
-rw-r--r--qc_app/files.py20
-rw-r--r--qc_app/samples.py95
-rw-r--r--qc_app/templates/samples/upload-samples.html2
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>