0
# Type System and Data Types
1
2
SQL data type system including numeric, string, date/time, binary, and specialized types with database-specific variants and custom type creation. SQLAlchemy's type system provides database abstraction and Python value processing.
3
4
## Capabilities
5
6
### Numeric Types
7
8
Integer, floating-point, and precision numeric types for numerical data.
9
10
```python { .api }
11
class Integer:
12
"""Integer type with platform-specific sizing."""
13
14
def __init__(self, **kwargs):
15
"""
16
Create integer type.
17
18
Parameters:
19
- autoincrement: bool, enable auto-increment
20
"""
21
22
class BigInteger(Integer):
23
"""Large integer type (BIGINT)."""
24
25
class SmallInteger(Integer):
26
"""Small integer type (SMALLINT)."""
27
28
class Numeric:
29
"""Fixed-precision numeric type (NUMERIC/DECIMAL)."""
30
31
def __init__(self, precision=None, scale=None, **kwargs):
32
"""
33
Create numeric type.
34
35
Parameters:
36
- precision: int, total number of digits
37
- scale: int, number of digits after decimal point
38
- asdecimal: bool, return Python Decimal (default True)
39
"""
40
41
class Float:
42
"""Floating-point type with optional precision."""
43
44
def __init__(self, precision=None, scale=None, **kwargs):
45
"""
46
Create float type.
47
48
Parameters:
49
- precision: int, precision specification
50
- scale: int, scale specification (some databases)
51
- asdecimal: bool, return Python Decimal (default False)
52
"""
53
54
class Double(Float):
55
"""Double-precision floating-point type."""
56
57
# Type constants for standard SQL types
58
INT: Integer
59
INTEGER: Integer
60
BIGINT: BigInteger
61
SMALLINT: SmallInteger
62
NUMERIC: Numeric
63
DECIMAL: Numeric
64
FLOAT: Float
65
REAL: Float
66
DOUBLE: Double
67
DOUBLE_PRECISION: Double
68
```
69
70
### String and Text Types
71
72
Variable and fixed-length string types with encoding support.
73
74
```python { .api }
75
class String:
76
"""Variable-length string type (VARCHAR)."""
77
78
def __init__(self, length=None, **kwargs):
79
"""
80
Create string type.
81
82
Parameters:
83
- length: int, maximum string length
84
- collation: str, database collation
85
- convert_unicode: bool, handle unicode conversion
86
"""
87
88
class Text:
89
"""Large text type for long strings."""
90
91
def __init__(self, length=None, **kwargs):
92
"""
93
Create text type.
94
95
Parameters:
96
- length: int, maximum text length (if supported)
97
- collation: str, database collation
98
"""
99
100
class Unicode(String):
101
"""Unicode string type with automatic conversion."""
102
103
def __init__(self, length=None, **kwargs):
104
"""
105
Create unicode string type.
106
107
Parameters:
108
- length: int, maximum string length
109
- error: str, unicode error handling
110
"""
111
112
class UnicodeText(Text):
113
"""Unicode text type for large text with automatic conversion."""
114
115
# String type constants
116
VARCHAR: String
117
CHAR: String
118
TEXT: Text
119
CLOB: Text
120
NVARCHAR: Unicode
121
NCHAR: Unicode
122
```
123
124
### Date and Time Types
125
126
Date, time, and timestamp types with timezone support.
127
128
```python { .api }
129
class Date:
130
"""Date type for date-only values."""
131
132
def __init__(self, **kwargs):
133
"""Create date type."""
134
135
class Time:
136
"""Time type for time-only values."""
137
138
def __init__(self, timezone=False, **kwargs):
139
"""
140
Create time type.
141
142
Parameters:
143
- timezone: bool, include timezone information
144
"""
145
146
class DateTime:
147
"""Date and time type (DATETIME/TIMESTAMP)."""
148
149
def __init__(self, timezone=False, **kwargs):
150
"""
151
Create datetime type.
152
153
Parameters:
154
- timezone: bool, include timezone information
155
"""
156
157
class Interval:
158
"""Time interval type for durations."""
159
160
def __init__(self, native=True, **kwargs):
161
"""
162
Create interval type.
163
164
Parameters:
165
- native: bool, use native INTERVAL type if available
166
- second_precision: int, precision for seconds
167
- day_precision: int, precision for days
168
"""
169
170
# Date/time type constants
171
DATE: Date
172
TIME: Time
173
DATETIME: DateTime
174
TIMESTAMP: DateTime
175
```
176
177
### Binary and LOB Types
178
179
Binary data and large object types for non-text data storage.
180
181
```python { .api }
182
class LargeBinary:
183
"""Binary large object type (BLOB/BYTEA)."""
184
185
def __init__(self, length=None, **kwargs):
186
"""
187
Create binary type.
188
189
Parameters:
190
- length: int, maximum binary length
191
"""
192
193
class PickleType:
194
"""Type for storing Python objects via pickle serialization."""
195
196
def __init__(self, protocol=None, pickler=None, **kwargs):
197
"""
198
Create pickle type.
199
200
Parameters:
201
- protocol: int, pickle protocol version
202
- pickler: pickle module to use
203
- mutable: bool, track object mutations
204
"""
205
206
# Binary type constants
207
BINARY: LargeBinary
208
VARBINARY: LargeBinary
209
BLOB: LargeBinary
210
```
211
212
### Boolean and Specialized Types
213
214
Boolean and other specialized data types.
215
216
```python { .api }
217
class Boolean:
218
"""Boolean type with database-specific handling."""
219
220
def __init__(self, create_constraint=True, **kwargs):
221
"""
222
Create boolean type.
223
224
Parameters:
225
- create_constraint: bool, create CHECK constraint for emulation
226
- name: str, constraint name for emulation
227
"""
228
229
class Enum:
230
"""Enumeration type with constraint generation."""
231
232
def __init__(self, *enums, **kwargs):
233
"""
234
Create enum type.
235
236
Parameters:
237
- enums: enumeration values
238
- name: str, enum type name
239
- native_enum: bool, use native ENUM type
240
- create_constraint: bool, create CHECK constraint
241
- length: int, string length for VARCHAR fallback
242
"""
243
244
class JSON:
245
"""JSON data type with database-specific operators."""
246
247
def __init__(self, none_as_null=False, **kwargs):
248
"""
249
Create JSON type.
250
251
Parameters:
252
- none_as_null: bool, store None as SQL NULL vs JSON null
253
"""
254
255
class UUID:
256
"""UUID type with string or binary storage."""
257
258
def __init__(self, as_uuid=True, **kwargs):
259
"""
260
Create UUID type.
261
262
Parameters:
263
- as_uuid: bool, return Python UUID objects vs strings
264
"""
265
266
# Specialized type constants
267
BOOLEAN: Boolean
268
```
269
270
### Array and Composite Types
271
272
Array types and composite data structures.
273
274
```python { .api }
275
class ARRAY:
276
"""Array type (PostgreSQL and others)."""
277
278
def __init__(self, item_type, **kwargs):
279
"""
280
Create array type.
281
282
Parameters:
283
- item_type: TypeEngine, array element type
284
- as_tuple: bool, return tuples instead of lists
285
- dimensions: int, number of array dimensions
286
- zero_indexes: bool, use zero-based indexing
287
"""
288
289
class TupleType:
290
"""Composite tuple type for multiple values."""
291
292
def __init__(self, *types, **kwargs):
293
"""
294
Create tuple type.
295
296
Parameters:
297
- types: TypeEngine objects for tuple elements
298
"""
299
```
300
301
### Type System Framework
302
303
Base classes and decorators for custom type implementation.
304
305
```python { .api }
306
class TypeDecorator:
307
"""Base class for custom type implementations."""
308
309
impl: TypeEngine # Underlying type implementation
310
cache_ok: bool = True # Caching safety flag
311
312
def process_bind_param(self, value, dialect):
313
"""
314
Process Python value for database binding.
315
316
Parameters:
317
- value: Python value to process
318
- dialect: database dialect
319
320
Returns:
321
Any: Value for database binding
322
"""
323
324
def process_result_value(self, value, dialect):
325
"""
326
Process database value for Python use.
327
328
Parameters:
329
- value: database value to process
330
- dialect: database dialect
331
332
Returns:
333
Any: Python value for application use
334
"""
335
336
def copy(self, **kwargs):
337
"""
338
Create copy of type with modifications.
339
340
Parameters:
341
- kwargs: attributes to override
342
343
Returns:
344
TypeDecorator: Copied type instance
345
"""
346
347
class TypeEngine:
348
"""Base class for all SQL types."""
349
350
def bind_processor(self, dialect):
351
"""
352
Return function for processing bind parameters.
353
354
Parameters:
355
- dialect: database dialect
356
357
Returns:
358
callable or None: Processing function
359
"""
360
361
def result_processor(self, dialect, coltype):
362
"""
363
Return function for processing result values.
364
365
Parameters:
366
- dialect: database dialect
367
- coltype: column type from database
368
369
Returns:
370
callable or None: Processing function
371
"""
372
373
def compare_values(self, x, y):
374
"""
375
Compare two values for equality.
376
377
Parameters:
378
- x: first value
379
- y: second value
380
381
Returns:
382
bool: True if values are equal
383
"""
384
385
class UserDefinedType(TypeEngine):
386
"""Base for user-defined custom types."""
387
388
def get_col_spec(self, **kwargs):
389
"""
390
Return database column specification.
391
392
Returns:
393
str: Database-specific column type specification
394
"""
395
```
396
397
### Type Coercion and Casting
398
399
Type coercion utilities for expression handling.
400
401
```python { .api }
402
def type_coerce(expr, type_):
403
"""
404
Coerce expression to specific type without casting.
405
406
Parameters:
407
- expr: expression to coerce
408
- type_: target type
409
410
Returns:
411
TypeCoerce: Type-coerced expression
412
"""
413
414
class TypeCoerce:
415
"""Expression with type coercion applied."""
416
417
def __init__(self, expr, type_):
418
"""
419
Create type coercion.
420
421
Parameters:
422
- expr: expression to coerce
423
- type_: target type
424
"""
425
```
426
427
## Usage Examples
428
429
### Basic Type Usage
430
431
```python
432
from sqlalchemy import Table, Column, Integer, String, DateTime, Boolean
433
from sqlalchemy import MetaData, create_engine
434
435
metadata = MetaData()
436
437
users = Table('users', metadata,
438
Column('id', Integer, primary_key=True),
439
Column('name', String(100), nullable=False),
440
Column('email', String(255), unique=True),
441
Column('is_active', Boolean, default=True),
442
Column('created_at', DateTime, server_default=func.now())
443
)
444
```
445
446
### Precision Numeric Types
447
448
```python
449
from sqlalchemy import Numeric, Float
450
451
products = Table('products', metadata,
452
Column('id', Integer, primary_key=True),
453
Column('price', Numeric(10, 2)), # 10 digits, 2 decimal places
454
Column('weight', Float(precision=24)), # Single precision
455
Column('rating', Numeric(3, 2)) # 0.00 to 9.99
456
)
457
```
458
459
### Custom Type Example
460
461
```python
462
from sqlalchemy import TypeDecorator, String
463
import json
464
465
class JSONType(TypeDecorator):
466
"""Custom JSON type using string storage."""
467
468
impl = String
469
cache_ok = True
470
471
def process_bind_param(self, value, dialect):
472
if value is not None:
473
return json.dumps(value)
474
return None
475
476
def process_result_value(self, value, dialect):
477
if value is not None:
478
return json.loads(value)
479
return None
480
481
# Usage
482
settings = Table('settings', metadata,
483
Column('id', Integer, primary_key=True),
484
Column('config', JSONType())
485
)
486
```
487
488
### Array and Specialized Types
489
490
```python
491
from sqlalchemy.dialects.postgresql import ARRAY, UUID as PG_UUID
492
from sqlalchemy import JSON
493
494
# PostgreSQL-specific types
495
logs = Table('logs', metadata,
496
Column('id', PG_UUID, primary_key=True),
497
Column('tags', ARRAY(String(50))),
498
Column('metadata', JSON),
499
Column('levels', ARRAY(Integer, dimensions=2)) # 2D array
500
)
501
502
# Cross-database JSON
503
documents = Table('documents', metadata,
504
Column('id', Integer, primary_key=True),
505
Column('data', JSON) # Works with PostgreSQL, MySQL 5.7+, SQLite 3.38+
506
)
507
```
508
509
### Enum Type Usage
510
511
```python
512
from sqlalchemy import Enum
513
514
# String-based enum
515
status_enum = Enum('pending', 'processing', 'completed', 'failed',
516
name='order_status')
517
518
orders = Table('orders', metadata,
519
Column('id', Integer, primary_key=True),
520
Column('status', status_enum, default='pending')
521
)
522
523
# Python enum integration
524
from enum import Enum as PyEnum
525
526
class OrderStatus(PyEnum):
527
PENDING = 'pending'
528
PROCESSING = 'processing'
529
COMPLETED = 'completed'
530
FAILED = 'failed'
531
532
orders_v2 = Table('orders_v2', metadata,
533
Column('id', Integer, primary_key=True),
534
Column('status', Enum(OrderStatus), default=OrderStatus.PENDING)
535
)
536
```