0
# Data Type Conversion
1

2
Automatic conversion between Python and MySQL data types, including comprehensive date/time handling, binary data processing, and DB API 2.0 compliant type classification system.
3

4
## Capabilities
5

6
### Type Conversion Functions
7

8
Core functions for converting Python objects to MySQL-compatible representations.
9

10
```python { .api }
11
def Binary(x):
12
    """
13
    Convert data to binary string representation for BLOB fields.
14
    
15
    Parameters:
16
    - x (str/bytes): Data to convert to binary format
17
    
18
    Returns:
19
    str: Binary string representation
20
    """
21

22
def DateFromTicks(ticks):
23
    """
24
    Convert UNIX timestamp to date object.
25
    
26
    Parameters:
27
    - ticks (float): UNIX timestamp (seconds since epoch)
28
    
29
    Returns:
30
    datetime.date: Date object
31
    """
32

33
def TimeFromTicks(ticks):
34
    """
35
    Convert UNIX timestamp to time object.
36
    
37
    Parameters:
38
    - ticks (float): UNIX timestamp (seconds since epoch)
39
    
40
    Returns:
41
    datetime.time: Time object
42
    """
43

44
def TimestampFromTicks(ticks):
45
    """
46
    Convert UNIX timestamp to datetime object.
47
    
48
    Parameters:
49
    - ticks (float): UNIX timestamp (seconds since epoch)
50
    
51
    Returns:
52
    datetime.datetime: Datetime object
53
    """
54
```
55

56
### Date and Time Classes
57

58
Date and time type aliases and classes for MySQL compatibility.
59

60
```python { .api }
61
# Type aliases from datetime module
62
Date = datetime.date          # Date class for DATE fields
63
Time = datetime.time          # Time class for TIME fields  
64
Timestamp = datetime.datetime # Timestamp class for DATETIME/TIMESTAMP fields
65
TimeDelta = datetime.timedelta # Time delta class for time intervals
66
```
67

68
### DB API Type Sets
69

70
DB API 2.0 compliant type classification sets for type checking and comparison.
71

72
```python { .api }
73
class DBAPISet(frozenset):
74
    """
75
    Special frozenset subclass for DB API type comparisons.
76
    Allows 'type in typeset' and 'typeset == type' comparisons.
77
    """
78
    
79
    def __eq__(self, other):
80
        """
81
        Compare with individual types or other DBAPISet instances.
82
        
83
        Parameters:
84
        - other: Type constant or DBAPISet to compare
85
        
86
        Returns:
87
        bool: True if other is member of this set or sets are equal
88
        """
89

90
# DB API 2.0 type sets
91
STRING: DBAPISet     # String field types (ENUM, STRING, VAR_STRING, etc.)
92
BINARY: DBAPISet     # Binary field types (BLOB, TINY_BLOB, MEDIUM_BLOB, etc.)
93
NUMBER: DBAPISet     # Numeric field types (DECIMAL, TINY, SHORT, LONG, etc.)
94
DATE: DBAPISet       # Date field types (DATE, NEWDATE)
95
TIME: DBAPISet       # Time field types (TIME)  
96
TIMESTAMP: DBAPISet  # Timestamp field types (TIMESTAMP, DATETIME)
97
DATETIME: DBAPISet   # Alias for TIMESTAMP
98
ROWID: DBAPISet      # Row ID field types (empty set for MySQL)
99
```
100

101
### Advanced Date/Time Processing
102

103
Specialized functions for parsing and formatting date/time values.
104

105
```python { .api }
106
def DateTime_or_None(s):
107
    """
108
    Parse datetime string or return None if invalid.
109
    
110
    Parameters:
111
    - s (str): Datetime string to parse
112
    
113
    Returns:
114
    datetime.datetime/None: Parsed datetime or None
115
    """
116

117
def Date_or_None(s):
118
    """
119
    Parse date string or return None if invalid.
120
    
121
    Parameters:
122
    - s (str): Date string to parse
123
    
124
    Returns:
125
    datetime.date/None: Parsed date or None
126
    """
127

128
def Time_or_None(s):
129
    """
130
    Parse time string or return None if invalid.
131
    
132
    Parameters:
133
    - s (str): Time string to parse
134
    
135
    Returns:
136
    datetime.time/None: Parsed time or None
137
    """
138

139
def TimeDelta_or_None(s):
140
    """
141
    Parse time delta string or return None if invalid.
142
    
143
    Parameters:
144
    - s (str): Time delta string to parse
145
    
146
    Returns:
147
    datetime.timedelta/None: Parsed time delta or None
148
    """
149

150
def mysql_timestamp_converter(s):
151
    """
152
    Convert MySQL timestamp format to datetime.
153
    
154
    Parameters:
155
    - s (str): MySQL timestamp string
156
    
157
    Returns:
158
    datetime.datetime: Converted datetime object
159
    """
160
```
161

