From c316519b4b84f16234db2939a7c8544b33c0667d Mon Sep 17 00:00:00 2001
From: Frederick Muriuki Muriithi
Date: Wed, 17 Apr 2024 02:40:03 +0300
Subject: Use `cursor.lastrowid` to get row identifier

Use the more robust `cursor.lastrowid` to get the row identifier
value, rather than using the `SELECT MAX(Id) …` form. With the
`cursor.lastrowid` form, we do not have to remember to add 1 to the
value to avoid conflict.
---
 qc_app/dbinsert.py | 26 ++++++++++++--------------
 1 file changed, 12 insertions(+), 14 deletions(-)

diff --git a/qc_app/dbinsert.py b/qc_app/dbinsert.py
index 150127d..ef08423 100644
--- a/qc_app/dbinsert.py
+++ b/qc_app/dbinsert.py
@@ -156,25 +156,24 @@ def create_study():
 
         with database_connection() as conn:
             with conn.cursor(cursorclass=DictCursor) as cursor:
-                cursor.execute("SELECT MAX(Id) AS last_id FROM ProbeFreeze")
-                new_studyid = cursor.fetchone()["last_id"] + 1
                 values = (
-                    new_studyid, new_studyid, form["genechipid"],
-                    form["tissueid"], form["studyname"],
+                    form["genechipid"],
+                    form["tissueid"],
+                    form["studyname"],
                     form.get("studyfullname", ""),
                     form.get("studyshortname", ""),
                     datetime.now().date().strftime("%Y-%m-%d"),
                     form["inbredsetid"])
                 query = (
                     "INSERT INTO ProbeFreeze("
-                    "Id, ProbeFreezeId, ChipId, TissueId, Name, FullName, "
-                    "ShortName, CreateTime, InbredSetId) "
-                    "VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s)")
+                    "ChipId, TissueId, Name, FullName, ShortName, CreateTime, "
+                    "InbredSetId"
+                    ") VALUES (%s, %s, %s, %s, %s, %s, %s)")
                 cursor.execute(query, values)
-                lastrowid = cursor.lastrowid
+                new_studyid = cursor.lastrowid
                 cursor.execute(
                     "UPDATE ProbeFreeze SET ProbeFreezeId=%s WHERE Id=%s",
-                    (lastrowid, lastrowid))
+                    (new_studyid, new_studyid))
                 flash("Study created successfully", "alert-success")
                 return render_template(
                     "continue_from_create_study.html",
@@ -266,10 +265,8 @@ def create_dataset():
                     flash("A dataset with that name already exists.",
                           "alert-error")
                     return redirect(url_for("dbinsert.select_dataset"), code=307)
-                cursor.execute("SELECT MAX(Id) AS last_id FROM ProbeSetFreeze")
-                new_datasetid = cursor.fetchone()["last_id"] + 1
                 values = (
-                    new_datasetid, form["studyid"], form["avgid"],
+                    form["studyid"], form["avgid"],
                     datasetname, form["datasetname2"],
                     form["datasetfullname"], form["datasetshortname"],
                     datetime.now().date().strftime("%Y-%m-%d"),
@@ -277,12 +274,13 @@ def create_dataset():
                     "williamslab", form["datasetdatascale"])
                 query = (
                     "INSERT INTO ProbeSetFreeze("
-                    "Id, ProbeFreezeId, AvgID, Name, Name2, FullName, "
+                    "ProbeFreezeId, AvgID, Name, Name2, FullName, "
                     "ShortName, CreateTime, OrderList, public, "
                     "confidentiality, AuthorisedUsers, DataScale) "
                     "VALUES"
-                    "(%s, %s, %s, %s, %s, %s, %s, %s, NULL, %s, %s, %s, %s)")
+                    "(%s, %s, %s, %s, %s, %s, %s, NULL, %s, %s, %s, %s)")
                 cursor.execute(query, values)
+                new_datasetid = cursor.lastrowid
                 return render_template(
                     "continue_from_create_dataset.html",
                     filename=form["filename"], filetype=form["filetype"],
-- 
cgit v1.2.3