0
# psycopg-binary
1

2
PostgreSQL database adapter for Python with C optimizations that provide significant performance improvements over the pure Python implementation. This package contains pre-compiled C extensions that enhance core database operations including connection handling, query execution, and data type conversions.
3

4
## Package Information
5

6
- **Package Name**: psycopg-binary
7
- **Language**: Python
8
- **Installation**: `pip install "psycopg[binary]"` (recommended) or `pip install psycopg-binary`
9
- **Python Versions**: 3.8-3.13
10
- **Platforms**: Windows, macOS, Linux (pre-built wheels)
11

12
## Core Imports
13

14
**Important**: psycopg-binary should not be imported directly. It provides optimization extensions that are automatically used by the main psycopg package when available.
15

16
```python
17
# Import the main psycopg package - optimizations are used automatically
18
import psycopg
19
```
20

21
The package will only work correctly when psycopg is already imported:
22

23
```python
24
# This will raise ImportError if psycopg is not imported first
25
import psycopg_binary  # Only for accessing version information
26
```
27

28
## Basic Usage
29

30
psycopg-binary works transparently as a drop-in performance enhancement. No code changes are required:
31

32
```python
33
import psycopg
34
from datetime import datetime
35

36
# All operations automatically use C optimizations when psycopg-binary is installed
37
conn = psycopg.connect("dbname=test user=postgres")
38
cur = conn.cursor()
39

40
# Query execution is automatically optimized
41
cur.execute("SELECT * FROM users WHERE id = %s", (123,))
42
rows = cur.fetchall()
43

44
# Data type conversions use optimized implementations
45
cur.execute("INSERT INTO logs (timestamp, data) VALUES (%s, %s)", 
46
           (datetime.now(), {"key": "value"}))
47

48
conn.commit()
49
cur.close()
50
conn.close()
51
```
52

53
To verify that binary optimizations are active:
54

55
```python
56
import psycopg
57

58
# Check if binary optimizations are loaded
59
try:
60
    import psycopg_binary
61
    from psycopg_binary import pq
62
    print(f"Binary optimizations active: {pq.__impl__}")  # Should print "binary"
63
    print(f"libpq version: {pq.version()}")
64
except ImportError:
65
    print("Binary optimizations not available")
66
```
67

68
## Architecture
69

70
psycopg-binary provides Cython-compiled optimizations organized into specialized modules:
71

72
- **Core Module (`psycopg_binary`)**: Package metadata and entry point validation
73
- **Optimization Engine (`_psycopg`)**: Core performance enhancements for data transformation, connection handling, and query execution
74
- **Protocol Layer (`pq`)**: Optimized PostgreSQL protocol implementations
75
- **Type System (`types`)**: Fast data type conversions and array handling
76
- **UUID Support (`_uuid`)**: Optimized UUID operations
77

78
The package integrates seamlessly with psycopg's architecture, replacing performance-critical components with C implementations while maintaining full API compatibility.
79

80
## Capabilities
81

82
### Package Information Access
83

84
Access to package version and metadata for version compatibility verification.
85

86
```python { .api }
87
# Version information (only accessible after psycopg is imported)
88
from psycopg_binary import __version__
89

90
# Version retrieval function
91
def get_version() -> str:
92
    """Get the psycopg-binary package version."""
93
```
94

95
### Core Performance Optimizations
96

97
High-performance C implementations of core psycopg functionality including data transformation, connection management, and query execution.
98

