0
# Query Execution
1

2
Prepared statement interface with parameter binding, result streaming, and transaction management for efficient and secure database operations.
3

4
## Capabilities
5

6
### Statement Preparation
7

8
Create prepared statements for efficient repeated execution with automatic parameter binding and type conversion.
9

10
```python { .api }
11
def prepare(statement):
12
    """
13
    Create a prepared statement from SQL with parameter placeholders.
14
    
15
    Parameters:
16
    - statement (str): SQL statement with $1, $2, etc. parameter placeholders
17
    
18
    Returns:
19
    Statement: Prepared statement object for repeated execution
20
    
21
    Raises:
22
    QueryError: If statement cannot be prepared
23
    """
24
```
25

26
### Statement Interface
27

28
Prepared statement object providing multiple execution modes for different use cases.
29

30
```python { .api }
31
class Statement:
32
    """
33
    Prepared statement with parameter binding and multiple execution modes.
34
    """
35
    
36
    def __call__(*parameters):
37
        """
38
        Execute statement and return all results.
39
        
40
        Parameters:
41
        - *parameters: Values for statement parameters ($1, $2, etc.)
42
        
43
        Returns:
44
        List of result rows or command result
45
        
46
        Raises:
47
        QueryError: If execution fails
48
        """
49
    
50
    def first(*parameters):
51
        """
52
        Execute statement and return first result row.
53
        
54
        Parameters:
55
        - *parameters: Values for statement parameters
56
        
57
        Returns:
58
        Row or None: First result row or None if no results
59
        """
60
    
61
    def rows(*parameters):
62
        """
63
        Execute statement and return iterator over result rows.
64
        
65
        Parameters:
66
        - *parameters: Values for statement parameters
67
        
68
        Returns:
69
        Iterator[Row]: Iterator over result rows for streaming
70
        """
71
    
72
    def chunks(*parameters):
73
        """
74
        Execute statement and return chunked results.
75
        
76
        Parameters:
77
        - *parameters: Values for statement parameters
78
        
79
        Returns:
80
        Chunks: Chunked result iterator for large datasets
81
        """
82
    
83
    def column(*parameters):
84
        """
85
        Execute statement and return iterator over single column values.
86
        
87
        Parameters:
88
        - *parameters: Values for statement parameters
89
        
90
        Returns:
91
        Iterator: Iterator over values from the first column
92
        """
93
    
94
    def declare(*parameters):
95
        """
96
        Create a scrollable cursor for the statement.
97
        
98
        Parameters:
99
        - *parameters: Values for statement parameters
100
        
101
        Returns:
102
        Cursor: Scrollable cursor for navigation and fetching
103
        """
104
    
105
    def close():
106
        """Close the prepared statement."""
107
    
108
    @property
109
    def sql():
110
        """
111
        Get the SQL text of the prepared statement.
112
        
113
        Returns:
114
        str: Original SQL statement text
115
        """
116
    
117
    @property
118
    def parameter_types():
119
        """
120
        Get parameter type information.
121
        
122
        Returns:
123
        List[int]: PostgreSQL type OIDs for parameters
124
        """
125
    
126
    @property
127
    def result_types():
128
        """
129
        Get result column type information.
130
        
131
        Returns:
132
        List[int]: PostgreSQL type OIDs for result columns
133
        """
134
```
135

136
### Direct Query Execution
137

138
Execute queries directly without preparation for one-time operations.
139

140
```python { .api }
141
def execute(statement, *parameters):
142
    """
143
    Execute a statement directly with parameters.
144
    
145
    Parameters:
146
    - statement (str): SQL statement
147
    - *parameters: Parameter values
148
    
149
    Returns:
150
    Command result or row data
151
    """
152

153
def query(statement, *parameters):
154
    """
155
    Execute a query and return all results.
156
    
157
    Parameters:
158
    - statement (str): SQL query
159
    - *parameters: Parameter values
160
    
161
    Returns:
162
    List[Row]: All result rows
163
    """
164
```
165

166
### Result Row Interface
167

168
Result rows providing both positional and named access to column values.
169

170
```python { .api }
171
class Row:
172
    """
173
    Result row with named and positional access to column values.
174
    """
175
    
176
    def __getitem__(key):
177
        """
178
        Get column value by index or name.
179
        
180
        Parameters:
181
        - key (int or str): Column index or name
182
        
183
        Returns:
184
        Column value with automatic type conversion
185
        """
186
    
187
    def __len__():
188
        """
189
        Get number of columns in row.
190
        
191
        Returns:
192
        int: Number of columns
193
        """
194
    
195
    def keys():
196
        """
197
        Get column names.
198
        
199
        Returns:
200
        List[str]: Column names
201
        """
202
    
203
    def values():
204
        """
205
        Get column values.
206
        
207
        Returns:
208
        List: Column values
209
        """
210
    
211
    def items():
212
        """
213
        Get column name-value pairs.
214
        
215
        Returns:
216
        List[tuple]: (name, value) pairs
217
        """
218
```
219

220
### Chunked Results
221

222
Interface for processing large result sets in chunks to manage memory usage.
223

224
```python { .api }
225
class Chunks:
226
    """
227
    Chunked result iterator for processing large datasets efficiently.
228
    """
229
    
230
    def __iter__():
231
        """
232
        Iterate over result chunks.
233
        
234
        Returns:
235
        Iterator[List[Row]]: Iterator over chunks of rows
236
        """
237
    
238
    def __next__():
239
        """
240
        Get next chunk of results.
241
        
242
        Returns:
243
        List[Row]: Next chunk of rows
244
        
245
        Raises:
246
        StopIteration: When no more chunks available
247
        """
248
    
249
    def close():
250
        """Close the chunked result iterator."""
251
```
252

253
### Cursor Interface
254

255
Scrollable cursor for bidirectional navigation through result sets with seek operations.
256

257
```python { .api }
258
class Cursor:
259
    """
260
    Scrollable cursor providing bidirectional navigation through query results.
261
    """
262
    
263
    def read(quantity=None, direction=None):
264
        """
265
        Read rows from the cursor position.
266
        
267
        Parameters:
268
        - quantity (int, optional): Number of rows to read (default: all remaining)
269
        - direction (str, optional): 'FORWARD' or 'BACKWARD' (default: cursor direction)
270
        
271
        Returns:
272
        List[Row]: List of rows read from cursor
273
        """
274
    
275
    def seek(offset, whence='ABSOLUTE'):
276
        """
277
        Move cursor to specified position.
278
        
279
        Parameters:
280
        - offset (int): Position offset
281
        - whence (str): 'ABSOLUTE', 'RELATIVE', 'FORWARD', or 'BACKWARD'
282
        """
283
    
284
    def __next__():
285
        """
286
        Get next row from cursor.
287
        
288
        Returns:
289
        Row: Next row in cursor direction
290
        
291
        Raises:
292
        StopIteration: When no more rows available
293
        """
294
    
295
    def clone():
296
        """
297
        Create a copy of the cursor.
298
        
299
        Returns:
300
        Cursor: New cursor instance at same position
301
        """
302
    
303
    def close():
304
        """Close the cursor and release resources."""
305
    
306
    @property
307
    def direction():
308
        """
309
        Get cursor direction.
310
        
311
        Returns:
312
        bool: True for FORWARD, False for BACKWARD
313
        """
314
    
315
    @property
316
    def cursor_id():
317
        """
318
        Get cursor identifier.
319
        
320
        Returns:
321
        str: Unique cursor identifier
322
        """
323
    
324
    @property
325
    def column_names():
326
        """
327
        Get result column names.
328
        
329
        Returns:
330
        List[str]: Column names in result order
331
        """
332
    
333
    @property
334
    def column_types():
335
        """
336
        Get result column types.
337
        
338
        Returns:
339
        List[type]: Python types for result columns
340
        """
341
    
342
    @property
343
    def statement():
344
        """
345
        Get associated statement.
346
        
347
        Returns:
348
        Statement: The statement that created this cursor
349
        """
350
```
351

352
## Usage Examples
353

354
### Basic Statement Preparation and Execution
355

356
```python
357
import postgresql
358

359
db = postgresql.open('pq://user:pass@localhost/mydb')
360

361
# Prepare statement for repeated use
362
get_user = db.prepare("SELECT id, name, email FROM users WHERE id = $1")
363

364
# Execute with parameter
365
user = get_user.first(123)
366
if user:
367
    print(f"User: {user['name']} ({user['email']})")
368

369
# Execute multiple times efficiently
370
user_ids = [1, 2, 3, 4, 5]
371
for user_id in user_ids:
372
    user = get_user.first(user_id)
373
    if user:
374
        print(f"ID {user_id}: {user['name']}")
375

376
get_user.close()
377
```
378

379
### Streaming Large Result Sets
380

381
```python
382
import postgresql
383

384
db = postgresql.open('pq://user:pass@localhost/mydb')
385

386
# Prepare query for large dataset
387
get_all_orders = db.prepare("""
388
    SELECT order_id, customer_id, order_date, total
389
    FROM orders
390
    WHERE order_date >= $1
391
    ORDER BY order_date
392
""")
393

394
# Stream results to avoid loading all into memory
395
from datetime import date
396
start_date = date(2023, 1, 1)
397

398
total_amount = 0
399
order_count = 0
400

401
for order in get_all_orders.rows(start_date):
402
    total_amount += order['total']
403
    order_count += 1
404
    
405
    # Process order
406
    print(f"Order {order['order_id']}: ${order['total']}")
407

408
print(f"Processed {order_count} orders, total: ${total_amount}")
409
```
410

411
### Chunked Processing
412

413
```python
414
import postgresql
415

416
db = postgresql.open('pq://user:pass@localhost/mydb')
417

418
# Process large dataset in chunks
419
get_transactions = db.prepare("SELECT * FROM transactions WHERE processed = false")
420

421
# Process in chunks of 1000 rows
422
for chunk in get_transactions.chunks():
423
    batch_updates = []
424
    
425
    for transaction in chunk:
426
        # Process transaction
427
        result = process_transaction(transaction)
428
        batch_updates.append((result, transaction['id']))
429
    
430
    # Batch update processed transactions
431
    update_stmt = db.prepare("UPDATE transactions SET result = $1, processed = true WHERE id = $2")
432
    for result, tx_id in batch_updates:
433
        update_stmt(result, tx_id)
434
    
435
    print(f"Processed chunk of {len(chunk)} transactions")
436
```
437

438
### Parameter Types and Query Building
439

440
```python
441
import postgresql
442

443
db = postgresql.open('pq://user:pass@localhost/mydb')
444

445
# Prepare statement with multiple parameter types
446
search_query = db.prepare("""
447
    SELECT product_id, name, price, in_stock
448
    FROM products
449
    WHERE category = $1
450
    AND price BETWEEN $2 AND $3
451
    AND in_stock = $4
452
    ORDER BY price
453
""")
454

455
# Execute with different parameter types
456
products = search_query(
457
    "electronics",  # str
458
    100.0,          # float
459
    500.0,          # float
460
    True            # bool
461
)
462

463
for product in products:
464
    stock_status = "In Stock" if product['in_stock'] else "Out of Stock"
465
    print(f"{product['name']}: ${product['price']} - {stock_status}")
466

467
# Check statement metadata
468
print(f"Parameter types: {search_query.parameter_types}")
469
print(f"Result types: {search_query.result_types}")
470
```
471

472
### Direct Query Execution
473

474
```python
475
import postgresql
476

477
db = postgresql.open('pq://user:pass@localhost/mydb')
478

479
# One-time queries don't need preparation
480
table_count = db.query("SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public'")[0][0]
481
print(f"Public tables: {table_count}")
482

483
# Execute DDL or commands
484
db.execute("CREATE INDEX IF NOT EXISTS idx_user_email ON users(email)")
485
db.execute("ANALYZE users")
486

487
# Execute with parameters
488
recent_users = db.query(
489
    "SELECT name FROM users WHERE created_at > $1",
490
    datetime.now() - timedelta(days=7)
491
)
492

493
for user in recent_users:
494
    print(f"Recent user: {user['name']}")
495
```
496

497
### Error Handling
498

499
```python
500
import postgresql
501
import postgresql.exceptions as pg_exc
502

503
db = postgresql.open('pq://user:pass@localhost/mydb')
504

505
try:
506
    # Prepare potentially problematic statement
507
    stmt = db.prepare("SELECT * FROM maybe_missing_table WHERE id = $1")
508
    result = stmt.first(123)
509
    
510
except pg_exc.ProgrammingError as e:
511
    print(f"SQL error: {e}")
512
    # Handle missing table, invalid SQL, etc.
513
    
514
except pg_exc.DataError as e:
515
    print(f"Data error: {e}")
516
    # Handle invalid parameter values, type conversion errors
517
    
518
except pg_exc.ConnectionError as e:
519
    print(f"Connection error: {e}")
520
    # Handle connection issues
521
    
522
finally:
523
    if 'stmt' in locals():
524
        stmt.close()
525
```