0
# Psycopg
1

2
A modern PostgreSQL database adapter for Python that provides a comprehensive interface for connecting to and interacting with PostgreSQL databases. Psycopg 3 offers both synchronous and asynchronous operations, connection pooling, comprehensive type support, and advanced PostgreSQL features like prepared statements, server-side cursors, and COPY operations.
3

4
## Package Information
5

6
- **Package Name**: psycopg
7
- **Language**: Python
8
- **Installation**: `pip install psycopg`
9
- **Optional Extensions**: `pip install psycopg[binary,pool]` for C speedups and connection pooling
10

11
## Core Imports
12

13
```python
14
import psycopg
15
```
16

17
Most common imports for database operations:
18

19
```python
20
from psycopg import Connection, Cursor
21
from psycopg import AsyncConnection, AsyncCursor
22
```
23

24
For specific functionality:
25

26
```python
27
from psycopg import sql, rows
28
from psycopg.types import TypeInfo, TypesRegistry
29
from psycopg import errors  # Exception classes
30
from psycopg import Pipeline, AsyncPipeline  # Pipeline operations
31
```
32

33
## Basic Usage
34

35
### Synchronous Connection
36

37
```python
38
import psycopg
39

40
# Connect to database
41
with psycopg.connect("dbname=test user=postgres") as conn:
42
    # Execute simple query
43
    with conn.cursor() as cur:
44
        cur.execute("SELECT version()")
45
        version = cur.fetchone()
46
        print(version)
47
    
48
    # Execute parameterized query
49
    with conn.cursor() as cur:
50
        cur.execute(
51
            "INSERT INTO users (name, email) VALUES (%s, %s)",
52
            ("John Doe", "john@example.com")
53
        )
54
        conn.commit()
55
```
56

57
### Asynchronous Connection
58

59
```python
60
import asyncio
61
import psycopg
62

63
async def main():
64
    # Connect asynchronously
65
    async with await psycopg.AsyncConnection.connect("dbname=test user=postgres") as conn:
66
        # Execute queries asynchronously
67
        async with conn.cursor() as cur:
68
            await cur.execute("SELECT * FROM users WHERE active = %s", (True,))
69
            users = await cur.fetchall()
70
            print(users)
71

72
asyncio.run(main())
73
```
74

75
### SQL Composition
76

77
```python
78
from psycopg import sql
79

80
# Safe SQL composition
81
query = sql.SQL("INSERT INTO {table} ({fields}) VALUES ({values})").format(
82
    table=sql.Identifier("users"),
83
    fields=sql.SQL(", ").join([sql.Identifier("name"), sql.Identifier("email")]),
84
    values=sql.SQL(", ").join([sql.Placeholder()] * 2)
85
)
86

87
with conn.cursor() as cur:
88
    cur.execute(query, ("Jane Smith", "jane@example.com"))
89
```
90

91
## Architecture
92

93
Psycopg 3 is built around several key architectural components:
94

95
- **Connection Layer**: Both sync (`Connection`) and async (`AsyncConnection`) variants providing database connectivity with full transaction support
96
- **Cursor Hierarchy**: Multiple cursor types (standard, server-side, client-side, raw) optimized for different use cases and performance requirements
97
- **Type System**: Comprehensive PostgreSQL type adaptation supporting built-in types, arrays, JSON, custom types, and third-party integrations
98
- **SQL Composition**: Safe query building with automatic escaping and identifier quoting
99
- **Pipeline Operations**: Batching support for high-performance bulk operations
100
- **Error Handling**: Complete DB-API 2.0 compliant exception hierarchy with PostgreSQL-specific diagnostics
101

102
## Capabilities
103

104
### Database Connections
105

106
Complete connection management for both synchronous and asynchronous operations, including connection pooling, transaction control, two-phase commit, and server configuration access.
107

108
```python { .api }
109
class Connection:
110
    @classmethod
111
    def connect(cls, conninfo="", **kwargs) -> Connection: ...
112
    def cursor(self, *, row_factory=None) -> Cursor: ...
113
    def execute(self, query, params=None, *, prepare=None, binary=None) -> Cursor: ...
114
    def commit(self) -> None: ...
115
    def rollback(self) -> None: ...
116
    def close(self) -> None: ...
117

118
class AsyncConnection:
119
    @classmethod
120
    async def connect(cls, conninfo="", **kwargs) -> AsyncConnection: ...
121
    def cursor(self, *, row_factory=None) -> AsyncCursor: ...
122
    async def execute(self, query, params=None, *, prepare=None, binary=None) -> AsyncCursor: ...
123
```
124

125
[Database Connections](./connections.md)
126

127
### Query Execution and Cursors
128

129
Comprehensive cursor functionality for query execution, result fetching, and data manipulation with support for various cursor types optimized for different performance and memory requirements.
130

