0
# Low-Level Operations
1
2
Direct access to MS SQL Server functionality through the `_mssql` module with manual query execution, result handling, and stored procedure support. Provides fine-grained control over database operations and access to SQL Server-specific features not available in the high-level DB-API interface.
3
4
## Capabilities
5
6
### Connection Creation
7
8
Create low-level connections with direct access to SQL Server features.
9
10
```python { .api }
11
def connect(
12
server='.',
13
user=None,
14
password=None,
15
database='',
16
charset='UTF-8',
17
appname=None,
18
port='1433',
19
encryption=None,
20
read_only=False,
21
tds_version=None,
22
conn_properties=None,
23
use_datetime2=False
24
) -> MSSQLConnection:
25
"""
26
Create a low-level connection to SQL Server.
27
28
Parameters:
29
- server (str): Database server and instance
30
- user (str): Database username
31
- password (str): User password
32
- database (str): Database name
33
- charset (str): Character set, default 'UTF-8'
34
- appname (str): Application name
35
- port (str): TCP port, default '1433'
36
- encryption (str): Encryption mode ('off', 'request', 'require')
37
- read_only (bool): Connect in read-only mode
38
- tds_version (str): TDS protocol version
39
- conn_properties (str|list): SQL queries to run on connection
40
- use_datetime2 (bool): Use DATETIME2 compatibility
41
42
Returns:
43
MSSQLConnection object
44
"""
45
```
46
47
### MSSQLConnection Object
48
49
Low-level connection object with direct query execution methods.
50
51
```python { .api }
52
class MSSQLConnection:
53
"""Low-level SQL Server connection."""
54
55
# Properties
56
connected: bool # Connection status
57
charset: str # Character set name
58
identity: int # Last inserted identity value
59
query_timeout: int # Query timeout in seconds
60
rows_affected: int # Rows affected by last query
61
debug_queries: bool # Enable query debugging
62
tds_version: str # TDS version string (e.g., "7.3")
63
tds_version_tuple: tuple # TDS version as tuple (e.g., (7, 3))
64
65
def execute_query(self, query_string: str, params=None) -> None:
66
"""
67
Execute a query that returns results.
68
69
Parameters:
70
- query_string (str): SQL query with %s placeholders
71
- params: Parameter values (tuple, dict, or single value)
72
73
Use with iteration to get results:
74
conn.execute_query("SELECT * FROM users WHERE age > %s", (25,))
75
for row in conn:
76
print(row)
77
"""
78
79
def execute_non_query(self, query_string: str, params=None) -> None:
80
"""
81
Execute a query that doesn't return results (INSERT, UPDATE, DELETE).
82
83
Parameters:
84
- query_string (str): SQL query with %s placeholders
85
- params: Parameter values (tuple, dict, or single value)
86
"""
87
88
def execute_scalar(self, query_string: str, params=None):
89
"""
90
Execute a query and return the first column of the first row.
91
92
Parameters:
93
- query_string (str): SQL query with %s placeholders
94
- params: Parameter values (tuple, dict, or single value)
95
96
Returns:
97
Single value from first column of first row
98
"""
99
100
def execute_row(self, query_string: str, params=None) -> tuple:
101
"""
102
Execute a query and return the first row.
103
104
Parameters:
105
- query_string (str): SQL query with %s placeholders
106
- params: Parameter values (tuple, dict, or single value)
107
108
Returns:
109
First row as tuple
110
"""
111
112
def nextresult(self) -> bool:
113
"""
114
Move to next result set.
115
116
Returns:
117
True if next result set available, None otherwise
118
"""
119
120
def cancel(self) -> None:
121
"""Cancel all pending results."""
122
123
def close(self) -> None:
124
"""Close the connection."""
125
126
def select_db(self, dbname: str) -> None:
127
"""
128
Change the current database.
129
130
Parameters:
131
- dbname (str): Database name to switch to
132
"""
133
134
def get_header(self) -> list:
135
"""
136
Get column header information for current result set.
137
138
Returns:
139
List of 7-element tuples with column descriptions
140
"""
141
142
def init_procedure(self, name: str) -> 'MSSQLStoredProcedure':
143
"""
144
Create a stored procedure object.
145
146
Parameters:
147
- name (str): Stored procedure name
148
149
Returns:
150
MSSQLStoredProcedure object
151
"""
152
153
def set_msghandler(self, handler) -> None:
154
"""
155
Set message handler function for server messages.
156
157
Parameters:
158
- handler: Function with signature (msgstate, severity, srvname, procname, line, msgtext)
159
"""
160
161
def __iter__(self):
162
"""Iterator protocol for result rows."""
163
164
def __next__(self) -> tuple:
165
"""Get next row from current result set."""
166
```
167
168
### Stored Procedure Support
169
170
Advanced stored procedure execution with parameter binding and output parameters.
171
172
```python { .api }
173
class MSSQLStoredProcedure:
174
"""Stored procedure execution object."""
175
176
# Properties
177
connection: MSSQLConnection # Parent connection
178
name: str # Procedure name
179
parameters: list # Bound parameters
180
181
def bind(
182
self,
183
value,
184
dbtype: int,
185
name=None,
186
output=False,
187
null=False,
188
max_length=-1
189
) -> None:
190
"""
191
Bind a parameter to the stored procedure.
192
193
Parameters:
194
- value: Parameter value
195
- dbtype (int): SQL Server data type constant (SQLINT4, SQLVARCHAR, etc.)
196
- name (str): Parameter name in "@name" format
197
- output (bool): True for output parameters
198
- null (bool): True to bind NULL value
199
- max_length (int): Maximum length for output parameters
200
"""
201
202
def execute(self) -> None:
203
"""Execute the stored procedure."""
204
```
205
206
### Result Iteration
207
208
```python { .api }
209
class MSSQLRowIterator:
210
"""Iterator for query results."""
211
212
def __init__(self, connection: MSSQLConnection, row_format: int): ...
213
def __iter__(self): ...
214
def __next__(self) -> tuple: ...
215
216
# Row format constants
217
ROW_FORMAT_TUPLE: int = 1
218
ROW_FORMAT_DICT: int = 2
219
```
220
221
## Usage Examples
222
223
### Basic Query Execution
224
225
```python
226
import _mssql
227
228
# Connect to database
229
conn = _mssql.connect('localhost', 'sa', 'password', 'testdb')
230
231
# Execute query with results
232
conn.execute_query("SELECT name, age FROM users WHERE active = %s", (True,))
233
for row in conn:
234
print(f"Name: {row[0]}, Age: {row[1]}")
235
236
# Execute scalar query
237
count = conn.execute_scalar("SELECT COUNT(*) FROM users")
238
print(f"Total users: {count}")
239
240
# Execute single row query
241
user = conn.execute_row("SELECT * FROM users WHERE id = %s", (1,))
242
print(f"User data: {user}")
243
244
# Execute non-query
245
conn.execute_non_query("INSERT INTO users (name, age) VALUES (%s, %s)", ('Alice', 25))
246
print(f"Rows affected: {conn.rows_affected}")
247
248
conn.close()
249
```
250
251
### Multiple Result Sets
252
253
```python
254
import _mssql
255
256
conn = _mssql.connect('localhost', 'sa', 'password', 'testdb')
257
258
# Execute query that returns multiple result sets
259
conn.execute_query("""
260
SELECT * FROM users;
261
SELECT * FROM orders;
262
""")
263
264
# Process first result set
265
print("Users:")
266
for row in conn:
267
print(row)
268
269
# Move to next result set
270
if conn.nextresult():
271
print("Orders:")
272
for row in conn:
273
print(row)
274
275
conn.close()
276
```
277
278
### Stored Procedure with Parameters
279
280
```python
281
import _mssql
282
283
conn = _mssql.connect('localhost', 'sa', 'password', 'testdb')
284
285
# Create stored procedure object
286
proc = conn.init_procedure('GetUsersByAge')
287
288
# Bind input parameter
289
proc.bind(25, _mssql.SQLINT4, '@MinAge')
290
291
# Bind output parameter
292
proc.bind(0, _mssql.SQLINT4, '@UserCount', output=True)
293
294
# Execute procedure
295
proc.execute()
296
297
# Get results
298
for row in conn:
299
print(f"User: {row}")
300
301
# Check output parameter value
302
print(f"Output parameters: {proc.parameters}")
303
304
conn.close()
305
```
306
307
### Connection Configuration
308
309
```python
310
import _mssql
311
312
# Advanced connection with custom properties
313
conn = _mssql.connect(
314
server='myserver.database.windows.net',
315
user='username@myserver',
316
password='password',
317
database='mydatabase',
318
charset='UTF-8',
319
appname='MyPythonApp',
320
tds_version='7.3',
321
conn_properties=[
322
'SET TEXTSIZE 2147483647',
323
'SET ARITHABORT ON',
324
'SET ANSI_NULLS ON'
325
]
326
)
327
328
# Set query timeout
329
conn.query_timeout = 30
330
331
# Enable query debugging
332
conn.debug_queries = True
333
334
# Execute query
335
conn.execute_query("SELECT @@VERSION")
336
for row in conn:
337
print(f"SQL Server version: {row[0]}")
338
339
conn.close()
340
```
341
342
## SQL Server Data Type Constants
343
344
Constants for stored procedure parameter binding:
345
346
```python { .api }
347
# Integer types
348
SQLBIT: int
349
SQLINT1: int # TINYINT
350
SQLINT2: int # SMALLINT
351
SQLINT4: int # INT
352
SQLINT8: int # BIGINT
353
SQLINTN: int # Variable length integer
354
355
# Floating point types
356
SQLFLT4: int # REAL
357
SQLFLT8: int # FLOAT
358
SQLFLTN: int # Variable length float
359
360
# Decimal types
361
SQLDECIMAL: int # DECIMAL
362
SQLNUMERIC: int # NUMERIC
363
364
# Money types
365
SQLMONEY: int # MONEY
366
SQLMONEY4: int # SMALLMONEY
367
SQLMONEYN: int # Variable length money
368
369
# Character types
370
SQLCHAR: int # CHAR
371
SQLVARCHAR: int # VARCHAR
372
SQLTEXT: int # TEXT
373
374
# Binary types
375
SQLBINARY: int # BINARY
376
SQLVARBINARY: int # VARBINARY
377
SQLIMAGE: int # IMAGE
378
379
# Date/time types
380
SQLDATETIME: int # DATETIME
381
SQLDATETIM4: int # SMALLDATETIME
382
SQLDATETIMN: int # Variable length datetime
383
384
# Other types
385
SQLBIT: int # BIT
386
SQLBITN: int # Variable length bit
387
SQLUUID: int # UNIQUEIDENTIFIER
388
```
389
390
## Utility Functions
391
392
```python { .api }
393
def quote_simple_value(value, use_datetime2=False, charset='utf-8') -> str:
394
"""
395
Quote a single value for SQL insertion.
396
397
Parameters:
398
- value: Value to quote
399
- use_datetime2 (bool): Use DATETIME2 formatting
400
- charset (str): Character encoding
401
402
Returns:
403
Quoted SQL string
404
"""
405
406
def quote_data(data, use_datetime2=False, charset='utf-8') -> str:
407
"""
408
Quote a data structure for SQL insertion.
409
410
Parameters:
411
- data: Data structure to quote (dict, tuple, list)
412
- use_datetime2 (bool): Use DATETIME2 formatting
413
- charset (str): Character encoding
414
415
Returns:
416
Quoted SQL string
417
"""
418
419
def substitute_params(toformat: str, params=None, use_datetime2=False, charset='utf-8') -> str:
420
"""
421
Substitute parameters in SQL string.
422
423
Parameters:
424
- toformat (str): SQL string with %s placeholders
425
- params: Parameter values
426
- use_datetime2 (bool): Use DATETIME2 formatting
427
- charset (str): Character encoding
428
429
Returns:
430
SQL string with substituted parameters
431
"""
432
433
def remove_locale(value: bytes) -> bytes:
434
"""
435
Remove locale-specific formatting from byte string.
436
437
Parameters:
438
- value (bytes): Input byte string
439
440
Returns:
441
Byte string with locale formatting removed
442
"""
443
```