0
# Advanced Cursor Types
1

2
Specialized cursor classes that enhance the standard cursor functionality by returning results as dictionaries, named tuples, or providing logging capabilities for development and debugging purposes.
3

4
## Capabilities
5

6
### Dictionary Cursors
7

8
Cursors that return rows as dictionary-like or real dictionary objects, providing attribute and key-based access to column values.
9

10
```python { .api }
11
class DictConnection(connection):
12
    """Connection using DictCursor by default."""
13
    
14
    def cursor(self, *args, **kwargs):
15
        """Create DictCursor."""
16

17
class DictCursor(cursor):
18
    """Cursor returning dict-like rows."""
19
    
20
    def execute(self, query, vars=None):
21
        """Execute query and prepare dict-like results."""
22
    
23
    def callproc(self, procname, vars=None):
24
        """Call stored procedure with dict-like results."""
25
    
26
    def fetchone(self):
27
        """
28
        Fetch next row as DictRow.
29
        
30
        Returns:
31
        DictRow or None: Dictionary-like row object
32
        """
33
    
34
    def fetchmany(self, size=None):
35
        """
36
        Fetch multiple rows as DictRow objects.
37
        
38
        Returns:
39
        list[DictRow]: List of dictionary-like row objects
40
        """
41
    
42
    def fetchall(self):
43
        """
44
        Fetch all rows as DictRow objects.
45
        
46
        Returns:
47
        list[DictRow]: List of dictionary-like row objects
48
        """
49

50
class DictRow(list):
51
    """Dictionary-like row object."""
52
    
53
    def __getitem__(self, x):
54
        """Get item by index or column name."""
55
    
56
    def __setitem__(self, x, v):
57
        """Set item by index or column name."""
58
    
59
    def items(self):
60
        """Return (column_name, value) pairs."""
61
    
62
    def keys(self):
63
        """Return column names."""
64
    
65
    def values(self):
66
        """Return column values."""
67
    
68
    def get(self, x, default=None):
69
        """Get column value with default."""
70
    
71
    def copy(self):
72
        """Return OrderedDict copy."""
73

74
class RealDictConnection(connection):
75
    """Connection using RealDictCursor by default."""
76

77
class RealDictCursor(cursor):
78
    """Cursor with real dict rows."""
79
    
80
    def fetchone(self):
81
        """
82
        Fetch next row as real dictionary.
83
        
84
        Returns:
85
        dict or None: Real dictionary row
86
        """
87
    
88
    def fetchmany(self, size=None):
89
        """
90
        Fetch multiple rows as real dictionaries.
91
        
92
        Returns:
93
        list[dict]: List of dictionary rows
94
        """
95
    
96
    def fetchall(self):
97
        """
98
        Fetch all rows as real dictionaries.
99
        
100
        Returns:
101
        list[dict]: List of dictionary rows
102
        """
103

104
class RealDictRow(dict):
105
    """Real dictionary row object (OrderedDict)."""
106
```
107

108
**Usage Example:**
109

110
```python
111
import psycopg2
112
from psycopg2.extras import DictCursor, RealDictCursor, DictConnection
113

114
# Using DictCursor
115
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser")
116
with conn.cursor(cursor_factory=DictCursor) as cur:
117
    cur.execute("SELECT id, name, email FROM users WHERE id = %s", (1,))
118
    user = cur.fetchone()
119
    
120
    # Access by column name or index
121
    print(f"Name: {user['name']}")
122
    print(f"Email: {user[2]}")
123
    print(f"Keys: {list(user.keys())}")
124

125
# Using RealDictCursor for real dictionaries
126
with conn.cursor(cursor_factory=RealDictCursor) as cur:
127
    cur.execute("SELECT * FROM users LIMIT 3")
128
    users = cur.fetchall()
129
    
130
    for user in users:
131
        # Real dictionary access
132
        print(f"User: {user}")
133
        print(f"Name: {user.get('name', 'Unknown')}")
134

135
# Using DictConnection for default dict cursors
136
dict_conn = psycopg2.connect(
137
    "host=localhost dbname=mydb user=myuser",
138
    connection_factory=DictConnection
139
)
140
with dict_conn.cursor() as cur:
141
    cur.execute("SELECT name, age FROM users")
142
    for row in cur:
143
        print(f"{row['name']} is {row['age']} years old")
144

145
conn.close()
146
dict_conn.close()
147
```
148

