0
# Data Type Handling
1

2
Comprehensive MySQL data type support with automatic conversion between Python and MySQL types, including support for dates, times, binary data, and JSON.
3

4
## Capabilities
5

6
### String Escaping and Conversion
7

8
Functions for safely escaping and converting string data to prevent SQL injection and ensure proper data encoding.
9

10
```python { .api }
11
def escape_string(value):
12
    """
13
    Escape a string value for safe SQL inclusion.
14
    
15
    Parameters:
16
    - value (str): String value to escape
17
    
18
    Returns:
19
    str: Escaped string with quotes
20
    """
21

22
def escape_dict(val, charset):
23
    """
24
    Escape all values in a dictionary.
25
    
26
    Parameters:
27
    - val (dict): Dictionary with values to escape
28
    - charset (str): Character set for encoding
29
    
30
    Returns:
31
    dict: Dictionary with escaped values
32
    """
33

34
def escape_sequence(val, charset):
35
    """
36
    Escape a sequence of values for SQL tuple.
37
    
38
    Parameters:
39
    - val (sequence): Sequence of values to escape
40
    - charset (str): Character set for encoding
41
    
42
    Returns:
43
    str: Escaped sequence as SQL tuple string
44
    """
45
```
46

47
### Binary Data Handling
48

49
Support for MySQL binary data types including BLOB fields and binary strings.
50

51
```python { .api }
52
def Binary(x):
53
    """
54
    Create a binary data object for MySQL BLOB fields.
55
    
56
    Parameters:
57
    - x: Data to convert to binary
58
    
59
    Returns:
60
    bytes: Binary data object
61
    """
62
```
63

64
### Date and Time Types
65

66
Python datetime integration with MySQL temporal data types.
67

68
```python { .api }
69
Date = date
70
"""Alias for datetime.date - represents MySQL DATE type."""
71

72
Time = time  
73
"""Alias for datetime.time - represents MySQL TIME type."""
74

75
Timestamp = datetime
76
"""Alias for datetime.datetime - represents MySQL DATETIME/TIMESTAMP types."""
77

78
def DateFromTicks(ticks):
79
    """
80
    Create a date object from Unix timestamp.
81
    
82
    Parameters:
83
    - ticks (float): Unix timestamp
84
    
85
    Returns:
86
    date: Date object from timestamp
87
    """
88

89
def TimeFromTicks(ticks):
90
    """
91
    Create a time object from Unix timestamp.
92
    
93
    Parameters:
94
    - ticks (float): Unix timestamp
95
    
96
    Returns:
97
    time: Time object from timestamp
98
    """
99

100
def TimestampFromTicks(ticks):
101
    """
102
    Create a datetime object from Unix timestamp.
103
    
104
    Parameters:
105
    - ticks (float): Unix timestamp
106
    
107
    Returns:
108
    datetime: Datetime object from timestamp
109
    """
110
```
111

112
### MySQL Field Type Constants
113

114
Database API 2.0 compliant field type constants for result set column identification.
115

116
```python { .api }
117
# Field type constants from cymysql.constants.FIELD_TYPE
118
DECIMAL = 0
119
TINY = 1
120
SHORT = 2
121
LONG = 3
122
FLOAT = 4
123
DOUBLE = 5
124
NULL = 6
125
TIMESTAMP = 7
126
LONGLONG = 8
127
INT24 = 9
128
DATE = 10
129
TIME = 11
130
DATETIME = 12
131
YEAR = 13
132
NEWDATE = 14
133
VARCHAR = 15
134
BIT = 16
135
VECTOR = 242
136
JSON = 245
137
NEWDECIMAL = 246
138
ENUM = 247
139
SET = 248
140
TINY_BLOB = 249
141
MEDIUM_BLOB = 250
142
LONG_BLOB = 251
143
BLOB = 252
144
VAR_STRING = 253
145
STRING = 254
146
GEOMETRY = 255
147

148
# Type aliases
149
CHAR = TINY
150
INTERVAL = ENUM
151
```
152

153
### DB-API Type Sets
154

155
Sets of field types grouped by category for type checking and conversion.
156

