0
# Data Types and Custom Types
1

2
Specialized data types for storing large text, JSON data, and array types. These extend Python's basic types for database-specific storage needs and provide seamless integration with database-specific features.
3

4
## Capabilities
5

6
### Large Text Types
7

8
Data types for storing large text content that exceeds normal string limitations.
9

10
```python { .api }
11
class LongStr(str):
12
    """String type for large text fields stored as TEXT/CLOB in database.
13
    
14
    Inherits from str but signals to database that this should be stored
15
    as a large text type (TEXT, CLOB, etc.) rather than VARCHAR.
16
    
17
    Usage:
18
        description = Required(LongStr)
19
        content = Optional(LongStr)
20
    """
21

22
class LongUnicode(LongStr):
23
    """Unicode string type for large text fields (alias for LongStr).
24
    
25
    Provided for backwards compatibility. Use LongStr instead.
26
    """
27
```
28

29
### JSON Data Type
30

31
Type for storing structured JSON data with automatic serialization/deserialization.
32

33
```python { .api }
34
class Json:
35
    """JSON data type for storing structured data.
36
    
37
    Automatically serializes Python objects to JSON when storing
38
    and deserializes JSON back to Python objects when retrieving.
39
    
40
    Supports: dict, list, str, int, float, bool, None
41
    
42
    Usage:
43
        metadata = Optional(Json)
44
        config = Required(Json, default={})
45
    """
46
```
47

48
### Array Types
49

50
Array types for storing collections of values (primarily for PostgreSQL).
51

52
```python { .api }
53
class IntArray:
54
    """Array type for storing lists of integers.
55
    
56
    Maps to database array types (PostgreSQL ARRAY) or serialized storage
57
    for databases that don't support arrays natively.
58
    
59
    Usage:
60
        scores = Optional(IntArray)
61
        ratings = Required(IntArray, default=[])
62
    """
63

64
class StrArray:
65
    """Array type for storing lists of strings.
66
    
67
    Usage:
68
        tags = Optional(StrArray)
69
        categories = Required(StrArray, default=[])
70
    """
71

72
class FloatArray:
73
    """Array type for storing lists of floating point numbers.
74
    
75
    Usage:
76
        coordinates = Optional(FloatArray)
77
        measurements = Required(FloatArray, default=[])
78
    """
79
```
80

81
### Compatibility Types
82

83
Types for cross-Python version compatibility.
84

85
```python { .api }
86
buffer: type
87
    """Binary data type for cross-Python compatibility.
88
    
89
    Maps to appropriate binary type based on Python version:
90
    - Python 2: buffer
91
    - Python 3: bytes
92
    
93
    Usage:
94
        file_data = Optional(buffer)
95
        binary_content = Required(buffer)
96
    """
97

98
unicode: type
99
    """Unicode string type for cross-Python compatibility.
100
    
101
    Maps to appropriate unicode type based on Python version:
102
    - Python 2: unicode
103
    - Python 3: str
104
    
105
    Usage:
106
        text_field = Required(unicode)
107
    """
108
```
109

110
## Usage Examples
111

112
### Large Text Storage
113

114
```python
115
from pony.orm import *
116

117
db = Database()
118

119
class Article(db.Entity):
120
    title = Required(str, max_len=200)
121
    summary = Optional(str, max_len=500)
122
    
123
    # Large text content
124
    content = Required(LongStr)  # Stored as TEXT/CLOB
125
    raw_html = Optional(LongStr)
126
    
127
    # Regular strings have length limits, LongStr does not
128
    author_bio = Optional(LongStr)
129

130
class BlogPost(db.Entity):
131
    title = Required(str)
132
    
133
    # LongUnicode for backwards compatibility
134
    content = Required(LongUnicode)
135

136
db.bind('sqlite', filename='blog.db')
137
db.generate_mapping(create_tables=True)
138

139
with db_session:
140
    # Create article with large content
141
    article = Article(
142
        title="Long Article",
143
        summary="This article has a lot of content...",
144
        content="This is a very long article content that can be thousands of characters long without any VARCHAR limitations. " * 1000
145
    )
146
    
147
    # LongStr behaves like regular string
148
    print(len(article.content))  # Can be very large
149
    print(article.content[:100])  # Slicing works normally
150
```
151

152
### JSON Data Storage
153