149
### Named Tuple Cursors
150

151
Cursors that return rows as named tuples, providing attribute access to column values with automatic type generation.
152

153
```python { .api }
154
class NamedTupleConnection(connection):
155
    """Connection using NamedTupleCursor by default."""
156

157
class NamedTupleCursor(cursor):
158
    """Cursor returning named tuples."""
159
    
160
    def execute(self, query, vars=None):
161
        """Execute query and prepare named tuple results."""
162
    
163
    def executemany(self, query, vars):
164
        """Execute multiple times with named tuple results."""
165
    
166
    def callproc(self, procname, vars=None):
167
        """Call stored procedure with named tuple results."""
168
    
169
    def fetchone(self):
170
        """
171
        Fetch next row as named tuple.
172
        
173
        Returns:
174
        namedtuple or None: Named tuple row
175
        """
176
    
177
    def fetchmany(self, size=None):
178
        """
179
        Fetch multiple rows as named tuples.
180
        
181
        Returns:
182
        list[namedtuple]: List of named tuple rows
183
        """
184
    
185
    def fetchall(self):
186
        """
187
        Fetch all rows as named tuples.
188
        
189
        Returns:
190
        list[namedtuple]: List of named tuple rows
191
        """
192
    
193
    # Class attributes
194
    Record = None  # Generated namedtuple class for current query
195
    MAX_CACHE = 1024  # Maximum cached namedtuple classes
196
```
197

198
**Usage Example:**
199

200
```python
201
import psycopg2
202
from psycopg2.extras import NamedTupleCursor
203

204
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser")
205

206
with conn.cursor(cursor_factory=NamedTupleCursor) as cur:
207
    cur.execute("SELECT id, name, email, age FROM users WHERE age > %s", (25,))
208
    
209
    # Access by attribute name
210
    for user in cur:
211
        print(f"ID: {user.id}")
212
        print(f"Name: {user.name}")
213
        print(f"Email: {user.email}")
214
        print(f"Age: {user.age}")
215
    
216
    # The Record class is auto-generated
217
    print(f"Record type: {cur.Record}")
218
    print(f"Record fields: {cur.Record._fields}")
219

220
conn.close()
221
```
222

223
### Logging Cursors
224

225
Cursors that log executed queries and their execution time, useful for development, debugging, and performance monitoring.
226

227
```python { .api }
228
class LoggingConnection(connection):
229
    """Connection that logs queries."""
230
    
231
    def initialize(self, logobj):
232
        """
233
        Initialize logging with logger or file.
234
        
235
        Parameters:
236
        - logobj: Logger object or file-like object
237
        """
238
    
239
    def filter(self, msg, curs):
240
        """
241
        Filter log messages.
242
        
243
        Parameters:
244
        - msg (str): Log message
245
        - curs: Cursor object
246
        
247
        Returns:
248
        str: Filtered message
249
        """
250
    
251
    def log(self, msg, curs):
252
        """
253
        Log method (set by initialize).
254
        
255
        Parameters:
256
        - msg (str): Message to log
257
        - curs: Cursor object
258
        """
259

260
class LoggingCursor(cursor):
261
    """Cursor that logs executed queries."""
262
    
263
    def execute(self, query, vars=None):
264
        """Execute and log query."""
265
    
266
    def callproc(self, procname, vars=None):
267
        """Execute procedure and log."""
268

269
class MinTimeLoggingConnection(LoggingConnection):
270
    """Log queries over time threshold."""
271
    
272
    def initialize(self, logobj, mintime=0):
273
        """
274
        Initialize with time threshold.
275
        
276
        Parameters:
277
        - logobj: Logger object or file-like object
278
        - mintime (float): Minimum execution time to log (seconds)
279
        """
280
    
281
    def filter(self, msg, curs):
282
        """Filter by execution time."""
283

284
class MinTimeLoggingCursor(LoggingCursor):
285
    """Cursor for MinTimeLoggingConnection."""
286
    
287
    @property
288
    def timestamp(self):
289
        """Execution timestamp."""
290
```
291

292
**Usage Example:**
293

