0
# Data Types and Conversion
1

2
Handle data type conversion between Python and MySQL with comprehensive type support, custom type definitions, and DB-API 2.0 compliance.
3

4
## DB-API 2.0 Type Constructors
5

6
### Date and Time Constructors
7

8
```python { .api }
9
# Type aliases for standard datetime objects
10
Date = datetime.date
11
"""Alias for datetime.date class. Use as constructor: Date(year, month, day)"""
12

13
Time = datetime.time  
14
"""Alias for datetime.time class. Use as constructor: Time(hour, minute, second, microsecond=0)"""
15

16
Timestamp = datetime.datetime
17
"""Alias for datetime.datetime class. Use as constructor: Timestamp(year, month, day, hour=0, minute=0, second=0, microsecond=0)"""
18
```
19

20
### Timestamp Constructors from Unix Time
21

22
```python { .api }
23
def DateFromTicks(ticks: int) -> datetime.date:
24
    """
25
    Create date from Unix timestamp.
26
    
27
    Args:
28
        ticks: Unix timestamp (seconds since epoch)
29
        
30
    Returns:
31
        datetime.date object
32
    """
33
    pass
34

35
def TimeFromTicks(ticks: int) -> datetime.time:
36
    """
37
    Create time from Unix timestamp.
38
    
39
    Args:
40
        ticks: Unix timestamp (seconds since epoch)
41
        
42
    Returns:
43
        datetime.time object (time portion only)
44
    """
45
    pass
46

47
def TimestampFromTicks(ticks: int) -> datetime.datetime:
48
    """
49
    Create datetime from Unix timestamp.
50
    
51
    Args:
52
        ticks: Unix timestamp (seconds since epoch)
53
        
54
    Returns:
55
        datetime.datetime object
56
    """
57
    pass
58
```
59

60
### Binary Data Constructor
61

62
```python { .api }
63
Binary = bytes
64
"""Alias for bytes class. Use as constructor: Binary(data)"""
65
```
66

67
## DB-API 2.0 Type Objects
68

69
Type objects for comparing column types in cursor descriptions:
70

71
```python { .api }
72
# Type constants for column type comparison
73
STRING: Type = object()      # String types (VARCHAR, CHAR, TEXT, etc.)
74
BINARY: Type = object()      # Binary types (BLOB, BINARY, VARBINARY, etc.)
75
NUMBER: Type = object()      # Numeric types (INT, DECIMAL, FLOAT, etc.)
76
DATETIME: Type = object()    # Date/time types (DATE, TIME, DATETIME, TIMESTAMP)
77
ROWID: Type = object()       # Row identifier types
78
```
79

80
## Custom Types
81

82
### HexLiteral
83

84
```python { .api }
85
class HexLiteral:
86
    """
87
    Represents MySQL hexadecimal literals for binary data.
88
    Used for inserting binary data as hex strings in SQL.
89
    """
90
    
91
    def __init__(self, value: Union[str, bytes]) -> None:
92
        """
93
        Initialize hex literal.
94
        
95
        Args:
96
            value: String or bytes to represent as hex literal
97
        """
98
        pass
99
    
100
    @property
101
    def value(self) -> bytes:
102
        """Get binary value."""
103
        pass
104
    
105
    def __str__(self) -> str:
106
        """String representation as hex literal."""
107
        pass
108
    
109
    def __repr__(self) -> str:
110
        """Developer representation."""
111
        pass
112
```
113

114
## Type Conversion Classes
115

116
### MySQLConverterBase
117

