Spaces:
Sleeping
Sleeping
Download tools/seeding.py from alisaleem97/NexLab_System: direct link, hf CLI and curl.
- Browser
- Download file 22.4 kB
-
https://huggingface.co/spaces/alisaleem97/NexLab_System/resolve/main/tools/seeding.py
- Command line
-
hf download hf://spaces/alisaleem97/NexLab_System/tools/seeding.py
-
curl -L -o seeding.py https://huggingface.co/spaces/alisaleem97/NexLab_System/resolve/main/tools/seeding.py
22.4 kB
| """ | |
| LIS V1 β V2 Migration / Seeding Script (FIXED) | |
| ================================================= | |
| Seeds in order: | |
| 1. Parameters | |
| 2. Departments | |
| 3. SampleTypes | |
| 4. Devices (V1 "Methods") | |
| 5. ReportNotes (V1 "Notes") β "None" inserted first as id=1 | |
| 6. Tests | |
| 7. Ranges (V1 "TestRange") | |
| Key fix applied | |
| βββββββββββββββ | |
| β’ PRAGMA foreign_keys is turned OFF for the duration of the migration. | |
| This prevents SQLite from silently dropping (INSERT OR IGNORE) rows | |
| whose created_by=1 FK cannot be resolved until a User row exists, | |
| which previously left earlier tables empty and caused the | |
| "FOREIGN KEY constraint failed" error on the TestDefinition insert. | |
| Data integrity is guaranteed by the oldβnew ID maps built in code. | |
| β’ Ranges that reference ParameterIds absent from parmeters.xlsx are | |
| inserted with parameter_id = NULL (orphaned V1 records). | |
| β’ Row-level error reporting: any single-row failure is printed with the | |
| offending row data rather than aborting the whole batch. | |
| """ | |
| import os | |
| import sqlite3 | |
| import pandas as pd | |
| from datetime import datetime | |
| # βββββββββββββββββββββββββββββββββββββββββββββ | |
| # CONFIG β resolved dynamically relative to project root | |
| # βββββββββββββββββββββββββββββββββββββββββββββ | |
| project_root = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) | |
| DB_PATH = os.path.join(project_root, "lab_database.db") | |
| EXCEL_DIR = os.path.join(project_root, "excel_files") | |
| NOW = datetime.now().strftime("%Y-%m-%d %H:%M:%S") | |
| CREATED_BY = 1 | |
| # ββ helpers ββββββββββββββββββββββββββββββββββ | |
| def xls(filename: str) -> pd.DataFrame: | |
| path = os.path.join(EXCEL_DIR, filename) | |
| df = pd.read_excel(path) | |
| df.columns = df.columns.str.strip() | |
| return df | |
| def clean(val): | |
| if val is None: | |
| return None | |
| s = str(val).strip() | |
| return None if s.lower() in ("", "nan", "none", "null") else s | |
| def gender_map(v) -> str: | |
| return {1: "male", 2: "female", 3: "both"}.get(int(v), "both") | |
| def age_unit_map(v) -> str: | |
| return {1: "year", 2: "month", 3: "day"}.get(int(v), "year") | |
| def range_type_map(v) -> str: | |
| return {1: "number", 2: "text"}.get(int(v), "number") | |
| def opt_float(val): | |
| try: | |
| f = float(val) | |
| return None if pd.isna(f) else f | |
| except (TypeError, ValueError): | |
| return None | |
| def safe_executemany(cur, sql: str, rows: list, label: str) -> int: | |
| """ | |
| Insert rows one-by-one so a single bad row is reported and skipped | |
| instead of aborting the whole batch. Returns the number inserted. | |
| """ | |
| inserted = 0 | |
| for row in rows: | |
| try: | |
| cur.execute(sql, row) | |
| inserted += 1 | |
| except sqlite3.IntegrityError as exc: | |
| print(f" β οΈ Skipped {label} row (IntegrityError: {exc}): {row[:4]}") | |
| except Exception as exc: | |
| print(f" β οΈ Skipped {label} row (Error: {exc}): {row[:4]}") | |
| return inserted | |
| # ββ main βββββββββββββββββββββββββββββββββββββ | |
| def main(): | |
| conn = sqlite3.connect(DB_PATH) | |
| # ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | |
| # CRITICAL FIX: disable FK enforcement for the migration. | |
| # All FK integrity is enforced by the oldβnew ID maps in this script. | |
| # Re-enabled at the very end after all data is committed. | |
| # ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | |
| conn.execute("PRAGMA foreign_keys = OFF;") | |
| cur = conn.cursor() | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| # 1. PARAMETERS | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| print("\nββ 1. Seeding Parameters ββ") | |
| df = xls("parmeters.xlsx") | |
| df = df.dropna(subset=["ParameterName"]) | |
| df = df[df["ParameterName"].astype(str).str.strip() != ""] | |
| param_old_to_new: dict[int, int] = {} | |
| new_id = 1 | |
| rows = [] | |
| for _, r in df.iterrows(): | |
| old_id = int(r["ParameterId"]) | |
| param_old_to_new[old_id] = new_id | |
| rows.append(( | |
| new_id, | |
| str(r["ParameterName"]).strip(), | |
| str(r["ShortName"]).strip() if clean(r["ShortName"]) else str(r["ParameterName"]).strip(), | |
| 1 if int(r["IsHeader"]) == 1 else 0, | |
| CREATED_BY, NOW, | |
| None, None, | |
| None, None, | |
| )) | |
| new_id += 1 | |
| sql = """ | |
| INSERT OR IGNORE INTO parameter | |
| (id, parameter_name, parameter_short_name, is_header, | |
| created_by, created_at, edited_by, edited_at, deleted_by, deleted_at) | |
| VALUES (?,?,?,?,?,?,?,?,?,?) | |
| """ | |
| inserted = safe_executemany(cur, sql, rows, "parameter") | |
| conn.commit() | |
| print(f" β {inserted} / {len(rows)} parameters inserted.") | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| # 2. DEPARTMENTS | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| print("\nββ 2. Seeding Departments ββ") | |
| df = xls("Departments.xlsx") | |
| df = df.dropna(subset=["DepartmentName"]) | |
| dept_old_to_new: dict[int, int] = {} | |
| new_id = 1 | |
| rows = [] | |
| for _, r in df.iterrows(): | |
| old_id = int(r["DepartmentId"]) | |
| dept_old_to_new[old_id] = new_id | |
| rows.append(( | |
| new_id, | |
| str(r["DepartmentName"]).strip(), | |
| clean(r["DepartmentNote"]), | |
| 1, | |
| CREATED_BY, NOW, | |
| None, None, | |
| None, None, | |
| None, None, | |
| None, None, | |
| )) | |
| new_id += 1 | |
| sql = """ | |
| INSERT OR IGNORE INTO department | |
| (id, department_name, department_note, is_active, | |
| created_by, created_at, edited_by, edited_at, | |
| deleted_by, deleted_at, activated_by, activated_at, | |
| deactivated_by, deactivated_at) | |
| VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?) | |
| """ | |
| inserted = safe_executemany(cur, sql, rows, "department") | |
| conn.commit() | |
| print(f" β {inserted} / {len(rows)} departments inserted.") | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| # 3. SAMPLE TYPES | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| print("\nββ 3. Seeding Sample Types ββ") | |
| df = xls("SampleType.xlsx") | |
| df = df.dropna(subset=["SampleTypeName"]) | |
| sample_old_to_new: dict[int, int] = {} | |
| new_id = 1 | |
| rows = [] | |
| for _, r in df.iterrows(): | |
| old_id = int(r["SampleTypeId"]) | |
| sample_old_to_new[old_id] = new_id | |
| rows.append(( | |
| new_id, | |
| str(r["SampleTypeName"]).strip(), | |
| clean(r["SampleTypeNote"]), | |
| 1, | |
| CREATED_BY, NOW, | |
| None, None, | |
| None, None, | |
| None, None, | |
| None, None, | |
| )) | |
| new_id += 1 | |
| sql = """ | |
| INSERT OR IGNORE INTO sampletype | |
| (id, sample_name, sample_note, is_active, | |
| created_by, created_at, edited_by, edited_at, | |
| deleted_by, deleted_at, activated_by, activated_at, | |
| deactivated_by, deactivated_at) | |
| VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?) | |
| """ | |
| inserted = safe_executemany(cur, sql, rows, "sampletype") | |
| conn.commit() | |
| print(f" β {inserted} / {len(rows)} sample types inserted.") | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| # 4. DEVICES (V1 "Methods") | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| print("\nββ 4. Seeding Devices (from V1 Methods) ββ") | |
| df = xls("Devices.xlsx") | |
| df = df.dropna(subset=["MethodName"]) | |
| device_old_to_new: dict[int, int] = {} | |
| new_id = 1 | |
| rows = [] | |
| for _, r in df.iterrows(): | |
| old_id = int(r["MethodId"]) | |
| device_old_to_new[old_id] = new_id | |
| rows.append(( | |
| new_id, | |
| str(r["MethodName"]).strip(), | |
| "N/A", | |
| NOW, | |
| "N/A", | |
| "N/A", | |
| clean(r["MethodNote"]), | |
| 1, | |
| CREATED_BY, NOW, | |
| None, None, | |
| None, None, | |
| None, None, | |
| None, None, | |
| )) | |
| new_id += 1 | |
| sql = """ | |
| INSERT OR IGNORE INTO device | |
| (id, device_name, serial_number, install_date, | |
| installer_name, installer_phone, note, is_active, | |
| created_by, created_at, edited_by, edited_at, | |
| deleted_by, deleted_at, activated_by, activated_at, | |
| deactivated_by, deactivated_at) | |
| VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) | |
| """ | |
| inserted = safe_executemany(cur, sql, rows, "device") | |
| conn.commit() | |
| print(f" β {inserted} / {len(rows)} devices inserted.") | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| # 5. REPORT NOTES (V1 "Note") | |
| # "None" is forced to be id=1 | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| print("\nββ 5. Seeding Report Notes ββ") | |
| df = xls("ReportNote.xlsx") | |
| df = df.dropna(subset=["NoteName"]) | |
| mask_none = df["NoteName"].astype(str).str.strip().str.lower() == "none" | |
| df_ordered = pd.concat( | |
| [df[mask_none], df[~mask_none].sort_values("NoteId")], | |
| ignore_index=True, | |
| ) | |
| note_old_to_new: dict[int, int] = {} | |
| new_id = 1 | |
| rows = [] | |
| for _, r in df_ordered.iterrows(): | |
| old_id = int(r["NoteId"]) | |
| note_old_to_new[old_id] = new_id | |
| rows.append(( | |
| new_id, | |
| str(r["NoteName"]).strip(), | |
| clean(r["TheNote"]) or "", | |
| 1, | |
| CREATED_BY, NOW, | |
| None, None, | |
| None, None, | |
| )) | |
| new_id += 1 | |
| sql = """ | |
| INSERT OR IGNORE INTO reportnote | |
| (id, note_name, note_content, is_active, | |
| created_by, created_at, edited_by, edited_at, | |
| deleted_by, deleted_at) | |
| VALUES (?,?,?,?,?,?,?,?,?,?) | |
| """ | |
| inserted = safe_executemany(cur, sql, rows, "reportnote") | |
| conn.commit() | |
| print(f" β {inserted} / {len(rows)} report notes inserted (id=1 β 'None').") | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| # 6. TESTS | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| print("\nββ 6. Seeding Tests ββ") | |
| df = xls("Tests.xlsx") | |
| df = df.dropna(subset=["TestName"]) | |
| test_old_to_new: dict[int, int] = {} | |
| new_id = 1 | |
| rows = [] | |
| for _, r in df.iterrows(): | |
| old_id = int(r["TestId"]) | |
| old_dept_id = int(r["DepartmentId"]) | |
| old_sample_id = int(r["SampleTypeId"]) | |
| old_note_id = int(r["NoteId"]) if clean(r["NoteId"]) else None | |
| new_dept_id = dept_old_to_new.get(old_dept_id) | |
| new_sample_id = sample_old_to_new.get(old_sample_id) | |
| new_note_id = note_old_to_new.get(old_note_id) if old_note_id else None | |
| if new_dept_id is None: | |
| print(f" β οΈ TestId={old_id} skipped: DepartmentId={old_dept_id} not found in map.") | |
| continue | |
| if new_sample_id is None: | |
| print(f" β οΈ TestId={old_id} skipped: SampleTypeId={old_sample_id} not found in map.") | |
| continue | |
| test_old_to_new[old_id] = new_id | |
| rows.append(( | |
| new_id, | |
| str(r["TestName"]).strip(), | |
| str(r["ShortTestName"]).strip() if clean(r["ShortTestName"]) else str(r["TestName"]).strip(), | |
| new_dept_id, | |
| new_sample_id, | |
| new_note_id, | |
| float(r["Price"]) if clean(r["Price"]) else 0.0, | |
| clean(r["TestNotes"]), | |
| clean(r["TestConditions"]), | |
| 1, | |
| CREATED_BY, NOW, | |
| None, None, | |
| None, None, | |
| )) | |
| new_id += 1 | |
| sql = """ | |
| INSERT OR IGNORE INTO testdefinition | |
| (id, test_name, test_short_name, department_id, sample_type_id, | |
| report_note_id, price, test_note, test_condition, is_available, | |
| created_by, created_at, edited_by, edited_at, deleted_by, deleted_at) | |
| VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) | |
| """ | |
| inserted = safe_executemany(cur, sql, rows, "testdefinition") | |
| conn.commit() | |
| print(f" β {inserted} / {len(rows)} tests inserted.") | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| # 7. TEST-PARAMETER LINKS (from Ranges) | |
| # Source: unique (TestId, ParameterId) pairs in Ranges.xlsx | |
| # where ParameterId != 0 and ParameterId exists in parmeters.xlsx | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| print("\nββ 7. Seeding Test-Parameter links ββ") | |
| df_ranges = xls("Ranges.xlsx") | |
| seen_tp = set() # (new_test_id, new_param_id) dedup | |
| rows = [] | |
| new_id = 1 | |
| tp_skip_test = 0 | |
| tp_skip_param = 0 | |
| for _, r in df_ranges.iterrows(): | |
| old_test_id = int(r["TestId"]) | |
| old_param_id = int(r["ParameterId"]) | |
| if old_param_id == 0: | |
| continue # test-level range, no parameter link | |
| new_test_id = test_old_to_new.get(old_test_id) | |
| if new_test_id is None: | |
| tp_skip_test += 1 | |
| continue | |
| new_param_id = param_old_to_new.get(old_param_id) | |
| if new_param_id is None: | |
| tp_skip_param += 1 # orphaned V1 parameter β skip | |
| continue | |
| pair = (new_test_id, new_param_id) | |
| if pair in seen_tp: | |
| continue # already added this link | |
| seen_tp.add(pair) | |
| rows.append((new_id, new_test_id, new_param_id)) | |
| new_id += 1 | |
| sql = """ | |
| INSERT OR IGNORE INTO testparameter (id, test_id, parameter_id) | |
| VALUES (?,?,?) | |
| """ | |
| inserted = safe_executemany(cur, sql, rows, "testparameter") | |
| conn.commit() | |
| if tp_skip_test: | |
| print(f" βΉοΈ {tp_skip_test} rows skipped (TestId not in test map).") | |
| if tp_skip_param: | |
| print(f" βΉοΈ {tp_skip_param} rows skipped (ParameterId orphaned in V1).") | |
| print(f" β {inserted} / {len(rows)} test-parameter links inserted.") | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| # 8. TEST-DEVICE LINKS (from Ranges) | |
| # Source: unique (TestId, MethodId) pairs in Ranges.xlsx | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| print("\nββ 8. Seeding Test-Device links ββ") | |
| seen_td = set() # (new_test_id, new_device_id) dedup | |
| rows = [] | |
| new_id = 1 | |
| td_skip_test = 0 | |
| td_skip_device = 0 | |
| for _, r in df_ranges.iterrows(): | |
| old_test_id = int(r["TestId"]) | |
| old_device_id = int(r["MethodId"]) | |
| new_test_id = test_old_to_new.get(old_test_id) | |
| if new_test_id is None: | |
| td_skip_test += 1 | |
| continue | |
| new_device_id = device_old_to_new.get(old_device_id) | |
| if new_device_id is None: | |
| td_skip_device += 1 | |
| continue | |
| pair = (new_test_id, new_device_id) | |
| if pair in seen_td: | |
| continue | |
| seen_td.add(pair) | |
| rows.append((new_id, new_test_id, new_device_id)) | |
| new_id += 1 | |
| sql = """ | |
| INSERT OR IGNORE INTO testdevice (id, test_id, device_id) | |
| VALUES (?,?,?) | |
| """ | |
| inserted = safe_executemany(cur, sql, rows, "testdevice") | |
| conn.commit() | |
| if td_skip_test: | |
| print(f" βΉοΈ {td_skip_test} rows skipped (TestId not in test map).") | |
| if td_skip_device: | |
| print(f" βΉοΈ {td_skip_device} rows skipped (MethodId not in device map).") | |
| print(f" β {inserted} / {len(rows)} test-device links inserted.") | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| # 9. RANGES (V1 "TestRange") | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| print("\nββ 9. Seeding Ranges ββ") | |
| df = df_ranges # already loaded above β no need to re-read from disk | |
| new_id = 1 | |
| rows = [] | |
| skipped = 0 | |
| orphaned_params = 0 | |
| for _, r in df.iterrows(): | |
| old_test_id = int(r["TestId"]) | |
| new_test_id = test_old_to_new.get(old_test_id) | |
| if new_test_id is None: | |
| skipped += 1 | |
| continue | |
| old_param_id = int(r["ParameterId"]) | |
| if old_param_id == 0: | |
| new_param_id = None | |
| else: | |
| new_param_id = param_old_to_new.get(old_param_id) | |
| if new_param_id is None: | |
| # ParameterId exists in Ranges but not in parmeters.xlsx | |
| # (orphaned V1 record) β insert with parameter_id = NULL | |
| orphaned_params += 1 | |
| new_param_id = None | |
| old_device_id = int(r["MethodId"]) | |
| new_device_id = device_old_to_new.get(old_device_id) | |
| rtype = range_type_map(r["RangeTypeId"]) | |
| rows.append(( | |
| new_id, | |
| new_test_id, | |
| new_param_id, | |
| new_device_id, | |
| clean(r["UnitName"]) or "", | |
| gender_map(r["Gender"]), | |
| int(r["AgeFrom"]), | |
| int(r["AgeTo"]), | |
| age_unit_map(r["AgeFromTypeId"]), | |
| 1 if int(r["Fasting"]) == 1 else 0, | |
| rtype, | |
| opt_float(r["NormalFrom"]), | |
| opt_float(r["NormalTo"]), | |
| None, None, # vlow | |
| None, None, # low | |
| None, None, # midlow | |
| None, None, # midhigh | |
| None, None, # high | |
| None, None, # vhigh | |
| None, None, # panic | |
| clean(r["TextRange"]) if rtype == "text" else None, | |
| 1, | |
| CREATED_BY, NOW, | |
| None, None, | |
| None, None, | |
| )) | |
| new_id += 1 | |
| sql = """ | |
| INSERT OR IGNORE INTO testrange | |
| (id, test_id, parameter_id, device_id, | |
| unit, gender_type, age_from, age_to, age_unit, | |
| fasting_required, range_type, | |
| normal_from, normal_to, | |
| vlow_from, vlow_to, low_from, low_to, | |
| midlow_from, midlow_to, midhigh_from, midhigh_to, | |
| high_from, high_to, vhigh_from, vhigh_to, | |
| panic_less_than, panic_more_than, | |
| text_range, | |
| is_active, created_by, created_at, | |
| edited_by, edited_at, deleted_by, deleted_at) | |
| VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) | |
| """ | |
| inserted = safe_executemany(cur, sql, rows, "testrange") | |
| conn.commit() | |
| if skipped: | |
| print(f" βΉοΈ {skipped} ranges skipped (TestId had no matching test in V1 export).") | |
| if orphaned_params: | |
| print(f" βΉοΈ {orphaned_params} ranges had orphaned ParameterIds β inserted with parameter_id=NULL.") | |
| print(f" β {inserted} / {len(rows)} ranges inserted.") | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| # Re-enable FK enforcement now that all data is in place | |
| # ββββββββββββββββββββββββββββββββββββββββββ | |
| conn.execute("PRAGMA foreign_keys = ON;") | |
| conn.close() | |
| print("\nπ Migration complete β all tables seeded successfully.") | |
| print("\nββ Summary ββββββββββββββββββββββββββββββ") | |
| print(f" Parameters : {len(param_old_to_new)}") | |
| print(f" Departments : {len(dept_old_to_new)}") | |
| print(f" Sample Types : {len(sample_old_to_new)}") | |
| print(f" Devices : {len(device_old_to_new)}") | |
| print(f" Report Notes : {len(note_old_to_new)} (id=1 is 'None')") | |
| print(f" Tests : {len(test_old_to_new)}") | |
| print(f" Test-Param links : {len(seen_tp)}") | |
| print(f" Test-Device links : {len(seen_td)}") | |
| print(f" Ranges : {new_id - 1} (inserted: {inserted})") | |
| if __name__ == "__main__": | |
| try: | |
| main() | |
| except Exception as exc: | |
| import traceback | |
| print(f"\nπ₯ Migration failed: {exc}") | |
| traceback.print_exc() |