0
# Constants and Error Handling
1

2
MySQL protocol constants, error codes, field type definitions, and comprehensive exception hierarchy providing robust error handling and detailed protocol-level control for MySQL database operations.
3

4
## Capabilities
5

6
### DB API Constants
7

8
Core DB API 2.0 specification constants that define the interface characteristics.
9

10
```python { .api }
11
# DB API 2.0 constants
12
apilevel: str = "2.0"        # DB API specification version
13
threadsafety: int = 1        # Thread safety level (module level sharing)
14
paramstyle: str = "format"   # Parameter substitution style (Python % formatting)
15
```
16

17
### Field Type Constants
18

19
MySQL column type constants for identifying and working with different data types.
20

21
```python { .api }
22
# MySQL field type constants (from MySQLdb.constants.FIELD_TYPE)
23
DECIMAL: int      # DECIMAL and NUMERIC types
24
TINY: int         # TINYINT type
25
SHORT: int        # SMALLINT type  
26
LONG: int         # INT type
27
FLOAT: int        # FLOAT type
28
DOUBLE: int       # DOUBLE and REAL types
29
NULL: int         # NULL type
30
TIMESTAMP: int    # TIMESTAMP type
31
LONGLONG: int     # BIGINT type
32
INT24: int        # MEDIUMINT type
33
DATE: int         # DATE type
34
TIME: int         # TIME type
35
DATETIME: int     # DATETIME type
36
YEAR: int         # YEAR type
37
NEWDATE: int      # Newer DATE format
38
VARCHAR: int      # VARCHAR type
39
BIT: int          # BIT type
40
NEWDECIMAL: int   # Newer DECIMAL format
41
ENUM: int         # ENUM type
42
SET: int          # SET type
43
TINY_BLOB: int    # TINYBLOB type
44
MEDIUM_BLOB: int  # MEDIUMBLOB type
45
LONG_BLOB: int    # LONGBLOB type
46
BLOB: int         # BLOB type
47
VAR_STRING: int   # Variable length string
48
STRING: int       # Fixed length string
49
GEOMETRY: int     # Spatial geometry type
50

51
# Type aliases
52
CHAR: int = TINY         # CHAR type (alias for TINY)
53
INTERVAL: int = ENUM     # INTERVAL type (alias for ENUM)
54
```
55

56
### Column Property Flags
57

58
Flags indicating column properties and constraints.
59

60
```python { .api }
61
# Column property flags (from MySQLdb.constants.FLAG)
62
NOT_NULL: int        # Column cannot contain NULL values
63
PRI_KEY: int         # Column is part of primary key
64
UNIQUE_KEY: int      # Column is part of unique key
65
MULTIPLE_KEY: int    # Column is part of non-unique key
66
BLOB: int            # Column is BLOB or TEXT type
67
UNSIGNED: int        # Numeric column is unsigned
68
ZEROFILL: int        # Numeric column uses zero-fill
69
BINARY: int          # Column has binary collation
70
ENUM: int            # Column is ENUM type
71
AUTO_INCREMENT: int  # Column is auto-increment
72
TIMESTAMP: int       # Column is TIMESTAMP type
73
SET: int             # Column is SET type
74
NUM: int             # Column is numeric type
75
PART_KEY: int        # Column is part of key
76
GROUP: int           # Column is part of GROUP BY
77
UNIQUE: int          # Column has unique constraint
78
```
79

80
### Client Connection Flags
81

82
Flags controlling client connection behavior and capabilities.
83

84
```python { .api }
85
# Client connection flags (from MySQLdb.constants.CLIENT)
86
LONG_PASSWORD: int      # Use improved password hashing
87
FOUND_ROWS: int         # Return found rows instead of affected rows
88
LONG_FLAG: int          # Allow long column flags
89
CONNECT_WITH_DB: int    # Allow database specification in connect
90
NO_SCHEMA: int          # Don't allow database.table.column syntax
91
COMPRESS: int           # Use compression protocol
92
ODBC: int               # Client is ODBC driver
93
LOCAL_FILES: int        # Allow LOAD DATA LOCAL
94
IGNORE_SPACE: int       # Ignore spaces before function names
95
CHANGE_USER: int        # Support change user command
96
INTERACTIVE: int        # Interactive client timeout
97
SSL: int                # Use SSL encryption
98
IGNORE_SIGPIPE: int     # Ignore SIGPIPE signals
99
TRANSACTIONS: int       # Client knows about transactions
100
RESERVED: int           # Reserved flag
101
SECURE_CONNECTION: int  # Use secure authentication
102
MULTI_STATEMENTS: int   # Allow multiple statements
103
MULTI_RESULTS: int      # Allow multiple result sets
104
```
105

