0
# Type Adaptation and Casting
1

2
Comprehensive type conversion system between Python and PostgreSQL types, including support for arrays, JSON, ranges, UUID, network types, and custom type registration for seamless data exchange.
3

4
## Capabilities
5

6
### Core Type Adaptation
7

8
Convert Python objects to SQL representations and PostgreSQL values back to Python objects.
9

10
```python { .api }
11
def adapt(obj):
12
    """
13
    Adapt Python object to SQL.
14
    
15
    Parameters:
16
    - obj: Python object to adapt
17
    
18
    Returns:
19
    ISQLQuote: Adapted object with getquoted() method
20
    """
21

22
def register_adapter(type, adapter):
23
    """
24
    Register object adapter.
25
    
26
    Parameters:
27
    - type: Python type to adapt
28
    - adapter: Adapter class or function
29
    """
30

31
def new_type(oids, name, castfunc):
32
    """
33
    Create new typecaster.
34
    
35
    Parameters:
36
    - oids (sequence): PostgreSQL type OIDs
37
    - name (str): Type name
38
    - castfunc (callable): Casting function
39
    
40
    Returns:
41
    type: New typecaster object
42
    """
43

44
def new_array_type(oids, name, base_caster):
45
    """
46
    Create array typecaster.
47
    
48
    Parameters:
49
    - oids (sequence): Array type OIDs
50
    - name (str): Array type name  
51
    - base_caster: Base element typecaster
52
    
53
    Returns:
54
    type: Array typecaster
55
    """
56

57
def register_type(obj, scope=None):
58
    """
59
    Register typecaster.
60
    
61
    Parameters:
62
    - obj: Typecaster object
63
    - scope (connection/cursor, optional): Registration scope
64
    """
65
```
66

67
**Usage Example:**
68

69
```python
70
import psycopg2
71
from psycopg2.extensions import adapt, register_adapter, new_type, register_type
72

73
# Basic adaptation
74
value = adapt("Hello World")
75
print(value.getquoted())  # b"'Hello World'"
76

77
number = adapt(42)
78
print(number.getquoted())  # b'42'
79

80
# Custom adapter for Python objects
81
class Point:
82
    def __init__(self, x, y):
83
        self.x = x
84
        self.y = y
85

86
class PointAdapter:
87
    def __init__(self, point):
88
        self.point = point
89
    
90
    def getquoted(self):
91
        return f"POINT({self.point.x}, {self.point.y})".encode()
92

93
# Register custom adapter
94
register_adapter(Point, PointAdapter)
95

96
# Now Point objects can be used in queries
97
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser")
98
with conn.cursor() as cur:
99
    point = Point(10, 20)
100
    cur.execute("INSERT INTO locations (position) VALUES (%s)", (point,))
101

102
# Custom typecaster for PostgreSQL to Python
103
def parse_point(value, cur):
104
    if value is None:
105
        return None
106
    # Parse "POINT(x, y)" format
107
    coords = value.strip("POINT()").split(",")
108
    return Point(float(coords[0]), float(coords[1]))
109

110
# Register typecaster (assuming point type OID is 600)
111
point_type = new_type((600,), "POINT", parse_point)
112
register_type(point_type)
113

114
conn.close()
115
```
116

117
### Built-in Adapter Classes
118

119
Standard adapters for common Python types to PostgreSQL representations.
120

121
```python { .api }
122
class Binary:
123
    """Binary data adapter."""
124
    
125
    def __init__(self, obj):
126
        """Initialize with bytes-like object."""
127
    
128
    def getquoted(self):
129
        """Return quoted binary representation."""
130

131
class AsIs:
132
    """Pass-through adapter."""
133
    
134
    def __init__(self, obj):
135
        """Initialize with object."""
136
    
137
    def getquoted(self):
138
        """Return object as-is."""
139

140
class QuotedString:
141
    """Quoted string adapter."""
142
    
143
    def __init__(self, obj):
144
        """Initialize with string."""
145
    
146
    def getquoted(self):
147
        """Return quoted string."""
148

149
class Boolean:
150
    """Boolean adapter."""
151
    
152
    def __init__(self, obj):
153
        """Initialize with boolean."""
154
    
155
    def getquoted(self):
156
        """Return 't' or 'f'."""
157

158
class Float:
159
    """Float adapter."""
160
    
161
    def __init__(self, obj):
162
        """Initialize with float."""
163
    
164
    def getquoted(self):
165
        """Return float representation."""
166

167
class Int:
168
    """Integer adapter."""
169
    
170
    def __init__(self, obj):
171
        """Initialize with integer."""
172
    
173
    def getquoted(self):
174
        """Return integer representation."""
175
```
176

