0
# pymssql
1

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

4
## Package Information
5

6
- **Package Name**: pymssql
7
- **Language**: Python  
8
- **Installation**: `pip install pymssql`
9
- **DB-API Version**: 2.0
10
- **Thread Safety**: Level 1 (module may be shared, but not connections)
11

12
## Core Imports
13

14
```python
15
import pymssql
16
```
17

18
Low-level interface access:
19

20
```python
21
from pymssql import _mssql
22
```
23

24
Exception handling:
25

26
```python
27
from pymssql.exceptions import *
28
# or
29
from pymssql import InterfaceError, DatabaseError, OperationalError
30
```
31

32
## Basic Usage
33

34
```python
35
import pymssql
36

37
# Create connection
38
conn = pymssql.connect(
39
    server='localhost',
40
    user='sa', 
41
    password='password',
42
    database='testdb'
43
)
44

45
cursor = conn.cursor(as_dict=True)
46

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

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

56
# Call stored procedure
57
cursor.callproc('GetUserStats', (25,))
58
for row in cursor:
59
    print(row)
60

61
conn.close()
62
```
63

64
## Architecture
65

66
pymssql provides two complementary interfaces:
67

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

73
## Capabilities
74

75
### DB-API 2.0 Interface
76

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

79
```python { .api }
80
def connect(
81
    server='.', user=None, password=None, database='', 
82
    timeout=0, login_timeout=60, charset='UTF-8', as_dict=False,
83
    host='', appname=None, port='1433', encryption=None, 
84
    read_only=False, conn_properties=None, autocommit=False, 
85
    tds_version=None, use_datetime2=False, arraysize=1
86
) -> Connection: ...
87

88
class Connection:
89
    def cursor(self, as_dict=None, arraysize=None) -> Cursor: ...
90
    def commit(self) -> None: ...
91
    def rollback(self) -> None: ...
92
    def close(self) -> None: ...
93
    def autocommit(self, status: bool) -> None: ...
94
    def bulk_copy(self, table_name: str, elements, column_ids=None, 
95
                  batch_size=1000, tablock=False, check_constraints=False, 
96
                  fire_triggers=False) -> None: ...
97
```
98

99
[DB-API Interface](./dbapi-interface.md)
100

101
### Low-Level Database Operations
102

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

105
```python { .api }
106
class _mssql.MSSQLConnection:
107
    def execute_query(self, query_string, params=None): ...
108
    def execute_non_query(self, query_string, params=None): ...
109
    def execute_scalar(self, query_string, params=None): ...
110
    def init_procedure(self, name): ...
111
```
112

113
[Low-Level Operations](./low-level-operations.md)
114

115
### Connection Configuration
116

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

119
```python { .api }
120
def connect(
121
    server='.', user=None, password=None, database='',
122
    timeout=0, login_timeout=60, charset='UTF-8', as_dict=False,
123
    host='', appname=None, port='1433', encryption=None, 
124
    read_only=False, conn_properties=None, autocommit=False, 
125
    tds_version=None, use_datetime2=False, arraysize=1
126
) -> Connection: ...
127
```
128

129
[Connection Configuration](./connection-config.md)
130

131
### Exception Handling
132

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

135
```python { .api }
136
class Error(Exception): ...
137
class DatabaseError(Error): ...
138
class OperationalError(DatabaseError): ...
139
class ProgrammingError(DatabaseError): ...
140
class IntegrityError(DatabaseError): ...
141
```
142

143
[Exception Handling](./exceptions.md)
144

145
## Module Constants
146

147
```python { .api }
148
# DB-API compliance constants
149
apilevel = '2.0'
150
threadsafety = 1
151
paramstyle = 'pyformat'
152

153
# Version information
154
__version__: str
155
__full_version__: str
156
VERSION: tuple
157

158
# Data type constants
159
STRING: DBAPIType
160
BINARY: DBAPIType  
161
NUMBER: DBAPIType
162
DATETIME: DBAPIType
163
DECIMAL: DBAPIType
164

165
# Type constructors
166
Date = datetime.date
167
Time = datetime.time
168
Timestamp = datetime.datetime
169
Binary = bytearray
170
DateFromTicks: callable
171
TimeFromTicks: callable  
172
TimestampFromTicks: callable
173
```
174

175
## Global Configuration
176

177
```python { .api }
178
def get_max_connections() -> int: ...
179
def set_max_connections(number: int) -> None: ...
180
def get_dbversion() -> str: ...
181
def set_wait_callback(callback) -> None: ...
182
def version_info() -> str: ...
183

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

187
# Output parameter class for stored procedures
188
class output:
189
    def __init__(self, param_type, value=None): ...
190
    @property
191
    def type(self): ...
192
    @property 
193
    def value(self): ...
194
```