118
```python { .api }
119
class MySQLConverterBase:
120
    """
121
    Base class for data converters with type mapping functionality.
122
    Defines interface for converting between Python and MySQL types.
123
    """
124
    
125
    def __init__(self, charset: str = 'utf8mb4', use_unicode: bool = True) -> None:
126
        """Initialize converter with character set configuration."""
127
        pass
128
    
129
    def escape(self, value: Any) -> Union[str, bytes]:
130
        """
131
        Escape value for safe inclusion in SQL statements.
132
        
133
        Args:
134
            value: Python value to escape
135
            
136
        Returns:
137
            Escaped string or bytes suitable for SQL
138
        """
139
        pass
140
    
141
    def quote(self, value: Any) -> str:
142
        """
143
        Quote value for SQL statements.
144
        
145
        Args:
146
            value: Python value to quote
147
            
148
        Returns:
149
            Quoted string for SQL inclusion
150
        """
151
        pass
152
```
153

154
### MySQLConverter
155

156
```python { .api }
157
class MySQLConverter(MySQLConverterBase):
158
    """
159
    Main data type converter between Python and MySQL types.
160
    Handles bidirectional conversion with proper type mapping.
161
    """
162
    
163
    def __init__(self, charset: str = 'utf8mb4', use_unicode: bool = True) -> None:
164
        """Initialize converter with character set and Unicode settings."""
165
        pass
166
    
167
    def to_mysql(self, value: Any) -> Union[str, bytes, None]:
168
        """
169
        Convert Python value to MySQL format.
170
        
171
        Args:
172
            value: Python value to convert
173
            
174
        Returns:
175
            MySQL-compatible value
176
        """
177
        pass
178
    
179
    def from_mysql(self, value: Any, type_name: str) -> Any:
180
        """
181
        Convert MySQL value to Python type.
182
        
183
        Args:
184
            value: MySQL value to convert
185
            type_name: MySQL column type name
186
            
187
        Returns:
188
            Python value with appropriate type
189
        """
190
        pass
191
    
192
    # Specific type converters
193
    def _str_to_mysql(self, value: str) -> bytes:
194
        """Convert string to MySQL bytes."""
195
        pass
196
    
197
    def _bytes_to_mysql(self, value: bytes) -> bytes:
198
        """Convert bytes to MySQL format.""" 
199
        pass
200
    
201
    def _int_to_mysql(self, value: int) -> str:
202
        """Convert integer to MySQL string."""
203
        pass
204
    
205
    def _float_to_mysql(self, value: float) -> str:
206
        """Convert float to MySQL string."""
207
        pass
208
    
209
    def _decimal_to_mysql(self, value: Decimal) -> str:
210
        """Convert Decimal to MySQL string."""
211
        pass
212
    
213
    def _bool_to_mysql(self, value: bool) -> str:
214
        """Convert boolean to MySQL string."""
215
        pass
216
    
217
    def _datetime_to_mysql(self, value: datetime.datetime) -> str:
218
        """Convert datetime to MySQL format."""
219
        pass
220
    
221
    def _date_to_mysql(self, value: datetime.date) -> str:
222
        """Convert date to MySQL format."""
223
        pass
224
    
225
    def _time_to_mysql(self, value: datetime.time) -> str:
226
        """Convert time to MySQL format."""
227
        pass
228
    
229
    def _timedelta_to_mysql(self, value: datetime.timedelta) -> str:
230
        """Convert timedelta to MySQL TIME format."""
231
        pass
232
    
233
    def _none_to_mysql(self, value: None) -> str:
234
        """Convert None to MySQL NULL."""
235
        pass
236
    
237
    # MySQL to Python converters
238
    def _mysql_to_python_datetime(self, value: bytes, dsc: Any = None) -> datetime.datetime:
239
        """Convert MySQL DATETIME to Python datetime."""
240
        pass
241
    
242
    def _mysql_to_python_date(self, value: bytes, dsc: Any = None) -> datetime.date:
243
        """Convert MySQL DATE to Python date."""
244
        pass
245
    
246
    def _mysql_to_python_time(self, value: bytes, dsc: Any = None) -> datetime.time:
247
        """Convert MySQL TIME to Python time."""
248
        pass
249
    
250
    def _mysql_to_python_timestamp(self, value: bytes, dsc: Any = None) -> datetime.datetime:
251
        """Convert MySQL TIMESTAMP to Python datetime."""
252
        pass
253
    
254
    def _mysql_to_python_decimal(self, value: bytes, dsc: Any = None) -> Decimal:
255
        """Convert MySQL DECIMAL to Python Decimal."""
256
        pass
257
    
258
    def _mysql_to_python_int(self, value: bytes, dsc: Any = None) -> int:
259
        """Convert MySQL integer to Python int."""
260
        pass
261
    
262
    def _mysql_to_python_float(self, value: bytes, dsc: Any = None) -> float:
263
        """Convert MySQL float to Python float."""
264
        pass
265
    
266
    def _mysql_to_python_bit(self, value: bytes, dsc: Any = None) -> int:
267
        """Convert MySQL BIT to Python int."""
268
        pass
269
    
270
    def _mysql_to_python_json(self, value: bytes, dsc: Any = None) -> Union[Dict, List, str, int, float, bool, None]:
271
        """Convert MySQL JSON to Python object."""
272
        pass
273
```
274

