recon_gen.common.db

Dialect-aware database connection + script execution helpers (P.9d).

Used by the CLI (demo apply) and the e2e harness fixtures. Both need to:

Open a DB-API 2.0 connection against Postgres (psycopg2), Oracle (oracledb), or SQLite (stdlib sqlite3), keyed off cfg.dialect.

Run multi-statement DDL/DML scripts. psycopg2 accepts the whole script in one cursor.execute call; oracledb requires per- statement execution and treats PL/SQL blocks (BEGIN…END;) as one unit; sqlite3 accepts whole scripts via executescript.

Both PG + Oracle surfaces existed inline in cli.py before P.9d. Lifting them here lets tests/e2e/test_harness_end_to_end.py consume the same helpers instead of hardcoding psycopg2 (which raised ProgrammingError at setup when the harness ran against an Oracle config — see PLAN.md P.9d). X.3 added the SQLite arm using the stdlib sqlite3 module — no extra dependency required.

Functions

`batch_oracle_inserts`(statements, *[, batch_size])	Coalesce consecutive ``INSERT INTO same_table .
`connect_demo_db`(cfg)	Open a DB-API 2.0 connection to `cfg.demo_database_url`.
`duckdb_path`(url)	Translate a `duckdb:///path/to/db.duckdb` URL to a path string.
`execute_script`(cur, sql, *, dialect[, ...])	Run a multi-statement SQL string against `cur`.
`fetch_one_required`(cur)	`cur.fetchone()` with a non-None assertion baked in.
`make_connection_pool`(cfg, *[, max_size])	Open an `AsyncConnectionPool` against `cfg.demo_database_url`.
`make_demo_database_url`(dialect, path)	Build the canonical `demo_database_url` for a dialect + path.
`oracle_dsn`(url)	Translate a SQLAlchemy-style Oracle URL into an oracledb DSN.
`split_oracle_script`(sql)	Split an Oracle-style script into individual statements.
`sqlite_path`(url)	Translate a `sqlite:///path/to/db.sqlite` URL to a path string.

Classes

`AsyncConnection`(args, *kwargs)	Minimal async DB-API connection surface used by the App2 executor.
`AsyncConnectionPool`(args, *kwargs)	Uniform async DB-connection pool across PG / Oracle / SQLite.
`AsyncCursor`(args, *kwargs)	Minimal async DB-API cursor surface used by `execute_visual_sql_async`.
`SyncConnection`(args, *kwargs)	DB-API 2.0 sync connection surface — pair of `SyncCursor`.
`SyncCursor`(args, *kwargs)	DB-API 2.0 sync cursor surface — the union of methods actually called across the spine + dialect-agnostic helpers.

class recon_gen.common.db.AsyncConnection(*args, **kwargs)[source]

Bases: Protocol

Minimal async DB-API connection surface used by the App2 executor.

The single async execute(query, params) method returns a cursor with results pre-staged — psycopg / oracledb / aiosqlite all support this one-shot shape, so the executor doesn’t need a separate cursor() step.

async execute(query, params=Ellipsis, /)[source]

Return type:

AsyncCursor

Parameters:

query (str)
params (Any)

class recon_gen.common.db.AsyncConnectionPool(*args, **kwargs)[source]

Bases: Protocol

Uniform async DB-connection pool across PG / Oracle / SQLite.

Each dialect’s native pool has a different surface (psycopg_pool.AsyncConnectionPool.connection(), oracledb async pool’s acquire(), no built-in pool for SQLite). This protocol normalizes them behind one acquire() method that returns an async context manager yielding an AsyncConnection, plus an async close() for application-shutdown lifecycle.

The connection yielded by acquire() returns to the pool on context exit (or is closed for SQLite, which has no real pool). Callers MUST use async with pool.acquire() as conn — a leaked acquire blocks one pool slot until interpreter shutdown.

acquire()[source]

Return type:: AbstractAsyncContextManager[AsyncConnection]

async close()[source]

Return type:: None

class recon_gen.common.db.AsyncCursor(*args, **kwargs)[source]

Bases: Protocol

Minimal async DB-API cursor surface used by execute_visual_sql_async.

All three drivers (psycopg / oracledb / aiosqlite) implement at least this much under their async modes. The protocol intentionally omits close() — drivers split between sync- and async-close semantics, and the executor’s call site does the right duck-typed thing with getattr + __await__ rather than declaring a single contract here.

property description: Sequence[Sequence[Any]] | None

async fetchall()[source]

Return type:: list[Any]

recon_gen.common.db.batch_oracle_inserts(statements, *, batch_size=500)[source]

Coalesce consecutive INSERT INTO same_table ... VALUES (...) statements into Oracle INSERT ALL blocks of up to batch_size rows each.

Format produced:

INSERT ALL
  INTO sasquatch_pr_transactions (col1, col2) VALUES ('a', 'b')
  INTO sasquatch_pr_transactions (col1, col2) VALUES ('c', 'd')
  ...
SELECT 1 FROM dual

Cuts Phase R seed-apply round-trips from ~60k to ~120 (60k rows / 500 per batch). Each Oracle round-trip is ~10-30ms remote, so the total seed-insert time drops from ~20 minutes to ~30 seconds.

Statements that DON’T match the simple INSERT INTO foo VALUES shape (CREATE TABLE, ALTER, complex INSERT…SELECT, PL/SQL blocks, etc.) pass through unchanged. The matcher only batches statements whose INSERT INTO <table> (<cols>) head is identical to the accumulating batch’s head — different tables / column lists flush the current batch before starting a new one.

Return type:

list[str]