99
```python { .api }
100
from psycopg_binary._psycopg import Transformer
101

102
class Transformer:
103
    """
104
    High-performance data transformation and adaptation context.
105
    
106
    Provides optimized implementations for converting Python objects
107
    to PostgreSQL wire format and vice versa.
108
    """
109
    
110
    types: tuple[int, ...] | None
111
    formats: list[Format] | None
112
    
113
    def __init__(self, context: AdaptContext | None = None) -> None:
114
        """Initialize transformer with optional adaptation context."""
115
    
116
    @classmethod
117
    def from_context(cls, context: AdaptContext | None) -> "Transformer":
118
        """Create transformer from existing adaptation context."""
119
    
120
    @property
121
    def connection(self) -> BaseConnection[Any] | None:
122
        """Get the associated database connection."""
123
    
124
    @property
125
    def encoding(self) -> str:
126
        """Get the current character encoding."""
127
    
128
    @property
129
    def adapters(self) -> AdaptersMap:
130
        """Get the adapter registry for type conversions."""
131
    
132
    @property
133
    def pgresult(self) -> PGresult | None:
134
        """Get the current PostgreSQL result object."""
135
    
136
    def set_pgresult(
137
        self,
138
        result: PGresult | None,
139
        *,
140
        set_loaders: bool = True,
141
        format: Format | None = None,
142
    ) -> None:
143
        """Set the PostgreSQL result and configure type loaders."""
144
    
145
    def set_dumper_types(self, types: Sequence[int], format: Format) -> None:
146
        """Configure dumpers for specific PostgreSQL types."""
147
    
148
    def set_loader_types(self, types: Sequence[int], format: Format) -> None:
149
        """Configure loaders for specific PostgreSQL types."""
150
    
151
    def dump_sequence(
152
        self, params: Sequence[Any], formats: Sequence[PyFormat]
153
    ) -> Sequence[Buffer | None]:
154
        """Convert Python objects to PostgreSQL wire format."""
155
    
156
    def as_literal(self, obj: Any) -> bytes:
157
        """Convert object to PostgreSQL literal representation."""
158
    
159
    def get_dumper(self, obj: Any, format: PyFormat) -> Dumper:
160
        """Get appropriate dumper for Python object."""
161
    
162
    def load_rows(self, row0: int, row1: int, make_row: RowMaker[Row]) -> list[Row]:
163
        """Load multiple rows from result set with optimized conversion."""
164
    
165
    def load_row(self, row: int, make_row: RowMaker[Row]) -> Row:
166
        """Load single row from result set with optimized conversion."""
167
    
168
    def load_sequence(self, record: Sequence[Buffer | None]) -> tuple[Any, ...]:
169
        """Convert PostgreSQL wire format to Python objects."""
170
    
171
    def get_loader(self, oid: int, format: Format) -> Loader:
172
        """Get appropriate loader for PostgreSQL type OID."""
173
```
174

175
### Connection and Execution Generators
176

177
Optimized asynchronous generators for database connection management and query execution.
178

179
```python { .api }
180
from psycopg_binary._psycopg import (
181
    connect, cancel, execute, send, fetch_many, fetch, 
182
    pipeline_communicate, wait_c
183
)
184

185
def connect(conninfo: str, *, timeout: float = 0.0) -> PQGenConn[PGconn]:
186
    """
187
    Optimized connection generator for establishing database connections.
188
    
189
    Args:
190
        conninfo: PostgreSQL connection string
191
        timeout: Connection timeout in seconds (0 = no timeout)
192
    
193
    Returns:
194
        Generator yielding connection establishment states
195
    """
196

197
def cancel(
198
    cancel_conn: PGcancelConn, *, timeout: float = 0.0
199
) -> PQGenConn[None]:
200
    """
201
    Generator for canceling ongoing database operations.
202
    
203
    Args:
204
        cancel_conn: PostgreSQL cancellation connection
205
        timeout: Cancellation timeout in seconds (0 = no timeout)
206
    
207
    Returns:
208
        Generator for cancellation process
209
    """
210

211
def execute(pgconn: PGconn) -> PQGen[list[PGresult]]:
212
    """
213
    Optimized query execution generator.
214
    
215
    Args:
216
        pgconn: PostgreSQL connection object
217
    
218
    Returns:
219
        Generator yielding query execution results
220
    """
221

222
def send(pgconn: PGconn) -> PQGen[None]:
223
    """
224
    Optimized generator for sending queries to PostgreSQL.
225
    
226
    Args:
227
        pgconn: PostgreSQL connection object
228
    
229
    Returns:
230
        Generator for send operation
231
    """
232

233
def fetch_many(pgconn: PGconn) -> PQGen[list[PGresult]]:
234
    """
235
    Generator for fetching multiple results efficiently.
236
    
237
    Args:
238
        pgconn: PostgreSQL connection object
239
    
240
    Returns:
241
        Generator yielding multiple results
242
    """
243

244
def fetch(pgconn: PGconn) -> PQGen[PGresult | None]:
245
    """
246
    Generator for fetching single results efficiently.
247
    
248
    Args:
249
        pgconn: PostgreSQL connection object
250
    
251
    Returns:
252
        Generator yielding single result or None
253
    """
254

255
def pipeline_communicate(
256
    pgconn: PGconn, commands: Deque[PipelineCommand]
257
) -> PQGen[list[list[PGresult]]]:
258
    """
259
    Optimized pipeline communication for batch operations.
260
    
261
    Args:
262
        pgconn: PostgreSQL connection object
263
        commands: Queue of pipeline commands to execute
264
    
265
    Returns:
266
        Generator yielding batched results
267
    """
268

269
def wait_c(
270
    gen: PQGen[RV], fileno: int, interval: float | None = None
271
) -> RV:
272
    """
273
    Optimized waiting function for generators.
274
    
275
    Args:
276
        gen: Generator to wait for
277
        fileno: File descriptor for polling
278
        interval: Polling interval in seconds
279
    
280
    Returns:
281
        Generator result when complete
282
    """
283
```
284

