0
# Query Execution
1
2
Cursor-based query execution with support for prepared statements, parameter binding, result set iteration, and bulk operations.
3
4
## Import Statement
5
6
```python
7
import phoenixdb.cursor
8
from phoenixdb.cursor import Cursor, DictCursor, ColumnDescription
9
```
10
11
## Capabilities
12
13
### Cursor Creation
14
15
Cursors are created from connections and provide the interface for executing SQL statements and retrieving results.
16
17
```python { .api }
18
class Connection:
19
def cursor(self, cursor_factory=None):
20
"""
21
Creates a new cursor.
22
23
Parameters:
24
- cursor_factory: Cursor class to use (default: Cursor)
25
Common options: Cursor, DictCursor
26
27
Returns:
28
Cursor: New cursor object
29
30
Raises:
31
ProgrammingError: If connection is closed
32
"""
33
```
34
35
### Standard Cursor
36
37
The standard cursor returns results as tuples and provides full DB API 2.0 functionality.
38
39
```python { .api }
40
class Cursor:
41
"""Database cursor for executing queries and iterating over results."""
42
43
def __init__(self, connection, id=None): ...
44
45
def close(self):
46
"""
47
Closes the cursor. No further operations allowed after closing.
48
Automatically called when using with statement.
49
"""
50
51
def execute(self, operation, parameters=None):
52
"""
53
Executes SQL statement with optional parameters.
54
55
Parameters:
56
- operation (str): SQL statement to execute
57
- parameters (list/tuple): Parameter values for placeholders
58
59
Raises:
60
ProgrammingError: SQL syntax errors, parameter count mismatch
61
DatabaseError: Database execution errors
62
"""
63
64
def executemany(self, operation, seq_of_parameters):
65
"""
66
Executes SQL statement with multiple parameter sets (bulk operation).
67
68
Parameters:
69
- operation (str): SQL statement to execute
70
- seq_of_parameters (list): List of parameter tuples/lists
71
72
Returns:
73
List of update counts
74
75
Raises:
76
ProgrammingError: SQL syntax errors, parameter issues
77
DatabaseError: Database execution errors
78
"""
79
80
def fetchone(self):
81
"""
82
Fetches next result row.
83
84
Returns:
85
tuple: Next row or None if no more rows
86
87
Raises:
88
ProgrammingError: No select statement executed
89
"""
90
91
def fetchmany(self, size=None):
92
"""
93
Fetches multiple result rows.
94
95
Parameters:
96
- size (int): Number of rows to fetch (default: arraysize)
97
98
Returns:
99
list: List of row tuples
100
"""
101
102
def fetchall(self):
103
"""
104
Fetches all remaining result rows.
105
106
Returns:
107
list: List of all remaining row tuples
108
"""
109
110
def setinputsizes(self, sizes):
111
"""DB API 2.0 compatibility method (no-op)."""
112
113
def setoutputsize(self, size, column=None):
114
"""DB API 2.0 compatibility method (no-op)."""
115
```
116
117
### Column Description
118
119
Result set column metadata structure returned by cursor.description.
120
121
```python { .api }
122
ColumnDescription = collections.namedtuple('ColumnDescription', 'name type_code display_size internal_size precision scale null_ok')
123
"""
124
Named tuple for representing column metadata in cursor.description.
125
126
Fields:
127
- name (str): Column name
128
- type_code (ColumnType): Type code object for comparison
129
- display_size (int): Display size (may be None)
130
- internal_size (int): Internal size (may be None)
131
- precision (int): Numeric precision (may be None)
132
- scale (int): Numeric scale (may be None)
133
- null_ok (bool/None): Whether column allows NULL values
134
"""
135
```
136
137
### Cursor Properties
138
139
```python { .api }
140
class Cursor:
141
@property
142
def description(self):
143
"""
144
Column descriptions as ColumnDescription namedtuples.
145
None if no result set available.
146
147
Returns:
148
list: List of ColumnDescription tuples with fields:
149
- name: Column name
150
- type_code: Type code
151
- display_size: Display size
152
- internal_size: Internal size
153
- precision: Numeric precision
154
- scale: Numeric scale
155
- null_ok: Nullable flag (True/False/None)
156
"""
157
158
@property
159
def rowcount(self):
160
"""
161
Number of rows affected by last DML statement.
162
-1 for SELECT statements or unknown count.
163
164
Returns:
165
int: Row count or -1
166
"""
167
168
@property
169
def rownumber(self):
170
"""
171
Current 0-based row index in result set.
172
None if position cannot be determined.
173
174
Returns:
175
int: Current row index or None
176
"""
177
178
@property
179
def connection(self):
180
"""
181
Associated Connection object.
182
183
Returns:
184
Connection: Connection that created this cursor
185
"""
186
187
@property
188
def closed(self):
189
"""
190
Boolean indicating if cursor is closed.
191
192
Returns:
193
bool: True if closed, False if open
194
"""
195
196
arraysize = 1
197
"""Number of rows fetchmany() retrieves by default."""
198
199
itersize = 2000
200
"""Number of rows to fetch per network round trip during iteration."""
201
```
202
203
### Dictionary Cursor
204
205
Returns results as dictionaries with column names as keys instead of tuples.
206
207
```python { .api }
208
class DictCursor(Cursor):
209
"""
210
Cursor that returns results as dictionaries with column names as keys.
211
212
Inherits all methods from Cursor but fetchone(), fetchmany(), fetchall()
213
and iteration return dictionaries instead of tuples.
214
"""
215
216
def fetchone(self):
217
"""
218
Fetches next result row as dictionary.
219
220
Returns:
221
dict: Row with column names as keys, or None if no more rows
222
"""
223
224
def fetchmany(self, size=None):
225
"""
226
Fetches multiple result rows as list of dictionaries.
227
228
Parameters:
229
- size (int): Number of rows to fetch
230
231
Returns:
232
list: List of dictionaries with column names as keys
233
"""
234
235
def fetchall(self):
236
"""
237
Fetches all remaining result rows as list of dictionaries.
238
239
Returns:
240
list: List of dictionaries with column names as keys
241
"""
242
```
243
244
Usage example:
245
246
```python
247
cursor = conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor)
248
cursor.execute("SELECT id, username FROM users WHERE id=1")
249
user = cursor.fetchone() # Returns {'id': 1, 'username': 'admin'}
250
print(user['username'])
251
```
252
253
### Context Manager Support
254
255
Cursors support context manager protocol for automatic cleanup.
256
257
```python { .api }
258
class Cursor:
259
def __enter__(self):
260
"""Context manager entry."""
261
return self
262
263
def __exit__(self, exc_type, exc_value, traceback):
264
"""Context manager exit with automatic cursor closing."""
265
```
266
267
### Iterator Protocol
268
269
Cursors can be used as iterators to process result sets row by row.
270
271
```python { .api }
272
class Cursor:
273
def __iter__(self):
274
"""Returns self as iterator."""
275
return self
276
277
def __next__(self):
278
"""
279
Fetches next row for iteration.
280
281
Returns:
282
tuple: Next row
283
284
Raises:
285
StopIteration: When no more rows available
286
"""
287
288
# Python 2 compatibility
289
next = __next__
290
```
291
292
## Usage Examples
293
294
### Basic Query Execution
295
296
```python
297
cursor = conn.cursor()
298
299
# Simple SELECT
300
cursor.execute("SELECT * FROM users")
301
rows = cursor.fetchall()
302
for row in rows:
303
print(row)
304
305
# Parameterized query
306
cursor.execute("SELECT * FROM users WHERE id = ?", (1,))
307
user = cursor.fetchone()
308
309
# INSERT with parameters
310
cursor.execute("INSERT INTO users (id, username) VALUES (?, ?)", (2, 'john'))
311
```
312
313
### Bulk Operations
314
315
```python
316
cursor = conn.cursor()
317
318
# Bulk INSERT
319
users = [(1, 'admin'), (2, 'john'), (3, 'jane')]
320
cursor.executemany("INSERT INTO users (id, username) VALUES (?, ?)", users)
321
322
# Check affected row counts
323
print(f"Inserted {cursor.rowcount} rows")
324
```
325
326
### Result Set Iteration
327
328
```python
329
cursor = conn.cursor()
330
cursor.execute("SELECT * FROM large_table")
331
332
# Iterate over results without loading all into memory
333
for row in cursor:
334
process_row(row)
335
# Automatically fetches more rows as needed
336
```
337
338
### Fetch Size Control
339
340
```python
341
cursor = conn.cursor()
342
cursor.arraysize = 100 # fetchmany() default size
343
cursor.itersize = 5000 # Network fetch size
344
345
cursor.execute("SELECT * FROM users")
346
347
# Fetch in chunks
348
while True:
349
rows = cursor.fetchmany()
350
if not rows:
351
break
352
process_rows(rows)
353
```
354
355
### Column Information
356
357
```python
358
cursor = conn.cursor()
359
cursor.execute("SELECT id, username, created_date FROM users")
360
361
# Examine column metadata
362
for col in cursor.description:
363
print(f"Column: {col.name}, Type: {col.type_code}, Nullable: {col.null_ok}")
364
365
# Access results
366
row = cursor.fetchone()
367
print(f"ID: {row[0]}, Username: {row[1]}, Created: {row[2]}")
368
```
369
370
### Phoenix-Specific Operations
371
372
```python
373
cursor = conn.cursor()
374
375
# Phoenix UPSERT operation
376
cursor.execute("UPSERT INTO users VALUES (?, ?)", (1, 'updated_admin'))
377
378
# Array parameter support
379
cursor.execute("SELECT * FROM table WHERE id = ANY(?)", ([1, 2, 3],))
380
381
# Phoenix timestamp precision handling
382
from datetime import datetime
383
timestamp = datetime.now()
384
cursor.execute("INSERT INTO events (id, timestamp) VALUES (?, ?)", (1, timestamp))
385
```
386
387
## Advanced Cursor Operations
388
389
### Prepared Statement Reuse
390
391
```python
392
cursor = conn.cursor()
393
394
# Prepare statement once
395
cursor.execute("INSERT INTO users (id, username) VALUES (?, ?)", (1, 'first'))
396
397
# Reuse prepared statement with different parameters
398
cursor.execute("INSERT INTO users (id, username) VALUES (?, ?)", (2, 'second'))
399
cursor.execute("INSERT INTO users (id, username) VALUES (?, ?)", (3, 'third'))
400
```
401
402
### Error Handling
403
404
```python
405
import phoenixdb
406
407
cursor = conn.cursor()
408
try:
409
cursor.execute("SELECT * FROM nonexistent_table")
410
except phoenixdb.ProgrammingError as e:
411
print(f"SQL Error: {e.message}")
412
print(f"SQL State: {e.sqlstate}")
413
print(f"Error Code: {e.code}")
414
except phoenixdb.OperationalError as e:
415
print(f"Database Error: {e.message}")
416
```