0
# Exception Handling
1
2
Complete exception hierarchy mapping PostgreSQL error codes to specific Python exception classes with detailed error information and SQL state codes.
3
4
## Capabilities
5
6
### Base Exception Classes
7
8
Core exception classes providing the foundation for PostgreSQL error handling.
9
10
```python { .api }
11
class Exception(Exception):
12
"""
13
Base class for all py-postgresql exceptions.
14
"""
15
16
@property
17
def code():
18
"""
19
Get PostgreSQL error/state code.
20
21
Returns:
22
str: PostgreSQL SQLSTATE code (5 characters)
23
"""
24
25
@property
26
def message():
27
"""
28
Get error message.
29
30
Returns:
31
str: Descriptive error message
32
"""
33
34
class LoadError(Exception):
35
"""
36
Raised when library loading fails (C extensions, shared libraries).
37
"""
38
39
class Disconnection(Exception):
40
"""
41
Raised when connection is unexpectedly terminated.
42
"""
43
```
44
45
### Core Error Categories
46
47
Primary error categories matching PostgreSQL error classes.
48
49
```python { .api }
50
class Error(Exception):
51
"""
52
Base class for all database operation errors.
53
"""
54
55
class DriverError(Error):
56
"""
57
Errors originating from the driver implementation rather than PostgreSQL.
58
"""
59
60
class ConnectionError(Error):
61
"""
62
Errors related to database connection establishment or maintenance.
63
"""
64
65
class TransactionError(Error):
66
"""
67
Errors related to transaction management and control.
68
"""
69
70
class QueryError(Error):
71
"""
72
Errors related to query execution and statement processing.
73
"""
74
75
class AuthenticationSpecificationError(Error):
76
"""
77
Errors in authentication specification or credentials.
78
"""
79
```
80
81
### SQL State Error Classes
82
83
Specific error classes mapped to PostgreSQL SQL state codes for precise error handling.
84
85
```python { .api }
86
# Class 08 - Connection Exception
87
class SEARVError(Error):
88
"""Connection exceptions (SQL state class 08)."""
89
90
# Class 23 - Integrity Constraint Violation
91
class ICVError(Error):
92
"""Integrity constraint violations (SQL state class 23)."""
93
94
# Class 22 - Data Exception
95
class DataError(Error):
96
"""Data exceptions (SQL state class 22)."""
97
98
# Class XX - Internal Error
99
class InternalError(Error):
100
"""Internal PostgreSQL errors (SQL state class XX)."""
101
102
# Class 42 - Syntax Error or Access Rule Violation
103
class ProgrammingError(Error):
104
"""Programming errors - syntax errors, access violations (SQL state class 42)."""
105
106
# Class P0 - PL/pgSQL Error
107
class PLPGSQLError(Error):
108
"""PL/pgSQL procedure errors (SQL state class P0)."""
109
110
# Class 53 - Insufficient Resources
111
class InsufficientResourcesError(Error):
112
"""System resource errors (SQL state class 53)."""
113
114
# Class 54 - Program Limit Exceeded
115
class ProgramLimitExceededError(Error):
116
"""Program limit exceeded errors (SQL state class 54)."""
117
118
# Class 55 - Object Not In Prerequisite State
119
class ObjectNotInPrerequisiteStateError(Error):
120
"""Object state errors (SQL state class 55)."""
121
122
# Class 57 - Operator Intervention
123
class OperatorInterventionError(Error):
124
"""Operator intervention errors (SQL state class 57)."""
125
126
# Class 58 - System Error
127
class SystemError(Error):
128
"""System errors (SQL state class 58)."""
129
```
130
131
### Warning Categories
132
133
Warning classes for non-fatal conditions that may require attention.
134
135
```python { .api }
136
class Warning(Exception):
137
"""
138
Base class for all warnings.
139
"""
140
141
class DriverWarning(Warning):
142
"""
143
Warnings from driver implementation.
144
"""
145
146
class DeprecationWarning(Warning):
147
"""
148
Warnings about deprecated functionality.
149
"""
150
151
class OptimizationWarning(Warning):
152
"""
153
Warnings about performance or optimization issues.
154
"""
155
156
class SecurityWarning(Warning):
157
"""
158
Warnings about security-related issues.
159
"""
160
161
class UnsupportedWarning(Warning):
162
"""
163
Warnings about unsupported operations or features.
164
"""
165
```
166
167
### Exception Lookup Functions
168
169
Functions for dynamically resolving exception classes based on PostgreSQL error codes.
170
171
```python { .api }
172
def ErrorLookup(state_code):
173
"""
174
Get appropriate exception class for PostgreSQL SQL state code.
175
176
Parameters:
177
- state_code (str): 5-character PostgreSQL SQLSTATE code
178
179
Returns:
180
type: Exception class corresponding to the error code
181
182
Example:
183
- ErrorLookup('23505') returns ICVError (unique violation)
184
- ErrorLookup('42P01') returns ProgrammingError (undefined table)
185
"""
186
187
def WarningLookup(state_code):
188
"""
189
Get appropriate warning class for PostgreSQL SQL state code.
190
191
Parameters:
192
- state_code (str): 5-character PostgreSQL SQLSTATE code
193
194
Returns:
195
type: Warning class corresponding to the warning code
196
"""
197
```
198
199
## Usage Examples
200
201
### Basic Exception Handling
202
203
```python
204
import postgresql
205
import postgresql.exceptions as pg_exc
206
207
try:
208
db = postgresql.open('pq://user:wrongpass@localhost/mydb')
209
except pg_exc.AuthenticationSpecificationError as e:
210
print(f"Authentication failed: {e}")
211
print(f"Error code: {e.code}")
212
213
except pg_exc.ConnectionError as e:
214
print(f"Connection failed: {e}")
215
print(f"Error code: {e.code}")
216
217
except pg_exc.Error as e:
218
print(f"Database error: {e}")
219
print(f"Error code: {e.code}")
220
```
221
222
### Query Exception Handling
223
224
```python
225
import postgresql
226
import postgresql.exceptions as pg_exc
227
228
db = postgresql.open('pq://user:pass@localhost/mydb')
229
230
try:
231
# This may raise various exceptions
232
stmt = db.prepare("SELECT * FROM nonexistent_table WHERE id = $1")
233
result = stmt.first(123)
234
235
except pg_exc.ProgrammingError as e:
236
print(f"SQL programming error: {e}")
237
if e.code == '42P01': # undefined_table
238
print("Table does not exist")
239
elif e.code == '42703': # undefined_column
240
print("Column does not exist")
241
else:
242
print(f"Other programming error: {e.code}")
243
244
except pg_exc.DataError as e:
245
print(f"Data error: {e}")
246
if e.code == '22P02': # invalid_text_representation
247
print("Invalid input format for type")
248
elif e.code == '22003': # numeric_value_out_of_range
249
print("Numeric value out of range")
250
else:
251
print(f"Other data error: {e.code}")
252
253
except pg_exc.Error as e:
254
print(f"General database error: {e}")
255
print(f"SQL state: {e.code}")
256
```
257
258
### Transaction Exception Handling
259
260
```python
261
import postgresql
262
import postgresql.exceptions as pg_exc
263
264
db = postgresql.open('pq://user:pass@localhost/mydb')
265
266
try:
267
with db.xact():
268
# Insert user
269
insert_user = db.prepare("INSERT INTO users (email, name) VALUES ($1, $2)")
270
insert_user("john@example.com", "John Doe")
271
272
# Insert duplicate email (assuming unique constraint)
273
insert_user("john@example.com", "Jane Doe") # This will fail
274
275
# This won't be reached due to exception
276
print("Both users inserted successfully")
277
278
except pg_exc.ICVError as e:
279
print(f"Integrity constraint violation: {e}")
280
if e.code == '23505': # unique_violation
281
print("Duplicate value violates unique constraint")
282
elif e.code == '23503': # foreign_key_violation
283
print("Foreign key constraint violation")
284
elif e.code == '23514': # check_violation
285
print("Check constraint violation")
286
else:
287
print(f"Other integrity error: {e.code}")
288
289
except pg_exc.TransactionError as e:
290
print(f"Transaction error: {e}")
291
print(f"Transaction was rolled back")
292
```
293
294
### Dynamic Exception Resolution
295
296
```python
297
import postgresql
298
import postgresql.exceptions as pg_exc
299
300
db = postgresql.open('pq://user:pass@localhost/mydb')
301
302
def handle_database_error(exception):
303
"""Handle database errors based on SQL state code."""
304
305
if hasattr(exception, 'code') and exception.code:
306
# Get specific exception class for this error code
307
error_class = pg_exc.ErrorLookup(exception.code)
308
309
print(f"Error type: {error_class.__name__}")
310
print(f"SQL state: {exception.code}")
311
print(f"Message: {exception}")
312
313
# Handle specific error categories
314
if issubclass(error_class, pg_exc.ICVError):
315
handle_integrity_error(exception)
316
elif issubclass(error_class, pg_exc.ProgrammingError):
317
handle_programming_error(exception)
318
elif issubclass(error_class, pg_exc.DataError):
319
handle_data_error(exception)
320
else:
321
print("General database error")
322
else:
323
print(f"Unknown error: {exception}")
324
325
def handle_integrity_error(e):
326
"""Handle integrity constraint violations."""
327
constraints = {
328
'23505': 'Unique constraint violation - duplicate value',
329
'23503': 'Foreign key constraint violation - referenced record not found',
330
'23514': 'Check constraint violation - invalid value',
331
'23502': 'Not null constraint violation - required field is null'
332
}
333
print(f"Integrity issue: {constraints.get(e.code, 'Unknown constraint violation')}")
334
335
def handle_programming_error(e):
336
"""Handle SQL programming errors."""
337
errors = {
338
'42P01': 'Table does not exist',
339
'42703': 'Column does not exist',
340
'42883': 'Function does not exist',
341
'42P07': 'Object already exists',
342
'42601': 'Syntax error'
343
}
344
print(f"Programming issue: {errors.get(e.code, 'SQL programming error')}")
345
346
def handle_data_error(e):
347
"""Handle data processing errors."""
348
errors = {
349
'22P02': 'Invalid input format for type',
350
'22003': 'Numeric value out of range',
351
'22007': 'Invalid datetime format',
352
'22012': 'Division by zero'
353
}
354
print(f"Data issue: {errors.get(e.code, 'Data processing error')}")
355
356
# Use the error handler
357
try:
358
stmt = db.prepare("INSERT INTO users (id, email) VALUES ($1, $2)")
359
stmt(999999999999999999999, "invalid-email") # Will cause data error
360
361
except pg_exc.Error as e:
362
handle_database_error(e)
363
```
364
365
### Exception Hierarchy Usage
366
367
```python
368
import postgresql
369
import postgresql.exceptions as pg_exc
370
371
def robust_database_operation(db, query, *params):
372
"""Execute database operation with comprehensive error handling."""
373
374
try:
375
stmt = db.prepare(query)
376
return stmt(*params)
377
378
except pg_exc.AuthenticationSpecificationError:
379
print("Authentication problem - check credentials")
380
raise
381
382
except pg_exc.ConnectionError:
383
print("Connection problem - check network/server")
384
raise
385
386
except pg_exc.ICVError as e:
387
print(f"Data integrity issue: {e}")
388
# Don't re-raise - handle gracefully
389
return None
390
391
except pg_exc.ProgrammingError as e:
392
print(f"SQL programming error: {e}")
393
# Log and re-raise for developer attention
394
import logging
395
logging.error(f"SQL error in query '{query}': {e}")
396
raise
397
398
except pg_exc.DataError as e:
399
print(f"Data format error: {e}")
400
# Return None for data errors
401
return None
402
403
except pg_exc.TransactionError as e:
404
print(f"Transaction error: {e}")
405
# Transaction errors should be re-raised
406
raise
407
408
except pg_exc.DriverError as e:
409
print(f"Driver error: {e}")
410
# Driver errors are usually fatal
411
raise
412
413
except pg_exc.Error as e:
414
print(f"General database error: {e}")
415
# Catch-all for other database errors
416
raise
417
418
# Usage
419
db = postgresql.open('pq://user:pass@localhost/mydb')
420
421
result = robust_database_operation(
422
db,
423
"SELECT * FROM users WHERE id = $1",
424
123
425
)
426
427
if result:
428
print(f"Found {len(result)} users")
429
else:
430
print("Query failed or returned no results")
431
```
432
433
### Warning Handling
434
435
```python
436
import postgresql
437
import postgresql.exceptions as pg_exc
438
import warnings
439
440
# Configure warning handling
441
warnings.filterwarnings('always', category=pg_exc.DriverWarning)
442
warnings.filterwarnings('always', category=pg_exc.OptimizationWarning)
443
444
db = postgresql.open('pq://user:pass@localhost/mydb')
445
446
# Function that might generate warnings
447
def execute_with_warnings(db, query):
448
try:
449
with warnings.catch_warnings(record=True) as w:
450
warnings.simplefilter("always")
451
452
result = db.query(query)
453
454
# Check for warnings
455
for warning in w:
456
if issubclass(warning.category, pg_exc.DriverWarning):
457
print(f"Driver warning: {warning.message}")
458
elif issubclass(warning.category, pg_exc.OptimizationWarning):
459
print(f"Performance warning: {warning.message}")
460
elif issubclass(warning.category, pg_exc.DeprecationWarning):
461
print(f"Deprecation warning: {warning.message}")
462
else:
463
print(f"Other warning: {warning.message}")
464
465
return result
466
467
except pg_exc.Error as e:
468
print(f"Error executing query: {e}")
469
return None
470
471
# Example usage
472
result = execute_with_warnings(db, "SELECT * FROM large_table LIMIT 1000000")
473
```