Constants and Error Handling

# DB API 2.0 constants
apilevel: str = "2.0"        # DB API specification version
threadsafety: int = 1        # Thread safety level (module level sharing)
paramstyle: str = "format"   # Parameter substitution style (Python % formatting)

# MySQL field type constants (from MySQLdb.constants.FIELD_TYPE)
DECIMAL: int      # DECIMAL and NUMERIC types
TINY: int         # TINYINT type
SHORT: int        # SMALLINT type  
LONG: int         # INT type
FLOAT: int        # FLOAT type
DOUBLE: int       # DOUBLE and REAL types
NULL: int         # NULL type
TIMESTAMP: int    # TIMESTAMP type
LONGLONG: int     # BIGINT type
INT24: int        # MEDIUMINT type
DATE: int         # DATE type
TIME: int         # TIME type
DATETIME: int     # DATETIME type
YEAR: int         # YEAR type
NEWDATE: int      # Newer DATE format
VARCHAR: int      # VARCHAR type
BIT: int          # BIT type
NEWDECIMAL: int   # Newer DECIMAL format
ENUM: int         # ENUM type
SET: int          # SET type
TINY_BLOB: int    # TINYBLOB type
MEDIUM_BLOB: int  # MEDIUMBLOB type
LONG_BLOB: int    # LONGBLOB type
BLOB: int         # BLOB type
VAR_STRING: int   # Variable length string
STRING: int       # Fixed length string
GEOMETRY: int     # Spatial geometry type

# Type aliases
CHAR: int = TINY         # CHAR type (alias for TINY)
INTERVAL: int = ENUM     # INTERVAL type (alias for ENUM)

# Column property flags (from MySQLdb.constants.FLAG)
NOT_NULL: int        # Column cannot contain NULL values
PRI_KEY: int         # Column is part of primary key
UNIQUE_KEY: int      # Column is part of unique key
MULTIPLE_KEY: int    # Column is part of non-unique key
BLOB: int            # Column is BLOB or TEXT type
UNSIGNED: int        # Numeric column is unsigned
ZEROFILL: int        # Numeric column uses zero-fill
BINARY: int          # Column has binary collation
ENUM: int            # Column is ENUM type
AUTO_INCREMENT: int  # Column is auto-increment
TIMESTAMP: int       # Column is TIMESTAMP type
SET: int             # Column is SET type
NUM: int             # Column is numeric type
PART_KEY: int        # Column is part of key
GROUP: int           # Column is part of GROUP BY
UNIQUE: int          # Column has unique constraint

# Client connection flags (from MySQLdb.constants.CLIENT)
LONG_PASSWORD: int      # Use improved password hashing
FOUND_ROWS: int         # Return found rows instead of affected rows
LONG_FLAG: int          # Allow long column flags
CONNECT_WITH_DB: int    # Allow database specification in connect
NO_SCHEMA: int          # Don't allow database.table.column syntax
COMPRESS: int           # Use compression protocol
ODBC: int               # Client is ODBC driver
LOCAL_FILES: int        # Allow LOAD DATA LOCAL
IGNORE_SPACE: int       # Ignore spaces before function names
CHANGE_USER: int        # Support change user command
INTERACTIVE: int        # Interactive client timeout
SSL: int                # Use SSL encryption
IGNORE_SIGPIPE: int     # Ignore SIGPIPE signals
TRANSACTIONS: int       # Client knows about transactions
RESERVED: int           # Reserved flag
SECURE_CONNECTION: int  # Use secure authentication
MULTI_STATEMENTS: int   # Allow multiple statements
MULTI_RESULTS: int      # Allow multiple result sets

