0
# DB-API 2.0 Interface
1
2
Standard Python database interface providing cursor-based operations, connection management, and exception handling for compatibility with existing database applications.
3
4
## Capabilities
5
6
### DB-API 2.0 Connection
7
8
Standard database connection interface compatible with Python's Database API Specification v2.0.
9
10
```python { .api }
11
def connect(user=None, password=None, host=None, port=None, database=None, **kw):
12
"""
13
Create a DB-API 2.0 compatible database connection.
14
15
Parameters:
16
- user (str, optional): Database username
17
- password (str, optional): Database password
18
- host (str, optional): Database server hostname
19
- port (int, optional): Database server port
20
- database (str, optional): Database name
21
- **kw: Additional connection parameters
22
23
Returns:
24
Connection: DB-API 2.0 connection object
25
26
Raises:
27
InterfaceError: If connection parameters are invalid
28
OperationalError: If connection cannot be established
29
"""
30
```
31
32
### Connection Interface
33
34
DB-API 2.0 connection providing cursor creation, transaction control, and connection management.
35
36
```python { .api }
37
class Connection:
38
"""
39
DB-API 2.0 compatible connection interface.
40
"""
41
42
def cursor():
43
"""
44
Create a new cursor object for executing queries.
45
46
Returns:
47
Cursor: Database cursor for query execution
48
"""
49
50
def commit():
51
"""
52
Commit the current transaction.
53
54
Raises:
55
DatabaseError: If commit fails
56
"""
57
58
def rollback():
59
"""
60
Roll back the current transaction.
61
62
Raises:
63
DatabaseError: If rollback fails
64
"""
65
66
def close():
67
"""
68
Close the database connection.
69
"""
70
71
@property
72
def autocommit():
73
"""
74
Get/set autocommit mode.
75
76
Returns:
77
bool: True if autocommit is enabled
78
"""
79
80
def __enter__():
81
"""Context manager entry."""
82
83
def __exit__(exc_type, exc_val, exc_tb):
84
"""Context manager exit with automatic commit/rollback."""
85
```
86
87
### Cursor Interface
88
89
DB-API 2.0 cursor providing query execution, result fetching, and metadata access.
90
91
```python { .api }
92
class Cursor:
93
"""
94
DB-API 2.0 compatible cursor interface for query execution.
95
"""
96
97
def execute(query, parameters=None):
98
"""
99
Execute a query with optional parameters.
100
101
Parameters:
102
- query (str): SQL query with %(name)s or %s parameter placeholders
103
- parameters (dict or sequence, optional): Parameter values
104
105
Raises:
106
ProgrammingError: If query is invalid
107
DataError: If parameters are invalid
108
"""
109
110
def executemany(query, parameter_sequences):
111
"""
112
Execute a query multiple times with different parameter sets.
113
114
Parameters:
115
- query (str): SQL query with parameter placeholders
116
- parameter_sequences (sequence): Sequence of parameter sets
117
118
Raises:
119
ProgrammingError: If query is invalid
120
"""
121
122
def fetchone():
123
"""
124
Fetch next row from query results.
125
126
Returns:
127
tuple or None: Next result row as tuple, or None if no more rows
128
"""
129
130
def fetchmany(size=None):
131
"""
132
Fetch multiple rows from query results.
133
134
Parameters:
135
- size (int, optional): Number of rows to fetch (default: cursor.arraysize)
136
137
Returns:
138
list: List of result rows as tuples
139
"""
140
141
def fetchall():
142
"""
143
Fetch all remaining rows from query results.
144
145
Returns:
146
list: All remaining result rows as tuples
147
"""
148
149
def close():
150
"""Close the cursor."""
151
152
def __iter__():
153
"""Iterator interface for result rows."""
154
155
def __next__():
156
"""Get next result row."""
157
158
@property
159
def description():
160
"""
161
Get column description for last query.
162
163
Returns:
164
list: List of 7-tuples describing each column:
165
(name, type_code, display_size, internal_size, precision, scale, null_ok)
166
"""
167
168
@property
169
def rowcount():
170
"""
171
Get number of rows affected by last query.
172
173
Returns:
174
int: Row count (-1 if not available)
175
"""
176
177
@property
178
def arraysize():
179
"""
180
Get/set default number of rows for fetchmany().
181
182
Returns:
183
int: Default fetch size
184
"""
185
```
186
187
### Module Constants
188
189
DB-API 2.0 compliance constants and type constructors.
190
191
```python { .api }
192
# API compliance constants
193
apilevel: str # "2.0"
194
threadsafety: int # 1 (threads may share module, not connections)
195
paramstyle: str # "pyformat" (%(name)s parameter style)
196
197
# Type objects for column type identification
198
STRING: type # String data type
199
BINARY: type # Binary data type
200
NUMBER: type # Numeric data type
201
DATETIME: type # Date/time data type
202
ROWID: type # Row ID data type
203
```
204
205
### Exception Hierarchy
206
207
Complete DB-API 2.0 exception hierarchy with PostgreSQL-specific error mapping.
208
209
```python { .api }
210
# Base exceptions
211
class Warning(Exception):
212
"""Important warnings raised by database operations."""
213
214
class Error(Exception):
215
"""Base class for all database errors."""
216
217
class InterfaceError(Error):
218
"""Errors in database interface usage."""
219
220
class DatabaseError(Error):
221
"""Errors in database operations."""
222
223
# DatabaseError subclasses
224
class DataError(DatabaseError):
225
"""Errors in processed data (invalid values, overflow, etc.)."""
226
227
class OperationalError(DatabaseError):
228
"""Errors in database operations outside user control."""
229
230
class IntegrityError(DatabaseError):
231
"""Database referential integrity violations."""
232
233
class InternalError(DatabaseError):
234
"""Internal database errors."""
235
236
class ProgrammingError(DatabaseError):
237
"""Programming errors (invalid queries, missing tables, etc.)."""
238
239
class NotSupportedError(DatabaseError):
240
"""Unsupported database operations."""
241
```
242
243
## Usage Examples
244
245
### Basic DB-API 2.0 Usage
246
247
```python
248
import postgresql.driver.dbapi20 as dbapi
249
250
# Connect using DB-API 2.0 interface
251
conn = dbapi.connect(
252
user='postgres',
253
password='password',
254
host='localhost',
255
port=5432,
256
database='mydb'
257
)
258
259
# Create cursor for queries
260
cur = conn.cursor()
261
262
# Execute query with parameters
263
cur.execute("SELECT id, name FROM users WHERE age > %(min_age)s", {'min_age': 18})
264
265
# Fetch results
266
results = cur.fetchall()
267
for user_id, name in results:
268
print(f"User {user_id}: {name}")
269
270
# Check query metadata
271
print("Columns:", [desc[0] for desc in cur.description])
272
print("Row count:", cur.rowcount)
273
274
# Close cursor and connection
275
cur.close()
276
conn.close()
277
```
278
279
### Transaction Management
280
281
```python
282
import postgresql.driver.dbapi20 as dbapi
283
284
conn = dbapi.connect(database='mydb', user='postgres')
285
286
try:
287
cur = conn.cursor()
288
289
# Start transaction (implicit)
290
cur.execute("INSERT INTO accounts (name, balance) VALUES (%s, %s)",
291
("Alice", 1000))
292
cur.execute("INSERT INTO accounts (name, balance) VALUES (%s, %s)",
293
("Bob", 500))
294
295
# Transfer money between accounts
296
cur.execute("UPDATE accounts SET balance = balance - %s WHERE name = %s",
297
(100, "Alice"))
298
cur.execute("UPDATE accounts SET balance = balance + %s WHERE name = %s",
299
(100, "Bob"))
300
301
# Commit transaction
302
conn.commit()
303
print("Transfer completed successfully")
304
305
except dbapi.DatabaseError as e:
306
# Rollback on any error
307
conn.rollback()
308
print(f"Transfer failed: {e}")
309
310
finally:
311
cur.close()
312
conn.close()
313
```
314
315
### Context Manager Usage
316
317
```python
318
import postgresql.driver.dbapi20 as dbapi
319
320
# Automatic connection management
321
with dbapi.connect(database='mydb', user='postgres') as conn:
322
with conn.cursor() as cur:
323
# Execute multiple queries
324
cur.execute("SELECT COUNT(*) FROM users")
325
user_count = cur.fetchone()[0]
326
327
cur.execute("SELECT COUNT(*) FROM orders")
328
order_count = cur.fetchone()[0]
329
330
print(f"Users: {user_count}, Orders: {order_count}")
331
332
# Transaction automatically committed if no exceptions
333
# Connection automatically closed
334
```
335
336
### Batch Operations
337
338
```python
339
import postgresql.driver.dbapi20 as dbapi
340
341
conn = dbapi.connect(database='mydb', user='postgres')
342
cur = conn.cursor()
343
344
# Prepare data
345
user_data = [
346
('Alice', 'alice@example.com', 25),
347
('Bob', 'bob@example.com', 30),
348
('Charlie', 'charlie@example.com', 35)
349
]
350
351
# Execute batch insert
352
cur.executemany(
353
"INSERT INTO users (name, email, age) VALUES (%s, %s, %s)",
354
user_data
355
)
356
357
print(f"Inserted {cur.rowcount} users")
358
359
# Fetch inserted users
360
cur.execute("SELECT * FROM users WHERE name IN %s", (('Alice', 'Bob', 'Charlie'),))
361
362
# Process results with iterator
363
for row in cur:
364
user_id, name, email, age = row
365
print(f"User {user_id}: {name} ({email}), age {age}")
366
367
conn.commit()
368
cur.close()
369
conn.close()
370
```
371
372
### Error Handling with DB-API 2.0
373
374
```python
375
import postgresql.driver.dbapi20 as dbapi
376
377
try:
378
conn = dbapi.connect(database='nonexistent', user='postgres')
379
except dbapi.OperationalError as e:
380
print(f"Connection failed: {e}")
381
382
try:
383
conn = dbapi.connect(database='mydb', user='postgres')
384
cur = conn.cursor()
385
386
# This will raise ProgrammingError if table doesn't exist
387
cur.execute("SELECT * FROM nonexistent_table")
388
389
except dbapi.ProgrammingError as e:
390
print(f"SQL error: {e}")
391
392
except dbapi.DataError as e:
393
print(f"Data error: {e}")
394
395
except dbapi.IntegrityError as e:
396
print(f"Integrity constraint violation: {e}")
397
398
except dbapi.DatabaseError as e:
399
print(f"Database error: {e}")
400
401
finally:
402
if 'cur' in locals():
403
cur.close()
404
if 'conn' in locals():
405
conn.close()
406
```
407
408
### Type Inspection
409
410
```python
411
import postgresql.driver.dbapi20 as dbapi
412
413
conn = dbapi.connect(database='mydb', user='postgres')
414
cur = conn.cursor()
415
416
# Execute query to get column information
417
cur.execute("""
418
SELECT id, name, email, created_at, is_active, balance
419
FROM users
420
LIMIT 1
421
""")
422
423
# Inspect column types
424
for i, desc in enumerate(cur.description):
425
name, type_code, display_size, internal_size, precision, scale, null_ok = desc
426
427
# Identify column types
428
if type_code == dbapi.STRING:
429
type_name = "STRING"
430
elif type_code == dbapi.NUMBER:
431
type_name = "NUMBER"
432
elif type_code == dbapi.DATETIME:
433
type_name = "DATETIME"
434
else:
435
type_name = "OTHER"
436
437
print(f"Column {i}: {name} ({type_name})")
438
439
cur.close()
440
conn.close()
441
```
442
443
### Compatibility with ORM Frameworks
444
445
```python
446
# Example showing DB-API 2.0 compatibility with SQLAlchemy
447
from sqlalchemy import create_engine
448
import postgresql.driver.dbapi20
449
450
# Register py-postgresql as DB-API driver
451
engine = create_engine(
452
'postgresql+pg8000://user:pass@localhost/mydb',
453
module=postgresql.driver.dbapi20
454
)
455
456
# Now SQLAlchemy can use py-postgresql through DB-API 2.0 interface
457
with engine.connect() as conn:
458
result = conn.execute("SELECT * FROM users")
459
for row in result:
460
print(row)
461
```