or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

connection-config.mddbapi-interface.mdexceptions.mdindex.mdlow-level-operations.md
tile.json

tessl/pypi-pymssql

DB-API interface to Microsoft SQL Server for Python with comprehensive SQL Server integration and FreeTDS support.

Workspace
tessl
Visibility
Public
Created
Last updated
Describes
pypipkg:pypi/pymssql@2.3.x

To install, run

npx @tessl/cli install tessl/pypi-pymssql@2.3.0

index.mddocs/

pymssql

A Python DB-API 2.0 compliant interface to Microsoft SQL Server that provides comprehensive SQL Server integration with support for stored procedures, transactions, and various data types. Built on top of FreeTDS to enable secure database connectivity from Python applications with high performance through Cython-based implementation, static FreeTDS linking, and platform compatibility across Windows, Linux, and macOS.

Package Information

  • Package Name: pymssql
  • Language: Python
  • Installation: pip install pymssql
  • DB-API Version: 2.0
  • Thread Safety: Level 1 (module may be shared, but not connections)

Core Imports

import pymssql

Low-level interface access:

from pymssql import _mssql

Exception handling:

from pymssql.exceptions import *
# or
from pymssql import InterfaceError, DatabaseError, OperationalError

Basic Usage

import pymssql

# Create connection
conn = pymssql.connect(
    server='localhost',
    user='sa', 
    password='password',
    database='testdb'
)

cursor = conn.cursor(as_dict=True)

# Execute query
cursor.execute('SELECT * FROM users WHERE age > %s', (25,))
for row in cursor:
    print(f"User: {row['name']}, Age: {row['age']}")

# Insert data with transaction
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ('John Doe', 30))
conn.commit()

# Call stored procedure
cursor.callproc('GetUserStats', (25,))
for row in cursor:
    print(row)

conn.close()

Architecture

pymssql provides two complementary interfaces:

  • High-level interface (pymssql): DB-API 2.0 compliant with Connection and Cursor objects for standard database operations, automatic parameter quoting, and transaction management
  • Low-level interface (_mssql): Direct access to MS SQL Server functionality with manual result handling, custom data type support, and stored procedure binding
  • Exception hierarchy: DB-API 2.0 compliant exceptions with SQL Server-specific error details and categorization
  • FreeTDS integration: Static linking with FreeTDS library providing TDS protocol support, SSL/TLS encryption, and Kerberos authentication

Capabilities

DB-API 2.0 Interface

Standard Python database interface providing Connection and Cursor objects with full transaction support, parameter binding, and result set iteration. Includes support for dictionary-style row access and bulk operations.

def connect(
    server='.', user=None, password=None, database='', 
    timeout=0, login_timeout=60, charset='UTF-8', as_dict=False,
    host='', appname=None, port='1433', encryption=None, 
    read_only=False, conn_properties=None, autocommit=False, 
    tds_version=None, use_datetime2=False, arraysize=1
) -> Connection: ...

class Connection:
    def cursor(self, as_dict=None, arraysize=None) -> Cursor: ...
    def commit(self) -> None: ...
    def rollback(self) -> None: ...
    def close(self) -> None: ...
    def autocommit(self, status: bool) -> None: ...
    def bulk_copy(self, table_name: str, elements, column_ids=None, 
                  batch_size=1000, tablock=False, check_constraints=False, 
                  fire_triggers=False) -> None: ...

DB-API Interface

Low-Level Database Operations

Direct access to MS SQL Server functionality with manual query execution, result handling, and stored procedure support. Provides fine-grained control over database operations and access to SQL Server-specific features.

class _mssql.MSSQLConnection:
    def execute_query(self, query_string, params=None): ...
    def execute_non_query(self, query_string, params=None): ...
    def execute_scalar(self, query_string, params=None): ...
    def init_procedure(self, name): ...

Low-Level Operations

Connection Configuration

Comprehensive connection management with support for various authentication methods, SSL/TLS encryption, connection pooling, and SQL Server-specific features like application names and read-only connections.

def connect(
    server='.', user=None, password=None, database='',
    timeout=0, login_timeout=60, charset='UTF-8', as_dict=False,
    host='', appname=None, port='1433', encryption=None, 
    read_only=False, conn_properties=None, autocommit=False, 
    tds_version=None, use_datetime2=False, arraysize=1
) -> Connection: ...

Connection Configuration

Exception Handling

DB-API 2.0 compliant exception hierarchy with SQL Server-specific error information including error codes, severity levels, and detailed error messages for comprehensive error handling and debugging.

class Error(Exception): ...
class DatabaseError(Error): ...
class OperationalError(DatabaseError): ...
class ProgrammingError(DatabaseError): ...
class IntegrityError(DatabaseError): ...

Exception Handling

Module Constants

# DB-API compliance constants
apilevel = '2.0'
threadsafety = 1
paramstyle = 'pyformat'

# Version information
__version__: str
__full_version__: str
VERSION: tuple

# Data type constants
STRING: DBAPIType
BINARY: DBAPIType  
NUMBER: DBAPIType
DATETIME: DBAPIType
DECIMAL: DBAPIType

# Type constructors
Date = datetime.date
Time = datetime.time
Timestamp = datetime.datetime
Binary = bytearray
DateFromTicks: callable
TimeFromTicks: callable  
TimestampFromTicks: callable

Global Configuration

def get_max_connections() -> int: ...
def set_max_connections(number: int) -> None: ...
def get_dbversion() -> str: ...
def set_wait_callback(callback) -> None: ...
def version_info() -> str: ...

# Utility functions
def row2dict(row) -> dict: ...

# Output parameter class for stored procedures
class output:
    def __init__(self, param_type, value=None): ...
    @property
    def type(self): ...
    @property 
    def value(self): ...