0
# psycopg2-binary
1

2
PostgreSQL database adapter for Python with thread-safe connection pooling and comprehensive SQL operations. psycopg2 is the most popular PostgreSQL adapter for Python, providing full DB API 2.0 compliance, thread safety, and high-performance PostgreSQL integration through its C-based libpq wrapper.
3

4
## Package Information
5

6
- **Package Name**: psycopg2-binary
7
- **Package Type**: Library
8
- **Language**: Python
9
- **Installation**: `pip install psycopg2-binary`
10

11
## Core Imports
12

13
```python
14
import psycopg2
15
```
16

17
Common specialized imports:
18

19
```python
20
from psycopg2 import extensions, extras, pool, sql, errors, errorcodes
21
from psycopg2.extras import DictCursor, RealDictCursor, Json
22
from psycopg2.pool import SimpleConnectionPool, ThreadedConnectionPool
23
```
24

25
## Basic Usage
26

27
```python
28
import psycopg2
29
from psycopg2.extras import DictCursor
30

31
# Basic connection
32
conn = psycopg2.connect(
33
    host="localhost",
34
    database="mydb",
35
    user="postgres",
36
    password="password"
37
)
38

39
# Execute a simple query
40
cur = conn.cursor()
41
cur.execute("SELECT version();")
42
version = cur.fetchone()
43
print(version)
44

45
# Dictionary cursor for named column access
46
cur = conn.cursor(cursor_factory=DictCursor)
47
cur.execute("SELECT id, name FROM users WHERE id = %s", (1,))
48
user = cur.fetchone()
49
print(user['name'])  # Access by column name
50

51
# Safe SQL composition
52
from psycopg2 import sql
53
query = sql.SQL("SELECT * FROM {} WHERE {} = %s").format(
54
    sql.Identifier('users'),
55
    sql.Identifier('name')
56
)
57
cur.execute(query, ('Alice',))
58

59
# Connection pooling for multi-threaded applications
60
from psycopg2.pool import ThreadedConnectionPool
61
pool = ThreadedConnectionPool(1, 20,
62
    host="localhost",
63
    database="mydb",
64
    user="postgres",
65
    password="password"
66
)
67

68
# Use pooled connection
69
conn = pool.getconn()
70
# ... perform operations
71
pool.putconn(conn)
72

73
# Always close connections
74
cur.close()
75
conn.close()
76
```
77

78
## Architecture
79

80
psycopg2 is built on a layered architecture optimized for performance and PostgreSQL feature coverage:
81

82
- **C Extension Core (_psycopg)**: High-performance libpq wrapper providing the foundation
83
- **DB API 2.0 Layer**: Standard Python database interface with connection and cursor objects  
84
- **Extensions Module**: PostgreSQL-specific features like custom types, adapters, and async operations
85
- **Extras Module**: Enhanced cursors, bulk operations, JSON/JSONB support, and specialized functionality
86
- **Type System**: Comprehensive bidirectional adaptation between Python and PostgreSQL data types
87
- **Connection Pooling**: Thread-safe connection management for scalable applications
88

89
## Capabilities
90

91
### Database Connections and Cursors
92

93
Core database connectivity with connection management, cursor operations, transaction control, and basic SQL execution. Provides the foundation of DB API 2.0 compliance.
94

95
```python { .api }
96
def connect(dsn=None, connection_factory=None, cursor_factory=None, **kwargs): ...
97

98
class connection:
99
    def cursor(self, name=None, cursor_factory=None, scrollable=None, withhold=False): ...
100
    def commit(self): ...
101
    def rollback(self): ...
102
    def close(self): ...
103

104
class cursor:
105
    def execute(self, query, vars=None): ...
106
    def executemany(self, query, vars_list): ...
107
    def fetchone(self): ...
108
    def fetchmany(self, size=None): ...
109
    def fetchall(self): ...
110
    def close(self): ...
111
```
112

113
[Database Connections and Cursors](./connections-cursors.md)
114

115
### Type Adaptation and PostgreSQL Types
116

117
Comprehensive type system for converting between Python objects and PostgreSQL data types, including arrays, JSON/JSONB, ranges, IP addresses, and custom type registration.
118

119
```python { .api }
120
def adapt(obj): ...
121
def register_adapter(typ, callable): ...
122
def new_type(oids, name, castfunc): ...
123
def register_type(obj, scope=None): ...
124

125
# PostgreSQL-specific types
126
class Json:
127
    def __init__(self, adapted, dumps=None): ...
128

129
class Range:
130
    def __init__(self, lower=None, upper=None, bounds='[)', empty=False): ...
131
```
132

133
[Type Adaptation and PostgreSQL Types](./types-adaptation.md)
134

135
### Enhanced Cursors and Row Access
136

137
Specialized cursor implementations providing dictionary-like row access, named tuple rows, real dictionary rows, and logging capabilities for debugging and development.
138

