0
# Data Types and Type System
1
2
Comprehensive type system supporting DuckDB's native types including integers, complex types (STRUCT, MAP, UNION), and extension types with proper SQLAlchemy mapping. This module provides DuckDB-specific type classes and type mapping functionality.
3
4
## Capabilities
5
6
### Integer Type Extensions
7
8
DuckDB-specific integer types with various bit widths and signedness.
9
10
```python { .api }
11
class UInt64(Integer):
12
"""64-bit unsigned integer type."""
13
14
class UInt32(Integer):
15
"""32-bit unsigned integer type."""
16
17
class UInt16(Integer):
18
"""16-bit unsigned integer type (alias: USMALLINT)."""
19
20
class UInt8(Integer):
21
"""8-bit unsigned integer type."""
22
23
class UTinyInteger(Integer):
24
"""Tiny unsigned integer type (alias: UInt1)."""
25
name = "UTinyInt"
26
27
class TinyInteger(Integer):
28
"""Tiny signed integer type (alias: Int1)."""
29
name = "TinyInt"
30
31
class USmallInteger(Integer):
32
"""Unsigned small integer type (alias: UInt2)."""
33
name = "usmallint"
34
min = 0
35
max = 65535
36
37
class UBigInteger(Integer):
38
"""Unsigned big integer type."""
39
name = "UBigInt"
40
min = 0
41
max = 18446744073709551615
42
43
class HugeInteger(Integer):
44
"""128-bit signed integer type."""
45
name = "HugeInt"
46
47
class UHugeInteger(Integer):
48
"""128-bit unsigned integer type."""
49
name = "UHugeInt"
50
51
class UInteger(Integer):
52
"""Unsigned 32-bit integer type."""
53
54
class VarInt(Integer):
55
"""Variable-width integer type (DuckDB v1.0+)."""
56
```
57
58
### Complex Data Types
59
60
DuckDB's complex data types for nested and structured data.
61
62
```python { .api }
63
class Struct(TypeEngine):
64
"""
65
Represents a STRUCT type in DuckDB for nested data structures.
66
67
Allows defining structured data with named fields of different types.
68
"""
69
__visit_name__ = "struct"
70
71
def __init__(self, fields=None):
72
"""
73
Initialize STRUCT type.
74
75
Parameters:
76
- fields (Dict[str, Union[Type[TypeEngine], TypeEngine]], optional):
77
Dictionary mapping field names to SQLAlchemy types
78
"""
79
self.fields = fields
80
81
class Map(TypeEngine):
82
"""
83
Represents a MAP type in DuckDB for key-value pair data.
84
85
Stores key-value mappings with specified key and value types.
86
"""
87
__visit_name__ = "map"
88
89
def __init__(self, key_type, value_type):
90
"""
91
Initialize MAP type.
92
93
Parameters:
94
- key_type (Union[Type[TypeEngine], TypeEngine]): Type for map keys
95
- value_type (Union[Type[TypeEngine], TypeEngine]): Type for map values
96
"""
97
self.key_type = key_type
98
self.value_type = value_type
99
100
def bind_processor(self, dialect):
101
"""
102
Process Python dict values for DuckDB MAP format.
103
104
Returns:
105
Callable: Function to convert dict to DuckDB MAP format
106
"""
107
108
def result_processor(self, dialect, coltype):
109
"""
110
Process DuckDB MAP results back to Python dict.
111
112
Returns:
113
Callable: Function to convert DuckDB MAP to dict
114
"""
115
116
class Union(TypeEngine):
117
"""
118
Represents a UNION type in DuckDB for multiple possible types.
119
120
Allows a column to contain values of different types.
121
"""
122
__visit_name__ = "union"
123
124
def __init__(self, fields):
125
"""
126
Initialize UNION type.
127
128
Parameters:
129
- fields (Dict[str, Union[Type[TypeEngine], TypeEngine]]):
130
Dictionary mapping variant names to SQLAlchemy types
131
"""
132
self.fields = fields
133
```
134
135
### Type Registration and Compilation
136
137
Functions for registering and compiling DuckDB types with SQLAlchemy.
138
139
```python { .api }
140
def register_extension_types():
141
"""
142
Register all DuckDB extension types with SQLAlchemy compiler.
143
144
This function sets up the necessary type compilation rules for
145
DuckDB-specific types to work with SQLAlchemy's type system.
146
"""
147
148
def compile_uint(element, compiler, **kw):
149
"""
150
Compile unsigned integer types to DuckDB SQL.
151
152
Parameters:
153
- element (Integer): Integer type instance
154
- compiler (PGTypeCompiler): SQLAlchemy compiler
155
- **kw: Additional compiler keyword arguments
156
157
Returns:
158
str: DuckDB type name
159
"""
160
```
161
162
### Type Mapping
163
164
Constants and dictionaries for type name mapping between DuckDB and SQLAlchemy.
165
166
```python { .api }
167
ISCHEMA_NAMES: Dict[str, Type[TypeEngine]]
168
"""
169
Maps DuckDB type names to SQLAlchemy type classes.
170
171
Includes mappings for:
172
- Integer types: hugeint, uhugeint, tinyint, utinyint, etc.
173
- Floating point: float4, float8
174
- Temporal: timetz, timestamptz, timestamp_s, timestamp_ms, timestamp_ns
175
- Other: enum, bool, varchar
176
"""
177
178
types: List[Type[Integer]]
179
"""List of all custom integer type subclasses defined in this module."""
180
```
181
182
### Type Processing Utilities
183
184
Helper functions for type processing and compilation.
185
186
```python { .api }
187
def process_type(value, compiler, **kw):
188
"""
189
Process a type value through SQLAlchemy compiler.
190
191
Parameters:
192
- value (Union[TypeEngine, Type[TypeEngine]]): Type to process
193
- compiler (PGTypeCompiler): SQLAlchemy compiler
194
- **kw: Additional compiler arguments
195
196
Returns:
197
str: Compiled type string
198
"""
199
200
def struct_or_union(instance, compiler, identifier_preparer, **kw):
201
"""
202
Generate SQL for STRUCT or UNION type definitions.
203
204
Parameters:
205
- instance (Union[Struct, Union]): Type instance
206
- compiler (PGTypeCompiler): SQLAlchemy compiler
207
- identifier_preparer (PGIdentifierPreparer): Identifier preparer
208
- **kw: Additional arguments
209
210
Returns:
211
str: Type definition SQL
212
"""
213
```
214
215
## Usage Examples
216
217
### Integer Types
218
219
```python
220
from duckdb_engine.datatypes import UInt64, HugeInteger, UTinyInteger
221
from sqlalchemy import Table, Column, MetaData
222
223
metadata = MetaData()
224
table = Table('data', metadata,
225
Column('big_id', UInt64),
226
Column('huge_number', HugeInteger),
227
Column('tiny_flag', UTinyInteger)
228
)
229
```
230
231
### Struct Type
232
233
```python
234
from duckdb_engine.datatypes import Struct
235
from sqlalchemy import Table, Column, String, Integer
236
237
# Define a struct with named fields
238
person_struct = Struct({
239
'first_name': String(50),
240
'last_name': String(50),
241
'age': Integer
242
})
243
244
table = Table('people', metadata,
245
Column('id', Integer, primary_key=True),
246
Column('person_info', person_struct)
247
)
248
```
249
250
### Map Type
251
252
```python
253
from duckdb_engine.datatypes import Map
254
from sqlalchemy import String, Integer
255
256
# Define a map from string keys to integer values
257
tag_counts = Map(String, Integer)
258
259
table = Table('articles', metadata,
260
Column('id', Integer, primary_key=True),
261
Column('title', String(200)),
262
Column('tag_counts', tag_counts)
263
)
264
```
265
266
### Union Type
267
268
```python
269
from duckdb_engine.datatypes import Union
270
from sqlalchemy import String, Integer, Float
271
272
# Define a union that can hold string, int, or float values
273
mixed_value = Union({
274
'text': String,
275
'number': Integer,
276
'decimal': Float
277
})
278
279
table = Table('flexible_data', metadata,
280
Column('id', Integer, primary_key=True),
281
Column('value', mixed_value)
282
)
283
```
284
285
### Type Registration
286
287
```python
288
from duckdb_engine.datatypes import register_extension_types
289
290
# This is automatically called when importing duckdb_engine
291
# but can be called manually if needed
292
register_extension_types()
293
```