285
### COPY Operations Support
286

287
High-performance functions for PostgreSQL COPY operations with optimized text and binary format handling.
288

289
```python { .api }
290
from psycopg_binary._psycopg import (
291
    format_row_text, format_row_binary, 
292
    parse_row_text, parse_row_binary
293
)
294

295
def format_row_text(
296
    row: Sequence[Any], tx: Transformer, out: bytearray | None = None
297
) -> bytearray:
298
    """
299
    Format row data for PostgreSQL COPY text format.
300
    
301
    Args:
302
        row: Row data as sequence of values
303
        tx: Transformer for type conversions
304
        out: Optional output buffer to reuse
305
    
306
    Returns:
307
        Formatted row data as bytearray
308
    """
309

310
def format_row_binary(
311
    row: Sequence[Any], tx: Transformer, out: bytearray | None = None
312
) -> bytearray:
313
    """
314
    Format row data for PostgreSQL COPY binary format.
315
    
316
    Args:
317
        row: Row data as sequence of values
318
        tx: Transformer for type conversions
319
        out: Optional output buffer to reuse
320
    
321
    Returns:
322
        Formatted row data as bytearray
323
    """
324

325
def parse_row_text(data: Buffer, tx: Transformer) -> tuple[Any, ...]:
326
    """
327
    Parse row data from PostgreSQL COPY text format.
328
    
329
    Args:
330
        data: Raw text format data
331
        tx: Transformer for type conversions
332
    
333
    Returns:
334
        Parsed row data as tuple
335
    """
336

337
def parse_row_binary(data: Buffer, tx: Transformer) -> tuple[Any, ...]:
338
    """
339
    Parse row data from PostgreSQL COPY binary format.
340
    
341
    Args:
342
        data: Raw binary format data
343
        tx: Transformer for type conversions
344
    
345
    Returns:
346
        Parsed row data as tuple
347
    """
348
```
349

350
### Array Optimization Functions
351

352
Specialized functions for handling PostgreSQL arrays with optimized parsing and loading.
353

354
```python { .api }
355
from psycopg_binary._psycopg import array_load_text, array_load_binary
356

357
def array_load_text(
358
    data: Buffer, loader: Loader, delimiter: bytes = b","
359
) -> list[Any]:
360
    """
361
    Load PostgreSQL array from text representation.
362
    
363
    Args:
364
        data: Array data in text format
365
        loader: Type loader for array elements
366
        delimiter: Element delimiter (default comma)
367
    
368
    Returns:
369
        Parsed array as Python list
370
    """
371

372
def array_load_binary(data: Buffer, tx: Transformer) -> list[Any]:
373
    """
374
    Load PostgreSQL array from binary representation.
375
    
376
    Args:
377
        data: Array data in binary format
378
        tx: Transformer for element type conversions
379
    
380
    Returns:
381
        Parsed array as Python list
382
    """
383
```
384

385
### PostgreSQL Protocol Layer (pq module)
386

387
Optimized libpq bindings providing direct access to PostgreSQL client library functionality with C-level performance.
388