162
### Formatting Functions
163

164
Functions for converting Python date/time objects to MySQL string formats.
165

166
```python { .api }
167
def format_TIME(v):
168
    """
169
    Format time value for MySQL TIME field.
170
    
171
    Parameters:
172
    - v (datetime.time): Time value to format
173
    
174
    Returns:
175
    str: MySQL TIME format string
176
    """
177

178
def format_DATE(v):
179
    """
180
    Format date value for MySQL DATE field.
181
    
182
    Parameters:
183
    - v (datetime.date): Date value to format
184
    
185
    Returns:
186
    str: MySQL DATE format string
187
    """
188

189
def format_TIMESTAMP(d):
190
    """
191
    Format datetime value for MySQL TIMESTAMP/DATETIME field.
192
    
193
    Parameters:
194
    - d (datetime.datetime): Datetime value to format
195
    
196
    Returns:
197
    str: MySQL TIMESTAMP format string
198
    """
199

200
def format_TIMEDELTA(v):
201
    """
202
    Format timedelta value for MySQL TIME field.
203
    
204
    Parameters:
205
    - v (datetime.timedelta): Timedelta value to format
206
    
207
    Returns:
208
    str: MySQL TIME format string
209
    """
210

211
def DateTime2literal(d, c):
212
    """
213
    Convert datetime to SQL literal string.
214
    
215
    Parameters:
216
    - d (datetime.datetime): Datetime value
217
    - c: Connection object (for conversion context)
218
    
219
    Returns:
220
    str: SQL literal representation
221
    """
222

223
def DateTimeDelta2literal(d, c):
224
    """
225
    Convert timedelta to SQL literal string.
226
    
227
    Parameters:
228
    - d (datetime.timedelta): Timedelta value
229
    - c: Connection object (for conversion context)
230
    
231
    Returns:
232
    str: SQL literal representation
233
    """
234
```
235

236
### Conversion System
237

238
The converters module provides automatic type conversion between Python and MySQL.
239

240
```python { .api }
241
# Main conversion mapping (from MySQLdb.converters)
242
conversions: dict  # Dictionary mapping MySQL field types to conversion functions
243
```
244

245
## Usage Examples
246

247
### Basic Type Conversion
248

249
```python
250
import MySQLdb
251
from datetime import datetime, date, time
252

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

256
# Date/time conversion
257
now = datetime.now()
258
today = date.today()
259
current_time = time(14, 30, 0)
260

261
cursor.execute("""
262
    INSERT INTO events (name, event_date, event_time, created_at) 
263
    VALUES (%s, %s, %s, %s)
264
""", ("Meeting", today, current_time, now))
265

266
db.commit()
267
cursor.close()
268
db.close()
269
```
270

271
### Binary Data Handling
272

273
```python
274
import MySQLdb
275

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

279
# Handle binary data
280
with open("image.jpg", "rb") as f:
281
    image_data = f.read()
282

283
# Convert to MySQL binary format
284
binary_data = MySQLdb.Binary(image_data)
285

286
cursor.execute(
287
    "INSERT INTO images (name, data) VALUES (%s, %s)",
288
    ("profile.jpg", binary_data)
289
)
290

291
db.commit()
292
cursor.close()
293
db.close()
294
```
295

296
### Using UNIX Timestamps
297