157
```python { .api }
158
class DBAPISet(frozenset):
159
    """
160
    Special frozenset for DB-API type comparison.
161
    
162
    Supports 'in' operator and equality comparison with individual types.
163
    """
164
    
165
    def __eq__(self, other):
166
        """Check if type is in this set."""
167
        
168
    def __ne__(self, other):
169
        """Check if type is not in this set."""
170

171
STRING = DBAPISet([FIELD_TYPE.ENUM, FIELD_TYPE.STRING, FIELD_TYPE.VAR_STRING])
172
"""Set of string-like field types."""
173

174
BINARY = DBAPISet([FIELD_TYPE.BLOB, FIELD_TYPE.LONG_BLOB, 
175
                  FIELD_TYPE.MEDIUM_BLOB, FIELD_TYPE.TINY_BLOB])
176
"""Set of binary field types."""
177

178
NUMBER = DBAPISet([FIELD_TYPE.DECIMAL, FIELD_TYPE.DOUBLE, FIELD_TYPE.FLOAT,
179
                  FIELD_TYPE.INT24, FIELD_TYPE.LONG, FIELD_TYPE.LONGLONG,
180
                  FIELD_TYPE.TINY, FIELD_TYPE.YEAR])
181
"""Set of numeric field types."""
182

183
DATE = DBAPISet([FIELD_TYPE.DATE, FIELD_TYPE.NEWDATE])
184
"""Set of date field types."""
185

186
TIME = DBAPISet([FIELD_TYPE.TIME])
187
"""Set of time field types."""
188

189
TIMESTAMP = DBAPISet([FIELD_TYPE.TIMESTAMP, FIELD_TYPE.DATETIME])
190
"""Set of timestamp/datetime field types."""
191

192
DATETIME = TIMESTAMP
193
"""Alias for TIMESTAMP type set."""
194

195
ROWID = DBAPISet()
196
"""Empty set - MySQL doesn't have ROWID."""
197
```
198

199
### Type Conversion System
200

201
CyMySQL automatically converts between Python and MySQL data types using built-in converters.
202

203
```python { .api }
204
# Built-in converter functions (from cymysql.converters module)
205
def escape_bool(value):
206
    """Convert Python bool to MySQL boolean representation."""
207

208
def escape_int(value):
209
    """Convert Python int to MySQL integer representation."""  
210

211
def escape_float(value):
212
    """Convert Python float to MySQL float representation."""
213

214
def escape_bytes(value):
215
    """Convert Python bytes to MySQL binary representation."""
216
```
217

218
## Usage Examples
219

220
### Working with Date and Time
221

222
```python
223
import cymysql
224
from datetime import date, datetime, time
225

226
conn = cymysql.connect(host='localhost', user='root', db='test')
227
cursor = conn.cursor()
228

229
# Insert date/time data
230
current_date = date.today()
231
current_time = datetime.now()
232

233
cursor.execute(
234
    "INSERT INTO events (event_date, event_timestamp) VALUES (%s, %s)",
235
    (current_date, current_time)
236
)
237

238
# Query date/time data
239
cursor.execute("SELECT event_date, event_timestamp FROM events")
240
for row in cursor.fetchall():
241
    event_date, event_timestamp = row
242
    print(f"Date: {event_date}, Timestamp: {event_timestamp}")
243

244
conn.commit()
245
cursor.close()
246
conn.close()
247
```
248

249
### Binary Data Handling
250

251
```python
252
import cymysql
253

254
conn = cymysql.connect(host='localhost', user='root', db='test')
255
cursor = conn.cursor()
256

257
# Store binary data
258
with open('image.jpg', 'rb') as f:
259
    image_data = f.read()
260

261
binary_data = cymysql.Binary(image_data)
262
cursor.execute(
263
    "INSERT INTO images (name, data) VALUES (%s, %s)",
264
    ('photo.jpg', binary_data)
265
)
266

267
# Retrieve binary data
268
cursor.execute("SELECT name, data FROM images WHERE name = %s", ('photo.jpg',))
269
name, data = cursor.fetchone()
270

271
with open('retrieved_image.jpg', 'wb') as f:
272
    f.write(data)
273

274
conn.commit()
275
cursor.close()
276
conn.close()
277
```
278

279
### Type Checking with Field Types
280

281
```python
282
import cymysql
283
from cymysql import FIELD_TYPE, STRING, NUMBER, DATE
284

285
conn = cymysql.connect(host='localhost', user='root', db='test')
286
cursor = conn.cursor()
287

288
cursor.execute("SELECT id, name, salary, hire_date FROM employees LIMIT 1")
289

290
# Check column types
291
for i, desc in enumerate(cursor.description):
292
    column_name = desc[0]
293
    field_type = desc[1]
294
    
295
    print(f"Column: {column_name}")
296
    
297
    if field_type in STRING:
298
        print("  Type: String")
299
    elif field_type in NUMBER:
300
        print("  Type: Number")
301
    elif field_type in DATE:
302
        print("  Type: Date")
303
    elif field_type == FIELD_TYPE.TIMESTAMP:
304
        print("  Type: Timestamp")
305
    else:
306
        print(f"  Type: Unknown ({field_type})")
307

308
cursor.close()
309
conn.close()
310
```
311

312
### Safe String Escaping
313

314
```python
315
import cymysql
316

317
# Manual escaping (not recommended - use parameterized queries instead)
318
unsafe_input = "'; DROP TABLE users; --"
319
safe_string = cymysql.escape_string(unsafe_input)
320
print(f"Escaped: {safe_string}")
321

322
# Proper parameterized query (recommended)
323
conn = cymysql.connect(host='localhost', user='root', db='test')
324
cursor = conn.cursor()
325

326
user_input = "O'Reilly"
327
cursor.execute("SELECT * FROM users WHERE name = %s", (user_input,))
328
# CyMySQL automatically escapes the parameter safely
329

330
cursor.close()
331
conn.close()
332
```
333