294
```python
295
import psycopg2
296
import logging
297
from psycopg2.extras import LoggingConnection, MinTimeLoggingConnection
298

299
# Set up logging
300
logging.basicConfig(level=logging.DEBUG)
301
logger = logging.getLogger('psycopg2')
302

303
# Basic query logging
304
conn = psycopg2.connect(
305
    "host=localhost dbname=mydb user=myuser",
306
    connection_factory=LoggingConnection
307
)
308
conn.initialize(logger)
309

310
with conn.cursor() as cur:
311
    cur.execute("SELECT * FROM users WHERE age > %s", (25,))
312
    users = cur.fetchall()
313
    # Logs: "SELECT * FROM users WHERE age > 25"
314

315
# Log only slow queries (> 0.1 seconds)
316
slow_conn = psycopg2.connect(
317
    "host=localhost dbname=mydb user=myuser", 
318
    connection_factory=MinTimeLoggingConnection
319
)
320
slow_conn.initialize(logger, mintime=0.1)
321

322
with slow_conn.cursor() as cur:
323
    cur.execute("SELECT COUNT(*) FROM large_table")
324
    # Only logs if query takes > 0.1 seconds
325

326
# Log to file
327
with open('queries.log', 'a') as logfile:
328
    file_conn = psycopg2.connect(
329
        "host=localhost dbname=mydb user=myuser",
330
        connection_factory=LoggingConnection
331
    )
332
    file_conn.initialize(logfile)
333
    
334
    with file_conn.cursor() as cur:
335
        cur.execute("INSERT INTO audit_log (action) VALUES (%s)", ("user_login",))
336
    
337
    file_conn.commit()
338
    file_conn.close()
339

340
conn.close()
341
slow_conn.close()
342
```
343

344
### Cursor Factories
345

346
Custom cursor creation and configuration for specialized use cases.
347

348
**Usage Example:**
349

350
```python
351
import psycopg2
352
from psycopg2.extras import DictCursor, LoggingCursor
353

354
# Custom cursor factory
355
def my_cursor_factory(conn):
356
    """Custom cursor combining dict and logging functionality."""
357
    class MyCustomCursor(DictCursor, LoggingCursor):
358
        pass
359
    return MyCustomCursor(conn)
360

361
# Use custom cursor factory
362
conn = psycopg2.connect(
363
    "host=localhost dbname=mydb user=myuser",
364
    cursor_factory=my_cursor_factory
365
)
366

367
# Set up logging for custom cursor
368
if hasattr(conn, 'initialize'):
369
    import logging
370
    logger = logging.getLogger('custom')
371
    conn.initialize(logger)
372

373
with conn.cursor() as cur:
374
    cur.execute("SELECT id, name FROM users")
375
    for row in cur:
376
        # Dictionary access + logging
377
        print(f"User: {row['name']}")
378

379
conn.close()
380
```
381

382
## Types
383

384
### Dictionary Row Types
385

386
```python { .api }
387
class DictRow(list):
388
    """Dictionary-like row that extends list."""
389
    
390
    def __getitem__(self, x) -> Any:
391
        """Get by index (int) or column name (str)."""
392
    
393
    def __setitem__(self, x, v) -> None:
394
        """Set by index (int) or column name (str)."""
395
    
396
    def items(self) -> list[tuple[str, Any]]:
397
        """Column name, value pairs."""
398
    
399
    def keys(self) -> list[str]:
400
        """Column names."""
401
    
402
    def values(self) -> list[Any]:
403
        """Column values."""
404
    
405
    def get(self, x, default=None) -> Any:
406
        """Get column value with default."""
407

408
class RealDictRow(dict):
409
    """Real dictionary row (OrderedDict)."""
410
```
411

412
### Named Tuple Cursor Attributes
413

414
```python { .api }
415
class NamedTupleCursor:
416
    Record: type  # Auto-generated namedtuple class
417
    MAX_CACHE: int  # 1024 - Maximum cached namedtuple classes
418
```
419

420
### Logging Connection Methods
421

422
```python { .api }
423
class LoggingConnection:
424
    def initialize(self, logobj) -> None:
425
        """Initialize with logger or file object."""
426
    
427
    def filter(self, msg: str, curs) -> str:
428
        """Filter log messages."""
429
    
430
    def log(self, msg: str, curs) -> None:
431
        """Log method (configured by initialize)."""
432

433
class MinTimeLoggingConnection(LoggingConnection):
434
    def initialize(self, logobj, mintime: float = 0) -> None:
435
        """Initialize with time threshold."""
436
```