177
**Usage Example:**
178

179
```python
180
from psycopg2.extensions import Binary, AsIs, QuotedString
181

182
# Binary data
183
binary_data = Binary(b'\x00\x01\x02\xff')
184
print(binary_data.getquoted())  # Escaped binary representation
185

186
# Raw SQL (dangerous - use carefully)
187
raw_sql = AsIs("NOW()")
188
cur.execute("INSERT INTO events (created_at) VALUES (%s)", (raw_sql,))
189

190
# Custom string quoting
191
custom_string = QuotedString("O'Reilly")
192
print(custom_string.getquoted())  # Properly escaped string
193
```
194

195
### JSON and JSONB Support
196

197
Handle JSON data types with automatic serialization and deserialization.
198

199
```python { .api }
200
class Json:
201
    """JSON adapter class."""
202
    
203
    def __init__(self, adapted, dumps=None):
204
        """
205
        Initialize JSON adapter.
206
        
207
        Parameters:
208
        - adapted: Python object to serialize
209
        - dumps (callable, optional): Custom JSON serializer
210
        """
211
    
212
    def dumps(self, obj):
213
        """Serialize object to JSON."""
214
    
215
    def getquoted(self):
216
        """Return quoted JSON representation."""
217

218
def register_json(conn_or_curs=None, globally=False, loads=None, oid=None, array_oid=None, name='json'):
219
    """
220
    Register JSON typecasters.
221
    
222
    Parameters:
223
    - conn_or_curs (connection/cursor, optional): Registration scope
224
    - globally (bool): Register globally
225
    - loads (callable, optional): Custom JSON deserializer
226
    - oid (int, optional): JSON type OID
227
    - array_oid (int, optional): JSON array type OID
228
    - name (str): Type name
229
    """
230

231
def register_default_json(conn_or_curs=None, globally=False, loads=None):
232
    """Register for PostgreSQL 9.2+ JSON."""
233

234
def register_default_jsonb(conn_or_curs=None, globally=False, loads=None):
235
    """Register for PostgreSQL 9.4+ JSONB."""
236
```
237

238
**Usage Example:**
239

240
```python
241
import psycopg2
242
import json
243
from psycopg2.extras import Json, register_default_json, register_default_jsonb
244

245
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser")
246

247
# Register JSON support
248
register_default_json(conn)
249
register_default_jsonb(conn)
250

251
# Insert JSON data
252
data = {'name': 'Alice', 'age': 30, 'hobbies': ['reading', 'swimming']}
253

254
with conn.cursor() as cur:
255
    cur.execute(
256
        "INSERT INTO users (profile) VALUES (%s)",
257
        (Json(data),)
258
    )
259
    
260
    # Query JSON data  
261
    cur.execute("SELECT profile FROM users WHERE profile->>'name' = %s", ('Alice',))
262
    result = cur.fetchone()
263
    print(type(result[0]))  # dict (automatically deserialized)
264
    print(result[0]['hobbies'])  # ['reading', 'swimming']
265

266
# Custom JSON serialization
267
def custom_serializer(obj):
268
    """Custom JSON serializer with date handling."""
269
    if hasattr(obj, 'isoformat'):
270
        return obj.isoformat()
271
    return json.dumps(obj)
272

273
from datetime import datetime
274
timestamp_data = {'event': 'login', 'timestamp': datetime.now()}
275

276
with conn.cursor() as cur:
277
    cur.execute(
278
        "INSERT INTO events (data) VALUES (%s)",
279
        (Json(timestamp_data, dumps=custom_serializer),)
280
    )
281

282
conn.commit()
283
conn.close()
284
```
285

286
### Range Types
287

288
Support for PostgreSQL range types with Python range objects.
289

