about summary refs log tree commit diff
diff options
context:
space:
mode:
authorzsloan <zachary.a.sloan@gmail.com>2026-04-29 18:55:31 +0000
committerzsloan <zachary.a.sloan@gmail.com>2026-04-29 19:08:14 +0000
commite1cf6c46ae35ae758e247f3ed09e48e9eddc47cb (patch)
treed49950c7a8e69d79b5c81ec6830e6f9c87286fb7
parent4c3bd1023be1670b56d82d646a126e9747ada931 (diff)
downloadgenenetwork3-e1cf6c46ae35ae758e247f3ed09e48e9eddc47cb.tar.gz
Fix how Batch Edit handles SE/N edits
-rw-r--r--gn3/db/sample_data.py53
1 files changed, 33 insertions, 20 deletions
diff --git a/gn3/db/sample_data.py b/gn3/db/sample_data.py
index 4e01a3a..52c1ec1 100644
--- a/gn3/db/sample_data.py
+++ b/gn3/db/sample_data.py
@@ -607,6 +607,10 @@ def insert_sample_data(
     except Exception as _e:
         raise MySQLdb.Error(_e) from _e
 
+def _is_n_cases_missing(value):
+    """Check if N value represents missing data (treat 0 as missing)."""
+    return value in ('x', '0', 0)
+
 def batch_update_sample_data(
     conn: Any, diff_data: Dict
 ):
@@ -641,11 +645,14 @@ def batch_update_sample_data(
             cursor.execute(
                 "SELECT Id FROM Strain WHERE Name = %s", (strain_name,)
             )
-            return cursor.fetchone()[0]
+            result = cursor.fetchone()
+            if result is None:
+                raise ValueError(f"Strain '{strain_name}' not found in the database. Please verify the strain name exists.")
+            return result[0]
 
     def __update_query(conn, db_type, data_id, strain_id, diffs):
         with conn.cursor() as cursor:
-            if 'value' in diffs:
+            if 'value' in diffs and diffs['value']['Current'] != 'x':
                 cursor.execute(
                     (
                         f"UPDATE {db_type}Data "
@@ -653,46 +660,52 @@ def batch_update_sample_data(
                         "WHERE Id = %s AND StrainId = %s"
                     ), (diffs['value']['Current'], data_id, strain_id)
                 )
-            if 'error' in diffs:
+            if 'error' in diffs and diffs['error']['Current'] != 'x':
+                # Use INSERT ... ON DUPLICATE KEY UPDATE for atomic upsert
                 cursor.execute(
                     (
-                        f"UPDATE {db_type}SE "
-                        "SET error = %s "
-                        "WHERE DataId = %s AND StrainId = %s"
-                    ), (diffs['error']['Current'], data_id, strain_id)
+                        f"INSERT INTO {db_type}SE (DataId, StrainId, error) "
+                        "VALUES (%s, %s, %s) "
+                        "ON DUPLICATE KEY UPDATE error = VALUES(error)"
+                    ), (data_id, strain_id, diffs['error']['Current'])
                 )
-            if 'n_cases' in diffs:
+            if 'n_cases' in diffs and not _is_n_cases_missing(diffs['n_cases']['Current']):
+                # Insert/update N value using upsert pattern
                 cursor.execute(
                     (
-                        "UPDATE NStrain "
-                        "SET count = %s "
-                        "WHERE DataId = %s AND StrainId = %s"
-                    ), (diffs['n_cases']['Current'], data_id, strain_id)
+                        "INSERT INTO NStrain (DataId, StrainId, count) "
+                        "VALUES (%s, %s, %s) "
+                        "ON DUPLICATE KEY UPDATE count = VALUES(count)"
+                    ), (data_id, strain_id, diffs['n_cases']['Current'])
                 )
 
         conn.commit()
 
     def __insert_query(conn, db_type, data_id, strain_id, diffs):
         with conn.cursor() as cursor:
-            if 'value' in diffs:
+            if 'value' in diffs and diffs['value'] != 'x':
                 cursor.execute(
                     (
                         f"INSERT INTO {db_type}Data (Id, StrainId, value)"
                         "VALUES (%s, %s, %s)"
                     ), (data_id, strain_id, diffs['value'])
                 )
-            if 'error' in diffs:
+            if 'error' in diffs and diffs['error'] != 'x':
+                # Use INSERT ... ON DUPLICATE KEY UPDATE for atomic upsert
                 cursor.execute(
                     (
-                        f"INSERT INTO {db_type}SE (DataId, StrainId, error)"
-                        "VALUES (%s, %s, %s)"
+                        f"INSERT INTO {db_type}SE (DataId, StrainId, error) "
+                        "VALUES (%s, %s, %s) "
+                        "ON DUPLICATE KEY UPDATE error = VALUES(error)"
                     ), (data_id, strain_id, diffs['error'])
                 )
-            if 'n_cases' in diffs:
+            if 'n_cases' in diffs and not _is_n_cases_missing(diffs['n_cases']):
+                # Insert/update N value using upsert pattern
                 cursor.execute(
                     (
-                        "INSERT INTO NStrain (DataId, StrainId, count)"
-                        "VALUES (%s, %s, %s)"
+                        "INSERT INTO NStrain (DataId, StrainId, count) "
+                        "VALUES (%s, %s, %s) "
+                        "ON DUPLICATE KEY UPDATE count = VALUES(count)"
                     ), (data_id, strain_id, diffs['n_cases'])
                 )
 
@@ -747,4 +760,4 @@ def batch_update_sample_data(
         __update_data(conn, db_type, data_id, diff_data[key]['Additions'], 'insert')
         __update_data(conn, db_type, data_id, diff_data[key]['Deletions'], 'delete')
 
-    return diff_data
+    return diff_data
\ No newline at end of file