Python Database APIs

Python Database APIs

This 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)	Function	Creates a Connection object to SQL Server.
apilevel	Module attribute	DB API spec level supported ("2.0").
paramstyle	Module attribute	Parameter style ("qmark" = "?").
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 ".").
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.

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

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.
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.
cursor_description	Attribute (Row)	Copy of Cursor.description from the cursor that created the row.

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