BulkCopy in mssql-python - API Spec & Discussion

[bewithgaurav]

BulkCopy API Specification

Issue #18

We’re adding a BulkCopy API (cursor.bulkcopy()) to mssql-python for fast, high-throughput inserts into SQL Server.

Due to all the interest, this discussion thread is here for us to understand from you, about your usage scenarios - feel free to comment your suggestions and propose improvements!

If you’re using (or planning to use) bulk copy, feel free to comment with:

• Your use case (ETL, migration, backfill, analytics, etc.)
• Options you ended up using (batch_size, table_lock etc.)
• Anything that felt unclear or surprising

---

Overview

The cursor.bulkcopy() method provides bulk data loading to SQL Server, similar to ADO.NET's SqlBulkCopy. It follows the standard DB-API cursor pattern.

---

API Revisions

Revision 1 (kwargs dict) — Deprecated

cursor.bulkcopy(
    table_name: str,
    data_source: Iterator[Tuple],
    **kwargs
) -> Dict[str, Any]

Limitation: Poor IDE discoverability - users couldn't see available options without reading docs.

---

Revision 2 (explicit parameters) ✅ Current

Based on community feedback, all options are now explicit parameters for better IDE autocomplete and discoverability.

cursor.bulkcopy(
    table_name: str,
    data_source: Iterator[Tuple],

    # Performance settings
    batch_size: int = 1000,
    timeout: int = 30,

    # Column mapping
    column_mappings: Optional[Union[List[str], List[Tuple[int, str]]]] = None,

    # Bulk copy options
    keep_identity: bool = False,
    check_constraints: bool = False,
    table_lock: bool = False,
    keep_nulls: bool = False,
    fire_triggers: bool = False,
    use_internal_transaction: bool = False
) -> Dict[str, Any]

Changes from Revision 1

Change	Before	After
Parameter style	**kwargs	Explicit parameters
IDE support	No autocomplete	Full autocomplete
column_mappings	List[Tuple[Union[str, int], str]]	Union[List[str], List[Tuple[int, str]]]

---

Usage Example

import mssql_python

conn = mssql_python.connect(connection_string)
cursor = conn.cursor()

# Prepare data
data = [
    (1, "Alice", 100.5),
    (2, "Bob", 200.75),
    (3, "Charlie", 300.25),
]

# All options visible in IDE autocomplete
result = cursor.bulkcopy(
    "users",
    data,
    batch_size=5000,
    timeout=60,
    table_lock=True,
    fire_triggers=True
)

print(f"Copied {result['rows_copied']} rows in {result['elapsed_time']:.2f}s")

---

Parameters Reference

Required Parameters

Parameter	Type	Description
table_name	str	Name of the destination table (can include schema: 'dbo.MyTable')
data_source	Iterator[Tuple]	Iterator yielding tuples of row data

---

Performance Settings

Parameter	Type	Default	Description
batch_size	int	None (server optimal)	Number of rows per batch
timeout	int	30	Operation timeout in seconds (0 = no timeout)

batch_size guidelines:

• Smaller values (100-500): Lower memory usage, more network round trips
• Larger values (5000-10000): Higher memory usage, better throughput
• Recommended: 1000–10000 depending on row size

---

Column Mapping

Parameter	Type	Default	Description
column_mappings	Union[List[str], List[Tuple[int, str]]]	None	Maps source columns to destination column names

Two formats supported:

Simple Format: List[str]

Just list destination column names in order — position in list = source index.

# Source data: (id, first_name, email)
# Maps: index 0 → UserID, index 1 → FirstName, index 2 → Email

column_mappings = ['UserID', 'FirstName', 'Email']

Advanced Format: List[Tuple[int, str]]

Explicit index mapping — allows skipping or reordering columns.

# Source data: (id, first_name, last_name, email)
# Skip last_name (index 2), reorder as needed

column_mappings = [
    (0, 'UserID'),      # source index 0 → UserID
    (1, 'FirstName'),   # source index 1 → FirstName
    (3, 'Email'),       # source index 3 → Email (skipped index 2)
]

When omitted: Columns are mapped by ordinal position (first source column → first table column, etc.)

---

Bulk Copy Options