# MySQL server error codes (from MySQLdb.constants.ER) - partial list
HASHCHK: int             # Hash check error
NISAMCHK: int           # ISAM check error  
NO: int                 # Generic "no" response
YES: int                # Generic "yes" response
CANT_CREATE_FILE: int   # Cannot create file error
CANT_CREATE_TABLE: int  # Cannot create table error
CANT_CREATE_DB: int     # Cannot create database error
DB_CREATE_EXISTS: int   # Database already exists error
DB_DROP_EXISTS: int     # Database doesn't exist for drop error
DB_DROP_DELETE: int     # Error dropping database
DB_DROP_RMDIR: int      # Cannot remove database directory
CANT_DELETE_FILE: int   # Cannot delete file error
CANT_FIND_SYSTEM_REC: int  # Cannot find system record
CANT_GET_STAT: int      # Cannot get file status
CANT_GET_WD: int        # Cannot get working directory
CANT_LOCK: int          # Cannot lock file
CANT_OPEN_FILE: int     # Cannot open file
FILE_NOT_FOUND: int     # File not found error
CANT_READ_DIR: int      # Cannot read directory
CANT_SET_WD: int        # Cannot set working directory
CHECKREAD: int          # Check read error
DISK_FULL: int          # Disk full error
DUP_KEY: int            # Duplicate key error
ERROR_ON_CLOSE: int     # Error on file close
ERROR_ON_READ: int      # Error on file read
ERROR_ON_RENAME: int    # Error on file rename
ERROR_ON_WRITE: int     # Error on file write
FILE_USED: int          # File is in use
FILSORT_ABORT: int      # File sort aborted
FORM_NOT_FOUND: int     # Form not found
GET_ERRNO: int          # Get errno error
ILLEGAL_HA: int         # Illegal handler error
KEY_NOT_FOUND: int      # Key not found
NOT_FORM_FILE: int      # Not a form file
NOT_KEYFILE: int        # Not a key file
OLD_KEYFILE: int        # Old key file format
OPEN_AS_READONLY: int   # Opened as read-only
OUTOFMEMORY: int        # Out of memory
OUT_OF_SORTMEMORY: int  # Out of sort memory
UNEXPECTED_EOF: int     # Unexpected end of file
CON_COUNT_ERROR: int    # Connection count error
OUT_OF_RESOURCES: int   # Out of resources
BAD_HOST_ERROR: int     # Bad host error
HANDSHAKE_ERROR: int    # Handshake error
DBACCESS_DENIED_ERROR: int  # Database access denied
ACCESS_DENIED_ERROR: int    # Access denied error
NO_DB_ERROR: int        # No database selected
UNKNOWN_COM_ERROR: int  # Unknown command error
BAD_NULL_ERROR: int     # Bad NULL value error
BAD_DB_ERROR: int       # Bad database error
TABLE_EXISTS_ERROR: int # Table already exists
BAD_TABLE_ERROR: int    # Bad table error
NON_UNIQ_ERROR: int     # Non-unique error
SERVER_SHUTDOWN: int    # Server shutdown error
BAD_FIELD_ERROR: int    # Bad field error
WRONG_FIELD_WITH_GROUP: int  # Wrong field with GROUP BY
WRONG_GROUP_FIELD: int  # Wrong GROUP BY field
WRONG_SUM_SELECT: int   # Wrong SUM select
WRONG_VALUE_COUNT: int  # Wrong value count
TOO_LONG_IDENT: int     # Identifier too long
DUP_FIELDNAME: int      # Duplicate field name
DUP_KEYNAME: int        # Duplicate key name
DUP_ENTRY: int          # Duplicate entry error

# Many more error codes available...