131
```python { .api }
132
class Cursor:
133
    def execute(self, query, params=None, *, prepare=None, binary=None) -> Cursor: ...
134
    def executemany(self, query, params_seq, *, returning=False) -> None: ...
135
    def fetchone(self) -> Any: ...
136
    def fetchmany(self, size=None) -> list: ...
137
    def fetchall(self) -> list: ...
138
    def scroll(self, value, mode="relative") -> None: ...
139

140
class ServerCursor:
141
    # Server-side cursor for large result sets
142
    
143
class AsyncCursor:
144
    async def execute(self, query, params=None, *, prepare=None, binary=None) -> AsyncCursor: ...
145
    async def fetchone(self) -> Any: ...
146
```
147

148
[Query Execution and Cursors](./cursors.md)
149

150
### Safe SQL Composition
151

152
SQL query building with automatic escaping, identifier quoting, and parameter placeholder management to prevent SQL injection while maintaining readability and flexibility.
153

154
```python { .api }
155
class sql.SQL:
156
    def __init__(self, template: str): ...
157
    def format(self, *args, **kwargs) -> Composed: ...
158
    def join(self, seq) -> Composed: ...
159

160
class sql.Identifier:
161
    def __init__(self, *names): ...
162

163
class sql.Literal:
164
    def __init__(self, obj): ...
165

166
def sql.quote(obj, context=None) -> str: ...
167
```
168

169
[Safe SQL Composition](./sql-composition.md)
170

171
### Row Factories and Result Processing
172

173
Flexible result formatting with built-in row factories for tuples, dictionaries, named tuples, and custom objects, plus protocols for creating custom result processors.
174

175
```python { .api }
176
def rows.tuple_row(cursor) -> RowMaker: ...
177
def rows.dict_row(cursor) -> RowMaker: ...
178
def rows.namedtuple_row(cursor) -> RowMaker: ...
179
def rows.class_row(cls) -> RowMaker: ...
180
def rows.scalar_row(cursor) -> RowMaker: ...
181
```
182

183
[Row Factories and Result Processing](./row-factories.md)
184

185
### Type System and Adaptation
186

187
PostgreSQL type system integration with automatic type conversion, custom type registration, and support for PostgreSQL-specific types like arrays, JSON, ranges, and geometric types.
188

189
```python { .api }
190
class TypeInfo:
191
    name: str
192
    oid: int
193
    array_oid: int
194
    @classmethod
195
    def fetch(cls, conn, name_or_oid) -> TypeInfo: ...
196

197
class TypesRegistry:
198
    def get_by_oid(self, oid: int) -> TypeInfo: ...
199
    def get_by_name(self, name: str) -> TypeInfo: ...
200
```
201

202
[Type System and Adaptation](./type-system.md)
203

204
### Advanced Operations
205

206
High-performance operations including COPY for bulk data transfer, pipeline operations for batching, prepared statements, and server-side cursors for memory-efficient large result set processing.
207

208
```python { .api }
209
class Copy:
210
    def write(self, data: bytes) -> None: ...
211
    def write_row(self, row) -> None: ...
212
    def read(self) -> bytes: ...
213

214
class Pipeline:
215
    def sync(self) -> None: ...
216
```
217

218
[Advanced Operations](./advanced-operations.md)
219

220
### Error Handling and Diagnostics
221

222
Complete DB-API 2.0 compliant exception hierarchy with PostgreSQL-specific error information, diagnostic details, and proper error classification for robust error handling.
223

224
```python { .api }
225
class Error(Exception): ...
226
class DatabaseError(Error): ...
227
class DataError(DatabaseError): ...
228
class IntegrityError(DatabaseError): ...
229
class OperationalError(DatabaseError): ...
230
class ProgrammingError(DatabaseError): ...
231
```
232

233
[Error Handling and Diagnostics](./error-handling.md)
234

235
## Connection Information
236

237
```python { .api }
238
class ConnectionInfo:
239
    dsn: str
240
    status: int
241
    transaction_status: int
242
    server_version: int
243
    encoding: str
244
    timezone: str
245
    host: str
246
    port: int
247
    dbname: str
248
    user: str
249
```
250

251
## DBAPI 2.0 Compliance
252

253
Psycopg 3 provides full DB-API 2.0 compliance with standard module-level attributes and functions:
254

255
```python { .api }
256
apilevel: str = "2.0"
257
threadsafety: int = 2
258
paramstyle: str = "pyformat"
259

260
def connect(conninfo="", **kwargs) -> Connection: ...
261

262
# Type constructors
263
def Binary(data) -> bytes: ...
264
def Date(year, month, day) -> date: ...
265
def Time(hour, minute, second) -> time: ...
266
def Timestamp(year, month, day, hour, minute, second) -> datetime: ...
267

268
# Type objects for column type comparison
269
STRING: type
270
BINARY: type  
271
NUMBER: type
272
DATETIME: type
273
ROWID: type
274

275
# Global adapters registry access
276
adapters: AdaptersMap
277
```