Exception Handling

# Base exception classes
class Warning(Exception):
    """
    Raised for important warnings like data truncations.
    
    Example: Data truncation during INSERT operations
    """

class Error(Exception):
    """
    Base class for all database errors.
    Use this to catch all database-related errors with one except statement.
    """

# Interface-related errors
class InterfaceError(Error):
    """
    Raised for errors related to the database interface rather than the database itself.
    
    Example: Invalid parameter types, connection issues
    """

class ColumnsWithoutNamesError(InterfaceError):
    """
    Raised when as_dict=True is used but query has columns without names.
    
    Attributes:
    - columns_without_names (list): List of unnamed column indices
    """
    
    def __init__(self, columns_without_names: list): ...
    def __str__(self) -> str: ...

# Database-related errors  
class DatabaseError(Error):
    """
    Base class for errors related to the database itself.
    """

class DataError(DatabaseError):
    """
    Raised for errors due to problems with processed data.
    
    Examples: Division by zero, numeric value out of range, 
    invalid date format, data type conversion errors
    """

class OperationalError(DatabaseError):
    """
    Raised for errors related to database operation, not necessarily 
    under programmer control.
    
    Examples: Unexpected disconnect, data source not found, 
    transaction processing failure, memory allocation error
    """

class IntegrityError(DatabaseError):
    """  
    Raised when relational integrity of database is affected.
    
    Examples: Foreign key constraint violations, unique constraint violations,
    check constraint failures
    """

class InternalError(DatabaseError):
    """
    Raised when database encounters an internal error.
    
    Examples: Cursor no longer valid, transaction out of sync,
    internal database corruption
    """

class ProgrammingError(DatabaseError):
    """
    Raised for programming errors.
    
    Examples: Table not found, syntax error in SQL statement,
    wrong number of parameters, invalid object names
    """

class NotSupportedError(DatabaseError):
    """
    Raised when unsupported method or database API is used.
    
    Examples: Requesting rollback on connection that doesn't support transactions,
    using unsupported SQL features
    """

class MSSQLException(Exception):
    """Base exception class for _mssql module."""

class MSSQLDriverException(MSSQLException):
    """
    Raised for problems within _mssql driver.
    
    Examples: Insufficient memory for data structures,
    internal driver errors, resource allocation failures
    """

class MSSQLDatabaseException(MSSQLException):
    """
    Raised for database-related problems in _mssql.
    
    Attributes:
    - number (int): SQL Server error number
    - severity (int): Error severity level (0-25)
    - state (int): Error state code
    - message (str): Error message from SQL Server
    """
    
    number: int      # SQL Server error number
    severity: int    # Error severity level
    state: int       # Error state code
    message: str     # Error message

import pymssql
from pymssql.exceptions import *

try:
    conn = pymssql.connect('server', 'user', 'password', 'database')
    cursor = conn.cursor()
    
    # This might raise various exceptions
    cursor.execute("INSERT INTO users (id, name) VALUES (%s, %s)", (1, 'John'))
    conn.commit()
    
except InterfaceError as e:
    print(f"Interface error: {e}")
    # Handle connection or parameter issues
    
except ProgrammingError as e:
    print(f"Programming error: {e}")
    # Handle SQL syntax errors, missing tables, etc.
    
except IntegrityError as e:
    print(f"Integrity error: {e}")
    # Handle constraint violations
    
except OperationalError as e:
    print(f"Operational error: {e}")  
    # Handle connection drops, timeouts, etc.
    
except DatabaseError as e:
    print(f"Database error: {e}")
    # Handle other database-related errors
    
except Error as e:
    print(f"General database error: {e}")
    # Catch-all for any database error
    
finally:
    if 'conn' in locals():
        conn.close()

import pymssql
from pymssql.exceptions import *

def insert_user(name, email):
    conn = None
    try:
        conn = pymssql.connect('server', 'user', 'pass', 'db')
        cursor = conn.cursor()
        
        cursor.execute(
            "INSERT INTO users (name, email) VALUES (%s, %s)", 
            (name, email)
        )
        conn.commit()
        return True
        
    except IntegrityError as e:
        # Handle constraint violations
        if "UNIQUE KEY constraint" in str(e):
            print(f"Email {email} already exists")
        elif "FOREIGN KEY constraint" in str(e):
            print("Invalid reference in user data")
        else:
            print(f"Data integrity error: {e}")
        return False
        
    except ProgrammingError as e:
        # Handle SQL errors
        if "Invalid object name" in str(e):
            print("Users table does not exist")
        elif "Invalid column name" in str(e):
            print("Invalid column referenced in query")
        else:
            print(f"SQL programming error: {e}")
        return False
        
    except OperationalError as e:
        # Handle connection/operational issues
        print(f"Database operation failed: {e}")
        return False
        
    finally:
        if conn:
            conn.close()

