diff options
| author | zsloan <zachary.a.sloan@gmail.com> | 2026-04-29 18:55:31 +0000 |
|---|---|---|
| committer | zsloan <zachary.a.sloan@gmail.com> | 2026-04-29 19:08:14 +0000 |
| commit | e1cf6c46ae35ae758e247f3ed09e48e9eddc47cb (patch) | |
| tree | d49950c7a8e69d79b5c81ec6830e6f9c87286fb7 | |
| parent | 4c3bd1023be1670b56d82d646a126e9747ada931 (diff) | |
| download | genenetwork3-e1cf6c46ae35ae758e247f3ed09e48e9eddc47cb.tar.gz | |
Fix how Batch Edit handles SE/N edits
| -rw-r--r-- | gn3/db/sample_data.py | 53 |
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 |
