Repository navigation
Closed
Labels
area: bulk-copyIssues in cursor.bulkcopy()Issues in cursor.bulkcopy()discussiontriage doneIssues that are triaged by dev team and are in investigation.Issues that are triaged by dev team and are in investigation.
Description
Describe the bug
If a nullable column in SQL Server has a default value, you can still insert a NULL by setting it explicitly in an INSERT statement.
However, with bulkcopy, there is no way to insert a NULL (or python None) into a column with a default. It will always take the default. This is unexpected, since it is different from the way INSERT works.
Since you can use the column_mappings param to "skip" columns like you can with INSERT, a column with explicit None/NULL should be treated the same as with INSERT, and be taken as NULL.
To reproduce
import mssql_python
def build_connection_string(host: str, port: int, database: str, username: str, password: str) -> str:
return (
f"Server={host},{port};"
f"DATABASE={database};"
f"UID={username};"
f"PWD={password};"
"Encrypt=no;TrustServerCertificate=yes"
)
connection_string = build_connection_string(...)
qualified_table_name = "dbo.test_defaults"
drop_table_query = f"DROP TABLE IF EXISTS {qualified_table_name}; "
create_table_query = f"""
CREATE TABLE {qualified_table_name}
(
id INT NOT NULL,
name VARCHAR(255) NULL DEFAULT 'test',
value INT NULL DEFAULT 42,
insert_by VARCHAR(255) NULL,
)"""
raw_insert_query = f"""
INSERT INTO {qualified_table_name} (id, name, value, insert_by)
VALUES (1, 'first_row', 1, 'raw')
, (2, NULL, 3, 'raw') -- The NULL on name should be respected and not replaced by the default
, (3, NULL, NULL, 'raw') -- Both NULLS should be respected and not replaced by the defaults
"""
raw_insert_query_2 = f"""
INSERT INTO {qualified_table_name} (id, name, insert_by)
VALUES
(4, NULL, 'raw') -- The value is not specified, so it should be replaced by the default 42
"""
raw_insert_query_3 = f"""
INSERT INTO {qualified_table_name} (id, insert_by)
VALUES (5, 'raw') -- Name and Value are not specified so they should take the defaults
"""
select_query =f"SELECT * FROM {qualified_table_name}"
truncate_table_query = f"TRUNCATE TABLE {qualified_table_name}"
"""
Create the table.
Insert the rows using raw SQL.
Read the rows back, and verify that they are correct.
They should respect the defaults and the explicit NULLs.
"""
with mssql_python.connect(connection_string) as conn:
with conn.cursor() as cursor:
cursor.execute(drop_table_query)
cursor.execute(create_table_query)
cursor.execute(raw_insert_query)
cursor.execute(raw_insert_query_2)
cursor.execute(raw_insert_query_3)
cursor.execute(select_query)
initial_rows = cursor.fetchall()
print("Results of raw insert")
for row in initial_rows:
print(row)
"""
Truncate the table, then insert the rows using bulk copy.
Read the rows back, and verify that they are correct.
They should be the same!
"""
with mssql_python.connect(connection_string) as conn:
with conn.cursor() as cursor:
cursor.execute(truncate_table_query)
with mssql_python.connect(connection_string) as conn:
with conn.cursor() as cursor:
# bulk copy seems to require its own connection
cursor.bulkcopy(qualified_table_name, initial_rows, timeout=90)
with mssql_python.connect(connection_string) as conn:
with conn.cursor() as cursor:
cursor.execute(select_query)
new_rows = cursor.fetchall()
print("Results of bulk copy")
for row in new_rows:
print(row)
"""
Cleanup: Drop the table.
"""
with mssql_python.connect(connection_string) as conn:
with conn.cursor() as cursor:
cursor.execute(drop_table_query)
print("Comparison:")
for old_row, new_row in zip(initial_rows, new_rows):
equal = old_row == new_row
print(f"{equal=} {old_row=} {new_row=}")Results of raw insert
(1, 'first_row', 1, 'raw')
(2, None, 3, 'raw')
(3, None, None, 'raw')
(4, None, 42, 'raw')
(5, 'test', 42, 'raw')
Results of bulk copy
(1, 'first_row', 1, 'raw')
(2, 'test', 3, 'raw')
(3, 'test', 42, 'raw')
(4, 'test', 42, 'raw')
(5, 'test', 42, 'raw')
Comparison:
equal=True old_row=(1, 'first_row', 1, 'raw') new_row=(1, 'first_row', 1, 'raw')
equal=False old_row=(2, None, 3, 'raw') new_row=(2, 'test', 3, 'raw')
equal=False old_row=(3, None, None, 'raw') new_row=(3, 'test', 42, 'raw')
equal=False old_row=(4, None, 42, 'raw') new_row=(4, 'test', 42, 'raw')
equal=True old_row=(5, 'test', 42, 'raw') new_row=(5, 'test', 42, 'raw')
Expected behavior
bulkcopy should treat None/NULL in the same way that INSERT does.
Further technical details
Python version: 3.12
SQL Server version: Azure SQL Database
Operating system: Windows 11
mssql_python: 1.17.0
Metadata
Metadata
Assignees
Labels
area: bulk-copyIssues in cursor.bulkcopy()Issues in cursor.bulkcopy()discussiontriage doneIssues that are triaged by dev team and are in investigation.Issues that are triaged by dev team and are in investigation.
Activity
github-actions commentedon Nov 12, 2026
Hi Jonathan Schuba (@jschuba), thank you for opening this issue!
Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.
If you have additional information to share, please feel free to update the issue.
Thank you for your patience!
jahnvi480 commentedon Nov 13, 2026
Hi Jonathan Schuba (@jschuba) , thanks for the detailed repro!
bulkcopydefaults tokeep_nulls=False, which allows column defaults to replace incomingNonevalues. Unlike regularINSERT, this follows SQL Server’s bulk-import behavior.To preserve explicit
NULLs, please passkeep_nulls=True, which is available in version 1.17.0:Could you retry your repro with this option and let us know whether it produces the expected results?
jschuba commentedon Nov 16, 2026
Thank you! I added
keep_nulls=Trueand I get the behavior I expected.