275
## Type Aliases and Definitions
276

277
```python { .api }
278
# Type aliases for type hints
279
StrOrBytes = Union[str, bytes]
280
MySQLConvertibleType = Union[str, bytes, int, float, bool, datetime.datetime, datetime.date, datetime.time, Decimal, None]
281
DescriptionType = Tuple[str, int, Optional[int], Optional[int], Optional[int], Optional[int], bool]
282
RowType = Union[Tuple[Any, ...], Dict[str, Any]]
283
ParamsSequenceType = Union[Tuple[Any, ...], List[Any]]
284
ParamsDictType = Dict[str, Any]
285
```
286

287
## Usage Examples
288

289
### Basic Type Usage
290

291
```python
292
import mysql.connector
293
from mysql.connector import Date, Time, Timestamp, Binary
294
import datetime
295

296
# Using DB-API type constructors
297
connection = mysql.connector.connect(
298
    host='localhost',
299
    user='myuser',
300
    password='mypassword',
301
    database='mydatabase'
302
)
303

304
cursor = connection.cursor()
305

306
# Insert with type constructors
307
insert_query = """
308
INSERT INTO events (name, event_date, event_time, created_at, binary_data)
309
VALUES (%s, %s, %s, %s, %s)
310
"""
311

312
data = (
313
    'Conference',
314
    Date(2024, 12, 25),                    # DATE column
315
    Time(14, 30, 0),                       # TIME column  
316
    Timestamp(2024, 1, 15, 10, 30, 45),   # DATETIME column
317
    Binary(b'binary data content')         # BLOB column
318
)
319

320
cursor.execute(insert_query, data)
321
connection.commit()
322

323
cursor.close()
324
connection.close()
325
```
326

327
### Timestamp from Unix Time
328

329
```python
330
import mysql.connector
331
from mysql.connector import DateFromTicks, TimeFromTicks, TimestampFromTicks
332
import time
333

334
connection = mysql.connector.connect(
335
    host='localhost',
336
    user='myuser',
337
    password='mypassword',
338
    database='mydatabase'
339
)
340

341
cursor = connection.cursor()
342

343
# Current Unix timestamp
344
current_timestamp = time.time()
345

346
# Convert to DB-API types
347
current_date = DateFromTicks(current_timestamp)
348
current_time = TimeFromTicks(current_timestamp)
349
current_datetime = TimestampFromTicks(current_timestamp)
350

351
cursor.execute("""
352
    INSERT INTO log_entries (log_date, log_time, created_at)
353
    VALUES (%s, %s, %s)
354
""", (current_date, current_time, current_datetime))
355

356
connection.commit()
357
cursor.close()
358
connection.close()
359
```
360

361
### Using HexLiteral
362

