0
# pg8000
1

2
A pure-Python PostgreSQL driver that provides DB-API 2.0 compliant database connectivity with comprehensive PostgreSQL data type support. pg8000 offers both a legacy DB-API 2.0 interface for compatibility with standard Python database patterns and a modern native interface for streamlined usage.
3

4
## Package Information
5

6
- **Package Name**: pg8000
7
- **Package Type**: pypi
8
- **Language**: Python
9
- **Installation**: `pip install pg8000`
10

11
## Core Imports
12

13
```python
14
import pg8000
15
```
16

17
For legacy DB-API 2.0 interface:
18

19
```python
20
import pg8000
21
# Use pg8000.connect() to get Connection objects
22
```
23

24
For native interface:
25

26
```python
27
import pg8000.native
28
# Use pg8000.native.Connection directly
29
```
30

31
### Module Constants
32

33
```python { .api }
34
__version__: str
35
"""Package version string."""
36

37
apilevel: str = "2.0"
38
"""DBAPI level supported by pg8000."""
39

40
threadsafety: int = 1
41
"""Thread safety level - module sharing only, connections not thread-safe."""
42

43
paramstyle: str = "format"
44
"""Parameter style used for SQL queries."""
45
```
46

47
## Basic Usage
48

49
### Legacy DB-API 2.0 Interface
50

51
```python
52
import pg8000
53

54
# Connect to database
55
conn = pg8000.connect(
56
    user="username",
57
    password="password", 
58
    host="localhost",
59
    port=5432,
60
    database="mydb"
61
)
62

63
# Create cursor and execute query
64
cursor = conn.cursor()
65
cursor.execute("SELECT version()")
66
result = cursor.fetchone()
67
print(result)
68

69
# Commit and close
70
conn.commit()
71
cursor.close()
72
conn.close()
73
```
74

75
### Native Interface
76

77
```python
78
import pg8000.native
79

80
# Connect to database
81
conn = pg8000.native.Connection(
82
    user="username",
83
    password="password",
84
    host="localhost", 
85
    port=5432,
86
    database="mydb"
87
)
88

89
# Execute query directly
90
result = conn.run("SELECT version()")
91
print(result)
92

93
# Close connection
94
conn.close()
95
```
96

97
## Architecture
98

99
pg8000 provides two distinct interfaces:
100

101
- **Legacy DB-API 2.0 Interface**: Complete Python Database API specification compliance with Connection and Cursor objects, supporting traditional database programming patterns including transactions, prepared statements, and cursor-based result iteration.
102

103
- **Native Interface**: Streamlined API optimized for modern Python usage with direct query execution, automatic parameter handling, and simplified connection management.
104

105
Both interfaces share the same comprehensive PostgreSQL type system, supporting all standard PostgreSQL data types including arrays, JSON/JSONB, UUIDs, ranges, intervals, and network types with automatic Python conversion.
106

107
## Capabilities
108

109
### Connection Management
110

111
Primary functions for establishing and managing database connections using both legacy DB-API 2.0 and native interfaces.
112

113
```python { .api }
114
def connect(
115
    user: str,
116
    host: str = "localhost", 
117
    database: str = None,
118
    port: int = 5432,
119
    password: str = None,
120
    source_address: tuple = None,
121
    unix_sock: str = None,
122
    ssl_context = None,
123
    timeout: float = None,
124
    tcp_keepalive: bool = True,
125
    application_name: str = None,
126
    replication: str = None,
127
    startup_params: dict = None,
128
    sock = None
129
) -> Connection: ...
130
```
131

132
[Connection Management](./connection-management.md)
133

134
### Legacy DB-API 2.0 Interface
135

136
Standard Python Database API 2.0 compliant interface with Connection and Cursor classes for compatibility with existing database code.
137

138
```python { .api }
139
class Connection:
140
    def cursor(self) -> Cursor: ...
141
    def commit(self) -> None: ...
142
    def rollback(self) -> None: ...
143
    def run(self, sql: str, stream=None, **params) -> tuple: ...
144
    def close(self) -> None: ...
145

146
class Cursor:
147
    def execute(self, operation: str, args: tuple = (), stream=None) -> Cursor: ...
148
    def fetchone(self) -> tuple: ...
149
    def fetchmany(self, num: int = None) -> tuple: ...
150
    def fetchall(self) -> tuple: ...
151
    def close(self) -> None: ...
152
```
153

154
[Legacy DB-API 2.0 Interface](./legacy-dbapi.md)
155

156
### Native Interface
157

158
Modern streamlined interface optimized for direct query execution with automatic parameter handling and simplified result processing.
159

160
```python { .api }
161
class Connection:
162
    def run(self, sql: str, stream=None, types=None, **params) -> list: ...
163
    def prepare(self, sql: str) -> PreparedStatement: ...
164
    def close(self) -> None: ...
165

166
class PreparedStatement:
167
    def run(self, stream=None, **params) -> list: ...
168
    def close(self) -> None: ...
169
```
170

171
[Native Interface](./native-interface.md)
172

173
### PostgreSQL Data Types
174

175
Comprehensive support for all PostgreSQL data types with automatic Python conversion, including numeric types, text types, date/time types, arrays, JSON, network types, and custom types.
176

177
```python { .api }
178
# Type constants
179
BIGINT: int = 20
180
INTEGER: int = 23
181
TEXT: int = 25
182
BOOLEAN: int = 16
183
JSON: int = 114
184
JSONB: int = 3802
185
UUID_TYPE: int = 2950
186
INTEGER_ARRAY: int = 1007
187
TEXT_ARRAY: int = 1009
188
```
189

190
[PostgreSQL Data Types](./postgresql-types.md)
191

192
### Custom Types
193

194
Custom Python classes for PostgreSQL-specific data types including intervals and ranges.
195

196
```python { .api }
197
class PGInterval:
198
    @classmethod
199
    def from_str(cls, interval_str: str) -> PGInterval: ...
200
    def normalize(self) -> PGInterval: ...
201
    def to_timedelta(self) -> timedelta: ...
202

203
class Range:
204
    def __init__(self, lower=None, upper=None, bounds: str = "[)", is_empty: bool = False): ...
205
```
206

207
[Custom Types](./custom-types.md)
208

209
### Exception Handling
210

211
Complete exception hierarchy for proper error handling following DB-API 2.0 standards.
212

213
```python { .api }
214
class Error(Exception): ...
215
class DatabaseError(Error): ...
216
class InterfaceError(Error): ...
217
class DataError(DatabaseError): ...
218
class OperationalError(DatabaseError): ...
219
class IntegrityError(DatabaseError): ...
220
class InternalError(DatabaseError): ...
221
class ProgrammingError(DatabaseError): ...
222
class NotSupportedError(DatabaseError): ...
223
```
224

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