Python Database APIs · microsoft/mssql-python Wiki · GitHub
Skip to content

Python Database APIs

Jump to bottom
Jahnvi Thakkar edited this page Apr 10, 2026 · 9 revisions

Following is the consolidated, comprehensive table combining all information about the mssql-python module, covering global attributes, connection management, cursor operations, row handling, exception classes, transaction management, and stored procedure execution:

Global Module APIs & Attributes

API / Attribute Type Description
connect(conn_str, autocommit=True, native_uuid=True) Function Creates a Connection object to SQL Server. Per-connection UUID format override via native_uuid.
apilevel Module attribute DB API spec level supported ("2.0").
paramstyle Module attribute Default parameter style ("pyformat"). Supports both pyformat (%(name)s) and qmark (?) with automatic detection.
threadsafety Module attribute Thread sharing capability (1: share module, not connections).
lowercase Setting Converts query column names to lowercase when True.
decimal_separator Setting Character used for parsing DECIMAL/NUMERIC (default ".").
native_uuid Setting If True (default), returns UUID as uuid.UUID; if False, returns uppercase strings.
get_settings() Function Returns global driver settings object.
setDecimalSeparator(separator) Function Sets decimal separator globally.
getDecimalSeparator() Function Gets current decimal separator.
pooling(max_size, idle_timeout, enabled) Function Configures client-side connection pooling.
all Module attribute Explicit list of public symbols for static type checker compatibility.

Connection Class

API / Attribute Type Description
cursor() Method Creates and returns a Cursor.
execute(sql, *args) Method Executes SQL with a temporary cursor; returns the cursor.
batch_execute(statements, params, ...) Method Efficient batch execution.
commit() Method Commits current transaction.
rollback() Method Rolls back current transaction.
close() Method Closes the connection and frees resources.
closed Property Read-only. Returns True if close() was called, False otherwise.
autocommit Property True when autocommit mode is enabled.
setautocommit(bool) Method Enables/disables autocommit.
setencoding(encoding, ctype) Method Sets encoding for outgoing SQL.
setdecoding(sqltype, encoding, ctype) Method Sets decoding for incoming text for SQL type.
add_output_converter(sqltype, func) Method Registers custom output converter.
get_output_converter(sqltype) Method Retrieves registered converter or None.
remove_output_converter(sqltype) Method Unregisters converter.
clear_output_converters() Method Removes all converters.
timeout Property SQL execution timeout in seconds (0 = disabled).
searchescape Property Escape char for LIKE patterns.
Context manager (with) Feature Ensures cleanup + correct commit/rollback.
Exception exposure Feature All DB API exceptions available via connection.
Connection Pooling Feature Built-in client-side pooling.
getinfo() Method Driver/Database metadata.
getencoding() Method Current text encoding settings
getdecoding() Method Returns decoding settings for specified SQL type.
set_attr() Method Sets connection attributes.

Cursor Class

API Type Description
execute(operation, parameters=None, use_prepare=False) Method Executes a SQL statement; supports prepared execution.
executemany(operation, seq_of_parameters) Method Executes repeatedly for multiple parameter sets.
fetchone() Method Fetches next row or None.
fetchmany(size=None) Method Fetches batch of rows.
fetchall() Method Fetches all remaining rows.
fetchval() Method Returns first column of first row.
arrow() Method Fetches all remaining rows as a PyArrow Table.
arrow_batch(size=None) Method Fetches rows as a PyArrow RecordBatch.
arrow_reader() Method Returns a PyArrow RecordBatchReader for streaming.
nextset() Method Moves to next result set.
next() Method Advances and returns next row (iteration support).
skip(count) Method Skips N rows.
scroll(value, mode='relative') Method Moves cursor position.
setinputsizes(sizes) Method Parameter type hints for performance.
close() Method Closes cursor.
description Attribute Column metadata (name, type, etc.).
rowcount Attribute Rows affected/returned; -1 if unknown.
arraysize Attribute Batch size for fetchmany().
rownumber Attribute Current row index.
messages Attribute SQL Server PRINT/log messages.
connection Attribute Parent connection.
Streaming support Feature Efficient streaming of large varchar(max), nvarchar(max), varbinary(max).

