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
```