0
# Enhanced Cursors and Row Access
1

2
Specialized cursor implementations providing dictionary-like row access, named tuple rows, real dictionary rows, and logging capabilities. These cursors extend the basic cursor functionality with convenient row access patterns for improved development experience.
3

4
## Capabilities
5

6
### Dictionary Cursors
7

8
Cursors that return rows as dictionary-like objects allowing access to columns by name instead of index position.
9

10
```python { .api }
11
class DictCursorBase(cursor):
12
    """Base class for dictionary-like cursors."""
13
    
14
    def __init__(self, *args, **kwargs):
15
        """Initialize dict cursor base."""
16

17
class DictCursor(DictCursorBase):
18
    """Cursor returning DictRow objects."""
19
    
20
    def __init__(self, *args, **kwargs):
21
        """Initialize dictionary cursor."""
22
    
23
    def fetchone(self):
24
        """
25
        Fetch next row as DictRow.
26
        
27
        Returns:
28
        DictRow/None: Dictionary-like row object
29
        """
30
    
31
    def fetchmany(self, size=None):
32
        """
33
        Fetch multiple rows as DictRow objects.
34
        
35
        Returns:
36
        list: List of DictRow objects
37
        """
38
    
39
    def fetchall(self):
40
        """
41
        Fetch all rows as DictRow objects.
42
        
43
        Returns:
44
        list: List of DictRow objects
45
        """
46

47
class DictConnection(connection):
48
    """Connection using DictCursor by default."""
49
    
50
    def cursor(self, *args, **kwargs):
51
        """Create DictCursor by default."""
52

53
class DictRow(list):
54
    """Dictionary-like row object."""
55
    
56
    def __init__(self, cursor):
57
        """Initialize from cursor description."""
58
    
59
    def __getitem__(self, x):
60
        """
61
        Get item by index or column name.
62
        
63
        Parameters:
64
        - x (int/str): Column index or name
65
        
66
        Returns:
67
        Value at column
68
        """
69
    
70
    def __setitem__(self, x, v):
71
        """
72
        Set item by index or column name.
73
        
74
        Parameters:
75
        - x (int/str): Column index or name
76
        - v: Value to set
77
        """
78
    
79
    def keys(self):
80
        """
81
        Get column names.
82
        
83
        Returns:
84
        iterator: Column names
85
        """
86
    
87
    def values(self):
88
        """
89
        Get column values.
90
        
91
        Returns:
92
        iterator: Column values
93
        """
94
    
95
    def items(self):
96
        """
97
        Get (name, value) pairs.
98
        
99
        Returns:
100
        iterator: (column_name, value) tuples
101
        """
102
    
103
    def get(self, x, default=None):
104
        """
105
        Get value with default.
106
        
107
        Parameters:
108
        - x (str): Column name
109
        - default: Default value if column not found
110
        
111
        Returns:
112
        Column value or default
113
        """
114
    
115
    def copy(self):
116
        """
117
        Create OrderedDict copy of row.
118
        
119
        Returns:
120
        OrderedDict: Copy of row data
121
        """
122
    
123
    def __contains__(self, x):
124
        """
125
        Check if column exists.
126
        
127
        Parameters:
128
        - x (str): Column name
129
        
130
        Returns:
131
        bool: True if column exists
132
        """
133
```
134

135
Usage examples:
136

137
```python
138
from psycopg2.extras import DictCursor, DictConnection
139

140
# Using DictCursor with existing connection
141
cur = conn.cursor(cursor_factory=DictCursor)
142
cur.execute("SELECT id, name, email FROM users WHERE id = %s", (1,))
143
user = cur.fetchone()
144

145
print(user['name'])     # Access by column name
146
print(user[1])          # Access by index still works
147
print(user.get('age', 'Unknown'))  # Safe access with default
148

149
# Iterate over columns
150
for key, value in user.items():
151
    print(f"{key}: {value}")
152

153
# Using DictConnection (DictCursor by default)
154
dict_conn = psycopg2.connect(..., connection_factory=DictConnection)
155
cur = dict_conn.cursor()  # Automatically a DictCursor
156
cur.execute("SELECT * FROM products")
157
for row in cur:
158
    print(f"Product: {row['name']}, Price: {row['price']}")
159
```
160

161
### Real Dictionary Cursors
162

