db: traits: Enable deletion of individual sample values

If a user replaces an individual value with an "x", delete that date entry
from the respective table.  Deletion here is the only option since by default
the Nstrain, PublishData and PublishSE don't accept null values.  Note that
deleting all 3 values is equivalent to removing the sample from the CSV file.

* gn3/db/traits.py (update_sample_data): If a value is "x", delete it from the
respective table.

1 files changed, 60 insertions, 42 deletions

diff --git a/gn3/db/traits.py b/gn3/db/traits.py
index 772db52..a7e7e7c 100644
--- a/gn3/db/traits.py
+++ b/gn3/db/traits.py
@@ -135,56 +135,74 @@ def update_sample_data(conn: Any, #pylint: disable=[R0913]
 
     with conn.cursor() as cursor:
         # Update the PublishData table
-        cursor.execute(("UPDATE PublishData SET value = %s "
+        if value == "x":
+            cursor.execute(("DELETE FROM PublishData "
+                            "WHERE StrainId = %s AND Id = %s")
+                           % (strain_id, data_id))
+            updated_published_data = cursor.rowcount
+        else:
+            cursor.execute(("UPDATE PublishData SET value = %s "
                         "WHERE StrainId = %s AND Id = %s"),
-                       (None if value == "x" else value,
-                        strain_id, data_id))
-        updated_published_data = cursor.rowcount
-        if not updated_published_data:
-            cursor.execute(
+                       (value, strain_id, data_id))
+            updated_published_data = cursor.rowcount
+
+            if not updated_published_data:
+                cursor.execute(
                     "SELECT * FROM "
                     "PublishData WHERE StrainId = "
                     "%s AND Id = %s" % (strain_id, data_id))
-            if not cursor.fetchone():
-                cursor.execute(("INSERT INTO PublishData (Id, StrainId, "
-                                " value) VALUES (%s, %s, %s)") %
-                               (data_id, strain_id, value))
-                updated_published_data = cursor.rowcount
+                if not cursor.fetchone():
+                    cursor.execute(("INSERT INTO PublishData (Id, StrainId, "
+                                    " value) VALUES (%s, %s, %s)") %
+                                   (data_id, strain_id, value))
+                    updated_published_data = cursor.rowcount
 
         # Update the PublishSE table
-        cursor.execute(("UPDATE PublishSE SET error = %s "
-                        "WHERE StrainId = %s AND DataId = %s"),
-                       (None if error == "x" else error,
-                        strain_id, data_id))
-        updated_se_data = cursor.rowcount
-        if not updated_se_data:
-            cursor.execute(
-                    "SELECT * FROM "
-                    "PublishSE WHERE StrainId = "
-                    "%s AND DataId = %s" % (strain_id, data_id))
-            if not cursor.fetchone():
-                cursor.execute(("INSERT INTO PublishSE (StrainId, DataId, "
-                                " error) VALUES (%s, %s, %s)") %
-                               (strain_id, data_id,
-                                None if error == "x" else error))
-                updated_se_data = cursor.rowcount
+        if error == "x":
+            cursor.execute(("DELETE FROM PublishSE "
+                            "WHERE StrainId = %s AND DataId = %s") %
+                           (strain_id, data_id))
+            updated_se_data = cursor.rowcount
+        else:
+            cursor.execute(("UPDATE PublishSE SET error = %s "
+                            "WHERE StrainId = %s AND DataId = %s"),
+                           (None if error == "x" else error,
+                            strain_id, data_id))
+            updated_se_data = cursor.rowcount
+            if not updated_se_data:
+                cursor.execute(
+                        "SELECT * FROM "
+                        "PublishSE WHERE StrainId = "
+                        "%s AND DataId = %s" % (strain_id, data_id))
+                if not cursor.fetchone():
+                    cursor.execute(("INSERT INTO PublishSE (StrainId, DataId, "
+                                    " error) VALUES (%s, %s, %s)") %
+                                   (strain_id, data_id,
+                                    None if error == "x" else error))
+                    updated_se_data = cursor.rowcount
 
         # Update the NStrain table
-        cursor.execute(("UPDATE NStrain SET count = %s "
-                        "WHERE StrainId = %s AND DataId = %s"),
-                       (count, strain_id, data_id))
-        updated_n_strains = cursor.rowcount
-        if not updated_n_strains:
-            cursor.execute(
-                    "SELECT * FROM "
-                    "NStrain WHERE StrainId = "
-                    "%s AND DataId = %s" % (strain_id, data_id))
-            if not cursor.fetchone():
-                cursor.execute(("INSERT INTO NStrain "
-                                "(StrainId, DataId, count) "
-                                "VALUES (%s, %s, %s)") %
-                               (strain_id, data_id, count))
-                updated_n_strains = cursor.rowcount
+        if count == "x":
+            cursor.execute(("DELETE FROM NStrain "
+                                "WHERE StrainId = %s AND DataId = %s" %
+                                (strain_id, data_id)))
+            updated_n_strains = cursor.rowcount
+        else:
+            cursor.execute(("UPDATE NStrain SET count = %s "
+                            "WHERE StrainId = %s AND DataId = %s"),
+                           (count, strain_id, data_id))
+            updated_n_strains = cursor.rowcount
+            if not updated_n_strains:
+                cursor.execute(
+                        "SELECT * FROM "
+                        "NStrain WHERE StrainId = "
+                        "%s AND DataId = %s" % (strain_id, data_id))
+                if not cursor.fetchone():
+                    cursor.execute(("INSERT INTO NStrain "
+                                    "(StrainId, DataId, count) "
+                                    "VALUES (%s, %s, %s)") %
+                                   (strain_id, data_id, count))
+                    updated_n_strains = cursor.rowcount
     return (updated_published_data,
             updated_se_data, updated_n_strains)