290
```python { .api }
291
class Range:
292
    """Base PostgreSQL range type."""
293
    
294
    def __init__(self, lower=None, upper=None, bounds='[)', empty=False):
295
        """
296
        Initialize range.
297
        
298
        Parameters:
299
        - lower: Lower bound value
300
        - upper: Upper bound value  
301
        - bounds (str): Bound inclusion ('[)', '(]', '[]', '()')
302
        - empty (bool): Empty range flag
303
        """
304
    
305
    @property
306
    def lower(self):
307
        """Lower bound value."""
308
    
309
    @property
310
    def upper(self):
311
        """Upper bound value."""
312
    
313
    @property
314
    def isempty(self):
315
        """True if range is empty."""
316
    
317
    @property
318
    def lower_inf(self):
319
        """True if lower bound is infinite."""
320
    
321
    @property
322
    def upper_inf(self):
323
        """True if upper bound is infinite."""
324
    
325
    @property
326
    def lower_inc(self):
327
        """True if lower bound is inclusive."""
328
    
329
    @property
330
    def upper_inc(self):
331
        """True if upper bound is inclusive."""
332
    
333
    def __contains__(self, x):
334
        """Check if value in range."""
335

336
class NumericRange(Range):
337
    """For numeric ranges (int4range, int8range, numrange)."""
338

339
class DateRange(Range):
340
    """For daterange."""
341

342
class DateTimeRange(Range):
343
    """For tsrange."""
344

345
class DateTimeTZRange(Range):
346
    """For tstzrange."""
347

348
def register_range(pgrange, pyrange, conn_or_curs, globally=False):
349
    """
350
    Register custom range type.
351
    
352
    Parameters:
353
    - pgrange (str): PostgreSQL range type name
354
    - pyrange: Python range class
355
    - conn_or_curs: Connection or cursor
356
    - globally (bool): Register globally
357
    """
358
```
359

360
**Usage Example:**
361

362
```python
363
import psycopg2
364
from psycopg2.extras import NumericRange, DateRange, register_range
365
from datetime import date
366

367
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser")
368

369
# Numeric ranges
370
age_range = NumericRange(18, 65, bounds='[)')  # 18 <= age < 65
371
print(25 in age_range)  # True
372
print(70 in age_range)  # False
373

374
# Date ranges  
375
vacation_range = DateRange(date(2023, 7, 1), date(2023, 7, 15), bounds='[]')
376
print(vacation_range.lower)  # 2023-07-01
377
print(vacation_range.upper)  # 2023-07-15
378

379
with conn.cursor() as cur:
380
    # Insert range data
381
    cur.execute(
382
        "INSERT INTO employee_ages (age_range) VALUES (%s)",
383
        (age_range,)
384
    )
385
    
386
    cur.execute(
387
        "INSERT INTO vacations (dates) VALUES (%s)",
388
        (vacation_range,)
389
    )
390
    
391
    # Query with range operations
392
    cur.execute(
393
        "SELECT * FROM employee_ages WHERE age_range @> %s",
394
        (30,)  # Find ranges containing 30
395
    )
396
    results = cur.fetchall()
397

398
# Custom range type
399
class IPRange(NumericRange):
400
    """Custom IP address range."""
401
    pass
402

403
# Register custom range (assuming custom IP range type)
404
register_range('iprange', IPRange, conn)
405

406
conn.commit()
407
conn.close()
408
```
409

410
### UUID Support
411

412
Handle UUID data types with Python's uuid module.
413

414
```python { .api }
415
class UUID_adapter:
416
    """Adapter for uuid.UUID objects."""
417
    
418
    def __init__(self, uuid):
419
        """Initialize with UUID object."""
420
    
421
    def getquoted(self):
422
        """Return quoted UUID representation."""
423

424
def register_uuid(oids=None, conn_or_curs=None):
425
    """
426
    Register UUID type support.
427
    
428
    Parameters:
429
    - oids (sequence, optional): UUID type OIDs
430
    - conn_or_curs (connection/cursor, optional): Registration scope
431
    """
432
```
433

434
**Usage Example:**
435

436
```python
437
import psycopg2
438
import uuid
439
from psycopg2.extras import register_uuid
440

441
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser")
442
register_uuid(conn)
443

444
# Generate and use UUIDs
445
user_id = uuid.uuid4()
446
session_id = uuid.uuid4()
447

448
with conn.cursor() as cur:
449
    cur.execute(
450
        "INSERT INTO users (id, session_id, name) VALUES (%s, %s, %s)",
451
        (user_id, session_id, "Alice")
452
    )
453
    
454
    # Query by UUID
455
    cur.execute("SELECT * FROM users WHERE id = %s", (user_id,))
456
    user = cur.fetchone()
457
    print(f"User ID type: {type(user[0])}")  # <class 'uuid.UUID'>
458

459
conn.commit()
460
conn.close()
461
```
462

