0
# psycopg2
1

2
psycopg2 is the most popular PostgreSQL database adapter for the Python programming language. It provides complete implementation of the Python DB API 2.0 specification with thread safety for heavily multi-threaded applications. The library is primarily implemented in C as a libpq wrapper, delivering both efficiency and security with support for client-side and server-side cursors, asynchronous communication and notifications, COPY TO/COPY FROM operations, and comprehensive type adaptation between Python and PostgreSQL data types.
3

4
## Package Information
5

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

10
Note: `psycopg2-binary` provides pre-compiled wheels for easier installation, while `psycopg2` requires compilation and PostgreSQL development headers.
11

12
## Core Imports
13

14
```python
15
import psycopg2
16
```
17

18
For specific functionality:
19

20
```python
21
from psycopg2 import connect, Error
22
from psycopg2.extensions import connection, cursor, adapt, register_adapter
23
from psycopg2.extras import DictCursor, RealDictCursor, execute_batch, execute_values
24
from psycopg2.sql import SQL, Identifier, Literal
25
from psycopg2.pool import SimpleConnectionPool, ThreadedConnectionPool
26
from psycopg2.tz import FixedOffsetTimezone, LocalTimezone
27
```
28

29
## Basic Usage
30

31
```python
32
import psycopg2
33
from psycopg2.extras import RealDictCursor
34

35
# Connect to PostgreSQL database
36
conn = psycopg2.connect(
37
    host="localhost",
38
    database="mydb",
39
    user="myuser",
40
    password="mypassword"
41
)
42

43
# Create cursor and execute query
44
with conn.cursor(cursor_factory=RealDictCursor) as cur:
45
    cur.execute("SELECT * FROM users WHERE age > %s", (25,))
46
    users = cur.fetchall()
47
    
48
    for user in users:
49
        print(f"{user['name']}: {user['email']}")
50

51
# Insert data
52
with conn.cursor() as cur:
53
    cur.execute(
54
        "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)",
55
        ("John Doe", "john@example.com", 30)
56
    )
57
    conn.commit()
58

59
conn.close()
60
```
61

62
## Architecture
63

64
psycopg2 follows the Python DB API 2.0 specification with these core components:
65

66
- **Connection Objects**: Database connections with transaction management and configuration
67
- **Cursor Objects**: Execute queries and fetch results with various cursor types for different use cases
68
- **Type System**: Comprehensive adaptation between Python and PostgreSQL types including arrays, JSON, ranges, and custom types
69
- **Extensions**: Advanced features like async operations, connection pooling, and specialized data types
70
- **Error Handling**: Complete PostgreSQL error code mapping to Python exceptions
71

72
The library supports both synchronous and asynchronous operations, providing flexibility for different application architectures from simple scripts to high-performance web applications.
73

74
## Capabilities
75

76
### Database Connections and Cursors
77

78
Core database connectivity with connection management, cursor operations, transaction handling, and various cursor types for different result formats.
79

80
```python { .api }
81
def connect(dsn=None, connection_factory=None, cursor_factory=None, **kwargs):
82
    """Create a new database connection."""
83

84
class connection:
85
    def cursor(self, name=None, cursor_factory=None):
86
        """Create a new cursor."""
87
    def commit(self):
88
        """Commit current transaction."""
89
    def rollback(self):
90
        """Rollback current transaction."""
91
    def close(self):
92
        """Close the connection."""
93

94
class cursor:
95
    def execute(self, query, vars=None):
96
        """Execute a database operation."""
97
    def fetchone(self):
98
        """Fetch the next row of query result."""
99
    def fetchmany(self, size=None):
100
        """Fetch multiple rows."""
101
    def fetchall(self):
102
        """Fetch all remaining rows."""
103
```
104

105
[Database Connections and Cursors](./connections-cursors.md)
106

107
### Advanced Cursor Types
108

109
Specialized cursor classes that return results as dictionaries, named tuples, or provide logging capabilities for development and debugging.
110