154
```python
155
from pony.orm import *
156

157
db = Database()
158

159
class User(db.Entity):
160
    name = Required(str)
161
    email = Required(str, unique=True)
162
    
163
    # JSON fields for structured data
164
    preferences = Optional(Json, default={})
165
    profile_data = Optional(Json)
166
    activity_log = Optional(Json, default=[])
167

168
class Product(db.Entity):
169
    name = Required(str)
170
    price = Required(float)
171
    
172
    # Complex JSON data
173
    specifications = Optional(Json, default={})
174
    reviews = Optional(Json, default=[])
175

176
db.bind('postgresql', host='localhost', user='app', password='secret', database='store')
177
db.generate_mapping(create_tables=True)
178

179
with db_session:
180
    # Create user with JSON data
181
    user = User(
182
        name="Alice",
183
        email="alice@example.com",
184
        preferences={
185
            "theme": "dark",
186
            "language": "en",
187
            "notifications": {
188
                "email": True,
189
                "push": False
190
            }
191
        },
192
        profile_data={
193
            "age": 25,
194
            "location": "San Francisco",
195
            "interests": ["programming", "music", "travel"]
196
        }
197
    )
198
    
199
    # Access JSON data like regular Python objects
200
    print(user.preferences["theme"])  # "dark"
201
    print(user.profile_data["interests"])  # ["programming", "music", "travel"]
202
    
203
    # Modify JSON data
204
    user.preferences["theme"] = "light"
205
    user.profile_data["interests"].append("photography")
206
    
207
    # Create product with complex specifications
208
    product = Product(
209
        name="Laptop",
210
        price=999.99,
211
        specifications={
212
            "cpu": "Intel i7",
213
            "ram": "16GB",
214
            "storage": "512GB SSD",
215
            "display": {
216
                "size": "15.6 inch",
217
                "resolution": "1920x1080",
218
                "type": "IPS"
219
            },
220
            "ports": ["USB-C", "USB-A", "HDMI", "Audio Jack"]
221
        },
222
        reviews=[
223
            {
224
                "user": "john_doe",
225
                "rating": 5,
226
                "comment": "Excellent laptop!",
227
                "date": "2023-01-15"
228
            },
229
            {
230
                "user": "jane_smith", 
231
                "rating": 4,
232
                "comment": "Good performance, bit heavy",
233
                "date": "2023-01-20"
234
            }
235
        ]
236
    )
237
    
238
    # Query JSON data (PostgreSQL supports JSON queries)
239
    # Find users who prefer dark theme
240
    dark_theme_users = select(u for u in User 
241
                             if u.preferences.get("theme") == "dark")
242
```
243

244
### Array Data Storage
245

246
```python
247
from pony.orm import *
248

249
db = Database()
250

251
class Student(db.Entity):
252
    name = Required(str)
253
    
254
    # Array fields
255
    test_scores = Optional(IntArray, default=[])
256
    subjects = Optional(StrArray, default=[])
257
    gpa_history = Optional(FloatArray, default=[])
258

259
class DataPoint(db.Entity):
260
    name = Required(str)
261
    
262
    # Numeric arrays for scientific data
263
    measurements = Required(FloatArray)
264
    sensor_ids = Optional(IntArray)
265
    tags = Optional(StrArray)
266

267
# PostgreSQL supports native arrays
268
db.bind('postgresql', host='localhost', user='app', password='secret', database='school')
269
db.generate_mapping(create_tables=True)
270

271
with db_session:
272
    # Create student with array data
273
    student = Student(
274
        name="Alice Johnson",
275
        test_scores=[85, 92, 78, 95, 88],
276
        subjects=["Math", "Physics", "Chemistry", "English"],
277
        gpa_history=[3.2, 3.5, 3.7, 3.8]
278
    )
279
    
280
    # Work with arrays like Python lists
281
    student.test_scores.append(90)
282
    student.subjects.extend(["History", "Art"])
283
    
284
    print(f"Average score: {sum(student.test_scores) / len(student.test_scores)}")
285
    print(f"Subjects: {', '.join(student.subjects)}")
286
    
287
    # Create data point with measurements
288
    data_point = DataPoint(
289
        name="Temperature Sensor 1",
290
        measurements=[20.5, 21.0, 20.8, 21.2, 20.9],
291
        sensor_ids=[1, 2, 3, 4, 5],
292
        tags=["outdoor", "temperature", "hourly"]
293
    )
294
    
295
    # Query array data (PostgreSQL specific)
296
    # Find students with high test scores
297
    high_achievers = select(s for s in Student 
298
                           if any(score > 90 for score in s.test_scores))
299
    
300
    # Find data points with specific tags
301
    temp_sensors = select(d for d in DataPoint 
302
                         if "temperature" in d.tags)
303
```
304