163
Cursors that return actual Python dictionary objects instead of DictRow objects.
164

165
```python { .api }
166
class RealDictCursor(DictCursorBase):
167
    """Cursor returning real dict objects."""
168
    
169
    def __init__(self, *args, **kwargs):
170
        """Initialize real dictionary cursor."""
171

172
class RealDictConnection(connection):
173
    """Connection using RealDictCursor by default."""
174
    
175
    def cursor(self, *args, **kwargs):
176
        """Create RealDictCursor by default."""
177

178
class RealDictRow(dict):
179
    """Real dictionary row object."""
180
    
181
    def __init__(self, cursor):
182
        """Initialize from cursor description."""
183
```
184

185
Usage examples:
186

187
```python
188
from psycopg2.extras import RealDictCursor
189

190
cur = conn.cursor(cursor_factory=RealDictCursor)
191
cur.execute("SELECT id, name, email FROM users")
192
users = cur.fetchall()
193

194
# Each row is a real Python dict
195
for user in users:
196
    print(user)  # {'id': 1, 'name': 'Alice', 'email': 'alice@example.com'}
197
    user['processed'] = True  # Can modify like normal dict
198

199
# Useful for JSON serialization
200
import json
201
json_users = json.dumps(users)  # Direct serialization
202
```
203

204
### Named Tuple Cursors
205

206
Cursors that return rows as named tuples with column names as attributes.
207

208
```python { .api }
209
class NamedTupleCursor(cursor):
210
    """Cursor returning named tuple objects."""
211
    
212
    def __init__(self, *args, **kwargs):
213
        """Initialize named tuple cursor."""
214

215
class NamedTupleConnection(connection):
216
    """Connection using NamedTupleCursor by default."""
217
    
218
    def cursor(self, *args, **kwargs):
219
        """Create NamedTupleCursor by default."""
220
```
221

222
Usage examples:
223

224
```python
225
from psycopg2.extras import NamedTupleCursor
226

227
cur = conn.cursor(cursor_factory=NamedTupleCursor)
228
cur.execute("SELECT id, name, email FROM users WHERE id = %s", (1,))
229
user = cur.fetchone()
230

231
# Access as attributes
232
print(user.name)    # Access by attribute name
233
print(user.email)   # Clean, readable access
234
print(user[0])      # Index access still works
235

236
# Named tuples are immutable and hashable
237
user_set = {user}   # Can be added to sets
238
# user.name = 'Bob'  # Would raise AttributeError
239

240
# Good for type hints and IDE support
241
def process_user(user_row):
242
    return f"Processing {user_row.name} ({user_row.email})"
243
```
244

245
### Logging Cursors
246

247
Cursors that log SQL statements and execution times for debugging and monitoring.
248

249
```python { .api }
250
class LoggingConnection(connection):
251
    """Connection with logging capabilities."""
252
    
253
    def __init__(self, *args, **kwargs):
254
        """Initialize logging connection."""
255
    
256
    def cursor(self, *args, **kwargs):
257
        """Create LoggingCursor."""
258

259
class LoggingCursor(cursor):
260
    """Cursor that logs SQL statements."""
261
    
262
    def __init__(self, *args, **kwargs):
263
        """Initialize logging cursor."""
264
    
265
    def execute(self, query, vars=None):
266
        """Execute with logging."""
267
    
268
    def executemany(self, query, vars_list):
269
        """Execute many with logging."""
270

271
class MinTimeLoggingConnection(LoggingConnection):
272
    """Connection with minimum time logging."""
273
    
274
    def __init__(self, *args, **kwargs):
275
        """
276
        Initialize with minimum logging time.
277
        
278
        Parameters:
279
        - mintime (float): Minimum execution time to log (seconds)
280
        """
281

282
class MinTimeLoggingCursor(LoggingCursor):
283
    """Cursor logging only slow queries."""
284
    
285
    def __init__(self, *args, **kwargs):
286
        """Initialize minimum time logging cursor."""
287
```
288

289
Usage examples:
290

