0
# SQL Execution and Cursors
1
2
SQL statement execution, parameter binding, result fetching, and cursor management with support for all Oracle SQL features and PL/SQL operations.
3
4
## Capabilities
5
6
### Cursor Creation and Management
7
8
Create and manage cursors for SQL execution and result processing.
9
10
```python { .api }
11
class Cursor:
12
def __init__(self, connection: Connection):
13
"""Create cursor from connection (usually via connection.cursor())"""
14
15
def close(self) -> None:
16
"""Close cursor and free resources"""
17
18
def prepare(self, sql: str) -> None:
19
"""Prepare SQL statement for execution"""
20
21
def parse(self, sql: str) -> None:
22
"""Parse SQL statement without execution"""
23
24
def __enter__(self) -> Cursor:
25
"""Context manager entry"""
26
27
def __exit__(self, exc_type, exc_val, exc_tb) -> None:
28
"""Context manager exit with automatic cleanup"""
29
```
30
31
### SQL Statement Execution
32
33
Execute SQL statements with parameter binding support.
34
35
```python { .api }
36
class Cursor:
37
def execute(self, sql: str, parameters=None) -> Cursor:
38
"""
39
Execute SQL statement with optional parameters.
40
41
Parameters:
42
- sql (str): SQL statement to execute
43
- parameters: Parameters for SQL statement (dict, list, or tuple)
44
45
Returns:
46
Self (for method chaining)
47
"""
48
49
def executemany(self, sql: str, seq_of_parameters, **kwargs) -> None:
50
"""
51
Execute SQL statement multiple times with different parameter sets.
52
53
Parameters:
54
- sql (str): SQL statement to execute
55
- seq_of_parameters: Sequence of parameter sets
56
- kwargs: Additional execution options
57
"""
58
59
def executemanyprepared(self, numRows: int, **kwargs) -> None:
60
"""
61
Execute many with prepared statement using bind arrays.
62
63
Parameters:
64
- numRows (int): Number of rows to execute
65
- kwargs: Additional execution options
66
"""
67
```
68
69
Usage examples:
70
71
```python
72
cursor = connection.cursor()
73
74
# Execute with named parameters
75
cursor.execute("SELECT * FROM employees WHERE department_id = :dept_id",
76
{"dept_id": 10})
77
78
# Execute with positional parameters
79
cursor.execute("INSERT INTO employees VALUES (:1, :2, :3)",
80
(1001, "John", "Doe"))
81
82
# Execute many with parameter sequences
83
data = [(1002, "Jane", "Smith"), (1003, "Bob", "Jones")]
84
cursor.executemany("INSERT INTO employees VALUES (:1, :2, :3)", data)
85
86
# Execute script
87
cursor.executescript("""
88
CREATE TABLE temp_table (id NUMBER, name VARCHAR2(50));
89
INSERT INTO temp_table VALUES (1, 'Test');
90
COMMIT;
91
""")
92
```
93
94
### Result Fetching
95
96
Retrieve query results using various fetch methods.
97
98
```python { .api }
99
class Cursor:
100
def fetchone(self) -> tuple:
101
"""
102
Fetch next row from query results.
103
104
Returns:
105
Tuple representing row, or None if no more rows
106
"""
107
108
def fetchmany(self, numRows=None) -> list:
109
"""
110
Fetch multiple rows from query results.
111
112
Parameters:
113
- numRows (int): Number of rows to fetch (default: arraysize)
114
115
Returns:
116
List of tuples representing rows
117
"""
118
119
def fetchall(self) -> list:
120
"""
121
Fetch all remaining rows from query results.
122
123
Returns:
124
List of tuples representing all remaining rows
125
"""
126
127
def fetchraw(self, numRows=None) -> list:
128
"""
129
Fetch raw data bypassing type conversion.
130
131
Parameters:
132
- numRows (int): Number of rows to fetch
133
134
Returns:
135
List of raw data tuples
136
"""
137
```
138
139
Usage examples:
140
141
```python
142
cursor.execute("SELECT employee_id, first_name, last_name FROM employees")
143
144
# Fetch one row at a time
145
row = cursor.fetchone()
146
while row:
147
print(f"ID: {row[0]}, Name: {row[1]} {row[2]}")
148
row = cursor.fetchone()
149
150
# Fetch multiple rows
151
cursor.execute("SELECT * FROM departments")
152
rows = cursor.fetchmany(5)
153
for row in rows:
154
print(row)
155
156
# Fetch all rows
157
cursor.execute("SELECT * FROM employees")
158
all_rows = cursor.fetchall()
159
print(f"Total employees: {len(all_rows)}")
160
161
# Iterator interface
162
cursor.execute("SELECT * FROM employees")
163
for row in cursor:
164
print(row)
165
```
166
167
### PL/SQL Execution
168
169
Execute stored procedures and functions.
170
171
```python { .api }
172
class Cursor:
173
def callfunc(self, name: str, returnType, parameters=None):
174
"""
175
Call stored function and return result.
176
177
Parameters:
178
- name (str): Function name
179
- returnType: Expected return type (cx_Oracle type or Python type)
180
- parameters: Function parameters (list or tuple)
181
182
Returns:
183
Function return value
184
"""
185
186
def callproc(self, name: str, parameters=None) -> list:
187
"""
188
Call stored procedure.
189
190
Parameters:
191
- name (str): Procedure name
192
- parameters: Procedure parameters (list or tuple)
193
194
Returns:
195
List of parameter values (including OUT parameters)
196
"""
197
```
198
199
Usage examples:
200
201
```python
202
# Call function
203
result = cursor.callfunc("get_employee_salary", cx_Oracle.NUMBER, [1001])
204
print(f"Salary: {result}")
205
206
# Call procedure with OUT parameters
207
params = [1001, None, None] # emp_id, OUT first_name, OUT last_name
208
cursor.callproc("get_employee_info", params)
209
print(f"Employee: {params[1]} {params[2]}")
210
211
# Call procedure with IN/OUT parameters
212
balance = [1000] # Initial balance
213
cursor.callproc("update_account_balance", [12345, 500, balance])
214
print(f"New balance: {balance[0]}")
215
```
216
217
### Variable Binding and Type Handling
218
219
Manage bind variables and data type conversion.
220
221
```python { .api }
222
class Cursor:
223
def var(self, typ, size=None, arraysize=None, inconverter=None,
224
outconverter=None) -> Var:
225
"""
226
Create bind variable of specified type.
227
228
Parameters:
229
- typ: Variable type (cx_Oracle type)
230
- size (int): Variable size
231
- arraysize (int): Array size for array variables
232
- inconverter: Input conversion function
233
- outconverter: Output conversion function
234
235
Returns:
236
Variable object
237
"""
238
239
def arrayvar(self, typ, value, size=None, **kwargs) -> Var:
240
"""
241
Create array bind variable.
242
243
Parameters:
244
- typ: Variable type (cx_Oracle type)
245
- value: Initial array value
246
- size (int): Variable size
247
- kwargs: Additional variable options
248
249
Returns:
250
Array variable object
251
"""
252
253
def setinputsizes(self, **kwargs) -> None:
254
"""
255
Set input variable sizes for subsequent execute operations.
256
257
Parameters:
258
- kwargs: Named parameter sizes (name=type or name=(type, size))
259
"""
260
261
def setoutputsize(self, size: int, column=None) -> None:
262
"""
263
Set output size for large columns.
264
265
Parameters:
266
- size (int): Output buffer size
267
- column: Column index or name (None for all columns)
268
"""
269
270
def bindnames(self) -> list:
271
"""
272
Get names of bind variables in prepared statement.
273
274
Returns:
275
List of bind variable names
276
"""
277
278
def getbatcherrors(self) -> list:
279
"""
280
Get batch execution errors from executemany operation.
281
282
Returns:
283
List of error objects for failed rows
284
"""
285
286
def getarraydmlrowcounts(self) -> list:
287
"""
288
Get row counts for each row in array DML operation.
289
290
Returns:
291
List of row counts for array DML
292
"""
293
294
def getimplicitresults(self) -> list:
295
"""
296
Get implicit result sets from PL/SQL procedure.
297
298
Returns:
299
List of cursor objects for each result set
300
"""
301
```
302
303
Usage examples:
304
305
```python
306
# Create bind variables
307
var_id = cursor.var(cx_Oracle.NUMBER)
308
var_name = cursor.var(cx_Oracle.STRING, 100)
309
310
# Bind variables to statement
311
cursor.execute("SELECT first_name INTO :name FROM employees WHERE employee_id = :id",
312
{"id": var_id, "name": var_name})
313
314
# Set input sizes for better performance
315
cursor.setinputsizes(name=100, salary=cx_Oracle.NUMBER)
316
cursor.execute("INSERT INTO employees (name, salary) VALUES (:name, :salary)",
317
{"name": "John Doe", "salary": 50000})
318
319
# Handle batch errors
320
try:
321
cursor.executemany("INSERT INTO employees VALUES (:1, :2)", data)
322
except cx_Oracle.Error:
323
errors = cursor.getbatcherrors()
324
for error in errors:
325
print(f"Row {error.offset}: {error.message}")
326
```
327
328
### Cursor Properties
329
330
Access cursor metadata and configuration.
331
332
```python { .api }
333
class Cursor:
334
@property
335
def connection(self) -> Connection:
336
"""Associated connection object"""
337
338
@property
339
def description(self) -> list:
340
"""Column descriptions for last query (list of 7-tuples)"""
341
342
@property
343
def rowcount(self) -> int:
344
"""Number of rows affected by last operation"""
345
346
@property
347
def arraysize(self) -> int:
348
"""Number of rows to fetch at once (default 100)"""
349
350
@property
351
def bindarraysize(self) -> int:
352
"""Array size for bind operations"""
353
354
@property
355
def bindvars(self) -> dict:
356
"""Dictionary of bind variables"""
357
358
@property
359
def fetchvars(self) -> list:
360
"""List of fetch variables"""
361
362
@property
363
def inputtypehandler(self):
364
"""Input type handler function"""
365
366
@property
367
def outputtypehandler(self):
368
"""Output type handler function"""
369
370
@property
371
def rowfactory(self):
372
"""Row factory function for result formatting"""
373
374
@property
375
def scrollable(self) -> bool:
376
"""Whether cursor is scrollable"""
377
378
@property
379
def prefetchrows(self) -> int:
380
"""Number of rows to prefetch"""
381
382
@property
383
def statement(self) -> str:
384
"""Last executed SQL statement"""
385
386
@property
387
def statementtype(self) -> int:
388
"""Type of last executed statement"""
389
```
390
391
### Advanced Cursor Features
392
393
Advanced cursor operations including scrolling and decimal handling.
394
395
```python { .api }
396
class Cursor:
397
def scroll(self, value: int, mode='relative') -> None:
398
"""
399
Scroll cursor position (requires scrollable cursor).
400
401
Parameters:
402
- value (int): Number of rows to scroll
403
- mode (str): 'relative' or 'absolute'
404
"""
405
406
def setdecimal(self, precision: int, scale: int) -> None:
407
"""
408
Set decimal precision and scale for NUMBER columns.
409
410
Parameters:
411
- precision (int): Total number of digits
412
- scale (int): Number of digits after decimal point
413
"""
414
```
415
416
Usage examples:
417
418
```python
419
# Create scrollable cursor
420
cursor = connection.cursor(scrollable=True)
421
cursor.execute("SELECT * FROM employees ORDER BY employee_id")
422
423
# Scroll through results
424
cursor.scroll(10) # Skip 10 rows
425
row = cursor.fetchone()
426
427
cursor.scroll(-5) # Go back 5 rows
428
row = cursor.fetchone()
429
430
cursor.scroll(0, 'absolute') # Go to beginning
431
row = cursor.fetchone()
432
433
# Custom row factory
434
cursor.rowfactory = lambda *args: dict(zip([d[0] for d in cursor.description], args))
435
cursor.execute("SELECT employee_id, first_name FROM employees")
436
for row in cursor:
437
print(f"ID: {row['EMPLOYEE_ID']}, Name: {row['FIRST_NAME']}")
438
439
# Set decimal handling
440
cursor.setdecimal(10, 2) # 10 digits total, 2 after decimal
441
cursor.execute("SELECT salary FROM employees")
442
```
443
444
## Context Management
445
446
Cursors support Python's context manager protocol for automatic cleanup:
447
448
```python
449
with connection.cursor() as cursor:
450
cursor.execute("SELECT * FROM employees")
451
for row in cursor:
452
print(row)
453
# Cursor automatically closed when exiting context
454
```
455
456
## Statement Types
457
458
Statement type constants for identifying SQL statement types:
459
460
```python { .api }
461
# Available via cursor.statementtype after execution
462
STMT_TYPE_UNKNOWN: int
463
STMT_TYPE_SELECT: int
464
STMT_TYPE_UPDATE: int
465
STMT_TYPE_DELETE: int
466
STMT_TYPE_INSERT: int
467
STMT_TYPE_CREATE: int
468
STMT_TYPE_DROP: int
469
STMT_TYPE_ALTER: int
470
STMT_TYPE_BEGIN: int
471
STMT_TYPE_DECLARE: int
472
```
473
474
## Performance Optimization
475
476
Tips for optimal cursor performance:
477
478
```python
479
# Set appropriate array size for bulk operations
480
cursor.arraysize = 1000
481
482
# Use executemany for bulk inserts/updates
483
cursor.executemany("INSERT INTO table VALUES (:1, :2)", data)
484
485
# Prepare statements for repeated execution
486
cursor.prepare("SELECT * FROM employees WHERE dept_id = :dept_id")
487
for dept_id in [10, 20, 30]:
488
cursor.execute(None, {"dept_id": dept_id})
489
490
# Use setinputsizes for better performance with large data
491
cursor.setinputsizes(data=cx_Oracle.CLOB)
492
cursor.execute("INSERT INTO documents (id, data) VALUES (:id, :data)",
493
{"id": 1, "data": large_text})
494
```