305
### Binary Data Storage
306

307
```python
308
from pony.orm import *
309

310
db = Database()
311

312
class Document(db.Entity):
313
    name = Required(str)
314
    mime_type = Required(str)
315
    
316
    # Binary data storage
317
    file_data = Required(buffer)  # Cross-platform binary type
318
    thumbnail = Optional(buffer)
319

320
class Image(db.Entity):
321
    filename = Required(str)
322
    
323
    # Unicode text with binary data
324
    description = Optional(unicode)  # Cross-platform unicode
325
    image_data = Required(buffer)
326

327
db.bind('sqlite', filename='files.db')
328
db.generate_mapping(create_tables=True)
329

330
with db_session:
331
    # Read binary file
332
    with open('document.pdf', 'rb') as f:
333
        file_content = f.read()
334
    
335
    # Store binary data
336
    doc = Document(
337
        name="Important Document",
338
        mime_type="application/pdf",
339
        file_data=file_content
340
    )
341
    
342
    # Retrieve and save binary data
343
    with open('retrieved_document.pdf', 'wb') as f:
344
        f.write(doc.file_data)
345
    
346
    # Work with images
347
    with open('photo.jpg', 'rb') as f:
348
        image_data = f.read()
349
    
350
    image = Image(
351
        filename="vacation_photo.jpg",
352
        description=u"Beautiful sunset at the beach",  # Unicode string
353
        image_data=image_data
354
    )
355
```
356

357
### Custom Type Combinations
358

359
```python
360
from pony.orm import *
361

362
db = Database()
363

364
class BlogPost(db.Entity):
365
    title = Required(str)
366
    slug = Required(str, unique=True)
367
    
368
    # Combine different custom types
369
    content = Required(LongStr)  # Large text content
370
    metadata = Optional(Json, default={})  # Structured metadata
371
    tags = Optional(StrArray, default=[])  # Array of tags
372
    
373
    # Rich content with attachments
374
    attachments = Optional(Json, default=[])  # JSON array of attachment info
375

376
class UserProfile(db.Entity):
377
    username = Required(str, unique=True)
378
    
379
    # Personal information
380
    bio = Optional(LongStr)  # Long biographical text
381
    preferences = Optional(Json, default={})  # User preferences
382
    
383
    # Activity tracking
384
    login_history = Optional(Json, default=[])  # JSON log of logins
385
    favorite_tags = Optional(StrArray, default=[])  # Array of interests
386
    
387
    # Binary profile data
388
    avatar = Optional(buffer)  # Profile picture binary data
389

390
db.bind('postgresql', host='localhost', user='blog', password='secret', database='blog')
391
db.generate_mapping(create_tables=True)
392

393
with db_session:
394
    # Create rich blog post
395
    post = BlogPost(
396
        title="Advanced Database Types in Pony ORM",
397
        slug="advanced-database-types-pony-orm",
398
        content="""
399
        This is a comprehensive guide to using advanced database types...
400
        """ * 100,  # Very long content
401
        metadata={
402
            "author_id": 123,
403
            "publish_date": "2023-12-01",
404
            "category": "tutorial",
405
            "reading_time": 15,
406
            "seo": {
407
                "meta_description": "Learn about advanced types",
408
                "keywords": ["database", "ORM", "Python"]
409
            }
410
        },
411
        tags=["python", "database", "orm", "tutorial"],
412
        attachments=[
413
            {
414
                "type": "image",
415
                "url": "/images/diagram1.png",
416
                "caption": "Database schema diagram"
417
            },
418
            {
419
                "type": "code",
420
                "language": "python",
421
                "content": "# Example code snippet"
422
            }
423
        ]
424
    )
425
    
426
    # Query combining different types
427
    python_posts = select(p for p in BlogPost 
428
                         if "python" in p.tags 
429
                         and p.metadata.get("category") == "tutorial")
430
```