tessl/pypi-vertica-python
Official native Python client for the Vertica database.
exception-handling.mddocs/
Exception Handling
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.
Capabilities
DB-API 2.0 Standard Exceptions
Base exception classes following the Python Database API specification.
class Error(Exception):
"""
Base exception class for all database errors.
"""
class Warning(Exception):
"""
Exception for important warnings like data truncations.
"""
class InterfaceError(Error):
"""
Error related to the database interface rather than the database itself.
Examples: invalid connection parameters, interface misuse.
"""
class DatabaseError(Error):
"""
Error related to the database.
Base class for all database-related errors.
"""
class InternalError(DatabaseError):
"""
Internal database error that the client cannot resolve.
Examples: internal database inconsistencies.
"""
class OperationalError(DatabaseError):
"""
Error related to database operation and not necessarily under user control.
Examples: connection lost, memory allocation errors, database restarts.
"""
class ProgrammingError(DatabaseError):
"""
Programming error in database usage.
Examples: table not found, SQL syntax errors, wrong parameter count.
"""
class IntegrityError(DatabaseError):
"""
Error when relational integrity of the database is affected.
Examples: foreign key constraint violations, duplicate key errors.
"""
class DataError(DatabaseError):
"""
Error due to problems with processed data.
Examples: division by zero, numeric value out of range.
"""
class NotSupportedError(DatabaseError):
"""
Method or database API not supported by the database.
Examples: unsupported SQL features, unsupported operations.
"""Vertica-Specific Extended Exceptions
Extended exception classes for Vertica-specific error scenarios.
class TimedOutError(OperationalError):
"""
Operation timeout error.
Raised when database operations exceed timeout limits.
"""
class ConnectionError(DatabaseError):
"""
Connection-related errors.
Base class for all connection-specific errors.
"""
class KerberosError(ConnectionError):
"""
Kerberos authentication errors.
Raised when Kerberos authentication fails.
"""
class SSLNotSupported(ConnectionError):
"""
SSL/TLS not supported errors.
Raised when SSL is requested but not available.
"""
class MessageError(InternalError):
"""
Database protocol message errors.
Raised when there are issues with the communication protocol.
"""
class EmptyQueryError(ProgrammingError):
"""
Empty query string submitted.
Raised when an empty or whitespace-only query is executed.
"""QueryError Class
Enhanced error class providing detailed SQL context and error information.
class QueryError(ProgrammingError):
"""
Query execution error with SQL context and detailed error information.
This error is the most commonly encountered error type, associated with
failures during query execution, invalid SQL statements, and more.
"""
def __init__(self, error_response, sql: str):
"""
Initialize QueryError with error response and SQL statement.
Parameters:
- error_response: Detailed error response from server
- sql (str): SQL statement that caused the error
"""
@property
def sql(self) -> str:
"""
Get the SQL statement that caused the error.
Returns:
str: Original SQL statement
"""
@property
def error_response(self):
"""
Get detailed error response from the server.
Returns:
ErrorResponse: Server error response object with detailed information
"""
def one_line_sql(self) -> str:
"""
Get single-line version of the SQL statement for compact display.
Returns:
str: SQL statement with newlines replaced by spaces
"""
@classmethod
def from_error_response(cls, error_response, sql: str) -> 'QueryError':
"""
Create appropriate QueryError subclass based on SQL state.
Parameters:
- error_response: Error response from server
- sql (str): SQL statement that caused the error
Returns:
QueryError: Appropriate subclass instance based on error type
"""
# Properties from NoticeResponseAttrMixin
@property
def severity(self) -> str:
"""Error severity level."""
@property
def sqlstate(self) -> str:
"""SQL state code."""
@property
def message(self) -> str:
"""Primary error message."""
@property
def detail(self) -> str:
"""Detailed error information."""
@property
def hint(self) -> str:
"""Hint for resolving the error."""
@property
def position(self) -> str:
"""Character position of error in SQL statement."""
@property
def internal_position(self) -> str:
"""Internal character position."""
@property
def internal_query(self) -> str:
"""Internal query that caused the error."""
@property
def where(self) -> str:
"""Context where error occurred."""
@property
def schema_name(self) -> str:
"""Schema name related to error."""
@property
def table_name(self) -> str:
"""Table name related to error."""
@property
def column_name(self) -> str:
"""Column name related to error."""
@property
def datatype_name(self) -> str:
"""Data type name related to error."""
@property
def constraint_name(self) -> str:
"""Constraint name related to error."""
@property
def file(self) -> str:
"""Source file where error occurred."""
@property
def line(self) -> str:
"""Line number where error occurred."""
@property
def routine(self) -> str:
"""Routine name where error occurred."""Specific Query Error Types
Automatically mapped based on SQL state codes from the server.
class LockFailure(QueryError):
"""
Lock acquisition failures (SQL State: 55V03).
Raised when unable to acquire required locks.
"""
class InsufficientResources(QueryError):
"""
Insufficient system resources (SQL State: 53000).
Raised when system runs out of resources.
"""
class OutOfMemory(QueryError):
"""
Out of memory errors (SQL State: 53200).
Raised when operations exceed available memory.
"""
class VerticaSyntaxError(QueryError):
"""
SQL syntax errors (SQL State: 42601).
Raised when SQL contains syntax errors.
"""
class MissingSchema(QueryError):
"""
Schema not found (SQL State: 3F000).
Raised when referencing non-existent schema.
"""
class MissingRelation(QueryError):
"""
Table or view not found (SQL State: 42V01).
Raised when referencing non-existent table or view.
"""
class MissingColumn(QueryError):
"""
Column not found (SQL State: 42703).
Raised when referencing non-existent column.
"""
class CopyRejected(QueryError):
"""
COPY operation data rejected (SQL State: 22V04).
Raised when COPY operation rejects input data.
"""
class PermissionDenied(QueryError):
"""
Insufficient privileges (SQL State: 42501).
Raised when user lacks required permissions.
"""
class InvalidDatetimeFormat(QueryError):
"""
Invalid date/time format (SQL State: 22007).
Raised when date/time values have invalid format.
"""
class DuplicateObject(QueryError):
"""
Duplicate object error (SQL State: 42710).
Raised when trying to create objects that already exist.
"""
class QueryCanceled(QueryError):
"""
Query was canceled (SQL State: 57014).
Raised when query execution is canceled.
"""
class ConnectionFailure(QueryError):
"""
Connection failure during query (SQL State: 08006).
Raised when connection is lost during query execution.
"""
class LostConnectivityFailure(QueryError):
"""
Lost connectivity with server (SQL State: V1001).
Raised when client loses connectivity with Vertica server.
"""SQL State to Exception Mapping
The system automatically maps SQL state codes to appropriate exception classes:
QUERY_ERROR_CLASSES = {
'55V03': LockFailure,
'53000': InsufficientResources,
'53200': OutOfMemory,
'42601': VerticaSyntaxError,
'3F000': MissingSchema,
'42V01': MissingRelation,
'42703': MissingColumn,
'22V04': CopyRejected,
'42501': PermissionDenied,
'22007': InvalidDatetimeFormat,
'42710': DuplicateObject,
'57014': QueryCanceled,
'08006': ConnectionFailure,
'V1001': LostConnectivityFailure
}Usage Examples
Basic Exception Handling
import vertica_python
from vertica_python import Error, DatabaseError, ProgrammingError
try:
with vertica_python.connect(host='localhost', user='dbadmin', database='mydb') as conn:
with conn.cursor() as cursor:
cursor.execute("SELECT * FROM nonexistent_table")
results = cursor.fetchall()
except vertica_python.MissingRelation as e:
print(f"Table not found: {e}")
print(f"SQL: {e.sql}")
print(f"SQL State: {e.sqlstate}")
except ProgrammingError as e:
print(f"Programming error: {e}")
except DatabaseError as e:
print(f"Database error: {e}")
except Error as e:
print(f"General database error: {e}")Connection Error Handling
try:
conn = vertica_python.connect(
host='nonexistent-server.com',
user='baduser',
password='wrongpassword',
database='mydb'
)
except vertica_python.ConnectionError as e:
print(f"Connection failed: {e}")
except vertica_python.KerberosError as e:
print(f"Kerberos authentication failed: {e}")
except vertica_python.SSLNotSupported as e:
print(f"SSL not supported: {e}")
except vertica_python.InterfaceError as e:
print(f"Interface error (bad parameters?): {e}")Detailed Query Error Information
try:
with conn.cursor() as cursor:
cursor.execute("SELECT * FROM users WHERE invalid_column = 'value'")
except vertica_python.QueryError as e:
print(f"Query failed: {e}")
print(f"SQL: {e.sql}")
print(f"One-line SQL: {e.one_line_sql()}")
print(f"SQL State: {e.sqlstate}")
print(f"Severity: {e.severity}")
print(f"Message: {e.message}")
print(f"Detail: {e.detail}")
print(f"Hint: {e.hint}")
if e.position:
print(f"Error position: {e.position}")
if e.column_name:
print(f"Column: {e.column_name}")
if e.table_name:
print(f"Table: {e.table_name}")Specific Error Type Handling
try:
with conn.cursor() as cursor:
cursor.execute("CREATE TABLE users (id INT, name VARCHAR(50))")
cursor.execute("CREATE TABLE users (id INT, email VARCHAR(100))") # Duplicate
except vertica_python.DuplicateObject as e:
print(f"Table already exists: {e.table_name}")
except vertica_python.PermissionDenied as e:
print(f"Insufficient privileges: {e}")
except vertica_python.VerticaSyntaxError as e:
print(f"SQL syntax error at position {e.position}: {e.hint}")COPY Operation Error Handling
import io
try:
with conn.cursor() as cursor:
bad_data = "1,Alice,30\n2,Bob,invalid_age\n3,Carol,35\n"
data_stream = io.StringIO(bad_data)
cursor.copy(
"COPY users (id, name, age) FROM STDIN DELIMITER ','",
data_stream
)
except vertica_python.CopyRejected as e:
print(f"COPY operation rejected data: {e}")
print(f"Detail: {e.detail}")
print(f"Hint: {e.hint}")Resource and Performance Error Handling
try:
with conn.cursor() as cursor:
# Query that might consume too much memory
cursor.execute("SELECT * FROM huge_table ORDER BY random() LIMIT 1000000")
results = cursor.fetchall()
except vertica_python.OutOfMemory as e:
print(f"Query exceeded memory limits: {e}")
print(f"Hint: {e.hint}")
except vertica_python.InsufficientResources as e:
print(f"Insufficient system resources: {e}")
except vertica_python.TimedOutError as e:
print(f"Operation timed out: {e}")Transaction Error Handling
try:
with conn.cursor() as cursor:
cursor.execute("BEGIN")
cursor.execute("INSERT INTO accounts (name, balance) VALUES ('Alice', 1000)")
cursor.execute("UPDATE accounts SET balance = balance - 1000 WHERE name = 'Bob'")
# This might fail due to insufficient funds constraint
cursor.execute("UPDATE accounts SET balance = balance - 500 WHERE name = 'Bob'")
conn.commit()
except vertica_python.IntegrityError as e:
print(f"Integrity constraint violation: {e}")
conn.rollback()
except vertica_python.LockFailure as e:
print(f"Could not acquire lock: {e}")
conn.rollback()
except vertica_python.QueryError as e:
print(f"Transaction failed: {e}")
conn.rollback()Generic Error Handling Pattern
def safe_execute_query(cursor, sql, params=None):
"""
Safely execute a query with comprehensive error handling.
"""
try:
cursor.execute(sql, params)
return cursor.fetchall()
except vertica_python.QueryCanceled:
print("Query was canceled")
return None
except vertica_python.MissingRelation as e:
print(f"Table/view not found: {e.table_name}")
return None
except vertica_python.PermissionDenied:
print("Access denied - check user privileges")
return None
except vertica_python.VerticaSyntaxError as e:
print(f"SQL syntax error: {e.hint}")
return None
except vertica_python.QueryError as e:
print(f"Query execution error: {e}")
if e.detail:
print(f"Details: {e.detail}")
return None
except vertica_python.OperationalError as e:
print(f"Operational error (may be temporary): {e}")
return None
except vertica_python.DatabaseError as e:
print(f"Database error: {e}")
return None
# Usage
with conn.cursor() as cursor:
results = safe_execute_query(cursor, "SELECT * FROM users WHERE age > :age", {'age': 25})
if results:
for row in results:
print(row)Install with Tessl CLI
npx tessl i tessl/pypi-vertica-python