from pymssql import _mssql
from pymssql._mssql import *

try:
    conn = _mssql.connect('server', 'user', 'password', 'database')
    
    # Execute query that might fail
    conn.execute_query("SELECT * FROM nonexistent_table")
    
except MSSQLDatabaseException as e:
    print(f"SQL Server Error {e.number}: {e.message}")
    print(f"Severity: {e.severity}, State: {e.state}")
    
    # Handle specific SQL Server error codes
    if e.number == 208:  # Invalid object name
        print("Table or view does not exist")
    elif e.number == 207:  # Invalid column name
        print("Column does not exist") 
    elif e.number == 102:  # Syntax error
        print("SQL syntax error")
    
except MSSQLDriverException as e:
    print(f"Driver error: {e}")
    # Handle driver-level issues
    
except MSSQLException as e:
    print(f"General _mssql error: {e}")
    
finally:
    if 'conn' in locals():
        conn.close()

import pymssql  
from pymssql.exceptions import *

def transfer_funds(from_account, to_account, amount):
    conn = None
    try:
        conn = pymssql.connect('server', 'user', 'pass', 'bank_db')
        cursor = conn.cursor()
        
        # Start transaction (implicit)
        cursor.execute(
            "UPDATE accounts SET balance = balance - %s WHERE id = %s",
            (amount, from_account)
        )
        
        cursor.execute(
            "UPDATE accounts SET balance = balance + %s WHERE id = %s", 
            (amount, to_account)
        )
        
        # Commit transaction
        conn.commit()
        return True
        
    except IntegrityError as e:
        # Handle constraint violations (e.g., insufficient funds check)
        print(f"Transaction violates business rules: {e}")
        conn.rollback()
        return False
        
    except OperationalError as e:
        # Handle connection issues during transaction
        print(f"Transaction failed due to operational error: {e}")
        try:
            conn.rollback()
        except:
            pass  # Connection might be dead
        return False
        
    except Exception as e:
        # Rollback on any other error
        print(f"Transaction failed: {e}")
        try:
            conn.rollback()
        except:
            pass
        return False
        
    finally:
        if conn:
            conn.close()

import pymssql
from pymssql.exceptions import *
import time

def connect_with_retry(max_retries=3, retry_delay=1):
    """Connect with retry logic for handling temporary failures."""
    
    for attempt in range(max_retries):
        try:
            conn = pymssql.connect(
                server='server',
                user='user', 
                password='password',
                database='database',
                login_timeout=10
            )
            return conn
            
        except InterfaceError as e:
            # Connection interface issues
            if attempt < max_retries - 1:
                print(f"Connection attempt {attempt + 1} failed: {e}")
                time.sleep(retry_delay)
                continue
            else:
                print(f"Failed to connect after {max_retries} attempts")
                raise
                
        except OperationalError as e:
            # Server not available, network issues, etc.
            if "timeout" in str(e).lower():
                if attempt < max_retries - 1:
                    print(f"Connection timeout, retrying... (attempt {attempt + 1})")
                    time.sleep(retry_delay * 2)  # Longer delay for timeouts
                    continue
            raise
            
    return None

import pymssql
from pymssql.exceptions import *

