0
# Query Execution
1
2
Database cursor operations for executing SQL statements, fetching results, handling bulk operations, and managing result sets. Supports both dynamic and prepared statements, parameter binding, streaming results, and Vertica-specific COPY operations for bulk data loading.
3
4
## Capabilities
5
6
### Cursor Class
7
8
The Cursor class provides methods for executing queries and fetching results, following the DB-API 2.0 specification.
9
10
```python { .api }
11
class Cursor:
12
"""
13
Database cursor for executing queries and fetching results.
14
"""
15
16
def execute(self, operation: str, parameters=None, use_prepared_statements=None,
17
copy_stdin=None, buffer_size=131072) -> None:
18
"""
19
Execute a database operation (query or command).
20
21
Parameters:
22
- operation (str): SQL statement to execute
23
- parameters (dict or sequence, optional): Parameters for SQL statement
24
- use_prepared_statements (bool, optional): Use prepared statements for performance
25
- copy_stdin (file-like, optional): Input stream for COPY FROM STDIN operations
26
- buffer_size (int): Buffer size for COPY operations (default: 131072)
27
28
Raises:
29
ProgrammingError: If SQL syntax is invalid
30
DatabaseError: If execution fails
31
"""
32
33
def executemany(self, operation: str, seq_of_parameters, use_prepared_statements=None) -> None:
34
"""
35
Execute a database operation against multiple parameter sets.
36
37
Parameters:
38
- operation (str): SQL statement to execute
39
- seq_of_parameters (sequence): Sequence of parameter dictionaries/sequences
40
- use_prepared_statements (bool, optional): Use prepared statements for performance
41
42
Raises:
43
ProgrammingError: If SQL syntax is invalid
44
DatabaseError: If execution fails
45
"""
46
47
def fetchone(self) -> tuple:
48
"""
49
Fetch the next row from the result set.
50
51
Returns:
52
tuple: Next row as a tuple, or None if no more rows
53
54
Raises:
55
ProgrammingError: If no query has been executed
56
"""
57
58
def fetchmany(self, size=None) -> list:
59
"""
60
Fetch multiple rows from the result set.
61
62
Parameters:
63
- size (int, optional): Number of rows to fetch (default: arraysize)
64
65
Returns:
66
list: List of tuples representing rows
67
68
Raises:
69
ProgrammingError: If no query has been executed
70
"""
71
72
def fetchall(self) -> list:
73
"""
74
Fetch all remaining rows from the result set.
75
76
Returns:
77
list: List of tuples representing all remaining rows
78
79
Raises:
80
ProgrammingError: If no query has been executed
81
"""
82
83
def nextset(self) -> bool:
84
"""
85
Move to the next result set (for multi-statement queries).
86
87
Returns:
88
bool: True if another result set is available, False otherwise
89
"""
90
91
def close(self) -> None:
92
"""
93
Close the cursor and free associated resources.
94
Cursor becomes unusable after this call.
95
"""
96
97
def copy(self, sql: str, data, buffer_size=131072, **kwargs) -> None:
98
"""
99
Execute COPY FROM STDIN operation for bulk data loading.
100
101
Parameters:
102
- sql (str): COPY FROM STDIN SQL statement
103
- data (file-like or iterable): Data source (file object or iterable of rows)
104
- buffer_size (int): Buffer size for copy operation (default: 131072)
105
106
Raises:
107
ProgrammingError: If COPY statement is invalid
108
CopyRejected: If data is rejected during copy
109
"""
110
111
def iterate(self) -> Generator:
112
"""
113
Return a generator for memory-efficient iteration through results.
114
115
Yields:
116
tuple: Each row as a tuple
117
118
Raises:
119
ProgrammingError: If no query has been executed
120
"""
121
122
def cancel(self) -> None:
123
"""
124
Cancel the current operation (deprecated, use Connection.cancel()).
125
126
Raises:
127
OperationalError: If cancellation fails
128
"""
129
130
def closed(self) -> bool:
131
"""
132
Check if cursor is closed.
133
134
Returns:
135
bool: True if cursor is closed, False otherwise
136
"""
137
138
@property
139
def description(self) -> tuple:
140
"""
141
Get column metadata for the last query.
142
143
Returns:
144
tuple: Sequence of 7-item tuples describing each column:
145
(name, type_code, display_size, internal_size, precision, scale, null_ok)
146
"""
147
148
@property
149
def rowcount(self) -> int:
150
"""
151
Get number of rows affected by the last execute() call.
152
153
Returns:
154
int: Number of affected rows, or -1 if not available
155
"""
156
157
@property
158
def arraysize(self) -> int:
159
"""
160
Get or set default fetch size for fetchmany().
161
162
Returns:
163
int: Current arraysize setting
164
"""
165
166
@arraysize.setter
167
def arraysize(self, size: int) -> None:
168
"""
169
Set default fetch size for fetchmany().
170
171
Parameters:
172
- size (int): New arraysize value
173
"""
174
175
@property
176
def cursor_type(self) -> str:
177
"""
178
Get or set cursor result format type.
179
180
Returns:
181
str: Current cursor type ('list', 'dict', etc.)
182
"""
183
184
@cursor_type.setter
185
def cursor_type(self, cursor_type: str) -> None:
186
"""
187
Set cursor result format type.
188
189
Parameters:
190
- cursor_type (str): New cursor type
191
"""
192
193
def __enter__(self) -> 'Cursor':
194
"""
195
Enter context manager.
196
197
Returns:
198
Cursor: Self for context manager protocol
199
"""
200
201
def __exit__(self, exc_type, exc_val, exc_tb) -> None:
202
"""
203
Exit context manager and close cursor.
204
205
Parameters:
206
- exc_type: Exception type (if any)
207
- exc_val: Exception value (if any)
208
- exc_tb: Exception traceback (if any)
209
"""
210
```
211
212
### Data Conversion Methods
213
214
The Cursor class provides methods for customizing data type conversion between Python and Vertica.
215
216
```python { .api }
217
def object_to_sql_literal(self, py_obj) -> str:
218
"""
219
Convert Python object to SQL literal string.
220
221
Parameters:
222
- py_obj: Python object to convert
223
224
Returns:
225
str: SQL literal representation
226
"""
227
228
def register_sql_literal_adapter(self, obj_type: type, adapter_func) -> None:
229
"""
230
Register custom SQL literal adapter for a Python type.
231
232
Parameters:
233
- obj_type (type): Python type to adapt
234
- adapter_func (callable): Function that converts instances to SQL literals
235
"""
236
237
def register_sqldata_converter(self, oid: int, converter_func) -> None:
238
"""
239
Register custom data type converter for a Vertica type OID.
240
241
Parameters:
242
- oid (int): Vertica type OID
243
- converter_func (callable): Function that converts raw data to Python objects
244
"""
245
246
def unregister_sqldata_converter(self, oid: int) -> None:
247
"""
248
Remove custom data type converter for a Vertica type OID.
249
250
Parameters:
251
- oid (int): Vertica type OID to remove converter for
252
"""
253
254
@property
255
def disable_sqldata_converter(self) -> bool:
256
"""
257
Get or set whether to bypass all data type conversions.
258
259
Returns:
260
bool: True if conversions are disabled, False otherwise
261
"""
262
263
@disable_sqldata_converter.setter
264
def disable_sqldata_converter(self, value: bool) -> None:
265
"""
266
Enable or disable all data type conversions.
267
268
Parameters:
269
- value (bool): True to disable conversions, False to enable
270
"""
271
```
272
273
## Usage Examples
274
275
### Basic Query Execution
276
277
```python
278
import vertica_python
279
280
with vertica_python.connect(host='localhost', user='dbadmin', database='mydb') as conn:
281
with conn.cursor() as cursor:
282
# Simple SELECT query
283
cursor.execute("SELECT name, age FROM users WHERE age > 25")
284
285
# Fetch results
286
rows = cursor.fetchall()
287
for row in rows:
288
name, age = row
289
print(f"Name: {name}, Age: {age}")
290
```
291
292
### Parameterized Queries
293
294
```python
295
with conn.cursor() as cursor:
296
# Named parameters (recommended)
297
cursor.execute(
298
"SELECT * FROM users WHERE age > :min_age AND city = :city",
299
{'min_age': 25, 'city': 'New York'}
300
)
301
302
# Positional parameters also supported
303
cursor.execute(
304
"SELECT * FROM users WHERE age > %s AND city = %s",
305
(25, 'New York')
306
)
307
308
results = cursor.fetchall()
309
```
310
311
### Bulk Operations with executemany
312
313
```python
314
with conn.cursor() as cursor:
315
# Insert multiple rows efficiently
316
users_data = [
317
{'name': 'Alice', 'age': 30, 'city': 'Boston'},
318
{'name': 'Bob', 'age': 25, 'city': 'Chicago'},
319
{'name': 'Carol', 'age': 35, 'city': 'Denver'}
320
]
321
322
cursor.executemany(
323
"INSERT INTO users (name, age, city) VALUES (:name, :age, :city)",
324
users_data
325
)
326
```
327
328
### Prepared Statements
329
330
```python
331
with conn.cursor() as cursor:
332
# Enable prepared statements for better performance with repeated queries
333
cursor.execute(
334
"SELECT * FROM large_table WHERE id = :user_id",
335
{'user_id': 12345},
336
use_prepared_statements=True
337
)
338
339
result = cursor.fetchone()
340
```
341
342
### Memory-Efficient Result Iteration
343
344
```python
345
with conn.cursor() as cursor:
346
cursor.execute("SELECT * FROM very_large_table")
347
348
# Use iterate() for memory-efficient processing
349
for row in cursor.iterate():
350
process_row(row) # Process one row at a time
351
352
# Alternative: Control batch size with fetchmany
353
cursor.arraysize = 1000
354
while True:
355
rows = cursor.fetchmany()
356
if not rows:
357
break
358
for row in rows:
359
process_row(row)
360
```
361
362
### COPY Operations for Bulk Loading
363
364
```python
365
import io
366
367
with conn.cursor() as cursor:
368
# COPY from string data
369
csv_data = "1,Alice,30\n2,Bob,25\n3,Carol,35\n"
370
data_stream = io.StringIO(csv_data)
371
372
cursor.copy(
373
"COPY users (id, name, age) FROM STDIN DELIMITER ','",
374
data_stream
375
)
376
377
# COPY from file
378
with open('users.csv', 'r') as f:
379
cursor.copy(
380
"COPY users FROM STDIN DELIMITER ',' ENCLOSED BY '\"'",
381
f
382
)
383
384
# COPY from list of rows
385
rows = [
386
[1, 'Alice', 30],
387
[2, 'Bob', 25],
388
[3, 'Carol', 35]
389
]
390
391
cursor.copy(
392
"COPY users (id, name, age) FROM STDIN DELIMITER ','",
393
rows
394
)
395
```
396
397
### Column Metadata Access
398
399
```python
400
with conn.cursor() as cursor:
401
cursor.execute("SELECT name, age, salary FROM employees LIMIT 1")
402
403
# Access column metadata
404
for col_desc in cursor.description:
405
name, type_code, display_size, internal_size, precision, scale, null_ok = col_desc
406
print(f"Column: {name}, Type: {type_code}, Nullable: {null_ok}")
407
408
# Check affected row count
409
cursor.execute("UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering'")
410
print(f"Updated {cursor.rowcount} rows")
411
```
412
413
### Custom Data Type Conversion
414
415
```python
416
import decimal
417
from datetime import datetime
418
419
with conn.cursor() as cursor:
420
# Register custom converter for high-precision decimals
421
def decimal_converter(value):
422
return decimal.Decimal(value)
423
424
cursor.register_sqldata_converter(vertica_python.datatypes.VerticaType.NUMERIC, decimal_converter)
425
426
# Register custom SQL literal adapter
427
def decimal_adapter(obj):
428
return str(obj)
429
430
cursor.register_sql_literal_adapter(decimal.Decimal, decimal_adapter)
431
432
# Use custom conversion
433
cursor.execute("SELECT price FROM products WHERE id = :id", {'id': 123})
434
price = cursor.fetchone()[0] # Returns decimal.Decimal instead of float
435
```
436
437
### Cursor Types for Different Result Formats
438
439
```python
440
# Default cursor returns tuples
441
with conn.cursor() as cursor:
442
cursor.execute("SELECT name, age FROM users LIMIT 1")
443
row = cursor.fetchone() # ('Alice', 30)
444
445
# Dictionary cursor returns dict objects
446
with conn.cursor(cursor_type='dict') as cursor:
447
cursor.execute("SELECT name, age FROM users LIMIT 1")
448
row = cursor.fetchone() # {'name': 'Alice', 'age': 30}
449
```
450
451
### Transaction Control with Cursors
452
453
```python
454
try:
455
with conn.cursor() as cursor:
456
# Begin transaction (implicit)
457
cursor.execute("INSERT INTO accounts (name, balance) VALUES ('Alice', 1000)")
458
cursor.execute("INSERT INTO accounts (name, balance) VALUES ('Bob', 500)")
459
460
# Verify data
461
cursor.execute("SELECT COUNT(*) FROM accounts")
462
count = cursor.fetchone()[0]
463
464
if count >= 2:
465
conn.commit() # Commit transaction
466
else:
467
conn.rollback() # Rollback on error
468
469
except Exception as e:
470
conn.rollback() # Rollback on exception
471
raise
472
```