Problem
Running `SQL/load_sql.py` only loads the `staging*` tables — the actual OED database tables end up empty.
There are two separate bugs:
1. The stored procedure call was commented out, and didn't commit anyway
The `EXEC usp_Database_Load` call at the end of the script was commented out. Even after uncommenting it, the load still silently failed to persist: the script used `engine.connect()`, which under SQLAlchemy 2.x does not auto-commit a raw `conn.execute()` — the transaction gets rolled back when the `with` block exits. The script would print `('Done',)` (the stored procedure's own return value) even though nothing was actually written to the database. Running `EXEC usp_Database_Load` manually (e.g. from VS Code / SSMS) worked fine, which is what made this confusing to diagnose.
2. Bulk insert (method='multi') doesn't scale to real OED input files
The staging table loads used `pandas.DataFrame.to_sql(..., method='multi')`, which builds a single multi-row `INSERT ... VALUES (...), (...), ...` statement. This is bounded by SQL Server's 2100-parameter-per-statement limit, so:
- A location file with a few thousand rows already fails with `pyodbc.Error: ('07002', ... COUNT field incorrect or syntax error ...)` unless a suitably small `chunksize` is chosen.
- The safe `chunksize` depends on column count, so a single fixed value isn't actually safe across tables with different widths.
- OED input files (particularly location files) can run into the millions of rows, which `method='multi'` handles very slowly regardless of chunksize, since each chunk is still a separate round trip.
Also noticed
Source account files aren't always in the OED-spec date format. Per `OpenExposureData/OEDInputFields.csv`, `PolInceptionDate`/`PolExpiryDate` must be ISO 8601 (`YYYY-MM-DD`), but real-world files sometimes arrive as `DD/MM/YYYY`. When that happens, SQL Server's default MDY session date format misparses the string (e.g. `31/12/2026` → invalid month 31), and `usp_Database_Load` fails with a `smalldatetime` conversion error.
Fix
See PR (to follow): switches to `fast_executemany=True` on the SQLAlchemy engine instead of `method='multi'` (removes the param-count ceiling and scales to large files), uses `engine.begin()` so the stored procedure call commits, and normalizes account date columns to ISO 8601 before loading.
Problem
Running `SQL/load_sql.py` only loads the `staging*` tables — the actual OED database tables end up empty.
There are two separate bugs:
1. The stored procedure call was commented out, and didn't commit anyway
The `EXEC usp_Database_Load` call at the end of the script was commented out. Even after uncommenting it, the load still silently failed to persist: the script used `engine.connect()`, which under SQLAlchemy 2.x does not auto-commit a raw `conn.execute()` — the transaction gets rolled back when the `with` block exits. The script would print `('Done',)` (the stored procedure's own return value) even though nothing was actually written to the database. Running `EXEC usp_Database_Load` manually (e.g. from VS Code / SSMS) worked fine, which is what made this confusing to diagnose.
2. Bulk insert (
method='multi') doesn't scale to real OED input filesThe staging table loads used `pandas.DataFrame.to_sql(..., method='multi')`, which builds a single multi-row `INSERT ... VALUES (...), (...), ...` statement. This is bounded by SQL Server's 2100-parameter-per-statement limit, so:
Also noticed
Source account files aren't always in the OED-spec date format. Per `OpenExposureData/OEDInputFields.csv`, `PolInceptionDate`/`PolExpiryDate` must be ISO 8601 (`YYYY-MM-DD`), but real-world files sometimes arrive as `DD/MM/YYYY`. When that happens, SQL Server's default MDY session date format misparses the string (e.g. `31/12/2026` → invalid month 31), and `usp_Database_Load` fails with a `smalldatetime` conversion error.
Fix
See PR (to follow): switches to `fast_executemany=True` on the SQLAlchemy engine instead of `method='multi'` (removes the param-count ceiling and scales to large files), uses `engine.begin()` so the stored procedure call commits, and normalizes account date columns to ISO 8601 before loading.