Data Type Conversion

Python Parameters Sent to the Database
The following table explains how Python objects passed to Cursor.execute() as parameters are formatted and sent to the driver/database.

Description	Python Datatype	ODBC Datatype
null	None	SQL_UNKNOWN_TYPE
boolean	bool	BIT
integer	int	SQL_TINYINT, SQL_SMALLINT, SQL_INTEGER, SQL_BIGINT
floating point	float	SQL_DOUBLE
decimal	decimal.Decimal	SQL_NUMERIC
string	str	SQL_VARCHAR, SQL_WVARCHAR or SQL_LONGVARCHAR (and corresponding MAX types)
binary	bytes, bytearray	SQL_VARBINARY
date	datetime.date	SQL_TYPE_DATE
time	datetime.time	SQL_TYPE_TIME
timestamp	datetime.datetime	SQL_TYPE_TIMESTAMP
UUID / GUID	uuid.UUID	SQL_GUID

---

Note on NULL parameters (None): When None is passed as a parameter, the driver uses SQL_UNKNOWN_TYPE and calls SQLDescribeParam to determine the actual SQL type of the target column (e.g. SQL_VARBINARY, SQL_INTEGER, etc.). The NULL is then bound with that resolved type. If SQLDescribeParam fails (for example, in a generic SELECT not referencing a table column), the driver falls back to SQL_VARCHAR.

---

Note on Large Text Data (varchar(max) / nvarchar(max)): Large text inputs passed as Python str are mapped to SQL_VARCHAR or SQL_WVARCHAR in the database, consistent with smaller text values. However, when the content size exceeds a certain threshold (e.g., beyond 8000 bytes for varchar or 4000 characters for nvarchar), the driver may use streaming techniques during the execute() operation to efficiently transmit the data without buffering it entirely in memory. For more details on how this streaming is handled, see Streaming Support for Large Text Data.

---

Note on Integer types: TINYINT/SMALLINT/INTEGER/BIGINT are automatically selected based on the value range

---

Note on Description Column:

The "Description" column in the table is intended to provide a generic label for the type, and does not always reflect the exact SQL Server data type name. Please refer to the SQL Server column type for precise behavior and compatibility.

Note on SQL Server's timestamp and rowversion types

• In SQL Server, the timestamp type is a deprecated alias for rowversion, which is a binary type (binary(8)) used for version-stamping table rows. _It does not represent a date or time value.
• The mapping table above may refer to "timestamp" as a generic type label, but this should not be confused with SQL Server's timestamp/rowversion type.
• At this time, rowversion/timestamp columns are not mapped to Python's datetime.datetime type; they should be treated as unsupported or as binary data.
• For actual date/time values, use SQL Server types such as datetime, datetime2, smalldatetime, or date.

Caution

If you need to use SQL Server's rowversion/timestamp, handle these columns as binary data in your Python code. They do not carry any date or time information.

---

SQL Values Received from the Database
The following table describes how database results are converted to Python objects.

Description	ODBC Datatype	Python Datatype
NULL	any	None
bit	SQL_BIT	bool
integers	SQL_TINYINT, SQL_SMALLINT, SQL_INTEGER, SQL_BIGINT	int
floating point	SQL_REAL, SQL_FLOAT, SQL_DOUBLE	float
decimal, numeric	SQL_DECIMAL, SQL_NUMERIC	decimal.Decimal
1-byte text	SQL_CHAR	str via UTF-8
2-byte text	SQL_WCHAR	str via UTF-16LE
binary	SQL_VARBINARY	bytes
date	SQL_TYPE_DATE	datetime.date
time	SQL_TYPE_TIME, SQL_SS_TIME2	datetime.time (with microsecond precision)
timestamp	SQL_TIMESTAMP	datetime.datetime
UUID / GUID	SQL_GUID	uuid.UUID (default) or uppercase string (when native_uuid=False)
datetimeoffset	SQL_SS_TIMESTAMPOFFSET	datetime (with timezone)
sql_variant	SQL_SS_VARIANT (-150)	Underlying base type (see sql_variant below)

---

TIME Column Precision

SQL Server TIME and TIME2 columns support fractional seconds with up to 7 decimal places (100 nanoseconds precision). The mssql-python driver fully preserves microsecond precision for datetime.time values on both insert and select operations.

import datetime
from mssql_python import connect

conn = connect(conn_str)
cursor = conn.cursor()

# TIME values with microseconds are preserved
original_time = datetime.time(14, 30, 45, 123456)  # 14:30:45.123456
cursor.execute("INSERT INTO events (event_time) VALUES (?)", original_time)
cursor.execute("SELECT event_time FROM events")
fetched_time = cursor.fetchone()[0]

print(fetched_time.microsecond)  # 123456 - microseconds are preserved

Note: Python's datetime.time supports microsecond precision (6 decimal places). SQL Server TIME(7) values with sub-microsecond precision will be rounded to the nearest microsecond.

---

UUID / UNIQUEIDENTIFIER Configuration

By default, SQL Server UNIQUEIDENTIFIER columns are returned as Python uuid.UUID objects. This behavior can be configured at the module level or per-connection to return uppercase string representations instead.

Module-level configuration:

import mssql_python

# Check current setting
settings = mssql_python.get_settings()
print(settings.native_uuid)  # True (default)

# Change to return UUIDs as uppercase strings
mssql_python.native_uuid = False

Per-connection configuration:

from mssql_python import connect

# Override for a specific connection
conn = connect(conn_str, native_uuid=False)

cursor = conn.cursor()
cursor.execute("SELECT id FROM items")
row = cursor.fetchone()
print(type(row[0]))  # <class 'str'> - e.g., "A1B2C3D4-E5F6-..."

Setting	UUID Format	Example Value
native_uuid=True (default)	uuid.UUID object	UUID('a1b2c3d4-e5f6-...')
native_uuid=False	Uppercase string	'A1B2C3D4-E5F6-...'

Note: Set native_uuid=False at the module level if you need uppercase string UUIDs, then incrementally adopt uuid.UUID objects on a per-connection basis.

---

sql_variant

SQL Server's sql_variant type can store values of various base data types in a single column. When mssql-python fetches a sql_variant column, it automatically detects the underlying data type of each stored value and converts it to the corresponding Python type.

The mapping follows the same rules as the standard SQL type conversion table above. The supported underlying base types are:

sql_variant Underlying Type	Python Type
INTEGER, SMALLINT, BIGINT, TINYINT	int
REAL, FLOAT, DOUBLE	float
NUMERIC, DECIMAL	decimal.Decimal
BIT	bool
CHAR, VARCHAR	str
NCHAR, NVARCHAR	str
VARBINARY, BINARY	bytes
DATE	datetime.date
TIME, TIME2	datetime.time
DATETIME, DATETIME2	datetime.datetime
UNIQUEIDENTIFIER	uuid.UUID
NULL	None

cursor.execute("SELECT variant_col FROM my_table")
row = cursor.fetchone()

# The Python type reflects the underlying SQL Server type of the stored value
value = row[0]
print(type(value), value)

Note: SQL Server restricts the types that can be stored in a sql_variant column. The following types cannot be stored in sql_variant: text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), rowversion/timestamp, spatial types, hierarchyid, and CLR UDTs.