298
```python
299
import MySQLdb
300
import time
301

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

305
# Create dates from UNIX timestamps
306
timestamp = time.time()
307
event_date = MySQLdb.DateFromTicks(timestamp)
308
event_time = MySQLdb.TimeFromTicks(timestamp)
309
event_datetime = MySQLdb.TimestampFromTicks(timestamp)
310

311
cursor.execute("""
312
    INSERT INTO schedule (date_only, time_only, full_datetime)
313
    VALUES (%s, %s, %s)
314
""", (event_date, event_time, event_datetime))
315

316
db.commit()
317
cursor.close()
318
db.close()
319
```
320

321
### Type Checking with DB API Sets
322

323
```python
324
import MySQLdb
325
from MySQLdb.constants import FIELD_TYPE
326

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

330
cursor.execute("SELECT id, name, email, created_at FROM users LIMIT 1")
331
description = cursor.description
332

333
for column in description:
334
    field_name = column[0]
335
    field_type = column[1]
336
    
337
    # Check field type using DB API sets
338
    if field_type in MySQLdb.STRING:
339
        print(f"{field_name}: String field")
340
    elif field_type in MySQLdb.NUMBER:
341
        print(f"{field_name}: Numeric field")
342
    elif field_type in MySQLdb.TIMESTAMP:
343
        print(f"{field_name}: Timestamp field")
344
    elif field_type == MySQLdb.TIMESTAMP:  # Alternative comparison
345
        print(f"{field_name}: Timestamp field (alternative check)")
346

347
cursor.close()
348
db.close()
349
```
350

351
### Custom Type Conversion
352

353
```python
354
import MySQLdb
355
from MySQLdb.converters import conversions
356
from MySQLdb.constants import FIELD_TYPE
357
import json
358

359
# Custom converter for JSON fields
360
def json_converter(data):
361
    if data is None:
362
        return None
363
    return json.loads(data)
364

365
# Create custom conversion dictionary
366
custom_conv = conversions.copy()
367
custom_conv[FIELD_TYPE.BLOB] = json_converter
368

369
# Use custom converter
370
db = MySQLdb.connect(
371
    host="localhost", 
372
    user="user", 
373
    passwd="pass", 
374
    db="test",
375
    conv=custom_conv
376
)
377

378
cursor = db.cursor()
379
cursor.execute("SELECT settings FROM user_preferences WHERE user_id = %s", (1,))
380
settings = cursor.fetchone()[0]  # Automatically converted from JSON
381

382
print(f"User settings: {settings}")
383
cursor.close()
384
db.close()
385
```
386

387
### Working with Time Zones
388

389
```python
390
import MySQLdb
391
from datetime import datetime, timezone, timedelta
392

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

396
# Create timezone-aware datetime
397
eastern = timezone(timedelta(hours=-5))
398
event_time = datetime(2023, 12, 25, 15, 30, 0, tzinfo=eastern)
399

400
# MySQL will store as UTC or local time depending on configuration
401
cursor.execute(
402
    "INSERT INTO events (name, scheduled_at) VALUES (%s, %s)",
403
    ("Holiday Party", event_time)
404
)
405

406
db.commit()
407
cursor.close()
408
db.close()
409
```
410

411
### Handling NULL Values
412

413
```python
414
import MySQLdb
415

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

419
# NULL values are automatically converted to Python None
420
cursor.execute("SELECT name, description FROM products WHERE id = %s", (1,))
421
row = cursor.fetchone()
422

423
name, description = row
424
if description is None:
425
    print(f"Product '{name}' has no description")
426
else:
427
    print(f"Product '{name}': {description}")
428

429
cursor.close()
430
db.close()
431
```
432

433
## Additional Type Converter Functions
434

435
Additional conversion functions for specialized data types and custom conversion scenarios.
436

437
### String and Primitive Type Converters
438

