Skip to content

executemany binds a whole str column as varchar when its longest value is ASCII, so other rows lose non-ASCII text #906

Open

Description

@rgant

Describe the bug

Cursor.executemany gives each parameter column one SQL type, and it takes that type from one sample value. Cursor._compute_column_type picks the longest string of the column as the sample. Cursor._map_sql_type declares a str as SQL_VARCHAR when Cursor._is_unicode_string is false, which means when the sample is ASCII. So if the longest string of a column is ASCII, the whole column binds as varchar. The server then converts every value of that column to the code page of the database collation, and each character outside that code page is replaced:

  • U+2212 MINUS SIGN becomes -
  • Ř becomes R
  • Thai text becomes ????

No exception is raised, so the data changes silently. A plain execute types each value by itself, and it stores the same values exactly.

The page Handle strings and Unicode says: "The mssql-python driver sends every str parameter as UTF-16LE and declares it as nvarchar by default." It also says: "Because the driver declares string parameters as nvarchar, characters reach the server intact regardless of the database collation." executemany does not do that.

The code is in mssql_python/cursor.py at v1.18.0: _compute_column_type ("For string/binary objects, prefer the longer one"), _map_sql_type and _is_unicode_string.

To reproduce

Set MSSQL_CONNECTION_STRING to any database whose collation uses code page 1252, such as SQL_Latin1_General_CP1_CI_AS.

import os
import platform

import mssql_python

conn = mssql_python.connect(os.environ["MSSQL_CONNECTION_STRING"])
cursor = conn.cursor()
cursor.execute("CREATE TABLE #t (id int, txt nvarchar(100))")

rows = [
    (1, "plain ASCII text, the longest value in the column"),
    (2, "a \u2212 b"),  # U+2212 MINUS SIGN
    (3, "\u0158\u00edm"),  # Czech
    (4, "\u0e23\u0e49\u0e32\u0e19"),  # Thai
]
cursor.executemany("INSERT INTO #t (id, txt) VALUES (?, ?)", rows)
for row in rows[1:]:
    cursor.execute("INSERT INTO #t (id, txt) VALUES (?, ?)", (row[0] + 10, row[1]))

cursor.execute("SELECT id, txt FROM #t ORDER BY id")
stored = {r[0]: r[1] for r in cursor.fetchall()}
for row_id, text in rows[1:]:
    print(f"sent {text!r}: executemany stored {stored[row_id]!r}, execute stored {stored[row_id + 10]!r}")

cursor.execute("SELECT CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(32)), CAST(DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS nvarchar(128))")
print("mssql-python", mssql_python.__version__, "| Python", platform.python_version(), "|", platform.platform(), "| server", tuple(cursor.fetchone()))
conn.close()

Output:

sent 'a − b': executemany stored 'a - b', execute stored 'a − b'
sent 'Řím': executemany stored 'Rím', execute stored 'Řím'
sent 'ร้าน': executemany stored '????', execute stored 'ร้าน'

Expected behavior

executemany stores every value as sent, as execute does and as the documentation describes. A str column binds as SQL_WVARCHAR, at least when any value of the column is not ASCII.

Further technical details

Python version: 3.14.9
mssql-python version: 1.18.0, and 1.17.0 behaves the same
SQL Server version: Azure SQL Database, ProductVersion 12.0.2000.8, collation SQL_Latin1_General_CP1_CI_AS
Operating system: macOS 27.2.1, arm64

Additional context

Activity

  1. github-actions commented on Nov 18, 2026

    @github-actions

    Hi J Rob Gant (@rgant), 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!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    triage neededFor new issues, not triaged yet.

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions