0
# Query Execution and Cursors
1

2
Comprehensive cursor functionality for query execution, result fetching, and data manipulation with multiple cursor types optimized for different performance and memory requirements.
3

4
## Capabilities
5

6
### Standard Cursors
7

8
Basic cursor functionality for query execution and result retrieval with full DB-API 2.0 compliance.
9

10
```python { .api }
11
class Cursor:
12
    def execute(
13
        self,
14
        query,
15
        params=None,
16
        *,
17
        prepare: bool | None = None,
18
        binary: bool | None = None
19
    ) -> Cursor:
20
        """
21
        Execute a database query.
22
        
23
        Args:
24
            query: SQL query string or sql.Composable object
25
            params: Query parameters (tuple, list, or dict)
26
            prepare: Use prepared statements for better performance
27
            binary: Request binary format for results
28
            
29
        Returns:
30
            Self for method chaining
31
        """
32
        
33
    def executemany(
34
        self,
35
        query,
36
        params_seq,
37
        *,
38
        returning: bool = False
39
    ) -> None:
40
        """
41
        Execute query multiple times with different parameter sets.
42
        
43
        Args:
44
            query: SQL query string or sql.Composable object
45
            params_seq: Sequence of parameter sets
46
            returning: True if query returns results
47
        """
48
        
49
    def executescript(self, script: str) -> None:
50
        """Execute multiple SQL statements from a script"""
51
        
52
    def fetchone(self) -> Any:
53
        """
54
        Fetch next row from query results.
55
        
56
        Returns:
57
            Next row or None if no more rows
58
        """
59
        
60
    def fetchmany(self, size: int | None = None) -> list:
61
        """
62
        Fetch multiple rows from query results.
63
        
64
        Args:
65
            size: Number of rows to fetch (uses arraysize if None)
66
            
67
        Returns:
68
            List of rows (may be empty)
69
        """
70
        
71
    def fetchall(self) -> list:
72
        """
73
        Fetch all remaining rows from query results.
74
        
75
        Returns:
76
            List of all remaining rows
77
        """
78
        
79
    def close(self) -> None:
80
        """Close the cursor and free resources"""
81
        
82
    def scroll(self, value: int, mode: str = "relative") -> None:
83
        """
84
        Scroll cursor position in scrollable cursors.
85
        
86
        Args:
87
            value: Number of rows to scroll
88
            mode: 'relative' or 'absolute' positioning
89
        """
90

91
class AsyncCursor:
92
    async def execute(
93
        self,
94
        query,
95
        params=None,
96
        *,
97
        prepare: bool | None = None,
98
        binary: bool | None = None
99
    ) -> AsyncCursor:
100
        """Async version of Cursor.execute()"""
101
        
102
    async def executemany(
103
        self,
104
        query,
105
        params_seq,
106
        *,
107
        returning: bool = False
108
    ) -> None:
109
        """Async version of Cursor.executemany()"""
110
        
111
    async def fetchone(self) -> Any:
112
        """Async version of Cursor.fetchone()"""
113
        
114
    async def fetchmany(self, size: int | None = None) -> list:
115
        """Async version of Cursor.fetchmany()"""
116
        
117
    async def fetchall(self) -> list:
118
        """Async version of Cursor.fetchall()"""
119
```
120

121
### Server-Side Cursors
122

123
Server-side cursors for memory-efficient processing of large result sets by keeping data on the PostgreSQL server.
124

125
```python { .api }
126
class ServerCursor:
127
    def __init__(
128
        self,
129
        connection,
130
        name: str,
131
        *,
132
        scrollable: bool | None = None,
133
        withhold: bool = False
134
    ):
135
        """
136
        Create a server-side cursor.
137
        
138
        Args:
139
            connection: Database connection
140
            name: Cursor name (must be unique)
141
            scrollable: Enable bidirectional scrolling
142
            withhold: Keep cursor after transaction commit
143
        """
144
        
145
    @property
146
    def name(self) -> str:
147
        """Server-side cursor name"""
148
        
149
    @property
150
    def scrollable(self) -> bool | None:
151
        """True if cursor supports scrolling"""
152
        
153
    @property
154
    def withhold(self) -> bool:
155
        """True if cursor survives transaction commit"""
156
        
157
    def execute(self, query, params=None, *, binary: bool | None = None) -> ServerCursor:
158
        """Execute query using server-side cursor"""
159
        
160
    def executemany(self, query, params_seq) -> None:
161
        """Execute query multiple times using server cursor"""
162
        
163
    def fetchone(self) -> Any:
164
        """Fetch one row from server cursor"""
165
        
166
    def fetchmany(self, size: int | None = None) -> list:
167
        """Fetch multiple rows from server cursor"""
168
        
169
    def fetchall(self) -> list:
170
        """Fetch all remaining rows from server cursor"""
171

172
class AsyncServerCursor:
173
    # Async version of ServerCursor with same interface but async methods
174
    async def execute(self, query, params=None, *, binary: bool | None = None) -> AsyncServerCursor: ...
175
    async def fetchone(self) -> Any: ...
176
    async def fetchmany(self, size: int | None = None) -> list: ...
177
    async def fetchall(self) -> list: ...
178
```
179

180
### Client-Side Cursors
181

182
Client-side cursors that fetch and buffer results locally for applications that need random access to result sets.
183

184
```python { .api }
185
class ClientCursor:
186
    def execute(self, query, params=None, *, binary: bool | None = None) -> ClientCursor:
187
        """Execute query with client-side result buffering"""
188
        
189
    def fetchone(self) -> Any:
190
        """Fetch one row from client buffer"""
191
        
192
    def fetchmany(self, size: int | None = None) -> list:
193
        """Fetch multiple rows from client buffer"""
194
        
195
    def fetchall(self) -> list:
196
        """Return all buffered rows"""
197
        
198
    def scroll(self, value: int, mode: str = "relative") -> None:
199
        """Scroll within buffered results"""
200

201
class AsyncClientCursor:
202
    # Async version of ClientCursor
203
    async def execute(self, query, params=None, *, binary: bool | None = None) -> AsyncClientCursor: ...
204
    async def fetchone(self) -> Any: ...
205
    async def fetchmany(self, size: int | None = None) -> list: ...
206
    async def fetchall(self) -> list: ...
207
```
208

209
### Raw Cursors
210

211
Low-level cursors that return raw bytes without type adaptation for maximum performance in specialized applications.
212

213
```python { .api }
214
class RawCursor:
215
    """Cursor returning raw bytes without type conversion"""
216
    
217
    def execute(self, query, params=None, *, binary: bool | None = None) -> RawCursor:
218
        """Execute query returning raw bytes"""
219
        
220
    def fetchone(self) -> tuple[bytes, ...] | None:
221
        """Fetch one row as tuple of bytes"""
222
        
223
    def fetchmany(self, size: int | None = None) -> list[tuple[bytes, ...]]:
224
        """Fetch multiple rows as tuples of bytes"""
225
        
226
    def fetchall(self) -> list[tuple[bytes, ...]]:
227
        """Fetch all rows as tuples of bytes"""
228

229
class RawServerCursor:
230
    """Server-side cursor returning raw bytes"""
231
    # Same interface as RawCursor but server-side
232

233
class AsyncRawCursor:
234
    """Async cursor returning raw bytes"""
235
    async def execute(self, query, params=None, *, binary: bool | None = None) -> AsyncRawCursor: ...
236
    async def fetchone(self) -> tuple[bytes, ...] | None: ...
237
    async def fetchmany(self, size: int | None = None) -> list[tuple[bytes, ...]]: ...
238
    async def fetchall(self) -> list[tuple[bytes, ...]]: ...
239

240
class AsyncRawServerCursor:
241
    """Async server-side cursor returning raw bytes"""
242
    # Same interface as AsyncRawCursor but server-side
243
```
244

245
### Cursor Properties and Metadata
246

247
Access cursor state, query information, and result metadata.
248

249
```python { .api }
250
class Cursor:
251
    @property
252
    def description(self) -> list[Column] | None:
253
        """Column information for last query results"""
254
        
255
    @property
256
    def rowcount(self) -> int:
257
        """Number of rows affected by last query"""
258
        
259
    @property
260
    def arraysize(self) -> int:
261
        """Default number of rows for fetchmany()"""
262
        
263
    @arraysize.setter
264
    def arraysize(self, size: int) -> None:
265
        """Set default fetchmany() size"""
266
        
267
    @property
268
    def itersize(self) -> int:
269
        """Number of rows fetched per network round-trip"""
270
        
271
    @itersize.setter
272
    def itersize(self, size: int) -> None:
273
        """Set network fetch batch size"""
274
        
275
    @property
276
    def query(self) -> bytes | None:
277
        """Last executed query as bytes"""
278
        
279
    @property
280
    def params(self) -> Sequence | None:
281
        """Parameters used in last query"""
282
        
283
    @property
284
    def pgresult(self) -> PGresult | None:
285
        """Low-level PostgreSQL result object"""
286
        
287
    @property
288
    def connection(self) -> Connection:
289
        """Connection associated with this cursor"""
290
        
291
    @property
292
    def row_factory(self) -> RowFactory | None:
293
        """Current row factory for result formatting"""
294
        
295
    @row_factory.setter
296
    def row_factory(self, factory: RowFactory | None) -> None:
297
        """Set row factory for result formatting"""
298
```
299

