0
# Legacy DB-API 2.0 Interface
1
2
Standard Python Database API 2.0 compliant interface providing Connection and Cursor classes for traditional database programming patterns with full transaction support.
3
4
## Capabilities
5
6
### Connection Class
7
8
DB-API 2.0 compliant connection class providing transaction management, cursor creation, and prepared statement support.
9
10
```python { .api }
11
class Connection:
12
"""
13
DB-API 2.0 compliant database connection.
14
15
Properties:
16
- autocommit: bool - Enable/disable automatic transaction commits
17
- description: tuple - Column descriptions from last query
18
- _in_transaction: bool - Whether currently in transaction
19
"""
20
21
def cursor(self) -> Cursor:
22
"""
23
Create a new cursor for executing queries.
24
25
Returns:
26
New Cursor instance bound to this connection
27
"""
28
29
def commit(self) -> None:
30
"""
31
Commit the current transaction.
32
33
Raises:
34
DatabaseError: If commit fails
35
"""
36
37
def rollback(self) -> None:
38
"""
39
Roll back the current transaction.
40
41
Raises:
42
DatabaseError: If rollback fails
43
"""
44
45
def run(self, sql: str, stream=None, **params) -> tuple:
46
"""
47
Execute SQL statement with named parameters.
48
49
Parameters:
50
- sql: SQL statement with :param placeholders
51
- stream: Optional stream for COPY operations
52
- **params: Named parameters for SQL statement
53
54
Returns:
55
Tuple of result rows
56
57
Raises:
58
ProgrammingError: Invalid SQL syntax
59
DataError: Invalid data values
60
"""
61
62
def prepare(self, operation: str) -> PreparedStatement:
63
"""
64
Create a prepared statement for efficient repeated execution.
65
66
Parameters:
67
- operation: SQL statement to prepare
68
69
Returns:
70
PreparedStatement object
71
72
Raises:
73
ProgrammingError: Invalid SQL syntax
74
"""
75
76
def close(self) -> None:
77
"""
78
Close the database connection.
79
"""
80
```
81
82
### Cursor Class
83
84
DB-API 2.0 compliant cursor for executing queries and fetching results with support for parameterized queries and result iteration.
85
86
```python { .api }
87
class Cursor:
88
"""
89
DB-API 2.0 compliant cursor for query execution.
90
91
Properties:
92
- arraysize: int - Number of rows to fetch with fetchmany()
93
- description: tuple - Column descriptions from last query
94
- rowcount: int - Number of rows affected by last query
95
- connection: Connection - Parent connection object
96
"""
97
98
def execute(self, operation: str, args: tuple = (), stream=None) -> Cursor:
99
"""
100
Execute SQL statement with positional parameters.
101
102
Parameters:
103
- operation: SQL statement with %s placeholders
104
- args: Tuple of parameter values
105
- stream: Optional stream for COPY operations
106
107
Returns:
108
Self for method chaining
109
110
Raises:
111
ProgrammingError: Invalid SQL syntax
112
DataError: Invalid parameter values
113
"""
114
115
def executemany(self, operation: str, param_sets: list) -> Cursor:
116
"""
117
Execute SQL statement multiple times with different parameters.
118
119
Parameters:
120
- operation: SQL statement with %s placeholders
121
- param_sets: List of parameter tuples
122
123
Returns:
124
Self for method chaining
125
126
Raises:
127
ProgrammingError: Invalid SQL syntax
128
DataError: Invalid parameter values
129
"""
130
131
def fetchone(self) -> tuple:
132
"""
133
Fetch the next row from query results.
134
135
Returns:
136
Single row tuple or None if no more rows
137
138
Raises:
139
Error: If no query has been executed
140
"""
141
142
def fetchmany(self, num: int = None) -> tuple:
143
"""
144
Fetch multiple rows from query results.
145
146
Parameters:
147
- num: Number of rows to fetch (defaults to arraysize)
148
149
Returns:
150
Tuple of row tuples
151
152
Raises:
153
Error: If no query has been executed
154
"""
155
156
def fetchall(self) -> tuple:
157
"""
158
Fetch all remaining rows from query results.
159
160
Returns:
161
Tuple of all remaining row tuples
162
163
Raises:
164
Error: If no query has been executed
165
"""
166
167
def close(self) -> None:
168
"""
169
Close the cursor and free associated resources.
170
"""
171
172
def setinputsizes(self, *sizes) -> None:
173
"""
174
Set input parameter sizes (no-op for compatibility).
175
176
Parameters:
177
- *sizes: Parameter size specifications (ignored)
178
"""
179
180
def setoutputsize(self, size: int, column: int = None) -> None:
181
"""
182
Set output column size (no-op for compatibility).
183
184
Parameters:
185
- size: Column size specification (ignored)
186
- column: Column index (ignored)
187
"""
188
```
189
190
### PreparedStatement Class
191
192
Prepared statement class for efficient repeated execution of SQL statements with different parameter values.
193
194
```python { .api }
195
class PreparedStatement:
196
"""
197
Prepared statement for efficient repeated execution.
198
"""
199
200
def run(self, **vals) -> tuple:
201
"""
202
Execute prepared statement with named parameters.
203
204
Parameters:
205
- **vals: Named parameter values
206
207
Returns:
208
Tuple of result rows
209
210
Raises:
211
DataError: Invalid parameter values
212
OperationalError: Execution errors
213
"""
214
215
def close(self) -> None:
216
"""
217
Close the prepared statement and free resources.
218
"""
219
```
220
221
### Two-Phase Commit Support
222
223
Support for distributed transactions using the Two-Phase Commit protocol.
224
225
```python { .api }
226
class Connection:
227
def xid(self, format_id: int, global_transaction_id: str, branch_qualifier: str) -> tuple:
228
"""
229
Create transaction identifier for two-phase commit.
230
231
Parameters:
232
- format_id: Format identifier
233
- global_transaction_id: Global transaction identifier
234
- branch_qualifier: Branch qualifier
235
236
Returns:
237
Transaction identifier tuple
238
"""
239
240
def tpc_begin(self, xid: tuple) -> None:
241
"""
242
Begin two-phase commit transaction.
243
244
Parameters:
245
- xid: Transaction identifier from xid()
246
247
Raises:
248
NotSupportedError: If two-phase commit not supported
249
"""
250
251
def tpc_prepare(self) -> None:
252
"""
253
Prepare two-phase commit transaction.
254
255
Raises:
256
DatabaseError: If prepare fails
257
"""
258
259
def tpc_commit(self, xid: tuple = None) -> None:
260
"""
261
Commit two-phase commit transaction.
262
263
Parameters:
264
- xid: Transaction identifier (optional)
265
266
Raises:
267
DatabaseError: If commit fails
268
"""
269
270
def tpc_rollback(self, xid: tuple = None) -> None:
271
"""
272
Roll back two-phase commit transaction.
273
274
Parameters:
275
- xid: Transaction identifier (optional)
276
277
Raises:
278
DatabaseError: If rollback fails
279
"""
280
281
def tpc_recover(self) -> list:
282
"""
283
Get list of pending transaction identifiers.
284
285
Returns:
286
List of pending transaction identifier tuples
287
"""
288
```
289
290
### DB-API 2.0 Constructor Functions
291
292
Standard constructor functions required by the DB-API 2.0 specification for creating date/time objects.
293
294
```python { .api }
295
def Date(year: int, month: int, day: int) -> datetime.date:
296
"""
297
Construct a date object (alias for PgDate).
298
299
This function is part of the DBAPI 2.0 specification.
300
"""
301
302
def Time(hour: int, minute: int, second: int) -> datetime.time:
303
"""
304
Construct a time object (alias for PgTime).
305
306
This function is part of the DBAPI 2.0 specification.
307
"""
308
309
def Timestamp(year: int, month: int, day: int, hour: int, minute: int, second: int) -> datetime.datetime:
310
"""
311
Construct a timestamp object.
312
313
This function is part of the DBAPI 2.0 specification.
314
"""
315
316
def DateFromTicks(ticks: float) -> datetime.date:
317
"""
318
Construct a date object from seconds since epoch.
319
320
This function is part of the DBAPI 2.0 specification.
321
"""
322
323
def TimeFromTicks(ticks: float) -> datetime.time:
324
"""
325
Construct a time object from seconds since epoch.
326
327
This function is part of the DBAPI 2.0 specification.
328
"""
329
330
def TimestampFromTicks(ticks: float) -> datetime.datetime:
331
"""
332
Construct a timestamp object from seconds since epoch.
333
334
This function is part of the DBAPI 2.0 specification.
335
"""
336
337
def Binary(value: bytes) -> bytes:
338
"""
339
Construct a binary data object.
340
341
This function is part of the DBAPI 2.0 specification.
342
"""
343
```
344
345
### Usage Examples
346
347
#### Basic Query Execution
348
349
```python
350
import pg8000
351
352
# Connect and create cursor
353
conn = pg8000.connect(user="myuser", password="mypass", database="mydb")
354
cursor = conn.cursor()
355
356
# Execute simple query
357
cursor.execute("SELECT id, name FROM users WHERE active = %s", (True,))
358
359
# Fetch results
360
while True:
361
row = cursor.fetchone()
362
if row is None:
363
break
364
print(f"ID: {row[0]}, Name: {row[1]}")
365
366
# Clean up
367
cursor.close()
368
conn.close()
369
```
370
371
#### Transaction Management
372
373
```python
374
import pg8000
375
376
conn = pg8000.connect(user="myuser", password="mypass", database="mydb")
377
cursor = conn.cursor()
378
379
try:
380
# Start transaction (implicit)
381
cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)",
382
("John Doe", "john@example.com"))
383
cursor.execute("UPDATE user_stats SET count = count + 1")
384
385
# Commit transaction
386
conn.commit()
387
print("Transaction committed successfully")
388
389
except pg8000.DatabaseError as e:
390
# Roll back on error
391
conn.rollback()
392
print(f"Transaction rolled back: {e}")
393
394
finally:
395
cursor.close()
396
conn.close()
397
```
398
399
#### Prepared Statements
400
401
```python
402
import pg8000
403
404
conn = pg8000.connect(user="myuser", password="mypass", database="mydb")
405
406
# Prepare statement
407
stmt = conn.prepare("INSERT INTO logs (level, message, timestamp) VALUES (:level, :msg, :ts)")
408
409
# Execute multiple times with different parameters
410
import datetime
411
412
for level, message in [("INFO", "App started"), ("ERROR", "Database error"), ("INFO", "Request processed")]:
413
result = stmt.run(
414
level=level,
415
msg=message,
416
ts=datetime.datetime.now()
417
)
418
419
# Clean up
420
stmt.close()
421
conn.close()
422
```