A Python loop that inserts one row per call can spend its day on network round trips. Bulk inserts from Python improve when rows travel in sensible batches. Measure the path before and after changing it, because faster code with partial data is no win.
Start With a Safe Target Shape
List the target table’s columns, types, null rules, defaults, and indexes before writing Python. Use an explicit INSERT column list and parameter placeholders. Do not make the script depend on the physical column order. Decide what identifies a duplicate and whether a retry should insert, skip, or update it. That rule belongs in the target design.
I first test a small set containing nulls, Unicode text, decimals, and a value near the largest expected length. A happy-path row is a weak type test. Which value will fail only after the load has been running for a while? Find it before the large batch. The catalog query below shows the target columns after you choose the right database and table.
SELECT
SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName,
c.column_id,
c.name AS ColumnName,
ty.name AS TypeName,
c.max_length,
c.is_nullable
FROM sys.tables AS t
JOIN sys.columns AS c
ON c.object_id = t.object_id
JOIN sys.types AS ty
ON ty.user_type_id = c.user_type_id
ORDER BY SchemaName, TableName, c.column_id;Use Parameterized executemany
With pyodbc, build a sequence of tuples and call cursor.executemany with one parameterized INSERT statement. Each question mark binds a value. Do not construct a giant SQL string by joining user data into VALUES clauses. Parameters protect the data boundary and avoid quoting surprises. Keep the connection transaction behavior explicit, including when each batch commits and how an exception rolls back.
For bulk inserts from Python, I prefer batches small enough to hold comfortably in memory and large enough to avoid one call per row. There is no universal batch size. The right size depends on row width, network, indexes, logging, and driver behavior. Record the size you test and the workload used. A favorite round number is not a benchmark.
Test fast_executemany, Then Compare
The pyodbc cursor has a fast_executemany property. Set it to True before executemany to use ODBC parameter arrays where the driver supports them. Test the exact ODBC driver, Python version, and column types you deploy. Some combinations expose type conversion or memory issues. A faster switch should be introduced by a measured test, not by changing a flag and trusting the name.
Run the same representative rows with the default path and the fast path in a disposable target. Reset the target between runs. Measure total elapsed time, inserted rows, failures, and server load. I compare complete transactions, not just the time spent inside one Python call. The commit can be where the wait appears.
Batch Bulk Inserts From Python for Recovery as Well as Speed
A batch boundary gives the load a place to commit, report progress, and restart. Keep a source key or range in a manifest so a retry can identify which rows were accepted. Do not assume executemany either inserted everything or inserted nothing unless your transaction handling makes that true. Test a deliberate bad row and verify the database state afterward.
I keep the source batch identifier with the load log. That makes it possible to compare the target with the original source and explain a partial run. If the target uses triggers or constraints, account for their work in the timing. Bulk inserts from Python measured against an empty test table give a different result from the same load on the indexed production table.
Consider a Table-Valued Parameter
A table-valued parameter sends a set of rows to a stored procedure as one structured argument. It can be a good fit when the server must validate or merge a small to moderate batch with set-based logic. Define a user table type and a procedure under change control, then confirm the chosen Python driver can bind the parameter in your environment. Driver support and calling syntax deserve a focused test.
A TVP is not the same switch as fast_executemany. The former is a server-side interface, while the latter changes how parameter arrays are sent for repeated statements. I choose based on the operation. A straightforward append can use executemany. A conditional merge can justify a procedure receiving rows.
Inspect the Server During Bulk Inserts From Python
The client timer does not explain whether SQL Server waits on locks, writes, or something else. While a test load runs, inspect active requests with the query below. The needed permissions depend on the server version and your role. Capture the relevant session and look at its waits and elapsed time. Pair that with Python-side timings for data preparation, execute, and commit.
Do not claim that Python is slow because the total run is slow. Converting source files, waiting for network, maintaining indexes, and logging all contribute. A useful comparison changes one factor at a time. If you change batch size, driver, and table indexes together, the result has no clear cause.
SELECT
r.session_id,
s.program_name,
r.command,
r.status,
r.wait_type,
r.total_elapsed_time
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
ON s.session_id = r.session_id
WHERE s.is_user_process = 1
ORDER BY r.total_elapsed_time DESC;Validate Bulk Inserts From Python by Rows, Not Just Speed
After the load, compare target count and key coverage with the source for the same batch identifier. Sample values that test decimal precision, Unicode, date boundaries, and null behavior. A load that completes quickly but rounds money or truncates text is a failed load. Store the comparison result with the run record.
I also inspect duplicates and unexpected defaults. A rerun can be the first time an idempotency bug becomes visible. Create a small repeatable test that runs the same batch twice and checks the intended outcome. That test is more valuable than a single peak throughput number.
Choose the Simplest Path That Meets the Target
For very large flat-file loads, bcp or BULK INSERT can be a better fit than a Python row stream, provided file access and security rules support it. Python still has a useful role in preparing and validating the file. Compare approaches on the actual target table with the same data and transaction expectations. Do not optimize away the process that makes a load recoverable.
The production script should log batch identity, row count, duration, and error details without exposing sensitive values. Include a controlled stop and restart procedure. Once you can rerun safely, you can tune the batch size with confidence. Until then, speed is just a shorter route to a harder incident.
Related reading on this blog: Automating SQL Server Deployments Across Multiple Databases Using Python and Validate Data Cleanliness Using Asserts in Python.
A fast Python load is not the shortest loop, it is a measured batch process that can recover cleanly.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
