Skip to content

fix: run usp_Database_Load and fix bulk insert scaling in load_sql.py - #294

Closed
benhayes21 wants to merge 2 commits into
mainfrom
fix/load-sql-database-load
Closed

fix: run usp_Database_Load and fix bulk insert scaling in load_sql.py#294
benhayes21 wants to merge 2 commits into
mainfrom
fix/load-sql-database-load

Conversation

@benhayes21

@benhayes21 benhayes21 commented Sep 3, 2026

Copy link
Copy Markdown
Contributor

Fixes #293

Summary

  • Uncomments the `EXEC usp_Database_Load` call at the end of `load_sql.py` and switches from `engine.connect()` to `engine.begin()`, so the stored procedure's changes actually commit instead of being silently rolled back when the connection closes.
  • Replaces `method='multi'` with `fast_executemany=True` on the engine for all four staging table loads. This batches inserts at the pyodbc driver level instead of building one big multi-row `INSERT`, removing SQL Server's 2100-parameter-per-statement ceiling and scaling to OED input files with millions of rows.
  • Normalizes `PolInceptionDate`/`PolExpiryDate` in the account file to ISO 8601 (per the OED spec in `OpenExposureData/OEDInputFields.csv`) before loading, since source files aren't always already in that format and SQL Server's default MDY session setting misparses `DD/MM/YYYY` dates.

Test plan

  • Ran against the standard test data (`SQL_Scripts/SourceFiles/location.csv`, `account.csv`, `ri_info.csv`, `ri_scope.csv`) and confirmed row counts landed in the real tables (`Location`, `Account`, etc.), not just the staging tables.
  • Ran against a larger real-world location file (~2,400 rows) that previously failed with `pyodbc.Error: COUNT field incorrect or syntax error` under `method='multi'`; confirmed it now loads successfully.
  • Confirmed account files with `DD/MM/YYYY` dates no longer trigger a `smalldatetime` conversion error in `usp_Database_Load`.

@benhayes21 @claude
fix: run usp_Database_Load and fix bulk insert scaling in load_sql.py
6f19f18 
The EXEC usp_Database_Load call was commented out, so staging tables
loaded but the full database load never ran. Uncommented it and switched
to engine.begin() so the call actually commits instead of being rolled
back on connection close.

Also replaced method='multi' with fast_executemany=True, which batches
inserts at the pyodbc driver level instead of building one big multi-row
INSERT bounded by SQL Server's 2100-param limit, so it scales to OED
input files with millions of rows. Account dates are normalized to ISO
8601 per the OED spec before loading.

Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
@github-actions

github-actions Bot commented Sep 3, 2026

Copy link
Copy Markdown

Build Preview

You can find files attached to the below linked Workflow Run URL (Logs).
Please note that files only stay for around 14 days!

Name Link
Commit 61890ee
Build https://github.com/OasisLMF/ODS_OpenExposureData/actions/runs/33739701111
Excel File excel_spec.zip
JSON File extracted_spec.zip

@benhayes21 @claude
fix: load full location.csv and populate LocationDetail table
3999147 
load_sql.py was truncating location.csv to 10 rows via a leftover
.head(10) debug call, so only a handful of locations ever reached
staging regardless of input size.

Separately, LocationDetail was defined in the schema but never
populated: usp_Database_Load only ran usp_Location_Load, which inserts
the 4-column Location skeleton, with no procedure loading the ~150
detail attributes (address, geocoding, occupancy/construction codes,
vulnerability fields, etc.) that land in _import_location. Added
usp_LocationDetail_Load, which joins _import_location to
_businesskeys_location on the natural key to resolve LocationId and
inserts the detail columns, and wired it into usp_Database_Load.

Verified against a local SQL Server instance: full 12598-row
location.csv now loads end-to-end into Location and LocationDetail
with no orphaned or duplicate rows.

Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
@github-actions

github-actions Bot commented Sep 17, 2026

Copy link
Copy Markdown

Build Preview

You can find files attached to the below linked Workflow Run URL (Logs).
Please note that files only stay for around 14 days!

Name Link
Commit b34cb47
Build https://github.com/OasisLMF/ODS_OpenExposureData/actions/runs/35246803662
Excel File excel_spec.zip
JSON File extracted_spec.zip

benhayes21 added a commit that referenced this pull request Sep 17, 2026
@benhayes21 @claude
fix: load full location.csv, populate LocationDetail, batch inserts
ddd6d0a 
Folds in the fixes from #294 on top of this branch's relational-
schema rework:

- load_sql.py: batch to_sql() writes with chunksize=10000 (works with
  fast_executemany to scale past SQL Server's 2100-param limit on
  large OED input files) and normalize PolInceptionDate/PolExpiryDate
  to ISO 8601 before staging, since the OED spec requires it and
  source files may use a locale-specific date format.

- LocationDetail was defined in the schema (and even cleared by
  usp_Database_Load's idempotency reset) but never populated: no
  procedure existed to load its ~160 detail attributes from
  _import_location. Added usp_LocationDetail_Load, joining
  _businesskeys_location to _import_location on the natural key
  (matching usp_Location_Load's existing join pattern) and wired it
  into usp_Database_Load right after usp_Location_Load. LocGroup is
  intentionally excluded from the column list since this branch
  already extracts it into the separate LocationGroup table.

Verified against a local SQL Server instance: full 12598-row
location.csv loads end-to-end into Location and LocationDetail with
no orphaned or duplicate rows, and correct attribute values.

Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
@benhayes21

benhayes21 commented Sep 17, 2026

Copy link
Copy Markdown
Contributor Author

Superseded — folded into #280 (commit ddd6d0a), which also independently needed these two fixes plus a new usp_LocationDetail_Load procedure adapted to that branch's restructured schema (LocationGroup extraction, column renames, PV fields).

@benhayes21 benhayes21 closed this Sep 17, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

None yet

Projects

Oasis Dev Team Tasks
Status: Done

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

load_sql.py: usp_Database_Load never actually runs / doesn't commit, and bulk insert breaks on large files

2 participants

@benhayes21 @awsbuild