363
```python
364
import mysql.connector
365
from mysql.connector.custom_types import HexLiteral
366

367
connection = mysql.connector.connect(
368
    host='localhost',
369
    user='myuser',
370
    password='mypassword',
371
    database='mydatabase'
372
)
373

374
cursor = connection.cursor()
375

376
# Binary data as hex literal
377
binary_data = b'\x00\x01\x02\x03\xFF'
378
hex_literal = HexLiteral(binary_data)
379

380
cursor.execute("INSERT INTO binary_table (data) VALUES (%s)", (hex_literal,))
381
connection.commit()
382

383
cursor.close()
384
connection.close()
385
```
386

387
### Type Checking with Type Objects
388

389
```python
390
import mysql.connector
391
from mysql.connector import STRING, BINARY, NUMBER, DATETIME, ROWID
392

393
connection = mysql.connector.connect(
394
    host='localhost',
395
    user='myuser',
396
    password='mypassword',
397
    database='mydatabase'
398
)
399

400
cursor = connection.cursor()
401
cursor.execute("SELECT id, name, email, created_at, profile_pic FROM users LIMIT 1")
402

403
# Check column types using description
404
for i, column in enumerate(cursor.description):
405
    column_name = column[0]
406
    column_type = column[1]
407
    
408
    if column_type in NUMBER:
409
        print(f"{column_name}: Numeric column")
410
    elif column_type in STRING:
411
        print(f"{column_name}: String column")
412
    elif column_type in DATETIME:
413
        print(f"{column_name}: Date/time column")
414
    elif column_type in BINARY:
415
        print(f"{column_name}: Binary column")
416
    elif column_type in ROWID:
417
        print(f"{column_name}: Row ID column")
418
    else:
419
        print(f"{column_name}: Other type")
420

421
cursor.close()
422
connection.close()
423
```
424

425
### Custom Converter Usage
426

427
```python
428
import mysql.connector
429
from mysql.connector.conversion import MySQLConverter
430
import datetime
431
from decimal import Decimal
432

433
# Create custom converter
434
converter = MySQLConverter(charset='utf8mb4', use_unicode=True)
435

436
# Python to MySQL conversion examples
437
python_values = [
438
    'Hello World',
439
    42,
440
    3.14159,
441
    Decimal('999.99'),
442
    True,
443
    datetime.datetime(2024, 1, 15, 10, 30, 45),
444
    datetime.date(2024, 1, 15),
445
    datetime.time(10, 30, 45),
446
    None,
447
    b'binary data'
448
]
449

450
print("Python to MySQL conversions:")
451
for value in python_values:
452
    mysql_value = converter.to_mysql(value)
453
    print(f"{repr(value)} -> {repr(mysql_value)}")
454

455
# MySQL to Python conversion (conceptual - would use actual MySQL data)
456
print("\nMySQL to Python conversions:")
457
mysql_data = [
458
    (b'2024-01-15 10:30:45', 'DATETIME'),
459
    (b'2024-01-15', 'DATE'),
460
    (b'10:30:45', 'TIME'),
461
    (b'123.45', 'DECIMAL'),
462
    (b'42', 'BIGINT'),
463
    (b'3.14159', 'DOUBLE'),
464
    (b'1', 'TINYINT'),
465
    (b'Hello World', 'VARCHAR')
466
]
467

468
for mysql_value, type_name in mysql_data:
469
    python_value = converter.from_mysql(mysql_value, type_name)
470
    print(f"{repr(mysql_value)} ({type_name}) -> {repr(python_value)} ({type(python_value).__name__})")
471
```
472

473
### Handling JSON Data
474