106
### MySQL Error Codes
107

108
Comprehensive error code constants for MySQL server errors.
109

110
```python { .api }
111
# MySQL server error codes (from MySQLdb.constants.ER) - partial list
112
HASHCHK: int             # Hash check error
113
NISAMCHK: int           # ISAM check error  
114
NO: int                 # Generic "no" response
115
YES: int                # Generic "yes" response
116
CANT_CREATE_FILE: int   # Cannot create file error
117
CANT_CREATE_TABLE: int  # Cannot create table error
118
CANT_CREATE_DB: int     # Cannot create database error
119
DB_CREATE_EXISTS: int   # Database already exists error
120
DB_DROP_EXISTS: int     # Database doesn't exist for drop error
121
DB_DROP_DELETE: int     # Error dropping database
122
DB_DROP_RMDIR: int      # Cannot remove database directory
123
CANT_DELETE_FILE: int   # Cannot delete file error
124
CANT_FIND_SYSTEM_REC: int  # Cannot find system record
125
CANT_GET_STAT: int      # Cannot get file status
126
CANT_GET_WD: int        # Cannot get working directory
127
CANT_LOCK: int          # Cannot lock file
128
CANT_OPEN_FILE: int     # Cannot open file
129
FILE_NOT_FOUND: int     # File not found error
130
CANT_READ_DIR: int      # Cannot read directory
131
CANT_SET_WD: int        # Cannot set working directory
132
CHECKREAD: int          # Check read error
133
DISK_FULL: int          # Disk full error
134
DUP_KEY: int            # Duplicate key error
135
ERROR_ON_CLOSE: int     # Error on file close
136
ERROR_ON_READ: int      # Error on file read
137
ERROR_ON_RENAME: int    # Error on file rename
138
ERROR_ON_WRITE: int     # Error on file write
139
FILE_USED: int          # File is in use
140
FILSORT_ABORT: int      # File sort aborted
141
FORM_NOT_FOUND: int     # Form not found
142
GET_ERRNO: int          # Get errno error
143
ILLEGAL_HA: int         # Illegal handler error
144
KEY_NOT_FOUND: int      # Key not found
145
NOT_FORM_FILE: int      # Not a form file
146
NOT_KEYFILE: int        # Not a key file
147
OLD_KEYFILE: int        # Old key file format
148
OPEN_AS_READONLY: int   # Opened as read-only
149
OUTOFMEMORY: int        # Out of memory
150
OUT_OF_SORTMEMORY: int  # Out of sort memory
151
UNEXPECTED_EOF: int     # Unexpected end of file
152
CON_COUNT_ERROR: int    # Connection count error
153
OUT_OF_RESOURCES: int   # Out of resources
154
BAD_HOST_ERROR: int     # Bad host error
155
HANDSHAKE_ERROR: int    # Handshake error
156
DBACCESS_DENIED_ERROR: int  # Database access denied
157
ACCESS_DENIED_ERROR: int    # Access denied error
158
NO_DB_ERROR: int        # No database selected
159
UNKNOWN_COM_ERROR: int  # Unknown command error
160
BAD_NULL_ERROR: int     # Bad NULL value error
161
BAD_DB_ERROR: int       # Bad database error
162
TABLE_EXISTS_ERROR: int # Table already exists
163
BAD_TABLE_ERROR: int    # Bad table error
164
NON_UNIQ_ERROR: int     # Non-unique error
165
SERVER_SHUTDOWN: int    # Server shutdown error
166
BAD_FIELD_ERROR: int    # Bad field error
167
WRONG_FIELD_WITH_GROUP: int  # Wrong field with GROUP BY
168
WRONG_GROUP_FIELD: int  # Wrong GROUP BY field
169
WRONG_SUM_SELECT: int   # Wrong SUM select
170
WRONG_VALUE_COUNT: int  # Wrong value count
171
TOO_LONG_IDENT: int     # Identifier too long
172
DUP_FIELDNAME: int      # Duplicate field name
173
DUP_KEYNAME: int        # Duplicate key name
174
DUP_ENTRY: int          # Duplicate entry error
175

176
# Many more error codes available...
177
```
178

179
### Client Error Codes
180

181
Error codes for client-side operations and connection issues. These represent client-side errors rather than server errors and typically raise OperationalError exceptions.
182