300
### Iterator Interface
301

302
Cursors support Python iterator protocol for convenient row-by-row processing.
303

304
```python { .api }
305
class Cursor:
306
    def __iter__(self) -> Iterator:
307
        """Return iterator over query results"""
308
        
309
    def __next__(self) -> Any:
310
        """Get next row (used by iterator protocol)"""
311

312
# Usage example
313
for row in cursor:
314
    print(row)
315
```
316

317
### Context Manager Support
318

319
All cursor types support context manager protocol for automatic resource cleanup.
320

321
```python { .api }
322
class Cursor:
323
    def __enter__(self) -> Cursor:
324
        """Enter context manager"""
325
        
326
    def __exit__(self, exc_type, exc_val, exc_tb) -> None:
327
        """Exit context manager and close cursor"""
328

329
class AsyncCursor:
330
    async def __aenter__(self) -> AsyncCursor:
331
        """Enter async context manager"""
332
        
333
    async def __aexit__(self, exc_type, exc_val, exc_tb) -> None:
334
        """Exit async context manager and close cursor"""
335
```
336

337
### COPY Operations
338

339
Specialized cursor methods for high-performance bulk data operations using PostgreSQL's COPY protocol.
340

341
```python { .api }
342
class Cursor:
343
    def copy(
344
        self,
345
        statement: str,
346
        params=None,
347
        *,
348
        writer: Callable | None = None
349
    ) -> Copy:
350
        """
351
        Start COPY operation for bulk data transfer.
352
        
353
        Args:
354
            statement: COPY SQL statement
355
            params: Query parameters
356
            writer: Custom data writer function
357
            
358
        Returns:
359
            Copy context manager
360
        """
361

362
class AsyncCursor:
363
    def copy(
364
        self,
365
        statement: str,
366
        params=None,
367
        *,
368
        writer: Callable | None = None
369
    ) -> AsyncCopy:
370
        """Async version of copy operation"""
371
```
372

373
### Prepared Statements
374

375
Automatic prepared statement support for improved performance with repeated queries.
376

377
```python { .api }
378
class Connection:
379
    def prepare(self, query: str) -> str:
380
        """
381
        Prepare a statement for repeated execution.
382
        
383
        Args:
384
            query: SQL query to prepare
385
            
386
        Returns:
387
            Prepared statement name
388
        """
389
        
390
    def prepared(self) -> dict[str, str]:
391
        """Get mapping of prepared statement names to queries"""
392

393
# Prepared statements are used automatically when prepare=True
394
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,), prepare=True)
395
```
396

397
## Column Metadata
398

399
```python { .api }
400
class Column:
401
    @property
402
    def name(self) -> str:
403
        """Column name"""
404
        
405
    @property
406
    def type_code(self) -> int:
407
        """PostgreSQL type OID"""
408
        
409
    @property
410
    def display_size(self) -> int | None:
411
        """Display size for column"""
412
        
413
    @property
414
    def internal_size(self) -> int | None:
415
        """Internal storage size"""
416
        
417
    @property
418
    def precision(self) -> int | None:
419
        """Numeric precision"""
420
        
421
    @property
422
    def scale(self) -> int | None:
423
        """Numeric scale"""
424
        
425
    @property
426
    def null_ok(self) -> bool | None:
427
        """True if column allows NULL values"""
428
```
429

430
## Usage Examples
431

432
### Basic Query Execution
433

434
```python
435
# Simple query
436
with conn.cursor() as cur:
437
    cur.execute("SELECT version()")
438
    result = cur.fetchone()
439
    print(result)
440

441
# Parameterized query
442
with conn.cursor() as cur:
443
    cur.execute("SELECT * FROM users WHERE age > %s", (25,))
444
    users = cur.fetchall()
445
    for user in users:
446
        print(user)
447
```
448

449
### Large Result Set Processing
450

451
```python
452
# Server-side cursor for large results
453
with conn.cursor(name="large_query") as cur:
454
    cur.execute("SELECT * FROM huge_table")
455
    while True:
456
        rows = cur.fetchmany(1000)  # Fetch in batches
457
        if not rows:
458
            break
459
        process_batch(rows)
460
```
461

462
### Bulk Operations
463

464
```python
465
# Bulk insert
466
data = [("Alice", 30), ("Bob", 25), ("Charlie", 35)]
467
with conn.cursor() as cur:
468
    cur.executemany(
469
        "INSERT INTO users (name, age) VALUES (%s, %s)",
470
        data
471
    )
472
```
473

474
### Async Operations
475

476
```python
477
# Async query execution
478
async with conn.cursor() as cur:
479
    await cur.execute("SELECT * FROM users")
480
    async for row in cur:
481
        print(row)
482
```