Bulk Ingest#

SQLSpec exposes native bulk-ingest fast paths through a small, adapter-agnostic storage-bridge API. High-volume writes use each driver's database primitive -- COPY, LOAD DATA LOCAL INFILE, direct path load, BulkCopy, Arrow ingest, load jobs, or mutations -- instead of generic row-by-row execution.

The API#

Three methods cover the common shapes. They share return type StorageBridgeJob (its telemetry dict reports rows_processed):

load_from_arrow(table, source, *, partitioner=None, overwrite=False) -- load an Arrow table, RecordBatch, RecordBatchReader, or an ArrowResult directly using the adapter's native ingest path.
load_from_storage(table, source, *, file_format, partitioner=None, overwrite=False) -- load a staged artifact (a local path or cloud URI) into a table.
load_from_records(table, records, *, columns=None, overwrite=False) -- load in-memory rows. records may be mappings (columns derived from the keys) or positional sequences (columns required). Adapters normally normalize records through their native Arrow ingest path. AsyncPG sends validated record tuples directly to binary COPY; callers that pass an actual Arrow input still use load_from_arrow unchanged.

In synchronous drivers:

# dict records -- columns inferred from keys
driver.load_from_records("orders", [{"id": 1, "total": 9.99}, {"id": 2, "total": 4.50}])

# positional records -- columns required
driver.load_from_records("orders", [(3, 1.0), (4, 2.0)], columns=["id", "total"])

# load staged local Parquet or CSV file
driver.load_from_storage("orders", "data/staging_orders.parquet", file_format="parquet")

In asynchronous drivers, all three methods are coroutines:

# Ingest in-memory records
await async_driver.load_from_records("orders", [{"id": 1, "total": 9.99}])

# Load from cloud storage (S3 / GCS / Azure)
await async_driver.load_from_storage(
    "orders",
    "s3://my-bucket/staging/orders.parquet",
    file_format="parquet",
    overwrite=True,
)

# Direct zero-copy ingest from an ArrowResult or pyarrow.Table
arrow_result = await source_driver.select_to_arrow("SELECT * FROM raw_orders", native_only=True)
job = await target_driver.load_from_arrow("orders", arrow_result)
print(f"Loaded {job.telemetry['rows_processed']} rows")

Empty input, mismatched mapping keys, or a positional/column width mismatch raise ImproperConfigurationError.

Capability matrix#

Adapter	Native ingest path	Transactionality	Gate / opt-in
asyncpg	`COPY` (`copy_records_to_table`)	Atomic; exact row counts	Always on
psycopg (sync/async)	`COPY` streaming `write_row`	Atomic; exact row counts	Always on
psqlpy	Binary `COPY` with `INSERT` fallback	Atomic	Always on
cockroach (asyncpg / psycopg)	`COPY` streaming for records/Arrow; `load_from_storage` appends remote CSV/Parquet through `IMPORT INTO`	Server-managed (`IMPORT INTO` takes table offline and invalidates FKs)	Opt-in via `enable_native_storage=True`; autocommit required on psycopg; transactions and overwrite retain client Arrow path
adbc	`adbc_ingest` (append/replace)	Driver-dependent; `adbc_ingest` is always attempted and unsupported drivers raise	Always on
duckdb	`register` + `INSERT ... SELECT`; `load_from_storage` appends remote Parquet through `INSERT ... SELECT read_parquet`	Single connection transaction	Native remote reads use a configured DuckDB filesystem, through a loaded extension or registered filesystem; CSV imports and overwrite retain Arrow
sqlite / aiosqlite	`executemany` inside one `BEGIN IMMEDIATE`	Atomic when the driver owns the transaction; rolls back on error	Always on
oracledb	direct path load (Thin mode, default); `executemany` fallback	Per `execute_many`; array-DML row counts available	`enable_direct_path_load=False` to force fallback; `oracle_batch_errors` / `oracle_array_dml_row_counts` execution args
MySQL family (pymysql, asyncmy, aiomysql, mysql-connector)	`executemany` (default); `LOAD DATA LOCAL INFILE` (opt-in)	Server-managed	Connection `local_infile=True` or `allow_local_infile=True`; `enable_local_infile_bulk_load=False` forces fallback
bigquery	Parquet load job (default); Arrow Storage Write API (opt-in)	All-or-nothing load job / PENDING write stream	`enable_storage_write_api`; load retry/timeout via job-control features
spanner	`Transaction.insert_or_update` mutations (upsert); Batch Write API (opt-in)	In-transaction (default); independently committed groups (Batch Write)	Always on; `enable_batch_write_api` for high-throughput groups
mssql-python	`cursor.bulkcopy()` via `load_from_arrow`	Driver-managed	Always on
arrow_odbc	`bulk_insert_arrow` via `load_from_arrow`	Driver-managed	Always on