439
```python { .api }
440
def Bool2Str(s, d):
441
    """
442
    Convert boolean to MySQL string representation.
443
    
444
    Parameters:
445
    - s (bool): Boolean value to convert
446
    - d: Conversion mapping dictionary
447
    
448
    Returns:
449
    str: "1" for True, "0" for False
450
    """
451

452
def Thing2Str(s, d):
453
    """
454
    Convert any object to string via str().
455
    
456
    Parameters:
457
    - s: Object to convert
458
    - d: Conversion mapping dictionary
459
    
460
    Returns:
461
    str: String representation of object
462
    """
463

464
def Float2Str(o, d):
465
    """
466
    Convert float to high-precision string representation.
467
    
468
    Parameters:
469
    - o (float): Float value to convert
470
    - d: Conversion mapping dictionary
471
    
472
    Returns:
473
    str: String with 15 decimal places precision
474
    """
475

476
def None2NULL(o, d):
477
    """
478
    Convert Python None to MySQL NULL.
479
    
480
    Parameters:
481
    - o: None value
482
    - d: Conversion mapping dictionary
483
    
484
    Returns:
485
    NULL: MySQL NULL constant
486
    """
487

488
def Thing2Literal(o, d):
489
    """
490
    Convert object to SQL string literal with proper quoting.
491
    
492
    Parameters:
493
    - o: Object to convert to literal
494
    - d: Conversion mapping dictionary
495
    
496
    Returns:
497
    str: Quoted SQL literal string
498
    """
499

500
def Unicode2Str(s, d):
501
    """
502
    Convert Unicode string to byte string using connection encoding.
503
    
504
    Parameters:
505
    - s (unicode): Unicode string to convert
506
    - d: Conversion mapping dictionary
507
    
508
    Returns:
509
    str: Encoded byte string
510
    """
511
```
512

513
### Set and Collection Converters
514

515
```python { .api }
516
def Str2Set(s):
517
    """
518
    Convert comma-separated string to Python set.
519
    
520
    Parameters:
521
    - s (str): Comma-separated string values
522
    
523
    Returns:
524
    set: Set of string values
525
    """
526

527
def Set2Str(s, d):
528
    """
529
    Convert Python set to comma-separated MySQL SET string.
530
    
531
    Parameters:
532
    - s (set): Set of values to convert
533
    - d: Conversion mapping dictionary
534
    
535
    Returns:
536
    str: Quoted comma-separated string literal
537
    """
538

539
def quote_tuple(t, d):
540
    """
541
    Convert tuple to SQL tuple literal with proper escaping.
542
    
543
    Parameters:
544
    - t (tuple): Tuple to convert
545
    - d: Conversion mapping dictionary
546
    
547
    Returns:
548
    str: SQL tuple literal like "(value1, value2, ...)"
549
    """
550
```
551

552
### Array and Binary Data Converters
553

554
```python { .api }
555
def array2Str(o, d):
556
    """
557
    Convert array.array to SQL string literal.
558
    
559
    Parameters:
560
    - o (array.array): Array object to convert
561
    - d: Conversion mapping dictionary
562
    
563
    Returns:
564
    str: SQL string literal of array contents
565
    """
566

567
def char_array(s):
568
    """
569
    Create character array from string.
570
    
571
    Parameters:
572
    - s (str): String to convert to character array
573
    
574
    Returns:
575
    array.array: Character array
576
    """
577
```
578

579
### Instance and Class Converters
580

581
```python { .api }
582
def Instance2Str(o, d):
583
    """
584
    Convert class instance to string representation.
585
    
586
    Searches conversion mapping for the exact class or compatible
587
    base class, falling back to string conversion if no specific
588
    converter is found.
589
    
590
    Parameters:
591
    - o: Class instance to convert
592
    - d: Conversion mapping dictionary
593
    
594
    Returns:
595
    str: String representation of instance
596
    """
597
```
598

599
### Usage Examples
600

601
```python
602
from MySQLdb import converters
603

604
# Boolean conversion
605
result = converters.Bool2Str(True, {})
606
print(result)  # "1"
607

608
# Set conversion
609
mysql_set = "red,green,blue"
610
python_set = converters.Str2Set(mysql_set)
611
print(python_set)  # {'red', 'green', 'blue'}
612

613
# Convert back to MySQL format
614
mysql_format = converters.Set2Str(python_set, converters.conversions)
615
print(mysql_format)  # "'red,green,blue'"
616

617
# Tuple conversion for IN clauses
618
values = (1, 2, 3, 4)
619
sql_tuple = converters.quote_tuple(values, converters.conversions)
620
print(sql_tuple)  # "(1, 2, 3, 4)"
621

622
# Array conversion
623
import array
624
char_data = array.array('c', b'hello')
625
sql_string = converters.array2Str(char_data, converters.conversions)
626
print(sql_string)  # "'hello'"
627
```