111
```python { .api }
112
class DictCursor(cursor):
113
    """Cursor returning dict-like rows."""
114

115
class RealDictCursor(cursor):
116
    """Cursor with real dict rows."""
117

118
class NamedTupleCursor(cursor):
119
    """Cursor returning named tuples."""
120

121
class LoggingCursor(cursor):
122
    """Cursor that logs executed queries."""
123
```
124

125
[Advanced Cursor Types](./advanced-cursors.md)
126

127
### SQL Composition
128

129
Safe SQL query construction with automatic quoting, parameter placeholders, and composable SQL elements to prevent SQL injection vulnerabilities.
130

131
```python { .api }
132
class SQL:
133
    """Raw SQL snippet."""
134
    def __init__(self, string): ...
135
    def format(self, *args, **kwargs): ...
136

137
class Identifier:
138
    """SQL identifier (quoted)."""
139
    def __init__(self, *strings): ...
140

141
class Literal:
142
    """SQL literal value."""
143

144
class Placeholder:
145
    """Parameter placeholder."""
146
```
147

148
[SQL Composition](./sql-composition.md)
149

150
### Type Adaptation and Casting
151

152
Comprehensive type conversion system between Python and PostgreSQL types, including support for arrays, JSON, ranges, UUID, network types, and custom type registration.
153

154
```python { .api }
155
def adapt(obj):
156
    """Adapt Python object to SQL."""
157

158
def register_adapter(type, adapter):
159
    """Register object adapter."""
160

161
def new_type(oids, name, castfunc):
162
    """Create new typecaster."""
163

164
def register_type(obj, scope=None):
165
    """Register typecaster."""
166

167
class Binary:
168
    """Binary data adapter."""
169

170
class Json:
171
    """JSON adapter class."""
172
```
173

174
[Type Adaptation and Casting](./type-adaptation.md)
175

176
### Connection Pooling
177

178
Thread-safe and non-thread-safe connection pools for managing database connections efficiently in multi-threaded applications.
179

180
```python { .api }
181
class SimpleConnectionPool:
182
    """Non-threadsafe connection pool."""
183
    def __init__(self, minconn, maxconn, *args, **kwargs): ...
184
    def getconn(self, key=None): ...
185
    def putconn(self, conn, key=None, close=False): ...
186

187
class ThreadedConnectionPool:
188
    """Thread-safe connection pool."""
189
    def __init__(self, minconn, maxconn, *args, **kwargs): ...
190
    def getconn(self, key=None): ...
191
    def putconn(self, conn=None, key=None, close=False): ...
192
```
193

194
[Connection Pooling](./connection-pooling.md)
195

196
### PostgreSQL Replication
197

198
Logical and physical replication support for PostgreSQL streaming replication, including replication slot management and message handling.
199

200
```python { .api }
201
class LogicalReplicationConnection:
202
    """Logical replication connection."""
203

204
class PhysicalReplicationConnection:
205
    """Physical replication connection."""
206

207
class ReplicationCursor:
208
    """Cursor for replication connections."""
209
    def create_replication_slot(self, slot_name, slot_type=None, output_plugin=None): ...
210
    def start_replication(self, slot_name=None, **kwargs): ...
211
```
212

213
[PostgreSQL Replication](./replication.md)
214

215
### Error Handling and Diagnostics
216

217
Complete PostgreSQL error code mapping to Python exceptions with detailed error information and diagnostic capabilities.
218

219
```python { .api }
220
class Error(Exception):
221
    """Base exception class."""
222

223
class DatabaseError(Error):
224
    """Database engine errors."""
225

226
class IntegrityError(DatabaseError):
227
    """Database integrity violations."""
228

229
class ProgrammingError(DatabaseError):
230
    """SQL programming errors."""
231

232
def lookup(code):
233
    """Look up exception class by error code."""
234
```
235

236
[Error Handling and Diagnostics](./error-handling.md)
237

238
### Batch Operations and Utilities
239

240
Efficient batch execution functions and utility operations for improved performance with multiple queries and specialized database operations.
241