183
```python { .api }
184
# Client error codes (from MySQLdb.constants.CR)
185
MIN_ERROR: int = 2000                # Minimum client error code
186
MAX_ERROR: int = 2999                # Maximum client error code
187
UNKNOWN_ERROR: int = 2000            # Unknown error
188
SOCKET_CREATE_ERROR: int = 2001      # Cannot create socket
189
CONNECTION_ERROR: int = 2002         # Cannot connect to server
190
CONN_HOST_ERROR: int = 2003          # Cannot connect to MySQL server
191
IPSOCK_ERROR: int = 2004             # Cannot create IP socket
192
UNKNOWN_HOST: int = 2005             # Unknown MySQL server host
193
SERVER_GONE_ERROR: int = 2006        # MySQL server has gone away
194
VERSION_ERROR: int = 2007            # Protocol version mismatch
195
OUT_OF_MEMORY: int = 2008            # Out of memory
196
WRONG_HOST_INFO: int = 2009          # Wrong host info
197
LOCALHOST_CONNECTION: int = 2010     # localhost connection
198
TCP_CONNECTION: int = 2011           # TCP/IP connection
199
SERVER_HANDSHAKE_ERR: int = 2012     # Error in server handshake
200
SERVER_LOST: int = 2013              # Lost connection during query
201
COMMANDS_OUT_OF_SYNC: int = 2014     # Commands out of sync (raises ProgrammingError)
202
NAMEDPIPE_CONNECTION: int = 2015     # Named pipe connection
203
NAMEDPIPEWAIT_ERROR: int = 2016      # Named pipe wait error
204
NAMEDPIPEOPEN_ERROR: int = 2017      # Cannot open named pipe
205
NAMEDPIPESETSTATE_ERROR: int = 2018  # Cannot set named pipe state
206
CANT_READ_CHARSET: int = 2019        # Cannot read character set
207
NET_PACKET_TOO_LARGE: int = 2020    # Network packet too large
208
```
209

210
### Refresh Operation Flags
211

212
Flags for MySQL refresh operations. These constants are used internally by the MySQL server for refresh operations.
213

214
```python { .api }
215
# Refresh operation flags (from MySQLdb.constants.REFRESH)
216
GRANT: int = 1      # Refresh grant tables
217
LOG: int = 2        # Refresh log files
218
TABLES: int = 4     # Refresh table cache
219
HOSTS: int = 8      # Refresh host cache
220
STATUS: int = 16    # Refresh status variables
221
THREADS: int = 32   # Refresh thread cache
222
SLAVE: int = 64     # Refresh slave status
223
MASTER: int = 128   # Refresh master status
224
READ_LOCK: int = 16384  # Refresh with read lock
225
FAST: int = 32768   # Fast refresh
226
```
227

228
### Exception Hierarchy
229

230
Comprehensive exception classes following DB API 2.0 specification for standardized error handling.
231

232
```python { .api }
233
class MySQLError(Exception):
234
    """
235
    Base exception class for all MySQL-python errors.
236
    Root of the exception hierarchy.
237
    """
238

239
class Warning(MySQLError):
240
    """
241
    Exception for important warnings like data truncations.
242
    Not raised for typical SQL warnings.
243
    """
244

245
class Error(MySQLError):
246
    """
247
    Base class for all database-related errors.
248
    Parent class for all error conditions.
249
    """
250

251
class InterfaceError(Error):
252
    """
253
    Exception for errors in the database interface rather than database itself.
254
    Examples: parameter errors, programming interface misuse.
255
    """
256

257
class DatabaseError(Error):
258
    """
259
    Exception for errors related to the database.
260
    Parent class for all database-specific errors.
261
    """
262

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

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

275
class IntegrityError(DatabaseError):
276
    """
277
    Exception for errors regarding relational integrity of database.
278
    Examples: foreign key constraint fails, duplicate key.
279
    """
280

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

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

293
class NotSupportedError(DatabaseError):
294
    """
295
    Exception for unsupported database operations.
296
    Examples: requesting rollback on connection that doesn't support transactions.
297
    """
298
```
299

300
## Usage Examples
301

302
### Basic Error Handling
303

304
```python
305
import MySQLdb
306

307
try:
308
    db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
309
    cursor = db.cursor()
310
    
311
    cursor.execute("SELECT * FROM nonexistent_table")
312
    results = cursor.fetchall()
313
    
314
except MySQLdb.Error as e:
315
    print(f"Database error: {e}")
316
    # Handle any MySQL-related error
317
    
318
except MySQLdb.OperationalError as e:
319
    print(f"Operational error: {e}")
320
    # Handle connection/server issues
321
    
322
except MySQLdb.ProgrammingError as e:
323
    print(f"Programming error: {e}")
324
    # Handle SQL syntax errors, missing tables, etc.
325
    
326
finally:
327
    if 'cursor' in locals():
328
        cursor.close()
329
    if 'db' in locals():
330
        db.close()
331
```
332

