0
# Cursor Operations
1
2
Comprehensive cursor functionality for executing SQL queries and fetching results, with multiple cursor types optimized for different use cases including tuple/dictionary rows and stored/streaming result handling.
3
4
## Capabilities
5
6
### Base Cursor Classes
7
8
Core cursor functionality providing the foundation for all cursor types.
9
10
```python { .api }
11
class BaseCursor:
12
"""Base cursor class with common functionality for all cursor types."""
13
14
def __init__(self, connection):
15
"""
16
Initialize cursor with database connection.
17
18
Parameters:
19
- connection: Database connection object
20
"""
21
22
def close(self):
23
"""Close the cursor and free associated resources."""
24
25
def execute(self, query, args=None):
26
"""
27
Execute a SQL statement.
28
29
Parameters:
30
- query (str): SQL query string with optional parameter placeholders
31
- args (tuple/dict, optional): Parameters for query placeholders
32
33
Returns:
34
int: Number of affected rows
35
"""
36
37
def executemany(self, query, args):
38
"""
39
Execute a SQL statement multiple times with different parameters.
40
41
Parameters:
42
- query (str): SQL query string with parameter placeholders
43
- args (sequence): Sequence of parameter tuples/dicts
44
45
Returns:
46
int: Number of affected rows for last execution
47
"""
48
49
def callproc(self, procname, args=()):
50
"""
51
Call a stored procedure.
52
53
Parameters:
54
- procname (str): Name of stored procedure
55
- args (tuple): Parameters for stored procedure
56
57
Returns:
58
tuple: Modified parameter values
59
"""
60
61
def fetchone(self):
62
"""
63
Fetch the next row from query results.
64
65
Returns:
66
tuple/dict/None: Next row or None if no more rows
67
"""
68
69
def fetchmany(self, size=None):
70
"""
71
Fetch multiple rows from query results.
72
73
Parameters:
74
- size (int, optional): Number of rows to fetch (default: arraysize)
75
76
Returns:
77
list: List of rows (tuples or dicts)
78
"""
79
80
def fetchall(self):
81
"""
82
Fetch all remaining rows from query results.
83
84
Returns:
85
list: List of all remaining rows
86
"""
87
88
def nextset(self):
89
"""
90
Skip to next result set (for multiple result sets).
91
92
Returns:
93
bool: True if more result sets available, False otherwise
94
"""
95
96
def setinputsizes(self, *args):
97
"""
98
Set input parameter sizes (no-op for MySQL-python).
99
100
Parameters:
101
- args: Parameter size specifications (ignored)
102
"""
103
104
def setoutputsizes(self, *args):
105
"""
106
Set output column sizes (no-op for MySQL-python).
107
108
Parameters:
109
- args: Column size specifications (ignored)
110
"""
111
112
def scroll(self, value, mode='relative'):
113
"""
114
Scroll the cursor in the result set to a new position.
115
116
Only available for stored result cursors (Cursor, DictCursor).
117
Not supported by streaming cursors (SSCursor, SSDictCursor).
118
119
Parameters:
120
- value (int): Position offset or absolute position
121
- mode (str): 'relative' (default) or 'absolute'
122
"""
123
124
def __iter__(self):
125
"""
126
Make cursor iterable for row-by-row processing.
127
128
Returns:
129
iterator: Cursor iterator
130
"""
131
132
def __del__(self):
133
"""Destructor for automatic cursor cleanup."""
134
135
# Cursor attributes
136
arraysize: int # Number of rows fetchmany() returns by default
137
description: tuple # Column descriptions for current result set
138
lastrowid: int # Row ID of last inserted row
139
rowcount: int # Number of rows affected/returned by last operation
140
rownumber: int # Current row position in result set
141
connection: object # Associated connection object
142
messages: list # List of messages/warnings from last operation
143
description_flags: tuple # Column flags for each field in result set
144
_executed: str # Last executed SQL statement
145
errorhandler: function # Custom error handler function
146
_warnings: int # Number of warnings from last operation
147
_info: str # Additional info from last operation
148
```
149
150
### Cursor Mixins
151
152
Mixin classes that provide specialized behaviors for different cursor types.
153
154
```python { .api }
155
class CursorStoreResultMixIn:
156
"""Mixin for cursors that store complete result sets in memory."""
157
158
class CursorUseResultMixIn:
159
"""Mixin for cursors that stream results from server (memory efficient)."""
160
161
class CursorTupleRowsMixIn:
162
"""Mixin for cursors that return rows as tuples."""
163
164
class CursorDictRowsMixIn:
165
"""Mixin for cursors that return rows as dictionaries with column names as keys."""
166
167
class CursorOldDictRowsMixIn:
168
"""Mixin for cursors that return rows as old-style dictionaries."""
169
```
170
171
### Standard Cursor Types
172
173
Pre-configured cursor classes combining mixins for common use cases.
174
175
```python { .api }
176
class Cursor(CursorStoreResultMixIn, CursorTupleRowsMixIn, BaseCursor):
177
"""
178
Standard cursor returning rows as tuples with results stored in memory.
179
Default cursor type providing best compatibility and performance for most use cases.
180
"""
181
182
class DictCursor(CursorStoreResultMixIn, CursorDictRowsMixIn, BaseCursor):
183
"""
184
Dictionary cursor returning rows as dictionaries with column names as keys.
185
Results stored in memory. Useful for applications that need named column access.
186
"""
187
188
class SSCursor(CursorUseResultMixIn, CursorTupleRowsMixIn, BaseCursor):
189
"""
190
Server-side cursor returning rows as tuples with streaming results.
191
Memory efficient for large result sets but requires careful handling.
192
"""
193
194
class SSDictCursor(CursorUseResultMixIn, CursorDictRowsMixIn, BaseCursor):
195
"""
196
Server-side dictionary cursor with streaming results and named column access.
197
Combines memory efficiency with convenient dictionary-style row access.
198
"""
199
```
200
201
## Usage Examples
202
203
### Basic Query Execution
204
205
```python
206
import MySQLdb
207
208
db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
209
cursor = db.cursor()
210
211
# Execute simple query
212
cursor.execute("SELECT id, name, email FROM users WHERE active = %s", (1,))
213
214
# Fetch results
215
results = cursor.fetchall()
216
for row in results:
217
user_id, name, email = row
218
print(f"User {user_id}: {name} ({email})")
219
220
cursor.close()
221
db.close()
222
```
223
224
### Dictionary Cursor Usage
225
226
```python
227
import MySQLdb
228
229
db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
230
231
# Use dictionary cursor for named column access
232
cursor = db.cursor(MySQLdb.cursors.DictCursor)
233
cursor.execute("SELECT id, name, email FROM users WHERE active = %s", (1,))
234
235
results = cursor.fetchall()
236
for row in results:
237
print(f"User {row['id']}: {row['name']} ({row['email']})")
238
239
cursor.close()
240
db.close()
241
```
242
243
### Server-Side Cursor for Large Results
244
245
```python
246
import MySQLdb
247
248
db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
249
250
# Use server-side cursor for memory efficiency with large result sets
251
cursor = db.cursor(MySQLdb.cursors.SSCursor)
252
cursor.execute("SELECT * FROM large_table")
253
254
# Process results one row at a time
255
for row in cursor:
256
process_row(row) # Process without loading all rows into memory
257
258
cursor.close()
259
db.close()
260
```
261
262
### Batch Operations with executemany
263
264
```python
265
import MySQLdb
266
267
db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
268
cursor = db.cursor()
269
270
# Insert multiple rows efficiently
271
users_data = [
272
("John Doe", "john@example.com"),
273
("Jane Smith", "jane@example.com"),
274
("Bob Wilson", "bob@example.com")
275
]
276
277
cursor.executemany(
278
"INSERT INTO users (name, email) VALUES (%s, %s)",
279
users_data
280
)
281
282
db.commit()
283
print(f"Inserted {cursor.rowcount} users")
284
285
cursor.close()
286
db.close()
287
```
288
289
### Stored Procedure Calls
290
291
```python
292
import MySQLdb
293
294
db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
295
cursor = db.cursor()
296
297
# Call stored procedure
298
args = (100, 0) # input parameter, output parameter
299
result = cursor.callproc("calculate_tax", args)
300
print(f"Tax calculation result: {result}")
301
302
# Fetch any result sets returned by procedure
303
if cursor.description:
304
results = cursor.fetchall()
305
for row in results:
306
print(row)
307
308
cursor.close()
309
db.close()
310
```
311
312
### Iterator Pattern for Row Processing
313
314
```python
315
import MySQLdb
316
317
db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
318
cursor = db.cursor()
319
320
cursor.execute("SELECT id, name FROM users WHERE department = %s", ("Engineering",))
321
322
# Use iterator pattern for memory-efficient row processing
323
for row in cursor:
324
user_id, name = row
325
print(f"Processing user: {name}")
326
# Process each row individually
327
328
cursor.close()
329
db.close()
330
```
331
332
### Context Manager with Cursors
333
334
```python
335
import MySQLdb
336
337
with MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test") as db:
338
with db.cursor(MySQLdb.cursors.DictCursor) as cursor:
339
cursor.execute("SELECT COUNT(*) as total FROM users")
340
result = cursor.fetchone()
341
print(f"Total users: {result['total']}")
342
343
cursor.execute("SELECT name FROM users ORDER BY created_at DESC LIMIT 5")
344
recent_users = cursor.fetchall()
345
print("Recent users:")
346
for user in recent_users:
347
print(f"- {user['name']}")
348
# Cursor automatically closed here
349
# Connection automatically closed here
350
```
351
352
### Multiple Result Sets
353
354
```python
355
import MySQLdb
356
357
db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
358
cursor = db.cursor()
359
360
# Execute query that returns multiple result sets
361
cursor.execute("CALL multi_result_procedure()")
362
363
# Process first result set
364
results1 = cursor.fetchall()
365
print(f"First result set: {len(results1)} rows")
366
367
# Move to next result set
368
if cursor.nextset():
369
results2 = cursor.fetchall()
370
print(f"Second result set: {len(results2)} rows")
371
372
cursor.close()
373
db.close()
374
```