463
### Network Address Types
464

465
Support for PostgreSQL inet and cidr types with Python's ipaddress module.
466

467
```python { .api }
468
class Inet:
469
    """Wrapper for inet values."""
470
    
471
    def __init__(self, addr):
472
        """Initialize with address string."""
473
    
474
    def getquoted(self):
475
        """Return quoted inet representation."""
476

477
def register_inet(oid=None, conn_or_curs=None):
478
    """Register inet type (deprecated)."""
479

480
def register_ipaddress(conn_or_curs=None):
481
    """
482
    Register ipaddress module support for inet/cidr types.
483
    
484
    Parameters:
485
    - conn_or_curs (connection/cursor, optional): Registration scope
486
    """
487

488
def cast_interface(s, cur=None):
489
    """Cast to IPv4/IPv6Interface."""
490

491
def cast_network(s, cur=None):
492
    """Cast to IPv4/IPv6Network."""
493

494
def adapt_ipaddress(obj):
495
    """Adapt ipaddress objects to SQL."""
496
```
497

498
**Usage Example:**
499

500
```python
501
import psycopg2
502
import ipaddress
503
from psycopg2.extras import register_ipaddress
504

505
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser")
506
register_ipaddress(conn)
507

508
# Use Python ipaddress objects
509
ip_addr = ipaddress.IPv4Address('192.168.1.1')
510
ip_network = ipaddress.IPv4Network('192.168.1.0/24')
511
ip_interface = ipaddress.IPv4Interface('192.168.1.1/24')
512

513
with conn.cursor() as cur:
514
    cur.execute(
515
        "INSERT INTO network_config (server_ip, subnet, interface) VALUES (%s, %s, %s)",
516
        (ip_addr, ip_network, ip_interface)
517
    )
518
    
519
    # Query network data
520
    cur.execute("SELECT server_ip, subnet FROM network_config")
521
    for row in cur.fetchall():
522
        print(f"IP: {row[0]}, Network: {row[1]}")
523
        print(f"Types: {type(row[0])}, {type(row[1])}")
524

525
conn.commit()
526
conn.close()
527
```
528

529
### HStore Support
530

531
Handle PostgreSQL hstore data type as Python dictionaries.
532

533
```python { .api }
534
class HstoreAdapter:
535
    """Adapter for dict to hstore."""
536
    
537
    def __init__(self, wrapped):
538
        """Initialize with dictionary."""
539
    
540
    def getquoted(self):
541
        """Return quoted hstore representation."""
542

543
def register_hstore(conn_or_curs, globally=False, unicode=False, oid=None, array_oid=None):
544
    """
545
    Register hstore support.
546
    
547
    Parameters:
548
    - conn_or_curs: Connection or cursor
549
    - globally (bool): Register globally
550
    - unicode (bool): Use unicode strings
551
    - oid (int, optional): HStore type OID
552
    - array_oid (int, optional): HStore array type OID
553
    """
554
```
555

556
**Usage Example:**
557

558
```python
559
import psycopg2
560
from psycopg2.extras import register_hstore
561

562
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser")
563

564
# Enable hstore extension in PostgreSQL first:
565
# CREATE EXTENSION IF NOT EXISTS hstore;
566

567
register_hstore(conn)
568

569
# Use Python dictionaries as hstore
570
metadata = {
571
    'color': 'blue',
572
    'size': 'large', 
573
    'material': 'cotton'
574
}
575

576
with conn.cursor() as cur:
577
    cur.execute(
578
        "INSERT INTO products (name, attributes) VALUES (%s, %s)",
579
        ("T-Shirt", metadata)
580
    )
581
    
582
    # Query hstore data
583
    cur.execute("SELECT attributes FROM products WHERE name = %s", ("T-Shirt",))
584
    result = cur.fetchone()
585
    print(type(result[0]))  # dict
586
    print(result[0]['color'])  # 'blue'
587
    
588
    # HStore operations
589
    cur.execute(
590
        "SELECT * FROM products WHERE attributes -> %s = %s",
591
        ('color', 'blue')
592
    )
593

594
conn.commit()
595
conn.close()
596
```
597

