0
# Exception Handling
1
2
Comprehensive exception hierarchy following DB-API 2.0 standards plus Vertica-specific error types. Includes automatic error classification based on SQL states, detailed error information with SQL context, and specialized exceptions for common database scenarios.
3
4
## Capabilities
5
6
### DB-API 2.0 Standard Exceptions
7
8
Base exception classes following the Python Database API specification.
9
10
```python { .api }
11
class Error(Exception):
12
"""
13
Base exception class for all database errors.
14
"""
15
16
class Warning(Exception):
17
"""
18
Exception for important warnings like data truncations.
19
"""
20
21
class InterfaceError(Error):
22
"""
23
Error related to the database interface rather than the database itself.
24
Examples: invalid connection parameters, interface misuse.
25
"""
26
27
class DatabaseError(Error):
28
"""
29
Error related to the database.
30
Base class for all database-related errors.
31
"""
32
33
class InternalError(DatabaseError):
34
"""
35
Internal database error that the client cannot resolve.
36
Examples: internal database inconsistencies.
37
"""
38
39
class OperationalError(DatabaseError):
40
"""
41
Error related to database operation and not necessarily under user control.
42
Examples: connection lost, memory allocation errors, database restarts.
43
"""
44
45
class ProgrammingError(DatabaseError):
46
"""
47
Programming error in database usage.
48
Examples: table not found, SQL syntax errors, wrong parameter count.
49
"""
50
51
class IntegrityError(DatabaseError):
52
"""
53
Error when relational integrity of the database is affected.
54
Examples: foreign key constraint violations, duplicate key errors.
55
"""
56
57
class DataError(DatabaseError):
58
"""
59
Error due to problems with processed data.
60
Examples: division by zero, numeric value out of range.
61
"""
62
63
class NotSupportedError(DatabaseError):
64
"""
65
Method or database API not supported by the database.
66
Examples: unsupported SQL features, unsupported operations.
67
"""
68
```
69
70
### Vertica-Specific Extended Exceptions
71
72
Extended exception classes for Vertica-specific error scenarios.
73
74
```python { .api }
75
class TimedOutError(OperationalError):
76
"""
77
Operation timeout error.
78
Raised when database operations exceed timeout limits.
79
"""
80
81
class ConnectionError(DatabaseError):
82
"""
83
Connection-related errors.
84
Base class for all connection-specific errors.
85
"""
86
87
class KerberosError(ConnectionError):
88
"""
89
Kerberos authentication errors.
90
Raised when Kerberos authentication fails.
91
"""
92
93
class SSLNotSupported(ConnectionError):
94
"""
95
SSL/TLS not supported errors.
96
Raised when SSL is requested but not available.
97
"""
98
99
class MessageError(InternalError):
100
"""
101
Database protocol message errors.
102
Raised when there are issues with the communication protocol.
103
"""
104
105
class EmptyQueryError(ProgrammingError):
106
"""
107
Empty query string submitted.
108
Raised when an empty or whitespace-only query is executed.
109
"""
110
```
111
112
### QueryError Class
113
114
Enhanced error class providing detailed SQL context and error information.
115
116
```python { .api }
117
class QueryError(ProgrammingError):
118
"""
119
Query execution error with SQL context and detailed error information.
120
121
This error is the most commonly encountered error type, associated with
122
failures during query execution, invalid SQL statements, and more.
123
"""
124
125
def __init__(self, error_response, sql: str):
126
"""
127
Initialize QueryError with error response and SQL statement.
128
129
Parameters:
130
- error_response: Detailed error response from server
131
- sql (str): SQL statement that caused the error
132
"""
133
134
@property
135
def sql(self) -> str:
136
"""
137
Get the SQL statement that caused the error.
138
139
Returns:
140
str: Original SQL statement
141
"""
142
143
@property
144
def error_response(self):
145
"""
146
Get detailed error response from the server.
147
148
Returns:
149
ErrorResponse: Server error response object with detailed information
150
"""
151
152
def one_line_sql(self) -> str:
153
"""
154
Get single-line version of the SQL statement for compact display.
155
156
Returns:
157
str: SQL statement with newlines replaced by spaces
158
"""
159
160
@classmethod
161
def from_error_response(cls, error_response, sql: str) -> 'QueryError':
162
"""
163
Create appropriate QueryError subclass based on SQL state.
164
165
Parameters:
166
- error_response: Error response from server
167
- sql (str): SQL statement that caused the error
168
169
Returns:
170
QueryError: Appropriate subclass instance based on error type
171
"""
172
173
# Properties from NoticeResponseAttrMixin
174
@property
175
def severity(self) -> str:
176
"""Error severity level."""
177
178
@property
179
def sqlstate(self) -> str:
180
"""SQL state code."""
181
182
@property
183
def message(self) -> str:
184
"""Primary error message."""
185
186
@property
187
def detail(self) -> str:
188
"""Detailed error information."""
189
190
@property
191
def hint(self) -> str:
192
"""Hint for resolving the error."""
193
194
@property
195
def position(self) -> str:
196
"""Character position of error in SQL statement."""
197
198
@property
199
def internal_position(self) -> str:
200
"""Internal character position."""
201
202
@property
203
def internal_query(self) -> str:
204
"""Internal query that caused the error."""
205
206
@property
207
def where(self) -> str:
208
"""Context where error occurred."""
209
210
@property
211
def schema_name(self) -> str:
212
"""Schema name related to error."""
213
214
@property
215
def table_name(self) -> str:
216
"""Table name related to error."""
217
218
@property
219
def column_name(self) -> str:
220
"""Column name related to error."""
221
222
@property
223
def datatype_name(self) -> str:
224
"""Data type name related to error."""
225
226
@property
227
def constraint_name(self) -> str:
228
"""Constraint name related to error."""
229
230
@property
231
def file(self) -> str:
232
"""Source file where error occurred."""
233
234
@property
235
def line(self) -> str:
236
"""Line number where error occurred."""
237
238
@property
239
def routine(self) -> str:
240
"""Routine name where error occurred."""
241
```
242
243
### Specific Query Error Types
244
245
Automatically mapped based on SQL state codes from the server.
246
247
```python { .api }
248
class LockFailure(QueryError):
249
"""
250
Lock acquisition failures (SQL State: 55V03).
251
Raised when unable to acquire required locks.
252
"""
253
254
class InsufficientResources(QueryError):
255
"""
256
Insufficient system resources (SQL State: 53000).
257
Raised when system runs out of resources.
258
"""
259
260
class OutOfMemory(QueryError):
261
"""
262
Out of memory errors (SQL State: 53200).
263
Raised when operations exceed available memory.
264
"""
265
266
class VerticaSyntaxError(QueryError):
267
"""
268
SQL syntax errors (SQL State: 42601).
269
Raised when SQL contains syntax errors.
270
"""
271
272
class MissingSchema(QueryError):
273
"""
274
Schema not found (SQL State: 3F000).
275
Raised when referencing non-existent schema.
276
"""
277
278
class MissingRelation(QueryError):
279
"""
280
Table or view not found (SQL State: 42V01).
281
Raised when referencing non-existent table or view.
282
"""
283
284
class MissingColumn(QueryError):
285
"""
286
Column not found (SQL State: 42703).
287
Raised when referencing non-existent column.
288
"""
289
290
class CopyRejected(QueryError):
291
"""
292
COPY operation data rejected (SQL State: 22V04).
293
Raised when COPY operation rejects input data.
294
"""
295
296
class PermissionDenied(QueryError):
297
"""
298
Insufficient privileges (SQL State: 42501).
299
Raised when user lacks required permissions.
300
"""
301
302
class InvalidDatetimeFormat(QueryError):
303
"""
304
Invalid date/time format (SQL State: 22007).
305
Raised when date/time values have invalid format.
306
"""
307
308
class DuplicateObject(QueryError):
309
"""
310
Duplicate object error (SQL State: 42710).
311
Raised when trying to create objects that already exist.
312
"""
313
314
class QueryCanceled(QueryError):
315
"""
316
Query was canceled (SQL State: 57014).
317
Raised when query execution is canceled.
318
"""
319
320
class ConnectionFailure(QueryError):
321
"""
322
Connection failure during query (SQL State: 08006).
323
Raised when connection is lost during query execution.
324
"""
325
326
class LostConnectivityFailure(QueryError):
327
"""
328
Lost connectivity with server (SQL State: V1001).
329
Raised when client loses connectivity with Vertica server.
330
"""
331
```
332
333
## SQL State to Exception Mapping
334
335
The system automatically maps SQL state codes to appropriate exception classes:
336
337
```python { .api }
338
QUERY_ERROR_CLASSES = {
339
'55V03': LockFailure,
340
'53000': InsufficientResources,
341
'53200': OutOfMemory,
342
'42601': VerticaSyntaxError,
343
'3F000': MissingSchema,
344
'42V01': MissingRelation,
345
'42703': MissingColumn,
346
'22V04': CopyRejected,
347
'42501': PermissionDenied,
348
'22007': InvalidDatetimeFormat,
349
'42710': DuplicateObject,
350
'57014': QueryCanceled,
351
'08006': ConnectionFailure,
352
'V1001': LostConnectivityFailure
353
}
354
```
355
356
## Usage Examples
357
358
### Basic Exception Handling
359
360
```python
361
import vertica_python
362
from vertica_python import Error, DatabaseError, ProgrammingError
363
364
try:
365
with vertica_python.connect(host='localhost', user='dbadmin', database='mydb') as conn:
366
with conn.cursor() as cursor:
367
cursor.execute("SELECT * FROM nonexistent_table")
368
results = cursor.fetchall()
369
370
except vertica_python.MissingRelation as e:
371
print(f"Table not found: {e}")
372
print(f"SQL: {e.sql}")
373
print(f"SQL State: {e.sqlstate}")
374
375
except ProgrammingError as e:
376
print(f"Programming error: {e}")
377
378
except DatabaseError as e:
379
print(f"Database error: {e}")
380
381
except Error as e:
382
print(f"General database error: {e}")
383
```
384
385
### Connection Error Handling
386
387
```python
388
try:
389
conn = vertica_python.connect(
390
host='nonexistent-server.com',
391
user='baduser',
392
password='wrongpassword',
393
database='mydb'
394
)
395
396
except vertica_python.ConnectionError as e:
397
print(f"Connection failed: {e}")
398
399
except vertica_python.KerberosError as e:
400
print(f"Kerberos authentication failed: {e}")
401
402
except vertica_python.SSLNotSupported as e:
403
print(f"SSL not supported: {e}")
404
405
except vertica_python.InterfaceError as e:
406
print(f"Interface error (bad parameters?): {e}")
407
```
408
409
### Detailed Query Error Information
410
411
```python
412
try:
413
with conn.cursor() as cursor:
414
cursor.execute("SELECT * FROM users WHERE invalid_column = 'value'")
415
416
except vertica_python.QueryError as e:
417
print(f"Query failed: {e}")
418
print(f"SQL: {e.sql}")
419
print(f"One-line SQL: {e.one_line_sql()}")
420
print(f"SQL State: {e.sqlstate}")
421
print(f"Severity: {e.severity}")
422
print(f"Message: {e.message}")
423
print(f"Detail: {e.detail}")
424
print(f"Hint: {e.hint}")
425
if e.position:
426
print(f"Error position: {e.position}")
427
if e.column_name:
428
print(f"Column: {e.column_name}")
429
if e.table_name:
430
print(f"Table: {e.table_name}")
431
```
432
433
### Specific Error Type Handling
434
435
```python
436
try:
437
with conn.cursor() as cursor:
438
cursor.execute("CREATE TABLE users (id INT, name VARCHAR(50))")
439
cursor.execute("CREATE TABLE users (id INT, email VARCHAR(100))") # Duplicate
440
441
except vertica_python.DuplicateObject as e:
442
print(f"Table already exists: {e.table_name}")
443
444
except vertica_python.PermissionDenied as e:
445
print(f"Insufficient privileges: {e}")
446
447
except vertica_python.VerticaSyntaxError as e:
448
print(f"SQL syntax error at position {e.position}: {e.hint}")
449
```
450
451
### COPY Operation Error Handling
452
453
```python
454
import io
455
456
try:
457
with conn.cursor() as cursor:
458
bad_data = "1,Alice,30\n2,Bob,invalid_age\n3,Carol,35\n"
459
data_stream = io.StringIO(bad_data)
460
461
cursor.copy(
462
"COPY users (id, name, age) FROM STDIN DELIMITER ','",
463
data_stream
464
)
465
466
except vertica_python.CopyRejected as e:
467
print(f"COPY operation rejected data: {e}")
468
print(f"Detail: {e.detail}")
469
print(f"Hint: {e.hint}")
470
```
471
472
### Resource and Performance Error Handling
473
474
```python
475
try:
476
with conn.cursor() as cursor:
477
# Query that might consume too much memory
478
cursor.execute("SELECT * FROM huge_table ORDER BY random() LIMIT 1000000")
479
results = cursor.fetchall()
480
481
except vertica_python.OutOfMemory as e:
482
print(f"Query exceeded memory limits: {e}")
483
print(f"Hint: {e.hint}")
484
485
except vertica_python.InsufficientResources as e:
486
print(f"Insufficient system resources: {e}")
487
488
except vertica_python.TimedOutError as e:
489
print(f"Operation timed out: {e}")
490
```
491
492
### Transaction Error Handling
493
494
```python
495
try:
496
with conn.cursor() as cursor:
497
cursor.execute("BEGIN")
498
cursor.execute("INSERT INTO accounts (name, balance) VALUES ('Alice', 1000)")
499
cursor.execute("UPDATE accounts SET balance = balance - 1000 WHERE name = 'Bob'")
500
501
# This might fail due to insufficient funds constraint
502
cursor.execute("UPDATE accounts SET balance = balance - 500 WHERE name = 'Bob'")
503
conn.commit()
504
505
except vertica_python.IntegrityError as e:
506
print(f"Integrity constraint violation: {e}")
507
conn.rollback()
508
509
except vertica_python.LockFailure as e:
510
print(f"Could not acquire lock: {e}")
511
conn.rollback()
512
513
except vertica_python.QueryError as e:
514
print(f"Transaction failed: {e}")
515
conn.rollback()
516
```
517
518
### Generic Error Handling Pattern
519
520
```python
521
def safe_execute_query(cursor, sql, params=None):
522
"""
523
Safely execute a query with comprehensive error handling.
524
"""
525
try:
526
cursor.execute(sql, params)
527
return cursor.fetchall()
528
529
except vertica_python.QueryCanceled:
530
print("Query was canceled")
531
return None
532
533
except vertica_python.MissingRelation as e:
534
print(f"Table/view not found: {e.table_name}")
535
return None
536
537
except vertica_python.PermissionDenied:
538
print("Access denied - check user privileges")
539
return None
540
541
except vertica_python.VerticaSyntaxError as e:
542
print(f"SQL syntax error: {e.hint}")
543
return None
544
545
except vertica_python.QueryError as e:
546
print(f"Query execution error: {e}")
547
if e.detail:
548
print(f"Details: {e.detail}")
549
return None
550
551
except vertica_python.OperationalError as e:
552
print(f"Operational error (may be temporary): {e}")
553
return None
554
555
except vertica_python.DatabaseError as e:
556
print(f"Database error: {e}")
557
return None
558
559
# Usage
560
with conn.cursor() as cursor:
561
results = safe_execute_query(cursor, "SELECT * FROM users WHERE age > :age", {'age': 25})
562
if results:
563
for row in results:
564
print(row)
565
```