291
```python
292
import logging
293
from psycopg2.extras import LoggingConnection, MinTimeLoggingConnection
294

295
# Setup logging
296
logging.basicConfig(level=logging.DEBUG)
297

298
# Log all queries
299
log_conn = psycopg2.connect(..., connection_factory=LoggingConnection)
300
cur = log_conn.cursor()
301
cur.execute("SELECT * FROM users")  # Will be logged
302

303
# Log only slow queries (>0.5 seconds)
304
slow_conn = psycopg2.connect(..., 
305
    connection_factory=MinTimeLoggingConnection,
306
    mintime=0.5
307
)
308
cur = slow_conn.cursor()
309
cur.execute("SELECT * FROM large_table")  # Only logged if slow
310
```
311

312
### Cursor Context Managers
313

314
All cursor types support context manager protocol for automatic resource cleanup.
315

316
```python
317
# Automatic cursor cleanup
318
with conn.cursor(cursor_factory=DictCursor) as cur:
319
    cur.execute("SELECT * FROM users")
320
    users = cur.fetchall()
321
    # Cursor automatically closed
322

323
# Named cursor with context manager
324
with conn.cursor('server_cursor') as cur:
325
    cur.execute("SELECT * FROM large_table")
326
    for row in cur:
327
        process_row(row)
328
    # Server cursor automatically closed
329
```
330

331
### Bulk Operations with Enhanced Cursors
332

333
Enhanced cursors work seamlessly with bulk operations for improved performance.
334

335
```python
336
from psycopg2.extras import execute_batch, execute_values, RealDictCursor
337

338
# Bulk insert with real dict cursor (for result processing)
339
cur = conn.cursor(cursor_factory=RealDictCursor)
340
data = [
341
    {'name': 'Alice', 'email': 'alice@example.com'},
342
    {'name': 'Bob', 'email': 'bob@example.com'}
343
]
344

345
# Convert dicts to tuples for bulk insert
346
tuples = [(d['name'], d['email']) for d in data]
347
execute_batch(cur, 
348
    "INSERT INTO users (name, email) VALUES (%s, %s)",
349
    tuples
350
)
351

352
# Bulk select with dict cursor
353
cur.execute("SELECT * FROM users WHERE created_date = CURRENT_DATE")
354
new_users = cur.fetchall()  # List of dicts
355

356
# Process results easily
357
for user in new_users:
358
    send_welcome_email(user['email'])
359
```
360

361
### Bulk Operations
362

363
Efficient bulk execution functions for improved performance with large data sets.
364

365
```python { .api }
366
def execute_batch(cur, sql, argslist, page_size=100):
367
    """
368
    Execute statement in batches.
369
    
370
    Parameters:
371
    - cur (cursor): Database cursor
372
    - sql (str): SQL statement with placeholders
373
    - argslist (sequence): Sequence of parameter tuples
374
    - page_size (int): Number of statements per batch
375
    """
376

377
def execute_values(cur, sql, argslist, template=None, page_size=100, fetch=False):
378
    """
379
    Execute statement using VALUES syntax.
380
    
381
    Parameters:
382
    - cur (cursor): Database cursor
383
    - sql (str): SQL statement with VALUES placeholder
384
    - argslist (sequence): Sequence of parameter tuples
385
    - template (str, optional): VALUES template
386
    - page_size (int): Number of tuples per page
387
    - fetch (bool): Return results if True
388
    
389
    Returns:
390
    list/None: Query results if fetch=True
391
    """
392
```
393

394
## Types
395

396
### Row Factory Functions
397

398
```python { .api }
399
def DictRowFactory(cursor):
400
    """Factory function for DictRow objects."""
401

402
def RealDictRowFactory(cursor):
403
    """Factory function for real dict objects."""
404

405
def NamedTupleRowFactory(cursor):
406
    """Factory function for named tuple objects."""
407
```
408

409
### Cursor Factory Types
410

411
```python { .api }
412
CursorFactory = callable[[connection], cursor]
413
RowFactory = callable[[cursor], Any]
414
```
415

416
### Connection Factory Examples
417

418
```python
419
# Custom connection with default cursor factory
420
class MyConnection(psycopg2.extensions.connection):
421
    def cursor(self, *args, **kwargs):
422
        kwargs.setdefault('cursor_factory', DictCursor)
423
        return super().cursor(*args, **kwargs)
424

425
# Use custom connection
426
conn = psycopg2.connect(..., connection_factory=MyConnection)
427
cur = conn.cursor()  # Automatically a DictCursor
428
```