598
### Composite Type Support
599

600
Handle PostgreSQL composite types as Python objects.
601

602
```python { .api }
603
class CompositeCaster:
604
    """Caster for composite types."""
605
    
606
    def __init__(self, name, oid, attrs, array_oid=None, schema=None):
607
        """
608
        Initialize composite caster.
609
        
610
        Parameters:
611
        - name (str): Type name
612
        - oid (int): Type OID
613
        - attrs (list): Attribute descriptions
614
        - array_oid (int, optional): Array type OID
615
        - schema (str, optional): Schema name
616
        """
617
    
618
    def parse(self, s, curs):
619
        """Parse composite string."""
620
    
621
    def make(self, values):
622
        """Create Python object."""
623

624
def register_composite(name, conn_or_curs, globally=False, factory=None):
625
    """
626
    Register composite type.
627
    
628
    Parameters:
629
    - name (str): Composite type name
630
    - conn_or_curs: Connection or cursor
631
    - globally (bool): Register globally  
632
    - factory (callable, optional): Object factory
633
    """
634
```
635

636
**Usage Example:**
637

638
```python
639
import psycopg2
640
from psycopg2.extras import register_composite
641
from collections import namedtuple
642

643
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser")
644

645
# Create composite type in PostgreSQL:
646
# CREATE TYPE address AS (street text, city text, zip text);
647

648
# Register composite type
649
register_composite('address', conn)
650

651
# Or with custom factory
652
Address = namedtuple('Address', ['street', 'city', 'zip'])
653
register_composite('address', conn, factory=Address)
654

655
with conn.cursor() as cur:
656
    # Insert composite data
657
    cur.execute(
658
        "INSERT INTO customers (name, address) VALUES (%s, %s)",
659
        ("John Doe", Address("123 Main St", "Anytown", "12345"))
660
    )
661
    
662
    # Query composite data
663
    cur.execute("SELECT address FROM customers WHERE name = %s", ("John Doe",))
664
    address = cur.fetchone()[0]
665
    print(f"Street: {address.street}")
666
    print(f"City: {address.city}")
667

668
conn.commit()
669
conn.close()
670
```
671

672
## Types
673

674
### Adapter Interface
675

676
```python { .api }
677
class ISQLQuote:
678
    """Interface for SQL adapters."""
679
    
680
    def getquoted(self) -> bytes:
681
        """Return quoted SQL representation."""
682
```
683

684
### Core Type Constants
685

686
```python { .api }
687
# PostgreSQL type OIDs
688
BOOLEAN: int
689
BYTEA: int  
690
DATE: int
691
DECIMAL: int
692
FLOAT: int
693
INTEGER: int
694
INTERVAL: int
695
TIME: int
696
TIMESTAMP: int
697
VARCHAR: int
698
TEXT: int
699

700
# Array type OIDs  
701
BOOLEANARRAY: int
702
BYTESARRAY: int
703
DATEARRAY: int
704
DECIMALARRAY: int
705
FLOATARRAY: int
706
INTEGERARRAY: int
707
INTERVALARRAY: int
708
TIMEARRAY: int
709
STRINGARRAY: int
710

711
# JSON type OIDs
712
JSON_OID: int      # 114
713
JSONB_OID: int     # 3802
714
JSONARRAY_OID: int # 199
715
JSONBARRAY_OID: int # 3807
716
```
717

718
### Range Type Interface
719

720
```python { .api }
721
class Range:
722
    lower: Any  # Lower bound value
723
    upper: Any  # Upper bound value
724
    isempty: bool  # Empty range flag
725
    lower_inf: bool  # Infinite lower bound
726
    upper_inf: bool  # Infinite upper bound  
727
    lower_inc: bool  # Inclusive lower bound
728
    upper_inc: bool  # Inclusive upper bound
729
    
730
    def __contains__(self, x: Any) -> bool:
731
        """Check if value in range."""
732
```
733

734
### Adaptation Registry
735

736
```python { .api }
737
adapters: dict  # Global adapter registry
738
encodings: dict  # Encoding name mappings
739
string_types: tuple  # String type checking
740
binary_types: tuple  # Binary type checking
741
```