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.)

[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.

[subrata-ms]

@amachanic , While I agree that removing **kwargs from the bulkcopy API is a step forward, listing all parameters individually—especially the boolean ones—may not lead to the cleanest or most scalable API design. I’ve shared my thoughts below on both the current implementation and the proposed approach -

Approach-1:: Current API specification
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]

Approach-1:: API Call Looks Like (Current Design) :

result = d._bulkcopy(
table_name="public.my_table",
data=data,
batch_size=2, # batching path
timeout=7, # statement_timeout path
column_mappings=column_mappings, # mapping path
keep_identity=True, # unsupported => warning
check_constraints=True, # unsupported => warning
table_lock=True, # lock path
keep_nulls=True, # unsupported => warning
fire_triggers=True, # unsupported => warning
use_internal_transaction=True # transaction context path
)

difficult to read and error prone. Many boolean flags = “boolean trap”: hard to read and easy to misuse.

Approach-2:: proposed API specification:

class BulkCopyOptions:
batch_size: int = 10000
timeout: int = 30
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

Approach-2:: API Call based on proposed design: Below seems to be cleaner approach

result = d._bulkcopy(
table_name="public.my_table",
data=data,
options: BulkCopyOptions = BulkCopyOptions(),
)

From my review, Approach 2 seems to present a cleaner and more object‑oriented pattern. A significant advantage is the flexibility it offers: future parameters can be added without altering the API interface, since changes are contained within BulkCopyOptions. This makes backward compatibility and API versioning easier to handle and less prone to mistakes. Overall, Approach 2 enhances maintainability and supports a more organized API design.

[amachanic]

@subrata-ms I do not agree with various parts of your assessment here:

A) This is not an example of the "boolean trap". That issue occurs when calling a function using positional rather than named parameters.
B) The example call is not realistic. Most users won't bother overriding most of the options, most of the time. They're optional with defaults.
C) The BulkCopyOptions class has the exact same structure as the current function call; adding this just moves everything one level up. And now, users are forced to instantiate that class on every call even if they didn't want to override anything.
D) I see no benefit of object-orientation in this very functionally-oriented library, and as mentioned above these APIs do not change often. So I don't buy into the maintainability aspect. Besides which, again, these are default parameters we're talking about.

Anyway, I'm not going to debate further on this; I'd like to actually be able to use this functionality. But I'll close in saying that I feel strongly that the current implementation is the best one.

[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.

[SandeepMummaneni]

When is the expected release.

[match-gabeflores]

it was released today
https://github.com/microsoft/mssql-python/wiki/Bulk-Copy-(BCP)-API-Reference

[sumitmsft]

BulkCopy is now released. Please use it, test it and share your firsthand experience with us.

We would be glad to hear your feedback!

[amachanic]

"Unfortunately that's the design constraint on bcp api. We need to be more clear about it"

So I can't use a transaction? That's really bad!!!

[amachanic]

@amachanic hows the API for col mapping working out for you?

I'll give it a go but honestly this transaction thing is a nonstarter for all of my workflows. Typical bulk copy pattern across my last several projects:

Begin a transaction
Create a temp table
Bulk copy some data into the temp table
Update some other table(s) with that data -- maybe an insert here, a delete there, etc
Commit the transaction

There's no way to make that work here in a safe and transactional way? Let me know if there are some workarounds, but the only ones I can think of are pretty ugly. (E.g. session-keyed permanent table.)

[leonkosak]

Just slightly offtopic question - is this Bulk API stable or will change in the future versions of mssql-python?

[amachanic]

I'll give it a go but honestly this transaction thing is a nonstarter for all of my workflows.

I take this back. I thought through it some more and I don't think it matters, I just need to do the bulk copying bit outside of the transaction and I'll use a uniquely named global temp table. I'm still annoyed by the whole thing but I'll get by for now :-)

[saurabh500]

API is stable @amachanic

Thanks for walking us through your thoughts. I will open a tracking issue.

We decided to leverage a TDS protocol runtime, built on Rust for bulkcopy. It is performant and helped us deliver the value of Bulkcopy API.
We had the transactions limitations in mind, and while session temp tables are a blocker, Global temp table seemed like a reasonable workaround (not ideal, but reasonable). We went through the similar thought process as you did, where we went from Transactions being a no go to "it's not ideal to not have them, but then for fast data load, we should be able to deliver value without transactions, on a new connection."

We will be working on the Rust runtime in https://github.com/microsoft/mssql-rs for enabling languages with Rust interop.

I will post the link to track the issue for Txn on BCP soon.

Thanks for trying this out quickly and providing feedback.

[match-gabeflores]

Would be great to get a quick start example of loading parquet files and using bulkcopy. It'll be a very common use case for data engineers
https://github.com/microsoft/mssql-python/wiki/Bulk-Copy-(BCP)-API-Reference

[amachanic]

They accept contributions :-)

(I actually think the best way to accomplish this today is with the recently released mssql duckdb extension ...)

[saurabh500]

@match-gabeflores feedback acknowledged.