A pandas to_sql load of two million rows through a SQLAlchemy engine is slow or hits packet limits: what do chunksize, method='multi' and an ADBC connection change?
answer
- rows per batch versus statements
- default writes everything at once
- one VALUES list per chunk
- bound-parameter cap per statement
- Arrow bulk ingest refuses batching
basics
~20 schunksize sets rows per batch (default: all at once); method='multi' packs each chunk into one multi-row INSERT, so chunksize must keep rows times columns under the database's parameter limit. An ADBC connection bulk-ingests through Arrow and rejects chunksize and method.
solid answer
~40 sBy default `to_sql` sends every row in one batch, as single-row `INSERT`s that the driver executes for each parameter set. `chunksize=N` splits that into batches of N rows, which avoids packet-size errors. `method='multi'` builds one `INSERT ... VALUES (...), (...)` per chunk: fewer round trips, but the statement carries rows × columns bound parameters, so without `chunksize` a big frame becomes one huge statement, and on wide tables it can be slower. With a SQLAlchemy engine pandas wraps the whole write in one transaction. An ADBC connection changes the path entirely: pandas converts the frame to Arrow and calls the driver's bulk ingest, and `chunksize`, `method`, `dtype` and `index_label` raise `NotImplementedError`.
code
python · 13 linesimport sqlite3
import pandas as pd
conn = sqlite3.connect(":memory:")
df = pd.DataFrame({"store_id": range(10_000), "revenue": 1.5})
# 400 rows x 2 columns = 800 bound parameters per INSERT statement
written = df.to_sql("daily_sales", conn, index=False, chunksize=400, method="multi")
print(written) # 10000
for chunk in pd.read_sql("SELECT * FROM daily_sales", conn, chunksize=4_000):
print(len(chunk)) # 4000, 4000, 2000go deeper
Recall that to_sql has a chunksize argument and that the default writes every row in one go.
Explain what method=None and method='multi' send to the database and why chunksize must accompany 'multi'.
Diagnose a slow or failing load: parameter caps, wide tables, the single transaction around an engine write, and when an ADBC connection or a custom method callable is the better path.
Decide whether a team's loads go through SQLAlchemy with tuned batches, ADBC bulk ingest, or a bulk-load callable, and what each costs in drivers and features.
## Where the time goes in `to_sql` `DataFrame.to_sql` with a SQLAlchemy connectable turns the frame into parameter rows and sends INSERT statements. Three arguments decide how: - **`chunksize`**: the number of rows per batch. The default `None` writes **all rows at once** as a single batch. - **`method=None`** (default): the standard `INSERT` clause, one statement per row. pandas passes the batch as a list of parameter sets, and the driver executes the single-row INSERT for each of them. - **`method='multi'`**: one `INSERT ... VALUES (...), (...), ...` statement per batch, carrying every row of the chunk. - **`method=callable`** with signature `(pd_table, conn, keys, data_iter)`: your own insert, for example a database's bulk-load command. The user guide notes that some databases reject very large writes on packet-size limits, and that setting `chunksize` avoids it. ## `method='multi'` and `chunksize` together | setting | statements per chunk | parameters per statement | typical effect | |---|---|---|---| | `method=None`, `chunksize=None` | one per row | columns | many round trips, unless the driver batches them | | `method=None`, `chunksize=N` | one per row | columns | same, sent in batches of N | | `method='multi'`, `chunksize=None` | **one for the whole frame** | rows × columns | can exceed a parameter or packet limit | | `method='multi'`, `chunksize=N` | one per chunk | N × columns | fewer round trips, bounded statement size | What to know about `'multi'`: 1. It uses multi-row VALUES syntax that **not every database supports**; the docstring says some do not support multi-value insert at all. 2. Many databases cap the number of **bound parameters** in one statement, so `chunksize × number_of_columns` must stay under that cap. Without `chunksize`, a large frame produces one enormous statement. 3. The user guide says it usually helps analytic databases but can perform **worse** on traditional databases when the table has many columns; the source comments that performance degrades quickly as columns increase. So `'multi'` is not a free speed-up; it is a statement-shape choice to measure. ## Transactions With a SQLAlchemy **engine**, or a connection that is not already in a transaction, pandas opens a transaction around the whole write, so every chunk commits together or not at all. A connection that is **already** in a transaction is not committed by pandas. With the `sqlite3` fallback, the docstring warns that the insertion cannot be rolled back. ## ADBC connections Since pandas 2.2, `con` may also be an **ADBC** (Arrow Database Connectivity) connection, which the user guide recommends where a driver exists, for performance, null handling and type detection. It changes the rules: - `to_sql` converts the frame to an Arrow table and hands it to the driver's **bulk ingest** in one call. - `chunksize`, `method`, `dtype` and `index_label` raise **`NotImplementedError`** on an ADBC connection. - On the read side, `read_sql` over ADBC fetches an Arrow table; `chunksize`, `params` and a non-default `coerce_float` raise `NotImplementedError`. That trade is explicit: ADBC gives the driver the whole frame, so batching and statement shape are no longer pandas' business. ## The read side: `read_sql(chunksize=...)` With SQLAlchemy or `sqlite3`, `read_sql(sql, con, chunksize=50_000)` returns an **iterator of DataFrames**, pulled with the cursor's `fetchmany`. pandas then holds one chunk at a time, but whether the full result was already buffered client-side is the driver's behaviour, so check the driver's streaming options before relying on it for memory. Other useful arguments: - **`params`** passes values to the driver in its own placeholder style instead of formatting them into the SQL string; pandas does not sanitise statements. - **`dtype_backend='pyarrow'`** keeps database types closer to the source than NumPy dtypes do. - **`parse_dates`** converts named columns, which matters for databases without a native datetime type. - **`read_sql`** itself only dispatches: a table name goes to `read_sql_table` (SQLAlchemy and ADBC; a `sqlite3` connection accepts queries only), anything else to `read_sql_query`. - Any other DBAPI2 connection is sent down the `sqlite3` code path with a `UserWarning` that only SQLAlchemy connectables, URI strings and `sqlite3` connections are supported and other DBAPI2 objects are not tested. ## A pragmatic order of attack 1. Time the default with a sensible `chunksize` first. 2. Try `method='multi'` with `chunksize` sized under the parameter cap, and measure. 3. If the database has an ADBC driver, compare a plain `to_sql` over ADBC. 4. For very large loads, write a `method` callable that uses the database's bulk-load path.
- How do you size chunksize for method='multi'?Divide the database's maximum bound parameters per statement by the number of columns written, including the index column if `index=True`, and leave headroom. Then measure: for wide tables a smaller chunk or `method=None` can win.
- Why can't you pass params to read_sql over an ADBC connection?pandas' ADBC path executes the SQL and fetches an Arrow table, and it raises `NotImplementedError` for `params`, `chunksize` and a non-default `coerce_float`. Use a SQLAlchemy connectable when the query needs bound parameters or chunked reads.
- Assumes method='multi' is always faster than the default
- Uses method='multi' without chunksize on a frame with millions of rows
- Believes to_sql commits after every chunk when given an engine
- Expects chunksize to batch an ADBC to_sql write
- Thinks method='multi' uses the database's bulk-load command