Security and opt-in paths#

Some fast paths are opt-in because they read local files or change semantics:

MySQL ``LOAD DATA LOCAL INFILE`` is enabled by either local_infile=True or allow_local_infile=True in the connection configuration. Both names work for each MySQL adapter. If both are set, either true value enables loading. If neither is true, loading stays off. SQLSpec sends only the driver's native flag.

This one flag also turns on bulk loads for supported data. Set driver_features={"enable_local_infile_bulk_load": False} to keep using executemany. Setting that feature to true with no connection opt-in raises ImproperConfigurationError when you create the config. The MySQL server must also have local_infile enabled. mysql-connector additionally honors allow_local_infile_in_path -- the staged temp file must live under that directory when it is set. Connection opt-in trusts the configured MySQL server to request client files.

For asyncmy bulk loads, the requested filename must match that operation's payload; this is not a connection-wide file restriction for other queries. SQLSpec uses asyncmy's native sender (version 0.2.13 or newer), removes its private UTF-8 payload after each attempt, and closes the connection if native loading fails or is cancelled. When bulk loading is disabled, and for nested, binary or duration values, asyncmy uses executemany. As with the other MySQL adapters, overwrite=True first truncates the table; a later load failure does not restore those rows.
Oracle direct path load is the default bulk-ingest transport in Thin mode. Set enable_direct_path_load=False to force executemany. Connections that do not expose the Direct Path Load API, including Thick-mode connections, silently fall back to executemany. Tables whose Arrow columns use types the driver cannot convert -- nested types, dates, times, durations and dictionary-encoded columns among them -- also fall back, because a conversion the driver refuses can leave a load partly written.
BigQuery Storage Write API (enable_storage_write_api) streams Arrow rows for load_from_arrow appends and falls back to the Parquet load job when the Storage client is unavailable; overwrite=True always uses a Parquet WRITE_TRUNCATE load job.
Spanner Batch Write API (enable_batch_write_api) routes load_from_arrow through Database.mutation_groups().batch_write() for high-throughput, independently committed insert_or_update groups instead of a single in-transaction flush. The upsert semantics keep each group idempotent on replay.
CockroachDB native storage (enable_native_storage) routes load_from_storage through server-side IMPORT INTO for remote CSV and Parquet. CockroachDB takes the target table offline during the import and invalidates foreign keys, which must be revalidated afterward. Psycopg connections require connection_config={"autocommit": True} because CockroachDB rejects import statements inside user transactions. Native CSV import requires explicit native_storage_csv_options={"skip": <count>} (e.g., skip=0 for headerless files or skip=1 for single-header files) rather than guessing headers.

Examples#

MySQL LOAD DATA LOCAL INFILE:

from sqlspec.adapters.pymysql import PyMysqlConfig

config = PyMysqlConfig(
    connection_config={"host": "localhost", "local_infile": True},
)
with config.provide_session() as driver:
    driver.load_from_arrow("orders", arrow_table)

Asyncmy with explicit LOCAL INFILE consent:

from sqlspec.adapters.asyncmy import AsyncmyConfig

config = AsyncmyConfig(
    connection_config={
        "host": "localhost",
        "allow_local_infile": True,
    },
)
async with config.provide_session() as driver:
    await driver.load_from_arrow("orders", arrow_table)

Oracle per-call batch error and array-DML row-count reporting:

statement_config = driver.statement_config.replace(
    execution_args={"oracle_batch_errors": True, "oracle_array_dml_row_counts": True}
)
result = driver.execute_many(
    "INSERT INTO orders (id, total) VALUES (:1, :2)", rows, statement_config=statement_config
)
failures = result.metadata["oracle_batch_errors"]      # list of {offset, code, message}
row_counts = result.metadata["oracle_dml_row_counts"]  # per-statement affected rows

Note

Spanner load_from_arrow uses insert_or_update mutations, so re-running the same rows is an idempotent upsert rather than a primary-key collision.