0
# Exception Handling
1
2
DB-API 2.0 compliant exception hierarchy with SQL Server-specific error information including error codes, severity levels, and detailed error messages. Provides comprehensive error handling and debugging capabilities for both high-level and low-level interfaces.
3
4
## Capabilities
5
6
### Exception Hierarchy
7
8
Complete DB-API 2.0 compliant exception structure with SQL Server-specific extensions.
9
10
```python { .api }
11
# Base exception classes
12
class Warning(Exception):
13
"""
14
Raised for important warnings like data truncations.
15
16
Example: Data truncation during INSERT operations
17
"""
18
19
class Error(Exception):
20
"""
21
Base class for all database errors.
22
Use this to catch all database-related errors with one except statement.
23
"""
24
25
# Interface-related errors
26
class InterfaceError(Error):
27
"""
28
Raised for errors related to the database interface rather than the database itself.
29
30
Example: Invalid parameter types, connection issues
31
"""
32
33
class ColumnsWithoutNamesError(InterfaceError):
34
"""
35
Raised when as_dict=True is used but query has columns without names.
36
37
Attributes:
38
- columns_without_names (list): List of unnamed column indices
39
"""
40
41
def __init__(self, columns_without_names: list): ...
42
def __str__(self) -> str: ...
43
44
# Database-related errors
45
class DatabaseError(Error):
46
"""
47
Base class for errors related to the database itself.
48
"""
49
50
class DataError(DatabaseError):
51
"""
52
Raised for errors due to problems with processed data.
53
54
Examples: Division by zero, numeric value out of range,
55
invalid date format, data type conversion errors
56
"""
57
58
class OperationalError(DatabaseError):
59
"""
60
Raised for errors related to database operation, not necessarily
61
under programmer control.
62
63
Examples: Unexpected disconnect, data source not found,
64
transaction processing failure, memory allocation error
65
"""
66
67
class IntegrityError(DatabaseError):
68
"""
69
Raised when relational integrity of database is affected.
70
71
Examples: Foreign key constraint violations, unique constraint violations,
72
check constraint failures
73
"""
74
75
class InternalError(DatabaseError):
76
"""
77
Raised when database encounters an internal error.
78
79
Examples: Cursor no longer valid, transaction out of sync,
80
internal database corruption
81
"""
82
83
class ProgrammingError(DatabaseError):
84
"""
85
Raised for programming errors.
86
87
Examples: Table not found, syntax error in SQL statement,
88
wrong number of parameters, invalid object names
89
"""
90
91
class NotSupportedError(DatabaseError):
92
"""
93
Raised when unsupported method or database API is used.
94
95
Examples: Requesting rollback on connection that doesn't support transactions,
96
using unsupported SQL features
97
"""
98
```
99
100
### Low-Level Exceptions
101
102
Exceptions specific to the `_mssql` low-level interface.
103
104
```python { .api }
105
class MSSQLException(Exception):
106
"""Base exception class for _mssql module."""
107
108
class MSSQLDriverException(MSSQLException):
109
"""
110
Raised for problems within _mssql driver.
111
112
Examples: Insufficient memory for data structures,
113
internal driver errors, resource allocation failures
114
"""
115
116
class MSSQLDatabaseException(MSSQLException):
117
"""
118
Raised for database-related problems in _mssql.
119
120
Attributes:
121
- number (int): SQL Server error number
122
- severity (int): Error severity level (0-25)
123
- state (int): Error state code
124
- message (str): Error message from SQL Server
125
"""
126
127
number: int # SQL Server error number
128
severity: int # Error severity level
129
state: int # Error state code
130
message: str # Error message
131
```
132
133
## Usage Examples
134
135
### Basic Exception Handling
136
137
```python
138
import pymssql
139
from pymssql.exceptions import *
140
141
try:
142
conn = pymssql.connect('server', 'user', 'password', 'database')
143
cursor = conn.cursor()
144
145
# This might raise various exceptions
146
cursor.execute("INSERT INTO users (id, name) VALUES (%s, %s)", (1, 'John'))
147
conn.commit()
148
149
except InterfaceError as e:
150
print(f"Interface error: {e}")
151
# Handle connection or parameter issues
152
153
except ProgrammingError as e:
154
print(f"Programming error: {e}")
155
# Handle SQL syntax errors, missing tables, etc.
156
157
except IntegrityError as e:
158
print(f"Integrity error: {e}")
159
# Handle constraint violations
160
161
except OperationalError as e:
162
print(f"Operational error: {e}")
163
# Handle connection drops, timeouts, etc.
164
165
except DatabaseError as e:
166
print(f"Database error: {e}")
167
# Handle other database-related errors
168
169
except Error as e:
170
print(f"General database error: {e}")
171
# Catch-all for any database error
172
173
finally:
174
if 'conn' in locals():
175
conn.close()
176
```
177
178
### Specific Error Handling
179
180
```python
181
import pymssql
182
from pymssql.exceptions import *
183
184
def insert_user(name, email):
185
conn = None
186
try:
187
conn = pymssql.connect('server', 'user', 'pass', 'db')
188
cursor = conn.cursor()
189
190
cursor.execute(
191
"INSERT INTO users (name, email) VALUES (%s, %s)",
192
(name, email)
193
)
194
conn.commit()
195
return True
196
197
except IntegrityError as e:
198
# Handle constraint violations
199
if "UNIQUE KEY constraint" in str(e):
200
print(f"Email {email} already exists")
201
elif "FOREIGN KEY constraint" in str(e):
202
print("Invalid reference in user data")
203
else:
204
print(f"Data integrity error: {e}")
205
return False
206
207
except ProgrammingError as e:
208
# Handle SQL errors
209
if "Invalid object name" in str(e):
210
print("Users table does not exist")
211
elif "Invalid column name" in str(e):
212
print("Invalid column referenced in query")
213
else:
214
print(f"SQL programming error: {e}")
215
return False
216
217
except OperationalError as e:
218
# Handle connection/operational issues
219
print(f"Database operation failed: {e}")
220
return False
221
222
finally:
223
if conn:
224
conn.close()
225
```
226
227
### Low-Level Exception Handling
228
229
```python
230
from pymssql import _mssql
231
from pymssql._mssql import *
232
233
try:
234
conn = _mssql.connect('server', 'user', 'password', 'database')
235
236
# Execute query that might fail
237
conn.execute_query("SELECT * FROM nonexistent_table")
238
239
except MSSQLDatabaseException as e:
240
print(f"SQL Server Error {e.number}: {e.message}")
241
print(f"Severity: {e.severity}, State: {e.state}")
242
243
# Handle specific SQL Server error codes
244
if e.number == 208: # Invalid object name
245
print("Table or view does not exist")
246
elif e.number == 207: # Invalid column name
247
print("Column does not exist")
248
elif e.number == 102: # Syntax error
249
print("SQL syntax error")
250
251
except MSSQLDriverException as e:
252
print(f"Driver error: {e}")
253
# Handle driver-level issues
254
255
except MSSQLException as e:
256
print(f"General _mssql error: {e}")
257
258
finally:
259
if 'conn' in locals():
260
conn.close()
261
```
262
263
### Transaction Error Handling
264
265
```python
266
import pymssql
267
from pymssql.exceptions import *
268
269
def transfer_funds(from_account, to_account, amount):
270
conn = None
271
try:
272
conn = pymssql.connect('server', 'user', 'pass', 'bank_db')
273
cursor = conn.cursor()
274
275
# Start transaction (implicit)
276
cursor.execute(
277
"UPDATE accounts SET balance = balance - %s WHERE id = %s",
278
(amount, from_account)
279
)
280
281
cursor.execute(
282
"UPDATE accounts SET balance = balance + %s WHERE id = %s",
283
(amount, to_account)
284
)
285
286
# Commit transaction
287
conn.commit()
288
return True
289
290
except IntegrityError as e:
291
# Handle constraint violations (e.g., insufficient funds check)
292
print(f"Transaction violates business rules: {e}")
293
conn.rollback()
294
return False
295
296
except OperationalError as e:
297
# Handle connection issues during transaction
298
print(f"Transaction failed due to operational error: {e}")
299
try:
300
conn.rollback()
301
except:
302
pass # Connection might be dead
303
return False
304
305
except Exception as e:
306
# Rollback on any other error
307
print(f"Transaction failed: {e}")
308
try:
309
conn.rollback()
310
except:
311
pass
312
return False
313
314
finally:
315
if conn:
316
conn.close()
317
```
318
319
### Connection Error Handling
320
321
```python
322
import pymssql
323
from pymssql.exceptions import *
324
import time
325
326
def connect_with_retry(max_retries=3, retry_delay=1):
327
"""Connect with retry logic for handling temporary failures."""
328
329
for attempt in range(max_retries):
330
try:
331
conn = pymssql.connect(
332
server='server',
333
user='user',
334
password='password',
335
database='database',
336
login_timeout=10
337
)
338
return conn
339
340
except InterfaceError as e:
341
# Connection interface issues
342
if attempt < max_retries - 1:
343
print(f"Connection attempt {attempt + 1} failed: {e}")
344
time.sleep(retry_delay)
345
continue
346
else:
347
print(f"Failed to connect after {max_retries} attempts")
348
raise
349
350
except OperationalError as e:
351
# Server not available, network issues, etc.
352
if "timeout" in str(e).lower():
353
if attempt < max_retries - 1:
354
print(f"Connection timeout, retrying... (attempt {attempt + 1})")
355
time.sleep(retry_delay * 2) # Longer delay for timeouts
356
continue
357
raise
358
359
return None
360
```
361
362
### Query Error Recovery
363
364
```python
365
import pymssql
366
from pymssql.exceptions import *
367
368
def execute_with_recovery(cursor, query, params=None):
369
"""Execute query with automatic error recovery."""
370
371
try:
372
cursor.execute(query, params)
373
return True
374
375
except ProgrammingError as e:
376
error_msg = str(e).lower()
377
378
if "invalid object name" in error_msg:
379
# Try to create missing table or suggest alternative
380
print(f"Missing table/view: {e}")
381
return False
382
383
elif "invalid column name" in error_msg:
384
# Try to suggest valid columns
385
print(f"Invalid column: {e}")
386
return False
387
388
elif "syntax error" in error_msg:
389
# Log syntax error for debugging
390
print(f"SQL syntax error: {e}")
391
return False
392
393
else:
394
# Re-raise unknown programming errors
395
raise
396
397
except DataError as e:
398
# Handle data conversion/validation errors
399
print(f"Data error: {e}")
400
401
if "conversion failed" in str(e).lower():
402
print("Check data types and formats")
403
elif "value out of range" in str(e).lower():
404
print("Data value exceeds column limits")
405
406
return False
407
408
except IntegrityError as e:
409
# Handle constraint violations
410
error_msg = str(e).lower()
411
412
if "primary key" in error_msg:
413
print("Primary key constraint violation")
414
elif "foreign key" in error_msg:
415
print("Foreign key constraint violation")
416
elif "unique" in error_msg:
417
print("Unique constraint violation")
418
elif "check" in error_msg:
419
print("Check constraint violation")
420
421
return False
422
```
423
424
### Custom Error Classes
425
426
```python
427
from pymssql.exceptions import DatabaseError, OperationalError
428
429
class ApplicationError(Exception):
430
"""Base exception for application-specific errors."""
431
pass
432
433
class BusinessRuleError(ApplicationError):
434
"""Raised when business logic rules are violated."""
435
436
def __init__(self, rule_name, message):
437
self.rule_name = rule_name
438
super().__init__(message)
439
440
class DataValidationError(ApplicationError):
441
"""Raised when data validation fails."""
442
443
def __init__(self, field_name, value, message):
444
self.field_name = field_name
445
self.value = value
446
super().__init__(message)
447
448
def process_order(order_data):
449
"""Process order with custom error handling."""
450
451
try:
452
# Database operations
453
conn = pymssql.connect('server', 'user', 'pass', 'db')
454
cursor = conn.cursor()
455
456
# Validate business rules
457
if order_data['quantity'] <= 0:
458
raise BusinessRuleError(
459
'positive_quantity',
460
'Order quantity must be positive'
461
)
462
463
# Validate data
464
if not isinstance(order_data['customer_id'], int):
465
raise DataValidationError(
466
'customer_id',
467
order_data['customer_id'],
468
'Customer ID must be an integer'
469
)
470
471
cursor.execute(
472
"INSERT INTO orders (customer_id, quantity) VALUES (%s, %s)",
473
(order_data['customer_id'], order_data['quantity'])
474
)
475
conn.commit()
476
477
except BusinessRuleError as e:
478
print(f"Business rule violation ({e.rule_name}): {e}")
479
return False
480
481
except DataValidationError as e:
482
print(f"Data validation error for {e.field_name} = {e.value}: {e}")
483
return False
484
485
except IntegrityError as e:
486
# Convert database integrity errors to business rule errors
487
if "customer_id" in str(e):
488
raise BusinessRuleError(
489
'valid_customer',
490
'Customer does not exist'
491
)
492
raise
493
494
except DatabaseError as e:
495
print(f"Database error: {e}")
496
return False
497
498
finally:
499
if 'conn' in locals():
500
conn.close()
501
502
return True
503
```
504
505
## Error Code Reference
506
507
### Common SQL Server Error Numbers
508
509
```python
510
# Error categorization for exception handling
511
PROGRAMMING_ERRORS = (
512
102, # Syntax error
513
207, # Invalid column name
514
208, # Invalid object name
515
2812, # Unknown procedure
516
4104 # Multi-part identifier could not be bound
517
)
518
519
INTEGRITY_ERRORS = (
520
515, # NULL insert into NOT NULL column
521
547, # Foreign key constraint violation
522
2601, # Duplicate key in unique index
523
2627, # Unique constraint violation
524
)
525
526
# Usage in error handling
527
def categorize_sql_error(error_number):
528
"""Categorize SQL Server error by number."""
529
530
if error_number in PROGRAMMING_ERRORS:
531
return "Programming Error"
532
elif error_number in INTEGRITY_ERRORS:
533
return "Integrity Error"
534
elif 50000 <= error_number <= 99999:
535
return "User-Defined Error"
536
elif error_number >= 100000:
537
return "System Error"
538
else:
539
return "General Error"
540
```
541
542
## Best Practices
543
544
### Error Logging
545
546
```python
547
import logging
548
import pymssql
549
from pymssql.exceptions import *
550
551
# Configure logging
552
logging.basicConfig(level=logging.ERROR)
553
logger = logging.getLogger(__name__)
554
555
def execute_with_logging(query, params=None):
556
"""Execute query with comprehensive error logging."""
557
558
conn = None
559
try:
560
conn = pymssql.connect('server', 'user', 'pass', 'db')
561
cursor = conn.cursor()
562
563
cursor.execute(query, params)
564
conn.commit()
565
566
except Exception as e:
567
# Log error with context
568
logger.error(
569
"Database operation failed",
570
extra={
571
'query': query,
572
'params': params,
573
'error_type': type(e).__name__,
574
'error_message': str(e)
575
}
576
)
577
raise
578
579
finally:
580
if conn:
581
conn.close()
582
```