475
```python
476
import mysql.connector
477
import json
478

479
connection = mysql.connector.connect(
480
    host='localhost',
481
    user='myuser',
482
    password='mypassword', 
483
    database='mydatabase'
484
)
485

486
cursor = connection.cursor()
487

488
# Insert JSON data
489
user_profile = {
490
    'preferences': {
491
        'theme': 'dark',
492
        'language': 'en',
493
        'notifications': True
494
    },
495
    'settings': {
496
        'timezone': 'UTC',
497
        'date_format': 'YYYY-MM-DD'
498
    }
499
}
500

501
cursor.execute(
502
    "INSERT INTO user_profiles (user_id, profile_data) VALUES (%s, %s)",
503
    (123, json.dumps(user_profile))
504
)
505

506
# Retrieve and parse JSON data
507
cursor.execute("SELECT profile_data FROM user_profiles WHERE user_id = %s", (123,))
508
result = cursor.fetchone()
509

510
if result:
511
    # MySQL Connector automatically converts JSON columns to Python objects
512
    profile_data = result[0]
513
    if isinstance(profile_data, str):
514
        profile_data = json.loads(profile_data)
515
    
516
    print(f"Theme: {profile_data['preferences']['theme']}")
517
    print(f"Language: {profile_data['preferences']['language']}")
518

519
connection.commit()
520
cursor.close()
521
connection.close()
522
```
523

524
### Working with Large Numbers
525

526
```python
527
import mysql.connector
528
from decimal import Decimal
529

530
connection = mysql.connector.connect(
531
    host='localhost',
532
    user='myuser',
533
    password='mypassword',
534
    database='mydatabase'
535
)
536

537
cursor = connection.cursor()
538

539
# Use Decimal for precise financial calculations
540
price = Decimal('1999.99')
541
tax_rate = Decimal('0.08')
542
total = price * (1 + tax_rate)
543

544
cursor.execute("""
545
    INSERT INTO orders (item_price, tax_rate, total_amount)
546
    VALUES (%s, %s, %s)
547
""", (price, tax_rate, total))
548

549
# Retrieve Decimal values
550
cursor.execute("SELECT item_price, tax_rate, total_amount FROM orders ORDER BY id DESC LIMIT 1")
551
result = cursor.fetchone()
552

553
if result:
554
    item_price, tax_rate, total_amount = result
555
    print(f"Item Price: {item_price} (type: {type(item_price).__name__})")
556
    print(f"Tax Rate: {tax_rate} (type: {type(tax_rate).__name__})")
557
    print(f"Total: {total_amount} (type: {type(total_amount).__name__})")
558

559
connection.commit()
560
cursor.close()
561
connection.close()
562
```
563

564
### Date/Time Handling with Timezones
565

566
```python
567
import mysql.connector
568
import datetime
569
import pytz
570

571
connection = mysql.connector.connect(
572
    host='localhost',
573
    user='myuser',
574
    password='mypassword',
575
    database='mydatabase',
576
    time_zone='+00:00'  # Set connection timezone to UTC
577
)
578

579
cursor = connection.cursor()
580

581
# Create timezone-aware datetime
582
utc = pytz.UTC
583
eastern = pytz.timezone('US/Eastern')
584

585
# Current time in different timezones
586
now_utc = datetime.datetime.now(utc)
587
now_eastern = now_utc.astimezone(eastern)
588

589
# Store as UTC in database
590
cursor.execute("""
591
    INSERT INTO events (name, event_time_utc, event_time_local)
592
    VALUES (%s, %s, %s)
593
""", ('Meeting', now_utc.replace(tzinfo=None), now_eastern.replace(tzinfo=None)))
594

595
# Retrieve and handle timezone conversion
596
cursor.execute("SELECT event_time_utc FROM events ORDER BY id DESC LIMIT 1")
597
result = cursor.fetchone()
598

599
if result:
600
    # Add timezone info back after retrieval
601
    event_time_utc = result[0].replace(tzinfo=utc)
602
    event_time_eastern = event_time_utc.astimezone(eastern)
603
    
604
    print(f"Event time UTC: {event_time_utc}")
605
    print(f"Event time Eastern: {event_time_eastern}")
606

607
connection.commit()
608
cursor.close()
609
connection.close()
610
```