0
# DB-API Interface
1
2
Standard Python DB-API 2.0 compliant interface providing Connection and Cursor objects with full transaction support, parameter binding, and result set iteration. This is the recommended interface for most Python applications accessing SQL Server.
3
4
## Capabilities
5
6
### Connection Management
7
8
Creates and manages database connections with comprehensive parameter support and context manager integration.
9
10
```python { .api }
11
def connect(
12
server='.',
13
user=None,
14
password=None,
15
database='',
16
timeout=0,
17
login_timeout=60,
18
charset='UTF-8',
19
as_dict=False,
20
host='',
21
appname=None,
22
port='1433',
23
encryption=None,
24
read_only=False,
25
conn_properties=None,
26
autocommit=False,
27
tds_version=None,
28
use_datetime2=False,
29
arraysize=1
30
) -> Connection:
31
"""
32
Create a connection to SQL Server.
33
34
Parameters:
35
- server (str): Database host, default '.'
36
- user (str): Database username
37
- password (str): User password
38
- database (str): Database name to connect to
39
- timeout (int): Query timeout in seconds, 0 = no timeout
40
- login_timeout (int): Connection timeout in seconds, default 60
41
- charset (str): Character set, default 'UTF-8'
42
- as_dict (bool): Return rows as dictionaries instead of tuples
43
- host (str): Database host and instance specification (alias for server)
44
- appname (str): Application name for connection
45
- port (str): TCP port, default '1433'
46
- encryption (str): Encryption mode ('off', 'request', 'require')
47
- read_only (bool): Connect in read-only mode
48
- conn_properties (str|list): SQL queries to run on connection
49
- autocommit (bool): Enable autocommit mode
50
- tds_version (str): TDS protocol version
51
- use_datetime2 (bool): Use DATETIME2 compatibility
52
- arraysize (int): Default cursor arraysize
53
54
Returns:
55
Connection object
56
"""
57
```
58
59
Usage example:
60
61
```python
62
# Basic connection
63
conn = pymssql.connect('localhost', 'sa', 'password', 'mydb')
64
65
# Advanced connection with SSL and custom settings
66
conn = pymssql.connect(
67
server='myserver.database.windows.net',
68
user='username@myserver',
69
password='password',
70
database='mydatabase',
71
as_dict=True,
72
autocommit=False,
73
tds_version='7.3',
74
conn_properties=[
75
'SET TEXTSIZE 2147483647',
76
'SET ARITHABORT ON'
77
]
78
)
79
```
80
81
### Connection Object
82
83
Represents an active database connection with transaction control and cursor creation.
84
85
```python { .api }
86
class Connection:
87
"""Database connection object."""
88
89
# Properties
90
messages: list # Server messages
91
timeout: int # Query timeout in seconds
92
bulk_copy_executemany_page_size: int # Bulk copy page size
93
arraysize: int # Default cursor array size
94
95
def cursor(self, as_dict=None, arraysize=None) -> Cursor:
96
"""
97
Create a cursor object for executing queries.
98
99
Parameters:
100
- as_dict (bool): Override connection's as_dict setting
101
- arraysize (int): Override connection's arraysize setting
102
103
Returns:
104
Cursor object
105
"""
106
107
def commit(self) -> None:
108
"""Commit the current transaction."""
109
110
def rollback(self) -> None:
111
"""Roll back the current transaction."""
112
113
def close(self) -> None:
114
"""Close the connection."""
115
116
def autocommit(self, status: bool) -> None:
117
"""
118
Set autocommit mode.
119
120
Parameters:
121
- status (bool): True to enable autocommit, False to disable
122
"""
123
124
def bulk_copy(
125
self,
126
table_name: str,
127
elements: list,
128
column_ids=None,
129
batch_size=1000,
130
tablock=False,
131
check_constraints=False,
132
fire_triggers=False
133
) -> None:
134
"""
135
Bulk copy data to a table.
136
137
Parameters:
138
- table_name (str): Target table name
139
- elements (list): List of tuples containing row data
140
- column_ids (list): Target column IDs (1-based), default all columns
141
- batch_size (int): Rows per batch, default 1000
142
- tablock (bool): Set TABLOCK hint
143
- check_constraints (bool): Set CHECK_CONSTRAINTS hint
144
- fire_triggers (bool): Set FIRE_TRIGGERS hint
145
"""
146
147
def __enter__(self) -> 'Connection':
148
"""Context manager entry."""
149
150
def __exit__(self, exc_type, exc_value, traceback) -> None:
151
"""Context manager exit with automatic cleanup."""
152
```
153
154
### Cursor Object
155
156
Executes SQL statements and fetches results with support for parameter binding and multiple result sets.
157
158
```python { .api }
159
class Cursor:
160
"""Database cursor for executing queries."""
161
162
# Properties
163
connection: Connection # Parent connection
164
description: tuple # Column descriptions
165
rowcount: int # Rows affected by last operation
166
rownumber: int # Current row position (0-based)
167
lastrowid: int # Last inserted identity value
168
returnvalue: int # Stored procedure return value
169
arraysize: int # Fetch array size
170
171
def execute(self, operation: str, params=None) -> None:
172
"""
173
Execute a SQL statement.
174
175
Parameters:
176
- operation (str): SQL statement with %s or %(name)s placeholders
177
- params: Parameter values (tuple, dict, or single value)
178
"""
179
180
def executemany(self, operation: str, seq_of_parameters, *, batch_size=-1) -> None:
181
"""
182
Execute SQL statement multiple times.
183
184
Parameters:
185
- operation (str): SQL statement
186
- seq_of_parameters: Sequence of parameter sets
187
- batch_size (int): Batch size, -1 uses arraysize
188
"""
189
190
def fetchone(self):
191
"""
192
Fetch next row.
193
194
Returns:
195
Row as tuple or dict (based on as_dict), None if no more rows
196
"""
197
198
def fetchmany(self, size=None):
199
"""
200
Fetch multiple rows.
201
202
Parameters:
203
- size (int): Number of rows to fetch, None uses arraysize
204
205
Returns:
206
List of rows as tuples or dicts
207
"""
208
209
def fetchall(self):
210
"""
211
Fetch all remaining rows.
212
213
Returns:
214
List of rows as tuples or dicts
215
"""
216
217
def nextset(self) -> bool:
218
"""
219
Move to next result set.
220
221
Returns:
222
True if next result set available, None otherwise
223
"""
224
225
def callproc(self, procname: str, parameters=()) -> tuple:
226
"""
227
Call a stored procedure.
228
229
Parameters:
230
- procname (str): Stored procedure name
231
- parameters (tuple): Input parameters
232
233
Returns:
234
Tuple of parameter values (input and output)
235
"""
236
237
def close(self) -> None:
238
"""Close the cursor."""
239
240
def setinputsizes(self, sizes=None) -> None:
241
"""Set input parameter sizes (no-op in pymssql)."""
242
243
def setoutputsize(self, size=None, column=0) -> None:
244
"""Set output column size (no-op in pymssql)."""
245
246
def __iter__(self):
247
"""Iterator protocol support."""
248
249
def __next__(self):
250
"""Get next row in iteration."""
251
252
def __enter__(self) -> 'Cursor':
253
"""Context manager entry."""
254
255
def __exit__(self, exc_type, exc_value, traceback) -> None:
256
"""Context manager exit with automatic cleanup."""
257
```
258
259
### Transaction Management
260
261
Example of transaction handling:
262
263
```python
264
conn = pymssql.connect('server', 'user', 'password', 'database')
265
266
try:
267
cursor = conn.cursor()
268
269
# Begin transaction (implicit)
270
cursor.execute("INSERT INTO users (name) VALUES (%s)", ('Alice',))
271
cursor.execute("INSERT INTO orders (user_id, amount) VALUES (%s, %s)", (1, 100.00))
272
273
# Commit transaction
274
conn.commit()
275
print("Transaction committed successfully")
276
277
except Exception as e:
278
# Rollback on error
279
conn.rollback()
280
print(f"Transaction rolled back: {e}")
281
282
finally:
283
conn.close()
284
```
285
286
### Context Manager Usage
287
288
```python
289
# Connection context manager
290
with pymssql.connect('server', 'user', 'password', 'database') as conn:
291
with conn.cursor(as_dict=True) as cursor:
292
cursor.execute("SELECT * FROM users WHERE active = %s", (True,))
293
for row in cursor:
294
print(f"User: {row['name']}")
295
# Cursor automatically closed
296
# Connection automatically closed
297
```
298
299
## Data Types
300
301
### DB-API Type Objects
302
303
```python { .api }
304
class DBAPIType:
305
"""DB-API type object for type comparison."""
306
307
def __init__(self, value: int): ...
308
def __eq__(self, other) -> bool: ...
309
310
# Type constants
311
STRING: DBAPIType # String types
312
BINARY: DBAPIType # Binary types
313
NUMBER: DBAPIType # Numeric types
314
DATETIME: DBAPIType # Date/time types
315
DECIMAL: DBAPIType # Decimal types
316
```
317
318
### Date/Time Support
319
320
```python { .api }
321
# Standard datetime aliases
322
Date = datetime.date
323
Time = datetime.time
324
Timestamp = datetime.datetime
325
326
# Helper object for parameterless queries
327
NoParams: object
328
```