Parameters:

statements (list[str])
batch_size (int)

recon_gen.common.db.connect_demo_db(cfg)[source]

Open a DB-API 2.0 connection to cfg.demo_database_url.

Return type:: SyncConnection
Parameters:: cfg (Config)

Branches on cfg.dialect:

Postgres: psycopg (v3, from the [prod] extra).
Oracle: oracledb thin client (from the [prod] extra).
SQLite: stdlib sqlite3 (no extra required).

Raises:

ImportError – if the matching driver isn’t installed (PG / Oracle only — SQLite ships with stdlib). The error message names the extras-install command.
ValueError – if cfg.demo_database_url is unset or cfg.dialect isn’t recognized.

Parameters:

cfg (Config)

Return type:

SyncConnection

recon_gen.common.db.duckdb_path(url)[source]

Translate a duckdb:///path/to/db.duckdb URL to a path string.

Accepts the SQLAlchemy-style duckdb:/// triple-slash form (the fourth slash starts the absolute path component) and the duckdb://:memory: in-memory form. Also accepts a bare path string for ergonomics — if the value isn’t a recognized URL scheme, it’s returned unchanged so the caller can pass the raw DuckDB file path directly. Mirrors the sqlite_path contract.

Return type:: str
Parameters:: url (str)

Examples

duckdb:///tmp/demo.duckdb → /tmp/demo.duckdb
duckdb:///./relative.duckdb → ./relative.duckdb
duckdb://:memory: → :memory:
/tmp/demo.duckdb → /tmp/demo.duckdb

recon_gen.common.db.execute_script(cur, sql, *, dialect, oracle_insert_batch=500)[source]

Run a multi-statement SQL string against cur.

Postgres (psycopg2): the whole string in one execute call works. Oracle (oracledb): cursor.execute requires single statements (not PL/SQL blocks; not ;-separated). Splits via split_oracle_script and executes each statement individually, surfacing which statement (out of N) failed and the first 1500 characters of its body for triage. SQLite (sqlite3): the connection’s executescript method handles multi-statement scripts natively — but the cur parameter is the cursor, not the connection. Iterate per-statement (split on ; boundaries with the same comment-aware splitter the Oracle path uses) for symmetry with Oracle’s per-statement error surfacing.

Oracle bulk-INSERT batching (R.4.a): consecutive INSERT INTO same_table VALUES (...) statements get coalesced into INSERT ALL blocks of oracle_insert_batch rows. Cuts Phase R seed apply from ~30+ minutes (60k per-row round-trips at ~20ms each) to ~30 seconds. Set oracle_insert_batch=1 to disable batching for debug.

Return type:

None

Parameters:

cur (Any)
sql (str)
dialect (Dialect)
oracle_insert_batch (int)

async recon_gen.common.db.make_connection_pool(cfg, *, max_size=10)[source]

Open an AsyncConnectionPool against cfg.demo_database_url.

Branches on cfg.dialect (mirrors connect_demo_db):

Postgres: psycopg_pool.AsyncConnectionPool with min_size=1, max_size=N. Pre-opens so connection failures surface here.
Oracle: oracledb.create_pool_async with the same shape.
SQLite: thin aiosqlite-per-acquire wrapper (no real pool).

Parameters:

cfg (Config) – Loaded Config; cfg.demo_database_url and cfg.dialect drive both URL parsing and driver selection.
max_size (int) – Pool size cap. Defaults to 10 — enough for a typical sheet’s visuals to fetch concurrently without queueing. Tune upward for high-fan-in dashboards or multi-user demo loads.

Raises:

ImportError – matching async driver isn’t installed (psycopg[binary,pool] for PG, oracledb for Oracle, aiosqlite for SQLite). Each ImportError names the extras-install command.
ValueError – cfg.demo_database_url unset or cfg.dialect unrecognized.

Return type:

AsyncConnectionPool

recon_gen.common.db.oracle_dsn(url)[source]

Translate a SQLAlchemy-style Oracle URL into an oracledb DSN.

Return type:: str
Parameters:: url (str)

Accepts either form:

oracle+oracledb://user:pass@host:port/?service_name=XEPDB1
user/pass@host:port/XEPDB1 (oracledb’s native format)

Returns a string oracledb.connect() understands.

recon_gen.common.db.split_oracle_script(sql)[source]

Split an Oracle-style script into individual statements.

Handles PL/SQL blocks (anything starting with BEGIN or DECLARE and ending with END;) as one unit; everything else splits on bare ;.

Trailing-semicolon contract differs between the two:

PL/SQL blocks: the ; is part of the END; terminator and Oracle’s parser rejects the block without it (PLS-00103 “encountered end-of-file”). Keep it.
Plain SQL statements: oracledb.Cursor.execute rejects a trailing ; (“invalid character”). Strip it.

Return type:: list[str]
Parameters:: sql (str)

recon_gen.common.db.sqlite_path(url)[source]

Translate a sqlite:///path/to/db.sqlite URL to a path string.

Accepts the SQLAlchemy-style sqlite:/// triple-slash form (the fourth slash starts the absolute path component) and the sqlite://:memory: in-memory form. Also accepts a bare path string for ergonomics — if the value isn’t a recognized URL scheme, it’s returned unchanged so the caller can pass the raw sqlite file path directly.

Return type:: str
Parameters:: url (str)

Examples

sqlite:///tmp/demo.sqlite → /tmp/demo.sqlite
sqlite:///./relative.sqlite → ./relative.sqlite
sqlite://:memory: → :memory:
/tmp/demo.sqlite → /tmp/demo.sqlite