139
```python { .api }
140
class DictCursor(cursor):
141
    def __init__(self, *args, **kwargs): ...
142

143
class RealDictCursor(cursor):
144
    def __init__(self, *args, **kwargs): ...
145

146
class NamedTupleCursor(cursor):
147
    def __init__(self, *args, **kwargs): ...
148

149
class DictRow(list):
150
    def __getitem__(self, x): ...
151
    def keys(self): ...
152
    def values(self): ...
153
    def items(self): ...
154
```
155

156
[Enhanced Cursors and Row Access](./cursors-rows.md)
157

158
### Connection Pooling
159

160
Thread-safe and simple connection pools for managing database connections in multi-threaded applications, with automatic connection recycling and configurable pool sizes.
161

162
```python { .api }
163
class AbstractConnectionPool:
164
    def __init__(self, minconn, maxconn, *args, **kwargs): ...
165

166
class SimpleConnectionPool(AbstractConnectionPool):
167
    def __init__(self, minconn, maxconn, *args, **kwargs): ...
168
    def getconn(self, key=None): ...
169
    def putconn(self, conn, key=None, close=False): ...
170

171
class ThreadedConnectionPool(SimpleConnectionPool):
172
    def getconn(self, key=None): ...
173
    def putconn(self, conn=None, key=None, close=False): ...
174
    def closeall(self): ...
175
```
176

177
[Connection Pooling](./connection-pooling.md)
178

179
### SQL Composition and Query Building
180

181
Safe SQL query construction using composable objects for identifiers, literals, and SQL fragments, preventing SQL injection while enabling dynamic query building.
182

183
```python { .api }
184
class Composable:
185
    def as_string(self, context): ...
186
    def __add__(self, other): ...
187
    def __mul__(self, n): ...
188

189
class SQL(Composable):
190
    def __init__(self, string): ...
191
    def format(self, *args, **kwargs): ...
192
    def join(self, seq): ...
193

194
class Identifier(Composable):
195
    def __init__(self, *strings): ...
196

197
class Literal(Composable):
198
    def __init__(self, wrapped): ...
199

200
class Composed(Composable):
201
    def __init__(self, seq): ...
202
    def join(self, joiner): ...
203
```
204

205
[SQL Composition and Query Building](./sql-composition.md)
206

207
### Advanced PostgreSQL Features
208

209
Large objects, server-side cursors, asynchronous operations, notifications (LISTEN/NOTIFY), replication support, and other PostgreSQL-specific advanced functionality.
210

211
```python { .api }
212
class lobject:
213
    def __init__(self, conn, oid=0, mode='r', new_oid=None, new_file=None): ...
214
    def read(self, size=-1): ...
215
    def write(self, data): ...
216

217
# Asynchronous support
218
def set_wait_callback(f): ...
219
def get_wait_callback(): ...
220

221
# Replication
222
class ReplicationConnection(connection): ...
223
class ReplicationCursor(cursor): ...
224
```
225

226
[Advanced PostgreSQL Features](./advanced-features.md)
227

228
### Error Handling and Diagnostics
229

230
Comprehensive PostgreSQL error code mapping, exception hierarchy, error diagnostics, and debugging support for robust database application development.
231

232
```python { .api }
233
# Exception hierarchy
234
class Error(Exception): ...
235
class DatabaseError(Error): ...
236
class IntegrityError(DatabaseError): ...
237
class ProgrammingError(DatabaseError): ...
238

239
# Error utilities
240
def lookup(code): ...  # from errorcodes module
241

242
class Diagnostics:
243
    def __init__(self, exception): ...
244
```
245

246
[Error Handling and Diagnostics](./error-handling.md)
247

248
## Types
249

250
### Connection Parameters
251

252
```python { .api }
253
ConnectionParams = {
254
    'host': str,         # Database server host
255
    'port': int,         # Port number (default 5432)
256
    'database': str,     # Database name
257
    'user': str,         # Username
258
    'password': str,     # Password
259
    'connect_timeout': int,  # Connection timeout in seconds
260
    'sslmode': str,      # SSL mode ('disable', 'require', etc.)
261
    'application_name': str,  # Application name for logging
262
    # Many other PostgreSQL connection parameters supported
263
}
264
```
265

266
### Cursor Description
267

268
```python { .api }
269
ColumnDescription = tuple[
270
    str,    # name
271
    int,    # type_code  
272
    int,    # display_size
273
    int,    # internal_size
274
    int,    # precision
275
    int,    # scale
276
    bool    # null_ok
277
]
278
```
279

280
### Constants
281

282
```python { .api }
283
# DB API 2.0 constants
284
apilevel: str = "2.0"
285
threadsafety: int = 2
286
paramstyle: str = "pyformat"
287

288
# Isolation levels
289
ISOLATION_LEVEL_AUTOCOMMIT: int = 0
290
ISOLATION_LEVEL_READ_UNCOMMITTED: int = 4
291
ISOLATION_LEVEL_READ_COMMITTED: int = 1
292
ISOLATION_LEVEL_REPEATABLE_READ: int = 2
293
ISOLATION_LEVEL_SERIALIZABLE: int = 3
294

295
# Connection status
296
STATUS_READY: int = 1
297
STATUS_BEGIN: int = 2
298
STATUS_IN_TRANSACTION: int = 2
299
```