def execute_with_recovery(cursor, query, params=None):
    """Execute query with automatic error recovery."""
    
    try:
        cursor.execute(query, params)
        return True
        
    except ProgrammingError as e:
        error_msg = str(e).lower()
        
        if "invalid object name" in error_msg:
            # Try to create missing table or suggest alternative
            print(f"Missing table/view: {e}")
            return False
            
        elif "invalid column name" in error_msg:
            # Try to suggest valid columns
            print(f"Invalid column: {e}")
            return False
            
        elif "syntax error" in error_msg:
            # Log syntax error for debugging
            print(f"SQL syntax error: {e}")
            return False
            
        else:
            # Re-raise unknown programming errors
            raise
            
    except DataError as e:
        # Handle data conversion/validation errors
        print(f"Data error: {e}")
        
        if "conversion failed" in str(e).lower():
            print("Check data types and formats")
        elif "value out of range" in str(e).lower():
            print("Data value exceeds column limits")
            
        return False
        
    except IntegrityError as e:
        # Handle constraint violations
        error_msg = str(e).lower()
        
        if "primary key" in error_msg:
            print("Primary key constraint violation")
        elif "foreign key" in error_msg:
            print("Foreign key constraint violation")  
        elif "unique" in error_msg:
            print("Unique constraint violation")
        elif "check" in error_msg:
            print("Check constraint violation")
            
        return False

from pymssql.exceptions import DatabaseError, OperationalError

class ApplicationError(Exception):
    """Base exception for application-specific errors."""
    pass

class BusinessRuleError(ApplicationError):
    """Raised when business logic rules are violated."""
    
    def __init__(self, rule_name, message):
        self.rule_name = rule_name
        super().__init__(message)

class DataValidationError(ApplicationError):
    """Raised when data validation fails."""
    
    def __init__(self, field_name, value, message):
        self.field_name = field_name
        self.value = value
        super().__init__(message)

def process_order(order_data):
    """Process order with custom error handling."""
    
    try:
        # Database operations
        conn = pymssql.connect('server', 'user', 'pass', 'db')
        cursor = conn.cursor()
        
        # Validate business rules
        if order_data['quantity'] <= 0:
            raise BusinessRuleError(
                'positive_quantity',
                'Order quantity must be positive'
            )
        
        # Validate data
        if not isinstance(order_data['customer_id'], int):
            raise DataValidationError(
                'customer_id',
                order_data['customer_id'], 
                'Customer ID must be an integer'
            )
        
        cursor.execute(
            "INSERT INTO orders (customer_id, quantity) VALUES (%s, %s)",
            (order_data['customer_id'], order_data['quantity'])
        )
        conn.commit()
        
    except BusinessRuleError as e:
        print(f"Business rule violation ({e.rule_name}): {e}")
        return False
        
    except DataValidationError as e:
        print(f"Data validation error for {e.field_name} = {e.value}: {e}")
        return False
        
    except IntegrityError as e:
        # Convert database integrity errors to business rule errors
        if "customer_id" in str(e):
            raise BusinessRuleError(
                'valid_customer',
                'Customer does not exist'
            )
        raise
        
    except DatabaseError as e:
        print(f"Database error: {e}")
        return False
        
    finally:
        if 'conn' in locals():
            conn.close()
    
    return True

# Error categorization for exception handling
PROGRAMMING_ERRORS = (
    102,    # Syntax error
    207,    # Invalid column name  
    208,    # Invalid object name
    2812,   # Unknown procedure
    4104    # Multi-part identifier could not be bound
)

INTEGRITY_ERRORS = (
    515,    # NULL insert into NOT NULL column
    547,    # Foreign key constraint violation
    2601,   # Duplicate key in unique index
    2627,   # Unique constraint violation
)

# Usage in error handling
def categorize_sql_error(error_number):
    """Categorize SQL Server error by number."""
    
    if error_number in PROGRAMMING_ERRORS:
        return "Programming Error"
    elif error_number in INTEGRITY_ERRORS:
        return "Integrity Error"
    elif 50000 <= error_number <= 99999:
        return "User-Defined Error"
    elif error_number >= 100000:
        return "System Error"
    else:
        return "General Error"

import logging
import pymssql
from pymssql.exceptions import *

# Configure logging
logging.basicConfig(level=logging.ERROR)
logger = logging.getLogger(__name__)

def execute_with_logging(query, params=None):
    """Execute query with comprehensive error logging."""
    
    conn = None
    try:
        conn = pymssql.connect('server', 'user', 'pass', 'db')
        cursor = conn.cursor()
        
        cursor.execute(query, params)
        conn.commit()
        
    except Exception as e:
        # Log error with context
        logger.error(
            "Database operation failed",
            extra={
                'query': query,
                'params': params,
                'error_type': type(e).__name__,
                'error_message': str(e)
            }
        )
        raise
        
    finally:
        if conn:
            conn.close()