From 8b5400fa04d4ca2e60d7e926800816245e2ab809 Mon Sep 17 00:00:00 2001 From: Frederick Muriuki Muriithi Date: Thu, 14 Jul 2022 06:20:44 +0300 Subject: Enable creation of new dataset Enable the user to create a new dataset should the need arise. A few extra fixes were done, such as: - Provide list of average methods to choose from - Provide input elements for some expected fields - Add a new confirmation step before doing the actual data update --- qc_app/dbinsert.py | 93 +++++++++++++++++++++++++++++++++++++++++------------- 1 file changed, 71 insertions(+), 22 deletions(-) (limited to 'qc_app/dbinsert.py') diff --git a/qc_app/dbinsert.py b/qc_app/dbinsert.py index c106366..2b29749 100644 --- a/qc_app/dbinsert.py +++ b/qc_app/dbinsert.py @@ -1,8 +1,8 @@ "Handle inserting data into the database" import os import json -import datetime from functools import reduce +from datetime import datetime import requests from redis import Redis @@ -167,7 +167,7 @@ def create_study(): form["tissueid"], form["studyname"], form.get("studyfullname", ""), form.get("studyshortname", ""), - datetime.datetime.now().date().strftime("%Y-%m-%d"), + datetime.now().date().strftime("%Y-%m-%d"), form["inbredsetid"]) query = ( "INSERT INTO ProbeFreeze() " @@ -188,12 +188,34 @@ def datasets_by_study(studyid:int) -> tuple: with database_connection() as conn: with conn.cursor(cursorclass=DictCursor) as cursor: query = "SELECT * FROM ProbeSetFreeze WHERE ProbeFreezeId=%s" - print(f"QUERY: {query}\n\tPARAMS: ({studyid},)") cursor.execute(query, (studyid,)) return tuple(cursor.fetchall()) return tuple() +def averaging_methods() -> tuple: + "Retrieve averaging methods from database" + with database_connection() as conn: + with conn.cursor(cursorclass=DictCursor) as cursor: + cursor.execute("SELECT * FROM AvgMethod") + return tuple(cursor.fetchall()) + + return tuple() + +def dataset_datascales() -> tuple: + "Retrieve datascales from database" + with database_connection() as conn: + with conn.cursor() as cursor: + cursor.execute( + 'SELECT DISTINCT DataScale FROM ProbeSetFreeze ' + 'WHERE DataScale IS NOT NULL AND DataScale != ""') + return tuple( + item for item in + (res[0].strip() for res in cursor.fetchall()) + if (item is not None and item != "")) + + return tuple() + @dbinsertbp.route("/dataset", methods=["POST"]) def select_dataset(): "Select the dataset to add the file contents against" @@ -210,27 +232,54 @@ def select_dataset(): return render_template( "select_dataset.html", filename=form["filename"], filetype=form["filetype"], species=form["species"], - genechipid=form["genechipid"], studyid=studyid, datasets=datasets) + genechipid=form["genechipid"], studyid=studyid, datasets=datasets, + avgmethods=averaging_methods(), datascales=dataset_datascales()) except AssertionError as aserr: return render_error(f"Missing data: {aserr.args[0]}") +@dbinsertbp.route("/create-dataset", methods=["POST"]) +def create_dataset(): + "Select the dataset to add the file contents against" + form = request.form + try: + assert form.get("filename"), "filename" + assert form.get("filetype"), "filetype" + assert form.get("species"), "species" + assert form.get("genechipid"), "platform" + assert form.get("studyid"), "study" + assert form.get("avgid"), "averaging method" + assert form.get("datasetname2"), "Dataset Name 2" + assert form.get("datasetfullname"), "Dataset Full Name" + assert form.get("datasetshortname"), "Dataset Short Name" + assert form.get("datasetpublic"), "Dataset public specification" + assert form.get("datasetconfidentiality"), "Dataset confidentiality" + assert form.get("datasetdatascale"), "Dataset Datascale" + + with database_connection() as conn: + with conn.cursor(cursorclass=DictCursor) as cursor: + cursor.execute("SELECT MAX(Id) AS last_id FROM ProbeSetFreeze") + new_datasetid = cursor.fetchone()["last_id"] + 1 + values = ( + new_datasetid, new_datasetid, form["avgid"], + form.get("datasetname",""), form["datasetname2"], + form["datasetfullname"], form["datasetshortname"], + datetime.now().date().strftime("%Y-%m-%d"), + form["datasetpublic"], form["datasetconfidentiality"], + form["datasetdatascale"]) + query = ( + "INSERT INTO ProbeSetFreeze VALUES" + "(%s, %s, %s, %s, %s, %s, %s, %s, NULL, %s, %s, NULL, %s)") + cursor.execute(query, values) + return render_template( + "continue_from_create_dataset.html", + filename=form["filename"], filetype=form["filetype"], + species=form["species"], genechipid=form["genechipid"], + studyid=form["studyid"], datasetid=new_datasetid) + except AssertionError as aserr: + flash(f"Missing data {aserr.args[0]}", "alert-error") + return redirect(url_for("dbinsert.select_dataset"), code=307) -@dbinsertbp.route("/insert_data", methods=["POST"]) -def insert_data(): +@dbinsertbp.route("/final-confirmation", methods=["POST"]) +def final_confirmation(): "Preview the data before triggering entry into the database" - form = request.form - filename = form["filename"] - filepath = f"{app.config['UPLOAD_FOLDER']}/{filename}" - if os.path.exists(filepath): - try: - species = form["species"] - filetype = form["filetype"] - datasetid = int(form["dataset"]) - genechipid = int(form["genechipid"]) - return (f"Would insert '{species}' data in '{filetype}' file " - f"'{filepath}' into the database with the dataset " - f"'{datasetid}' and genechip '{genechipid}'.") - except ValueError as verr: - msg = "::".join(verr.args) - return render_error(f"Invalid value: {msg}") - return render_error(f"File '{filename}' no longer exists.") + return str(request.form) -- cgit v1.2.3