Schema Discovery Functions (Cursor)

API / Name Type / Feature Description
tables(...) Method (Cursor) Returns metadata on tables matching criteria.
columns(...) Method (Cursor) Returns metadata on columns belonging to specified tables.
statistics(...) Method (Cursor) Returns indexes and statistics info for tables.
rowIdColumns(...) Method (Cursor) Returns columns uniquely identifying rows.
rowVerColumns(...) Method (Cursor) Returns columns automatically updated on row changes.
primaryKeys(...) Method (Cursor) Returns primary key columns of a table.
foreignKeys(...) Method (Cursor) Returns foreign key info of tables.
procedures(...) Method (Cursor) Returns stored procedure metadata.
getTypeInfo(...) Method (Cursor) Returns detailed info about supported SQL types.
Context Manager Support Feature Cursors support with statement for automatic close (transaction not controlled by cursor).

Row Object

API / Name Type / Feature Description
Row object Object Represents a single row; supports column access by name or index; iterable. Directly importable: from mssql_python import Row.
cursor_description Attribute (Row) Copy of Cursor.description from the cursor that created the row.
lowercase Attribute Case-insensitive column access when global lowercase=True

Exception Classes

API / Name Type / Feature Description
Warning Exception class General database warnings.
Error Base Exception class Base class for all database exceptions.
InterfaceError Exception class DB API interface related errors.
DatabaseError Exception class Database related errors.
DataError Exception class Data-specific errors (conversion, constraints).
OperationalError Exception class Operational failures (disconnects, timeouts).
IntegrityError Exception class Integrity constraints violations.
InternalError Exception class Internal driver or DB errors.
ProgrammingError Exception class Programming errors or SQL syntax issues.
NotSupportedError Exception class Unsupported feature or request.
ConnectionStringParseError Exception class Raised when parsing the connection string fails.
Error Code Mapping Mechanism Maps SQLSTATE codes to matching exceptions.

Database Transaction Management

API / Name Type / Feature Description
Database Transaction Concept Unit of SQL work executed atomically.
Autocommit Mode Feature When enabled, each statement commits automatically.
Manual Transaction Management Feature (default) Explicit commit / rollback required.
conn.setautocommit(True/False) Method Enables or disables autocommit mode.
conn.commit() / cursor.commit() Method Commits all pending changes.
conn.rollback() / cursor.rollback() Method Rolls back all pending changes.
Implicit Transaction Start Behavior New transaction starts automatically on connection or after commit/rollback.
No Explicit BEGIN Needed Behavior No need for BEGIN TRANSACTION; managed implicitly.
Cursors Do NOT Control Transactions Principle All cursors share the same transaction on a connection.
Concurrent Transactions Pattern Use separate connections for independent transactions.
Automatic Rollback on Close Behavior Closing a connection with uncommitted work triggers rollback.
Connection Context Manager Feature Using with connection: auto-commits on success or rollbacks on exception.

Stored Procedure Execution

API / Name Type / Feature Description
callproc() Not supported callproc() not implemented; use alternatives below.
{CALL ...} / execute() Usage Use ODBC escape syntax in cursor.execute().
Calling without parameters Example cursor.execute("{CALL usp_NoParameters}")
Calling with input parameters Example cursor.execute("{CALL usp_UpdateName (?,?)}", (42, "Arthur"))
Output parameters & return values Pattern Use DECLARE + EXEC with OUTPUT → SELECT; handle via nextset().
Multiple result sets Behavior Retrieve via fetchall() + nextset() sequence.
Example output parameter retrieval Example DECLARE var, EXEC with OUTPUT, SELECT var, fetch value.
Example return value retrieval Example DECLARE return_value, EXEC proc, SELECT return_value, fetchval().

Clone this wiki locally