333
### Specific Error Handling
334

335
```python
336
import MySQLdb
337
from MySQLdb.constants import ER
338

339
try:
340
    db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
341
    cursor = db.cursor()
342
    
343
    cursor.execute("INSERT INTO users (email) VALUES (%s)", ("user@example.com",))
344
    db.commit()
345
    
346
except MySQLdb.IntegrityError as e:
347
    error_code, error_message = e.args
348
    if error_code == ER.DUP_ENTRY:
349
        print("Email already exists in database")
350
    else:
351
        print(f"Integrity constraint violation: {error_message}")
352
        
353
except MySQLdb.OperationalError as e:
354
    error_code, error_message = e.args
355
    if error_code == ER.CON_COUNT_ERROR:
356
        print("Too many connections to database")
357
    elif error_code == ER.ACCESS_DENIED_ERROR:
358
        print("Access denied - check credentials")
359
    else:
360
        print(f"Operational error: {error_message}")
361

362
finally:
363
    if 'cursor' in locals():
364
        cursor.close()
365
    if 'db' in locals():
366
        db.close()
367
```
368

369
### Field Type Inspection
370

371
```python
372
import MySQLdb
373
from MySQLdb.constants import FIELD_TYPE, FLAG
374

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

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

381
for column in description:
382
    name, field_type, display_size, internal_size, precision, scale, null_ok = column
383
    
384
    print(f"Column: {name}")
385
    print(f"  Type: {field_type}")
386
    
387
    # Check specific field types
388
    if field_type == FIELD_TYPE.VARCHAR:
389
        print("  VARCHAR field")
390
    elif field_type == FIELD_TYPE.INT24:
391
        print("  MEDIUMINT field") 
392
    elif field_type == FIELD_TYPE.TIMESTAMP:
393
        print("  TIMESTAMP field")
394
    
395
    # Check field flags
396
    if null_ok & FLAG.NOT_NULL:
397
        print("  NOT NULL constraint")
398
    if null_ok & FLAG.PRI_KEY:
399
        print("  Primary key")
400
    if null_ok & FLAG.AUTO_INCREMENT:
401
        print("  Auto increment")
402
    if null_ok & FLAG.UNSIGNED:
403
        print("  Unsigned numeric")
404

405
cursor.close()
406
db.close()
407
```
408

409
### Connection Flag Usage
410

411
```python
412
import MySQLdb
413
from MySQLdb.constants import CLIENT
414

415
# Connect with specific client flags
416
db = MySQLdb.connect(
417
    host="localhost",
418
    user="user", 
419
    passwd="pass",
420
    db="test",
421
    client_flag=CLIENT.MULTI_STATEMENTS | CLIENT.MULTI_RESULTS
422
)
423

424
cursor = db.cursor()
425

426
# Now can execute multiple statements
427
cursor.execute("""
428
    INSERT INTO log (message) VALUES ('First message');
429
    INSERT INTO log (message) VALUES ('Second message');
430
    SELECT COUNT(*) FROM log;
431
""")
432

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

438
cursor.close()
439
db.close()
440
```
441

442
### Comprehensive Error Information
443

444
```python
445
import MySQLdb
446
import sys
447

448
def handle_mysql_error(e):
449
    """Comprehensive MySQL error handling with detailed information."""
450
    
451
    print(f"MySQL Error occurred: {type(e).__name__}")
452
    
453
    if hasattr(e, 'args') and len(e.args) >= 2:
454
        error_code, error_message = e.args[:2]
455
        print(f"Error Code: {error_code}")
456
        print(f"Error Message: {error_message}")
457
    else:
458
        print(f"Error: {e}")
459
    
460
    # Additional context for specific error types
461
    if isinstance(e, MySQLdb.OperationalError):
462
        print("This is an operational error - likely connection or server issue")
463
    elif isinstance(e, MySQLdb.IntegrityError):
464
        print("This is an integrity error - constraint violation")
465
    elif isinstance(e, MySQLdb.ProgrammingError):
466
        print("This is a programming error - check SQL syntax and table names")
467
    
468
    print(f"Exception traceback: {sys.exc_info()}")
469

470
try:
471
    db = MySQLdb.connect(host="localhost", user="baduser", passwd="badpass", db="test")
472
except MySQLdb.Error as e:
473
    handle_mysql_error(e)
474
```