242
```python { .api }
243
def execute_batch(cur, sql, argslist, page_size=100):
244
    """Execute SQL with multiple parameter sets efficiently."""
245

246
def execute_values(cur, sql, argslist, template=None, page_size=100, fetch=False):
247
    """Execute INSERT with VALUES clause."""
248

249
def wait_select(conn):
250
    """Wait callback for select-based waiting."""
251

252
def make_dsn(dsn=None, **kwargs):
253
    """Build connection string from parameters."""
254

255
def parse_dsn(dsn):
256
    """Parse connection string into components."""
257

258
def quote_ident(name, scope=None):
259
    """Quote SQL identifier."""
260
```
261

262
[Batch Operations and Utilities](./batch-operations.md)
263

264
### Timezone Support
265

266
Timezone handling utilities for PostgreSQL timestamp types with timezone information, including fixed offset and local timezone support.
267

268
```python { .api }
269
class FixedOffsetTimezone(datetime.tzinfo):
270
    """Fixed UTC offset timezone."""
271
    def __init__(self, offset=None, name=None): ...
272

273
class LocalTimezone(datetime.tzinfo):
274
    """Platform's local timezone."""
275

276
LOCAL: LocalTimezone  # Local timezone instance
277
ZERO: timedelta  # Zero timedelta
278
```
279

280
[Timezone Support](./timezone-support.md)
281

282
## Types
283

284
### Core Database API Types
285

286
```python { .api }
287
# DB API 2.0 constants
288
apilevel: str  # '2.0'
289
threadsafety: int  # 2
290
paramstyle: str  # 'pyformat'
291

292
# Version information
293
__version__: str
294
__libpq_version__: int
295

296
# Type constants
297
BINARY: type
298
NUMBER: type  
299
STRING: type
300
DATETIME: type
301
ROWID: type
302
```
303

304
### Connection and Transaction Constants
305

306
```python { .api }
307
# Isolation levels
308
ISOLATION_LEVEL_AUTOCOMMIT: int  # 0
309
ISOLATION_LEVEL_READ_UNCOMMITTED: int  # 4
310
ISOLATION_LEVEL_READ_COMMITTED: int  # 1
311
ISOLATION_LEVEL_REPEATABLE_READ: int  # 2
312
ISOLATION_LEVEL_SERIALIZABLE: int  # 3
313

314
# Connection status
315
STATUS_READY: int  # 1
316
STATUS_BEGIN: int  # 2
317
STATUS_IN_TRANSACTION: int  # STATUS_BEGIN
318

319
# Transaction status  
320
TRANSACTION_STATUS_IDLE: int  # 0
321
TRANSACTION_STATUS_ACTIVE: int  # 1
322
TRANSACTION_STATUS_INTRANS: int  # 2
323
TRANSACTION_STATUS_INERROR: int  # 3
324
TRANSACTION_STATUS_UNKNOWN: int  # 4
325
```
326

327
### Polling Constants
328

329
```python { .api }
330
# Asynchronous connection polling
331
POLL_OK: int  # 0
332
POLL_READ: int  # 1
333
POLL_WRITE: int  # 2
334
POLL_ERROR: int  # 3
335
```
336

337
### Replication Constants
338

339
```python { .api }
340
# Replication types
341
REPLICATION_PHYSICAL: int  # Physical replication
342
REPLICATION_LOGICAL: int  # Logical replication
343
```
344

345
### Data Type Constructors
346

347
```python { .api }
348
def Binary(obj) -> bytes:
349
    """Create binary data object."""
350

351
def Date(year: int, month: int, day: int):
352
    """Create date object."""
353

354
def Time(hour: int, minute: int, second: int):
355
    """Create time object."""
356

357
def Timestamp(year: int, month: int, day: int, hour: int, minute: int, second: int):
358
    """Create timestamp object."""
359

360
def DateFromTicks(ticks: float):
361
    """Create date from timestamp."""
362

363
def TimeFromTicks(ticks: float):
364
    """Create time from timestamp."""
365

366
def TimestampFromTicks(ticks: float):
367
    """Create timestamp from timestamp."""
368
```