Parameter	Type	Default	Description
keep_identity	bool	False	Preserve source identity values (equivalent to SET IDENTITY_INSERT ON)
check_constraints	bool	False	Enforce table constraints during bulk copy
table_lock	bool	False	Acquire table-level lock (reduces locking overhead)
keep_nulls	bool	False	Preserve explicit NULL values instead of applying column defaults
fire_triggers	bool	False	Fire INSERT triggers during bulk copy
use_internal_transaction	bool	False	Wrap operation in internal transaction (auto-rollback on error)

---

Return Value

Returns a dictionary containing operation statistics:

{
    'rows_copied': 10000,        # Number of rows successfully copied
    'batch_count': 10,           # Number of batches processed
    'elapsed_time': 1.5,         # Total elapsed time (seconds)
    'rows_per_second': 6666.67   # Throughput
}

---

Feedback

We're actively developing this API based on community feedback!

cc: @dlevy-msft-sql @saurabh500 @sumitmsft

[saurabh500]

Re

Column Mapping
...
column_mappings (List[Tuple], optional)
source: column name (str) or column index (int)

I think we dont have a usecase for column name (str) in the source.
We may end up limiting the source to only support column index(int)

[amachanic]

Agree, column name in the source is not useful, unless you decide to support mapping collections there - which I also don't think is a necessary thing.

What I would ideally like to see here personally is a slightly simpler implementation: Just let the user pass a list of destination column names, in order, that correspond to the input tuples. So if my input tuple has three elements corresponding to colB, colA, and colC respectively, I'd like to pass in ['colB', 'colA', 'colC'], rather than [(0, 'colB'), (1, 'colA'), (2, 'colC')].

[saurabh500]

I like this suggestion. We will consider this an addition or replacement over the proposed column mapping APIs.
I will take this topic up with the team internally.

[bewithgaurav]

@amachanic - We have incorporated the feedback and have added a new revision to the API spec
Please let us know once you review. Thanks again for your inputs!

[amachanic]

@bewithgaurav Looks amazing, ship it!!! :-)

[amachanic]

I'd like to understand the reasoning behind hiding so much behind the kwargs instead of using optional parameters. I feel that this really makes the interface much more difficult to use. (The various options aside from the column mappings -- which I feel should be a first-class thing -- could all be grouped in a named tuple or class with defaults, if you want to cut it down to just two optional params.)

[saurabh500]

Thanks, I appreciate this suggestion and I can see the benefit you are alluding to. We will make an update to the proposed API and ping this discussion thread.

cc @bewithgaurav

[jblair-LCOG]

For clarity, are we talking about requiring a **kwargs to get at those, or that passing nondefaults for those arguments requires the keyword (i.e. those come after a * in the signature)?

Most of the extra args are booleans, so allowing them as positional args fall into the Flake8 boolean trap. Requiring the keyword for less common args makes a pretty clean interface.
I tend to agree that column_mappings will be used in most calls, so probably deserves to be promoted from keyword-land.

[amachanic]

**kwargs. (See the cursor.bulkcopy spec above.) The * feature is a good solution for this, but seems to be unpopular for some reason. Optional params with defaults are fine too IMO. If users decide to do crazy things with positional args I think that's their problem, not that of the library!

[bewithgaurav]

@amachanic - Thanks for the suggestion!

Completely agreed on both points - we can let users take care of param positioning (library shouldn't necessarily enforce with *), and the interface should expose explicit params rather than hiding everything behind **kwargs.

We'll revise the API spec and will ping this thread once updated.

[leonkosak]

My use case is for ELT for fast inserts from parquet file. I agree with @amachanic about hiding so much parameters behind **kwargs.
What performance is expected compared to OPENROWSET command where SQL engine directly reads parquet files?

[saurabh500]

@leonkosak

Re: comparison of performance to OPENROWSET

It depends. OPENROWSET is a server-side pull: SQL Server reads from the external source (files/remote storage/another SQL Server), assuming the instance has the required access and configuration.
Bulk Copy is an application-side push: your app streams rows into SQL Server via the bulk copy API.
Because the architecture and constraints are different, there isn’t a universal “faster” option. We generally recommend choosing based on your use case and what you can/are willing to configure on the SQL Server side (e.g., access to the source, server settings), versus pushing from the client application.