389
```python { .api }
390
from psycopg_binary import pq
391

392
# Module-level constants and functions
393
__impl__: str = "binary"  # Implementation identifier
394
__build_version__: int  # Build-time PostgreSQL version
395

396
def version() -> int:
397
    """
398
    Get the libpq version number.
399
    
400
    Returns:
401
        Integer version number of the linked libpq library
402
    """
403

404
# Core libpq object classes
405
class PGconn:
406
    """
407
    Optimized PostgreSQL connection object.
408
    
409
    Provides C-level performance for connection management,
410
    query execution, and result processing.
411
    """
412

413
class PGresult:
414
    """
415
    Optimized PostgreSQL query result object.
416
    
417
    Provides efficient access to query results with
418
    minimal Python object overhead.
419
    """
420

421
class PGcancel:
422
    """
423
    Optimized PostgreSQL query cancellation object.
424
    
425
    Enables efficient cancellation of running queries
426
    with reduced latency.
427
    """
428
```
429

430
### UUID Optimization Support
431

432
Enhanced UUID handling with optimized creation and conversion operations.
433

434
```python { .api }
435
from psycopg_binary._uuid import UUID, SafeUUID_unknown
436

437
# Re-exported standard UUID class
438
UUID = uuid.UUID
439

440
# Re-exported SafeUUID enum member  
441
SafeUUID_unknown = uuid.SafeUUID.unknown
442

443
class _WritableUUID(UUID):
444
    """
445
    Internal optimization class for fast UUID creation.
446
    
447
    This class has the same memory layout as UUID but allows
448
    writing to normally read-only attributes for performance.
449
    Used internally by C extensions for efficient UUID instantiation.
450
    """
451
```
452

453
## Types
454

455
Core type definitions and imports required for the optimization interfaces:
456

457
```python { .api }
458
from typing import Any, Sequence, Deque, TypeVar
459
from psycopg import BaseConnection
460
from psycopg import abc, pq
461
from psycopg.rows import Row, RowMaker
462
from psycopg.adapt import AdaptersMap, PyFormat
463
from psycopg.pq.abc import PGcancelConn, PGconn, PGresult
464
import uuid
465

466
# Type aliases for buffer operations
467
Buffer = bytes | bytearray | memoryview
468

469
# Format types for data conversion
470
Format = pq.Format  # Binary or text format for PostgreSQL data
471
PyFormat = int  # Python format code
472

473
# Core psycopg adaptation interfaces
474
AdaptContext = abc.AdaptContext
475
Dumper = abc.Dumper  # Converts Python objects to PostgreSQL format
476
Loader = abc.Loader  # Converts PostgreSQL data to Python objects
477

478
# Generator and pipeline interfaces
479
PipelineCommand = abc.PipelineCommand
480
PQGen = abc.PQGen[Any]  # Generic PostgreSQL generator
481
PQGenConn = abc.PQGenConn[Any]  # Connection generator
482

483
# Generic return value type
484
RV = TypeVar('RV')  # Return value type variable
485

486
# Base connection type for optimization context
487
BaseConnectionType = BaseConnection[Any]
488
```
489

490
## Installation Notes
491

492
- **Recommended Installation**: Use `pip install "psycopg[binary]"` to install both psycopg and psycopg-binary with matching versions
493
- **Alternative**: `pip install psycopg-binary` after installing psycopg separately (version compatibility required)
494
- **Requirements**: 
495
  - psycopg >= 3.0.0 must be installed and imported before importing psycopg_binary
496
  - pip >= 20.3 required for proper installation
497
- **Platform Support**: Pre-built wheels available for Windows, macOS, and Linux
498
- **Build Requirements**: If building from source, requires PostgreSQL development headers and C compiler
499
- **Python Support**: CPython 3.8-3.13 (PyPy not supported due to C extensions)
500

501
## Performance Benefits
502

503
Installing psycopg-binary provides significant performance improvements:
504

505
- **Data Type Conversions**: 2-10x faster conversion between Python and PostgreSQL types
506
- **Query Execution**: Reduced overhead in query processing and result handling  
507
- **Connection Management**: Optimized connection establishment and management
508
- **Array Operations**: Highly optimized PostgreSQL array parsing and formatting
509
- **COPY Operations**: High-performance bulk data import/export operations
510

511
The optimizations are most beneficial for applications with high database throughput, frequent type conversions, or bulk data operations.