0
# SQL Execution
1
2
SQL and PL/SQL execution through cursors with support for bind variables, batch operations, stored procedures, and functions. Includes comprehensive result fetching and data type handling with both synchronous and asynchronous operation modes.
3
4
## Capabilities
5
6
### Cursor Class
7
8
Execute SQL statements and fetch results with full control over execution parameters and result handling.
9
10
```python { .api }
11
class Cursor:
12
"""Cursor for executing SQL statements and fetching results."""
13
14
# Properties
15
arraysize: int
16
description: list
17
rowcount: int
18
statement: str
19
bindnames: list
20
fetchvars: list
21
22
def execute(self, statement, parameters=None) -> None:
23
"""
24
Execute a SQL statement with optional bind parameters.
25
26
Parameters:
27
- statement (str): SQL statement to execute
28
- parameters (dict|list|tuple): Bind parameters
29
"""
30
31
def executemany(self, statement, parameters) -> None:
32
"""
33
Execute a SQL statement with multiple parameter sets.
34
35
Parameters:
36
- statement (str): SQL statement to execute
37
- parameters (list): List of parameter sets
38
"""
39
40
def fetchone(self) -> tuple:
41
"""
42
Fetch the next row from the result set.
43
44
Returns:
45
tuple: Single row or None if no more rows
46
"""
47
48
def fetchmany(self, size=None) -> list:
49
"""
50
Fetch multiple rows from the result set.
51
52
Parameters:
53
- size (int): Number of rows to fetch (default: arraysize)
54
55
Returns:
56
list: List of tuples representing rows
57
"""
58
59
def fetchall(self) -> list:
60
"""
61
Fetch all remaining rows from the result set.
62
63
Returns:
64
list: List of tuples representing all rows
65
"""
66
67
def callfunc(self, name, return_type, parameters=None):
68
"""
69
Call a stored function and return its result.
70
71
Parameters:
72
- name (str): Function name
73
- return_type: Expected return type
74
- parameters (list): Function parameters
75
76
Returns:
77
Function return value
78
"""
79
80
def callproc(self, name, parameters=None) -> list:
81
"""
82
Call a stored procedure.
83
84
Parameters:
85
- name (str): Procedure name
86
- parameters (list): Procedure parameters
87
88
Returns:
89
list: Modified parameter values
90
"""
91
92
def var(self, data_type, size=None, arraysize=None, inconverter=None, outconverter=None, encoding=None, nencoding=None, bypass_decode=False) -> Var:
93
"""
94
Create a bind variable.
95
96
Parameters:
97
- data_type: Variable data type
98
- size (int): Maximum size
99
- arraysize (int): Array size for array variables
100
- inconverter: Input converter function
101
- outconverter: Output converter function
102
103
Returns:
104
Var: Variable object
105
"""
106
107
def setinputsizes(self, **parameters) -> None:
108
"""
109
Set input parameter sizes for better performance.
110
111
Parameters:
112
- **parameters: Parameter names and their types/sizes
113
"""
114
115
def close(self) -> None:
116
"""Close the cursor and free resources."""
117
118
def parse(self, statement) -> None:
119
"""
120
Parse a SQL statement without executing it.
121
122
Parameters:
123
- statement (str): SQL statement to parse
124
"""
125
126
def scroll(self, value, mode="relative") -> None:
127
"""
128
Scroll the cursor position (scrollable cursors only).
129
130
Parameters:
131
- value (int): Number of rows to scroll
132
- mode (str): "relative" or "absolute"
133
"""
134
135
def __iter__(self):
136
"""Iterator interface for fetching rows."""
137
138
def __next__(self):
139
"""Get next row in iteration."""
140
```
141
142
### Async Cursor Class
143
144
Asynchronous version of Cursor with async/await support for all operations.
145
146
```python { .api }
147
class AsyncCursor:
148
"""Asynchronous cursor for executing SQL statements."""
149
150
# Properties (same as Cursor)
151
arraysize: int
152
description: list
153
rowcount: int
154
statement: str
155
bindnames: list
156
fetchvars: list
157
158
async def execute(self, statement, parameters=None) -> None:
159
"""
160
Execute a SQL statement with optional bind parameters.
161
162
Parameters:
163
- statement (str): SQL statement to execute
164
- parameters (dict|list|tuple): Bind parameters
165
"""
166
167
async def executemany(self, statement, parameters) -> None:
168
"""
169
Execute a SQL statement with multiple parameter sets.
170
171
Parameters:
172
- statement (str): SQL statement to execute
173
- parameters (list): List of parameter sets
174
"""
175
176
async def fetchone(self) -> tuple:
177
"""
178
Fetch the next row from the result set.
179
180
Returns:
181
tuple: Single row or None if no more rows
182
"""
183
184
async def fetchmany(self, size=None) -> list:
185
"""
186
Fetch multiple rows from the result set.
187
188
Parameters:
189
- size (int): Number of rows to fetch (default: arraysize)
190
191
Returns:
192
list: List of tuples representing rows
193
"""
194
195
async def fetchall(self) -> list:
196
"""
197
Fetch all remaining rows from the result set.
198
199
Returns:
200
list: List of tuples representing all rows
201
"""
202
203
async def callfunc(self, name, return_type, parameters=None):
204
"""
205
Call a stored function and return its result.
206
207
Parameters:
208
- name (str): Function name
209
- return_type: Expected return type
210
- parameters (list): Function parameters
211
212
Returns:
213
Function return value
214
"""
215
216
async def callproc(self, name, parameters=None) -> list:
217
"""
218
Call a stored procedure.
219
220
Parameters:
221
- name (str): Procedure name
222
- parameters (list): Procedure parameters
223
224
Returns:
225
list: Modified parameter values
226
"""
227
228
async def close(self) -> None:
229
"""Close the cursor and free resources."""
230
```
231
232
### Variable Management
233
234
Create and manage bind variables for optimal performance and data type handling.
235
236
```python { .api }
237
class Var:
238
"""Bind variable for SQL parameters."""
239
240
def getvalue(self, pos=0):
241
"""
242
Get the variable value.
243
244
Parameters:
245
- pos (int): Position for array variables
246
247
Returns:
248
Variable value
249
"""
250
251
def setvalue(self, pos, value) -> None:
252
"""
253
Set the variable value.
254
255
Parameters:
256
- pos (int): Position for array variables
257
- value: Value to set
258
"""
259
```
260
261
### Result Metadata
262
263
Access detailed information about query result columns.
264
265
```python { .api }
266
class FetchInfo:
267
"""Column metadata for fetch operations."""
268
269
name: str
270
type_code: int
271
type: type
272
display_size: int
273
internal_size: int
274
precision: int
275
scale: int
276
null_ok: bool
277
annotations: dict
278
is_json: bool
279
vector_dimensions: int
280
vector_format: int
281
```
282
283
## Usage Examples
284
285
### Basic SQL Execution
286
287
```python
288
import oracledb
289
290
# Connect to database
291
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
292
293
with connection.cursor() as cursor:
294
# Simple query
295
cursor.execute("SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10")
296
297
# Fetch results
298
for row in cursor:
299
print(f"Employee {row[0]}: {row[1]} {row[2]}")
300
301
connection.close()
302
```
303
304
### Using Bind Variables
305
306
```python
307
import oracledb
308
309
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
310
311
with connection.cursor() as cursor:
312
# Named bind variables
313
cursor.execute("""
314
SELECT employee_id, first_name, salary
315
FROM employees
316
WHERE department_id = :dept_id
317
AND salary > :min_salary
318
""", dept_id=10, min_salary=5000)
319
320
results = cursor.fetchall()
321
for row in results:
322
print(f"Employee {row[0]}: {row[1]}, Salary: ${row[2]}")
323
324
# Positional bind variables
325
cursor.execute("""
326
SELECT COUNT(*) FROM employees WHERE hire_date >= :1 AND hire_date < :2
327
""", ['2020-01-01', '2021-01-01'])
328
329
count = cursor.fetchone()[0]
330
print(f"Employees hired in 2020: {count}")
331
332
connection.close()
333
```
334
335
### Batch Operations
336
337
```python
338
import oracledb
339
340
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
341
342
with connection.cursor() as cursor:
343
# Insert multiple rows
344
data = [
345
(1001, 'John', 'Doe', 50000),
346
(1002, 'Jane', 'Smith', 55000),
347
(1003, 'Bob', 'Johnson', 60000)
348
]
349
350
cursor.executemany("""
351
INSERT INTO temp_employees (employee_id, first_name, last_name, salary)
352
VALUES (:1, :2, :3, :4)
353
""", data)
354
355
connection.commit()
356
print(f"Inserted {cursor.rowcount} rows")
357
358
connection.close()
359
```
360
361
### Calling Stored Procedures and Functions
362
363
```python
364
import oracledb
365
366
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
367
368
with connection.cursor() as cursor:
369
# Call a function
370
result = cursor.callfunc("calculate_bonus", oracledb.NUMBER, [12000, 0.15])
371
print(f"Calculated bonus: {result}")
372
373
# Call a procedure with IN/OUT parameters
374
emp_id = cursor.var(oracledb.NUMBER)
375
emp_id.setvalue(0, 100)
376
377
salary = cursor.var(oracledb.NUMBER)
378
379
cursor.callproc("get_employee_salary", [emp_id, salary])
380
print(f"Employee {emp_id.getvalue()} salary: {salary.getvalue()}")
381
382
connection.close()
383
```
384
385
### Working with Large Result Sets
386
387
```python
388
import oracledb
389
390
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
391
392
with connection.cursor() as cursor:
393
# Set fetch array size for better performance
394
cursor.arraysize = 1000
395
396
cursor.execute("SELECT * FROM large_table")
397
398
# Process results in chunks
399
while True:
400
rows = cursor.fetchmany()
401
if not rows:
402
break
403
404
# Process chunk of rows
405
print(f"Processing {len(rows)} rows...")
406
for row in rows:
407
# Process each row
408
pass
409
410
connection.close()
411
```
412
413
### Async SQL Execution
414
415
```python
416
import asyncio
417
import oracledb
418
419
async def main():
420
connection = await oracledb.connect_async(user="hr", password="password", dsn="localhost/xepdb1")
421
422
async with connection.cursor() as cursor:
423
# Async query execution
424
await cursor.execute("SELECT COUNT(*) FROM employees")
425
result = await cursor.fetchone()
426
print(f"Total employees: {result[0]}")
427
428
# Async batch operations
429
data = [(f"Name{i}", i * 1000) for i in range(1, 101)]
430
await cursor.executemany("INSERT INTO temp_table (name, value) VALUES (:1, :2)", data)
431
await connection.commit()
432
433
await connection.close()
434
435
asyncio.run(main())
436
```
437
438
### Advanced Variable Usage
439
440
```python
441
import oracledb
442
443
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
444
445
with connection.cursor() as cursor:
446
# Create variables with specific types and sizes
447
cursor.setinputsizes(
448
name=oracledb.STRING,
449
salary=oracledb.NUMBER,
450
hire_date=oracledb.DATETIME
451
)
452
453
# Use variables for better performance in loops
454
name_var = cursor.var(oracledb.STRING)
455
salary_var = cursor.var(oracledb.NUMBER)
456
457
for i in range(100):
458
name_var.setvalue(0, f"Employee_{i}")
459
salary_var.setvalue(0, 50000 + i * 100)
460
461
cursor.execute("""
462
INSERT INTO employees_temp (name, salary)
463
VALUES (:name, :salary)
464
""", name=name_var, salary=salary_var)
465
466
connection.commit()
467
468
connection.close()
469
```