334
### Working with JSON Data
335

336
```python
337
import cymysql
338
import json
339

340
conn = cymysql.connect(host='localhost', user='root', db='test')
341
cursor = conn.cursor()
342

343
# Store JSON data
344
user_profile = {
345
    'preferences': {'theme': 'dark', 'language': 'en'},
346
    'settings': {'notifications': True, 'email_updates': False}
347
}
348

349
cursor.execute(
350
    "INSERT INTO users (name, profile) VALUES (%s, %s)",
351
    ('john_doe', json.dumps(user_profile))
352
)
353

354
# Retrieve and parse JSON data
355
cursor.execute("SELECT name, profile FROM users WHERE name = %s", ('john_doe',))
356
name, profile_json = cursor.fetchone()
357

358
profile = json.loads(profile_json) if profile_json else {}
359
print(f"User: {name}")
360
print(f"Theme: {profile.get('preferences', {}).get('theme')}")
361

362
conn.commit()
363
cursor.close()
364
conn.close()
365
```
366

367
### Handling NULL Values
368

369
```python
370
import cymysql
371

372
conn = cymysql.connect(host='localhost', user='root', db='test')
373
cursor = conn.cursor()
374

375
# Insert NULL values
376
cursor.execute(
377
    "INSERT INTO users (name, email, phone) VALUES (%s, %s, %s)",
378
    ('John Doe', 'john@example.com', None)  # None becomes NULL
379
)
380

381
# Query with NULL handling
382
cursor.execute("SELECT name, email, phone FROM users")
383
for row in cursor.fetchall():
384
    name, email, phone = row
385
    phone_display = phone if phone is not None else 'No phone'
386
    print(f"{name}: {email}, {phone_display}")
387

388
conn.commit()
389
cursor.close()
390
conn.close()
391
```
392

393
### Custom Type Conversion
394

395
```python
396
import cymysql
397
import decimal
398
from datetime import datetime
399

400
# Custom converter for high-precision decimals
401
def convert_decimal(value):
402
    return decimal.Decimal(value.decode('utf-8'))
403

404
# Custom converter for timestamps
405
def convert_timestamp(value):
406
    return datetime.strptime(value.decode('utf-8'), '%Y-%m-%d %H:%M:%S')
407

408
# Note: Custom converters would be registered with connection
409
# This is an advanced feature for specialized type handling
410
```
411

412
### Bulk Data Type Handling
413

414
```python
415
import cymysql
416
from datetime import datetime, date
417

418
conn = cymysql.connect(host='localhost', user='root', db='test')
419
cursor = conn.cursor()
420

421
# Mixed data types in bulk insert
422
employees = [
423
    ('Alice Johnson', 75000.50, date(2020, 1, 15), True),
424
    ('Bob Smith', 82000.00, date(2019, 6, 10), True),
425
    ('Carol Davis', 68000.25, date(2021, 3, 8), False)
426
]
427

428
cursor.executemany(
429
    "INSERT INTO employees (name, salary, hire_date, active) VALUES (%s, %s, %s, %s)",
430
    employees
431
)
432

433
print(f"Inserted {cursor.rowcount} employees")
434

435
conn.commit()
436
cursor.close()
437
conn.close()
438
```
439

440
## Data Type Mapping
441

442
### Python to MySQL
443

444
| Python Type | MySQL Type | Notes |
445
|-------------|------------|-------|
446
| `None` | `NULL` | Null values |
447
| `bool` | `TINYINT(1)` | 0 for False, 1 for True |
448
| `int` | `INT`/`BIGINT` | Size depends on value |
449
| `float` | `DOUBLE` | IEEE 754 double precision |
450
| `decimal.Decimal` | `DECIMAL` | Exact precision |
451
| `str` | `VARCHAR`/`TEXT` | UTF-8 encoded |
452
| `bytes` | `BLOB`/`BINARY` | Binary data |
453
| `date` | `DATE` | Date only |
454
| `time` | `TIME` | Time only |
455
| `datetime` | `DATETIME`/`TIMESTAMP` | Date and time |
456
| `list`/`tuple` | `JSON` | When using JSON columns |
457
458
### MySQL to Python
459

460
| MySQL Type | Python Type | Notes |
461
|------------|-------------|-------|
462
| `NULL` | `None` | Null values |
463
| `TINYINT(1)` | `bool` | When configured |
464
| `INT`/`BIGINT` | `int` | All integer types |
465
| `FLOAT`/`DOUBLE` | `float` | Floating point |
466
| `DECIMAL` | `decimal.Decimal` | Exact precision |
467
| `VARCHAR`/`TEXT` | `str` | UTF-8 decoded |
468
| `BLOB`/`BINARY` | `bytes` | Binary data |
469
| `DATE` | `datetime.date` | Date objects |
470
| `TIME` | `datetime.time` | Time objects |
471
| `DATETIME`/`TIMESTAMP` | `datetime.datetime` | Datetime objects |
472
| `JSON` | `str` | JSON string (parse manually) |