# Client error codes (from MySQLdb.constants.CR)
MIN_ERROR: int = 2000                # Minimum client error code
MAX_ERROR: int = 2999                # Maximum client error code
UNKNOWN_ERROR: int = 2000            # Unknown error
SOCKET_CREATE_ERROR: int = 2001      # Cannot create socket
CONNECTION_ERROR: int = 2002         # Cannot connect to server
CONN_HOST_ERROR: int = 2003          # Cannot connect to MySQL server
IPSOCK_ERROR: int = 2004             # Cannot create IP socket
UNKNOWN_HOST: int = 2005             # Unknown MySQL server host
SERVER_GONE_ERROR: int = 2006        # MySQL server has gone away
VERSION_ERROR: int = 2007            # Protocol version mismatch
OUT_OF_MEMORY: int = 2008            # Out of memory
WRONG_HOST_INFO: int = 2009          # Wrong host info
LOCALHOST_CONNECTION: int = 2010     # localhost connection
TCP_CONNECTION: int = 2011           # TCP/IP connection
SERVER_HANDSHAKE_ERR: int = 2012     # Error in server handshake
SERVER_LOST: int = 2013              # Lost connection during query
COMMANDS_OUT_OF_SYNC: int = 2014     # Commands out of sync (raises ProgrammingError)
NAMEDPIPE_CONNECTION: int = 2015     # Named pipe connection
NAMEDPIPEWAIT_ERROR: int = 2016      # Named pipe wait error
NAMEDPIPEOPEN_ERROR: int = 2017      # Cannot open named pipe
NAMEDPIPESETSTATE_ERROR: int = 2018  # Cannot set named pipe state
CANT_READ_CHARSET: int = 2019        # Cannot read character set
NET_PACKET_TOO_LARGE: int = 2020    # Network packet too large

# Refresh operation flags (from MySQLdb.constants.REFRESH)
GRANT: int = 1      # Refresh grant tables
LOG: int = 2        # Refresh log files
TABLES: int = 4     # Refresh table cache
HOSTS: int = 8      # Refresh host cache
STATUS: int = 16    # Refresh status variables
THREADS: int = 32   # Refresh thread cache
SLAVE: int = 64     # Refresh slave status
MASTER: int = 128   # Refresh master status
READ_LOCK: int = 16384  # Refresh with read lock
FAST: int = 32768   # Fast refresh

class MySQLError(Exception):
    """
    Base exception class for all MySQL-python errors.
    Root of the exception hierarchy.
    """

class Warning(MySQLError):
    """
    Exception for important warnings like data truncations.
    Not raised for typical SQL warnings.
    """

class Error(MySQLError):
    """
    Base class for all database-related errors.
    Parent class for all error conditions.
    """

class InterfaceError(Error):
    """
    Exception for errors in the database interface rather than database itself.
    Examples: parameter errors, programming interface misuse.
    """

class DatabaseError(Error):
    """
    Exception for errors related to the database.
    Parent class for all database-specific errors.
    """

class DataError(DatabaseError):
    """
    Exception for errors due to problems with processed data.
    Examples: division by zero, numeric value out of range.
    """

class OperationalError(DatabaseError):
    """
    Exception for errors related to database operation and not user control.
    Examples: connection lost, database name not found, transaction failed.
    """

class IntegrityError(DatabaseError):
    """
    Exception for errors regarding relational integrity of database.
    Examples: foreign key constraint fails, duplicate key.
    """

class InternalError(DatabaseError):
    """
    Exception for errors internal to database module.
    Examples: cursor not valid anymore, transaction out of sync.
    """

class ProgrammingError(DatabaseError):
    """
    Exception for programming errors.
    Examples: table not found, syntax error in SQL, wrong number of parameters.
    """

class NotSupportedError(DatabaseError):
    """
    Exception for unsupported database operations.
    Examples: requesting rollback on connection that doesn't support transactions.
    """

import MySQLdb

try:
    db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
    cursor = db.cursor()
    
    cursor.execute("SELECT * FROM nonexistent_table")
    results = cursor.fetchall()
    
except MySQLdb.Error as e:
    print(f"Database error: {e}")
    # Handle any MySQL-related error
    
except MySQLdb.OperationalError as e:
    print(f"Operational error: {e}")
    # Handle connection/server issues
    
except MySQLdb.ProgrammingError as e:
    print(f"Programming error: {e}")
    # Handle SQL syntax errors, missing tables, etc.
    
