0
# py-postgresql
1

2
A comprehensive Python library for PostgreSQL database connectivity providing both modern PostgreSQL-specific PG-API and standard DB-API 2.0 interfaces. The package includes full protocol implementation, type system support, cluster management tools, and command-line utilities for robust PostgreSQL integration.
3

4
## Package Information
5

6
- **Package Name**: py-postgresql
7
- **Language**: Python (3.3+)
8
- **Installation**: `pip install py-postgresql`
9

10
## Core Imports
11

12
Primary connection interface:
13

14
```python
15
import postgresql
16
```
17

18
DB-API 2.0 interface:
19

20
```python
21
import postgresql.driver.dbapi20 as dbapi
22
```
23

24
Type system and exceptions:
25

26
```python
27
import postgresql.types
28
import postgresql.exceptions
29
```
30

31
## Basic Usage
32

33
```python
34
import postgresql
35

36
# Connect using pq:// URI format
37
db = postgresql.open('pq://user:password@host:port/database')
38

39
# Prepare and execute statements
40
get_users = db.prepare("SELECT id, name FROM users WHERE active = $1")
41
active_users = get_users(True)
42

43
# Transaction support
44
with db.xact():
45
    insert_user = db.prepare("INSERT INTO users (name, email) VALUES ($1, $2)")
46
    insert_user("John Doe", "john@example.com")
47

48
# Streaming results
49
for user in get_users.rows(True):
50
    print(f"User: {user['name']}")
51

52
db.close()
53
```
54

55
## Architecture
56

57
py-postgresql provides multiple interfaces for PostgreSQL connectivity:
58

59
- **High-level PG-API**: Modern PostgreSQL-specific interface with prepared statements, streaming, and advanced features
60
- **DB-API 2.0**: Standard Python database interface for compatibility with existing code
61
- **Protocol Layer**: Low-level PostgreSQL wire protocol implementation
62
- **Type System**: Complete PostgreSQL type support with automatic conversion
63
- **Cluster Management**: Tools for PostgreSQL installation and cluster control
64

65
## Capabilities
66

67
### Connection Management
68

69
Core database connection functionality supporting both high-level PG-API and standard DB-API 2.0 interfaces, with flexible connection parameters and authentication methods.
70

71
```python { .api }
72
def open(iri=None, prompt_title=None, **kw): ...
73
```
74

75
[Connection Management](./connection-management.md)
76

77
### Query Execution
78

79
Prepared statement interface with parameter binding, result streaming, and transaction management for efficient and secure database operations.
80

81
```python { .api }
82
class Statement:
83
    def __call__(*parameters): ...
84
    def rows(*parameters): ...
85
    def first(*parameters): ...
86
    def chunks(*parameters): ...
87
```
88

89
[Query Execution](./query-execution.md)
90

91
### DB-API 2.0 Interface
92

93
Standard Python database interface providing cursor-based operations, connection management, and exception handling for compatibility with existing database applications.
94

95
```python { .api }
96
def connect(user=None, password=None, host=None, port=None, database=None, **kw): ...
97

98
class Connection:
99
    def cursor(): ...
100
    def commit(): ...
101
    def rollback(): ...
102
    def close(): ...
103

104
class Cursor:
105
    def execute(query, parameters=None): ...
106
    def executemany(query, parameter_sequences): ...
107
    def fetchone(): ...
108
    def fetchmany(size=None): ...
109
    def fetchall(): ...
110
```
111

112
[DB-API 2.0 Interface](./dbapi-interface.md)
113

114
### Type System
115

116
Comprehensive PostgreSQL type support including primitive types, arrays, composite types, and custom type conversion with automatic serialization and deserialization.
117

118
```python { .api }
119
# Type constants
120
BOOLOID: int
121
INT2OID: int
122
INT4OID: int
123
INT8OID: int
124
TEXTOID: int
125
JSONBOID: int
126
# ... 60+ additional type OIDs
127

128
class Array:
129
    def __init__(elements, element_type): ...
130

131
class Row:
132
    def __getitem__(key): ...
133
    def keys(): ...
134
    def values(): ...
135
```
136

137
[Type System](./type-system.md)
138

139
### Exception Handling
140

141
Complete exception hierarchy mapping PostgreSQL error codes to specific Python exception classes with detailed error information and SQL state codes.
142

143
```python { .api }
144
class Error(Exception): ...
145
class ConnectionError(Error): ...
146
class TransactionError(Error): ...
147
class AuthenticationSpecificationError(Error): ...
148

149
def ErrorLookup(state_code): ...
150
def WarningLookup(state_code): ...
151
```
152

153
[Exception Handling](./exception-handling.md)
154

155
### Transaction Management
156

157
Transaction control with savepoints, context managers, and isolation level management for reliable database operations.
158

159
```python { .api }
160
class Transaction:
161
    def start(): ...
162
    def commit(): ...
163
    def rollback(): ...
164
    def savepoint(name=None): ...
165

166
class Database:
167
    def xact(): ...
168
```
169

170
[Transaction Management](./transaction-management.md)
171

172
### Cluster Management
173

174
PostgreSQL cluster and installation management tools for controlling local PostgreSQL instances, configuration, and maintenance operations.
175

176
```python { .api }
177
class Cluster:
178
    def start(): ...
179
    def stop(): ...
180
    def restart(): ...
181
    def initialize(): ...
182

183
class Installation:
184
    def version(): ...
185
    def bin_directory(): ...
186
```
187

188
[Cluster Management](./cluster-management.md)
189

190
### Advanced Features
191

192
Advanced PostgreSQL features including COPY operations, LISTEN/NOTIFY, advisory locks, and streaming results for high-performance database applications.
193

194
```python { .api }
195
class CopyManager:
196
    def load_rows(statement, rows): ...
197
    def dump_rows(statement): ...
198

199
class NotificationManager:
200
    def listen(channel): ...
201
    def notify(channel, payload=None): ...
202

203
class ALock:
204
    def __enter__(): ...
205
    def __exit__(): ...
206
```
207

208
[Advanced Features](./advanced-features.md)
209

210
## Types
211

212
Core types used across the API:
213

214
```python { .api }
215
class Connection:
216
    """Database connection interface with query and transaction methods."""
217
    
218
class Database:
219
    """Base database interface providing core operations."""
220
    
221
class Statement:
222
    """Prepared statement with parameter binding and execution methods."""
223
    
224
class Row:
225
    """Named tuple-like interface for query result rows."""
226
    
227
class Array:
228
    """PostgreSQL array type with multi-dimensional support."""
229
```