0
# Cursor Operations
1
2
SQL execution interface providing methods for running queries, managing transactions, and retrieving results with support for prepared statements and parameterized queries.
3
4
## Capabilities
5
6
### Cursor Creation and Management
7
8
Cursors are created from connection objects and provide the primary interface for executing SQL statements and retrieving results.
9
10
```python { .api }
11
class Cursor:
12
def __init__(self, connection):
13
"""
14
Create a cursor object. Do not instantiate directly - use Connection.cursor().
15
16
Parameters:
17
- connection: Database connection object
18
"""
19
20
def close(self):
21
"""
22
Close the cursor and free associated resources.
23
"""
24
25
@property
26
def rowcount(self):
27
"""
28
Number of rows affected by the last execute() operation.
29
30
Returns:
31
int: Row count, -1 if no execute() performed
32
"""
33
34
@property
35
def description(self):
36
"""
37
Sequence describing columns in the result set.
38
39
Returns:
40
tuple: Column descriptions with (name, type_code, display_size,
41
internal_size, precision, scale, null_ok)
42
"""
43
44
@property
45
def arraysize(self):
46
"""
47
Number of rows to fetch at a time with fetchmany().
48
49
Returns:
50
int: Array size (default: 1)
51
"""
52
53
@arraysize.setter
54
def arraysize(self, value):
55
"""Set array size for fetchmany()."""
56
57
@property
58
def lastrowid(self):
59
"""
60
The row ID of the last INSERT operation.
61
62
Returns:
63
int: ID of the last inserted row, or None if no INSERT performed
64
"""
65
```
66
67
### SQL Execution
68
69
Execute SQL statements with optional parameter binding for safe query execution.
70
71
```python { .api }
72
def execute(self, query, args=None):
73
"""
74
Execute a SQL statement.
75
76
Parameters:
77
- query (str): SQL statement with optional %s placeholders
78
- args (tuple/list/dict): Parameters to bind to placeholders
79
80
Returns:
81
int: Number of affected rows
82
83
Raises:
84
ProgrammingError: SQL syntax error
85
OperationalError: Database operation error
86
IntegrityError: Constraint violation
87
DataError: Invalid data
88
"""
89
90
def executemany(self, query, args_list):
91
"""
92
Execute a SQL statement multiple times with different parameter sets.
93
94
Parameters:
95
- query (str): SQL statement with %s placeholders
96
- args_list (list/tuple): Sequence of parameter tuples/lists
97
98
Returns:
99
int: Number of affected rows from all executions
100
101
Raises:
102
ProgrammingError: SQL syntax error
103
OperationalError: Database operation error
104
"""
105
106
def callproc(self, procname, args=()):
107
"""
108
Call a stored procedure.
109
110
Parameters:
111
- procname (str): Stored procedure name
112
- args (tuple): Procedure parameters
113
114
Returns:
115
tuple: Modified parameter list
116
"""
117
118
def nextset(self):
119
"""
120
Move to the next result set when multiple result sets are available.
121
122
Returns:
123
bool: True if another result set is available, False otherwise
124
"""
125
```
126
127
### Result Retrieval
128
129
Fetch query results in various formats and quantities.
130
131
```python { .api }
132
def fetchone(self):
133
"""
134
Fetch the next row from the result set.
135
136
Returns:
137
tuple: Next row data, or None if no more rows
138
"""
139
140
def fetchmany(self, size=None):
141
"""
142
Fetch multiple rows from the result set.
143
144
Parameters:
145
- size (int): Number of rows to fetch (default: arraysize)
146
147
Returns:
148
list: List of row tuples, empty list if no more rows
149
"""
150
151
def fetchall(self):
152
"""
153
Fetch all remaining rows from the result set.
154
155
Returns:
156
list: List of all remaining row tuples
157
"""
158
159
def scroll(self, value, mode='relative'):
160
"""
161
Scroll the cursor position in the result set.
162
163
Parameters:
164
- value (int): Number of rows to move
165
- mode (str): 'relative' or 'absolute' positioning mode
166
"""
167
168
def __iter__(self):
169
"""
170
Iterator interface for row-by-row processing.
171
172
Returns:
173
Iterator: Cursor iterator yielding rows until None
174
"""
175
```
176
177
### Context Manager Support
178
179
Cursors support context manager protocol for automatic cleanup:
180
181
```python { .api }
182
def __enter__(self):
183
"""Enter context manager."""
184
185
def __exit__(self, exc_type, exc_val, exc_tb):
186
"""Exit context manager and close cursor."""
187
```
188
189
### Dictionary Cursor
190
191
Dictionary cursor that returns results as dictionaries with column names as keys instead of tuples.
192
193
```python { .api }
194
class DictCursor(Cursor):
195
"""
196
Cursor that returns rows as dictionaries instead of tuples.
197
198
Each row is returned as a dictionary mapping column names to values,
199
making it easier to access specific columns by name.
200
"""
201
202
def execute(self, query, args=None):
203
"""Execute query and prepare field mapping for dictionary results."""
204
205
def fetchone(self):
206
"""
207
Fetch the next row as a dictionary.
208
209
Returns:
210
dict: Row data as {column_name: value} mapping, or None if no more rows
211
"""
212
213
def fetchmany(self, size=None):
214
"""
215
Fetch multiple rows as dictionaries.
216
217
Parameters:
218
- size (int): Number of rows to fetch (default: arraysize)
219
220
Returns:
221
tuple: Tuple of dictionaries representing rows
222
"""
223
224
def fetchall(self):
225
"""
226
Fetch all remaining rows as dictionaries.
227
228
Returns:
229
tuple: Tuple of dictionaries representing all remaining rows
230
"""
231
```
232
233
## Usage Examples
234
235
### Basic Query Execution
236
237
```python
238
import cymysql
239
240
conn = cymysql.connect(host='localhost', user='root', db='test')
241
cursor = conn.cursor()
242
243
# Execute query
244
cursor.execute("SELECT id, name FROM users WHERE age > %s", (18,))
245
246
# Fetch results
247
print(f"Query returned {cursor.rowcount} rows")
248
for row in cursor.fetchall():
249
print(f"ID: {row[0]}, Name: {row[1]}")
250
251
cursor.close()
252
conn.close()
253
```
254
255
### Parameterized Queries
256
257
```python
258
# Safe parameter binding
259
cursor.execute(
260
"INSERT INTO users (name, email, age) VALUES (%s, %s, %s)",
261
('John Doe', 'john@example.com', 25)
262
)
263
264
# Dictionary parameter binding
265
cursor.execute(
266
"SELECT * FROM users WHERE name = %(name)s AND age > %(min_age)s",
267
{'name': 'John', 'min_age': 18}
268
)
269
```
270
271
### Batch Operations
272
273
```python
274
# Insert multiple records efficiently
275
users_data = [
276
('Alice', 'alice@example.com', 30),
277
('Bob', 'bob@example.com', 25),
278
('Charlie', 'charlie@example.com', 35)
279
]
280
281
cursor.executemany(
282
"INSERT INTO users (name, email, age) VALUES (%s, %s, %s)",
283
users_data
284
)
285
print(f"Inserted {cursor.rowcount} records")
286
```
287
288
### Result Set Navigation
289
290
```python
291
cursor.execute("SELECT * FROM large_table")
292
293
# Process results in chunks
294
while True:
295
rows = cursor.fetchmany(100) # Fetch 100 rows at a time
296
if not rows:
297
break
298
299
for row in rows:
300
process_row(row)
301
```
302
303
### Iterator Interface
304
305
```python
306
cursor.execute("SELECT * FROM users")
307
308
# Use cursor as iterator
309
for row in cursor:
310
print(f"Processing user: {row[1]}")
311
```
312
313
### Context Manager Usage
314
315
```python
316
conn = cymysql.connect(host='localhost', user='root', db='test')
317
318
with conn.cursor() as cursor:
319
cursor.execute("SELECT COUNT(*) FROM users")
320
count = cursor.fetchone()[0]
321
print(f"Total users: {count}")
322
# Cursor automatically closed
323
```
324
325
### Transaction Management
326
327
```python
328
conn = cymysql.connect(host='localhost', user='root', db='test')
329
cursor = conn.cursor()
330
331
try:
332
# Start transaction
333
cursor.execute("START TRANSACTION")
334
335
# Execute multiple statements
336
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = %s", (1,))
337
cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = %s", (2,))
338
339
# Commit if all successful
340
conn.commit()
341
print("Transaction committed")
342
343
except Exception as e:
344
# Rollback on error
345
conn.rollback()
346
print(f"Transaction rolled back: {e}")
347
finally:
348
cursor.close()
349
conn.close()
350
```
351
352
### Stored Procedure Calls
353
354
```python
355
cursor = conn.cursor()
356
357
# Call stored procedure
358
result_args = cursor.callproc('GetUserStats', (user_id, 2023))
359
print(f"Procedure returned: {result_args}")
360
361
# Fetch procedure results
362
for row in cursor.fetchall():
363
print(row)
364
```
365
366
### Column Information
367
368
```python
369
cursor.execute("SELECT id, name, email FROM users LIMIT 1")
370
371
# Access column metadata
372
print("Column information:")
373
for desc in cursor.description:
374
print(f" {desc[0]}: {desc[1]} (size: {desc[3]})")
375
```
376
377
### Dictionary Cursor Usage
378
379
```python
380
import cymysql
381
from cymysql.cursors import DictCursor
382
383
conn = cymysql.connect(host='localhost', user='root', db='test')
384
385
# Create dictionary cursor
386
cursor = conn.cursor(DictCursor)
387
388
# Execute query - results returned as dictionaries
389
cursor.execute("SELECT id, name, email FROM users WHERE active = %s", (True,))
390
391
# Fetch results as dictionaries
392
for row in cursor.fetchall():
393
print(f"User {row['name']} has email {row['email']} (ID: {row['id']})")
394
395
# Access specific columns by name
396
cursor.execute("SELECT COUNT(*) as user_count FROM users")
397
result = cursor.fetchone()
398
print(f"Total users: {result['user_count']}")
399
400
cursor.close()
401
conn.close()
402
```
403
404
### Lastrowid Example
405
406
```python
407
import cymysql
408
409
conn = cymysql.connect(host='localhost', user='root', db='test')
410
cursor = conn.cursor()
411
412
# Insert new record and get the ID
413
cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)",
414
('New User', 'new@example.com'))
415
416
# Get the ID of the inserted row
417
new_user_id = cursor.lastrowid
418
print(f"Inserted user with ID: {new_user_id}")
419
420
conn.commit()
421
cursor.close()
422
conn.close()
423
```
424
425
## Performance Considerations
426
427
- Use parameterized queries to avoid SQL injection and improve performance
428
- Fetch results in batches for large result sets using `fetchmany()`
429
- Close cursors explicitly or use context managers to free resources
430
- Use `executemany()` for bulk operations instead of multiple `execute()` calls
431
- Consider cursor.arraysize for optimal `fetchmany()` performance