finally:
    if 'cursor' in locals():
        cursor.close()
    if 'db' in locals():
        db.close()

import MySQLdb
from MySQLdb.constants import ER

try:
    db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
    cursor = db.cursor()
    
    cursor.execute("INSERT INTO users (email) VALUES (%s)", ("user@example.com",))
    db.commit()
    
except MySQLdb.IntegrityError as e:
    error_code, error_message = e.args
    if error_code == ER.DUP_ENTRY:
        print("Email already exists in database")
    else:
        print(f"Integrity constraint violation: {error_message}")
        
except MySQLdb.OperationalError as e:
    error_code, error_message = e.args
    if error_code == ER.CON_COUNT_ERROR:
        print("Too many connections to database")
    elif error_code == ER.ACCESS_DENIED_ERROR:
        print("Access denied - check credentials")
    else:
        print(f"Operational error: {error_message}")

finally:
    if 'cursor' in locals():
        cursor.close()
    if 'db' in locals():
        db.close()

import MySQLdb
from MySQLdb.constants import FIELD_TYPE, FLAG

db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
cursor = db.cursor()

cursor.execute("SELECT * FROM users LIMIT 1")
description = cursor.description

for column in description:
    name, field_type, display_size, internal_size, precision, scale, null_ok = column
    
    print(f"Column: {name}")
    print(f"  Type: {field_type}")
    
    # Check specific field types
    if field_type == FIELD_TYPE.VARCHAR:
        print("  VARCHAR field")
    elif field_type == FIELD_TYPE.INT24:
        print("  MEDIUMINT field") 
    elif field_type == FIELD_TYPE.TIMESTAMP:
        print("  TIMESTAMP field")
    
    # Check field flags
    if null_ok & FLAG.NOT_NULL:
        print("  NOT NULL constraint")
    if null_ok & FLAG.PRI_KEY:
        print("  Primary key")
    if null_ok & FLAG.AUTO_INCREMENT:
        print("  Auto increment")
    if null_ok & FLAG.UNSIGNED:
        print("  Unsigned numeric")

cursor.close()
db.close()

import MySQLdb
from MySQLdb.constants import CLIENT

# Connect with specific client flags
db = MySQLdb.connect(
    host="localhost",
    user="user", 
    passwd="pass",
    db="test",
    client_flag=CLIENT.MULTI_STATEMENTS | CLIENT.MULTI_RESULTS
)

cursor = db.cursor()

# Now can execute multiple statements
cursor.execute("""
    INSERT INTO log (message) VALUES ('First message');
    INSERT INTO log (message) VALUES ('Second message');
    SELECT COUNT(*) FROM log;
""")

# Process multiple result sets
cursor.fetchall()  # Results from SELECT
if cursor.nextset():
    print("Additional result sets available")

cursor.close()
db.close()

import MySQLdb
import sys

def handle_mysql_error(e):
    """Comprehensive MySQL error handling with detailed information."""
    
    print(f"MySQL Error occurred: {type(e).__name__}")
    
    if hasattr(e, 'args') and len(e.args) >= 2:
        error_code, error_message = e.args[:2]
        print(f"Error Code: {error_code}")
        print(f"Error Message: {error_message}")
    else:
        print(f"Error: {e}")
    
    # Additional context for specific error types
    if isinstance(e, MySQLdb.OperationalError):
        print("This is an operational error - likely connection or server issue")
    elif isinstance(e, MySQLdb.IntegrityError):
        print("This is an integrity error - constraint violation")
    elif isinstance(e, MySQLdb.ProgrammingError):
        print("This is a programming error - check SQL syntax and table names")
    
    print(f"Exception traceback: {sys.exc_info()}")

try:
    db = MySQLdb.connect(host="localhost", user="baduser", passwd="badpass", db="test")
except MySQLdb.Error as e:
    handle_mysql_error(e)