0
# Cursor Operations
1

2
Execute SQL statements and retrieve results using various cursor types optimized for different use cases and data access patterns.
3

4
## Cursor Types Overview
5

6
MySQL Connector/Python provides several cursor types:
7

8
- **MySQLCursor**: Standard cursor returning tuples
9
- **MySQLCursorBuffered**: Buffered cursor for immediate result fetching
10
- **MySQLCursorDict**: Dictionary cursor with column names as keys
11
- **MySQLCursorRaw**: Raw cursor without automatic type conversion
12
- **MySQLCursorPrepared**: Prepared statement cursor for repeated execution
13
- **C Extension Cursors**: High-performance variants when C extension is available
14

15
## Creating Cursors
16

17
```python { .api }
18
# From connection object
19
cursor = connection.cursor(
20
    buffered: Optional[bool] = None,       # Buffer all results immediately
21
    raw: Optional[bool] = None,            # Return raw MySQL types
22
    prepared: Optional[bool] = None,       # Use prepared statements
23
    cursor_class: Optional[Type] = None,   # Custom cursor class
24
    dictionary: Optional[bool] = None,     # Return results as dictionaries
25
    named_tuple: Optional[bool] = None     # Return results as named tuples
26
)
27
```
28

29
## Base Cursor Classes
30

31
### MySQLCursor
32

33
```python { .api }
34
class MySQLCursor:
35
    """
36
    Standard cursor for executing SQL statements.
37
    Returns results as tuples.
38
    """
39
    
40
    def execute(self, operation: str, params: Optional[Union[Sequence, Dict]] = None, multi: bool = False) -> Optional[Iterator]:
41
        """Execute SQL statement with optional parameters."""
42
        pass
43
    
44
    def executemany(self, operation: str, seq_params: Sequence[Union[Sequence, Dict]]) -> None:
45
        """Execute SQL statement multiple times with parameter sequences."""
46
        pass
47
    
48
    def fetchone(self) -> Optional[Tuple]:
49
        """Fetch next row from result set."""
50
        pass
51
    
52
    def fetchmany(self, size: Optional[int] = None) -> List[Tuple]:
53
        """Fetch specified number of rows from result set."""
54
        pass
55
    
56
    def fetchall(self) -> List[Tuple]:
57
        """Fetch all remaining rows from result set."""
58
        pass
59
    
60
    def close(self) -> None:
61
        """Close cursor and free resources."""
62
        pass
63
    
64
    def callproc(self, procname: str, args: Sequence = ()) -> Optional[Dict]:
65
        """Call stored procedure with arguments."""
66
        pass
67
    
68
    def stored_results(self) -> Iterator['MySQLCursor']:
69
        """Return iterator for stored procedure result sets."""
70
        pass
71
    
72
    def nextset(self) -> Optional[bool]:
73
        """Skip to next result set in multi-result query."""
74
        pass
75
    
76
    def setinputsizes(self, sizes: Sequence) -> None:
77
        """Set input parameter sizes (DB-API compliance)."""
78
        pass
79
    
80
    def setoutputsize(self, size: int, column: Optional[int] = None) -> None:
81
        """Set output column size (DB-API compliance)."""
82
        pass
83
    
84
    @property 
85
    def description(self) -> Optional[List[Tuple]]:
86
        """Column metadata for last executed query."""
87
        pass
88
    
89
    @property
90
    def rowcount(self) -> int:
91
        """Number of rows affected by last operation."""
92
        pass
93
    
94
    @property
95
    def lastrowid(self) -> Optional[int]:
96
        """Auto-generated ID from last INSERT operation.""" 
97
        pass
98
    
99
    @property
100
    def arraysize(self) -> int:
101
        """Default number of rows fetchmany() should return."""
102
        pass
103
    
104
    @arraysize.setter  
105
    def arraysize(self, value: int) -> None:
106
        """Set default fetchmany() size."""
107
        pass
108
    
109
    @property
110
    def statement(self) -> Optional[str]:
111
        """Last executed SQL statement."""
112
        pass
113
    
114
    @property
115
    def with_rows(self) -> bool:
116
        """Whether last operation produced result rows."""
117
        pass
118
    
119
    @property
120
    def column_names(self) -> Tuple[str, ...]:
121
        """Column names from result set."""
122
        pass
123
    
124
    def __iter__(self) -> Iterator[Tuple]:
125
        """Make cursor iterable over result rows."""
126
        pass
127
    
128
    def __next__(self) -> Tuple:
129
        """Get next row for iteration."""
130
        pass
131
    
132
    def __enter__(self) -> 'MySQLCursor':
133
        """Context manager entry."""
134
        pass
135
    
136
    def __exit__(self, exc_type, exc_val, exc_tb) -> None:
137
        """Context manager exit with automatic cleanup."""
138
        pass
139
```
140

141
### MySQLCursorBuffered
142

143
```python { .api }
144
class MySQLCursorBuffered(MySQLCursor):
145
    """
146
    Buffered cursor that fetches all results immediately.
147
    Useful when you need to access all rows or get accurate rowcount.
148
    """
149
    
150
    @property
151
    def rowcount(self) -> int:
152
        """Accurate row count (buffered results)."""
153
        pass
154
    
155
    def reset(self, free: bool = True) -> None:
156
        """Reset cursor position to beginning of result set."""
157
        pass
158
```
159

160
### MySQLCursorRaw
161

162
```python { .api }
163
class MySQLCursorRaw(MySQLCursor):
164
    """
165
    Raw cursor that returns results without automatic type conversion.
166
    MySQL values returned as received from server.
167
    """
168
    
169
    def fetchone(self) -> Optional[Tuple]:
170
        """Fetch next row as raw MySQL types."""
171
        pass
172
    
173
    def fetchmany(self, size: Optional[int] = None) -> List[Tuple]:
174
        """Fetch rows as raw MySQL types."""
175
        pass
176
    
177
    def fetchall(self) -> List[Tuple]:
178
        """Fetch all rows as raw MySQL types."""
179
        pass
180
```
181

182
### MySQLCursorBufferedRaw
183

184
```python { .api }
185
class MySQLCursorBufferedRaw(MySQLCursorBuffered, MySQLCursorRaw):
186
    """
187
    Combination of buffered and raw cursor features.
188
    Buffers all results as raw MySQL types.
189
    """
190
    pass
191
```
192

193
## Dictionary Cursors
194

195
### MySQLCursorDict
196

197
```python { .api }
198
class MySQLCursorDict(MySQLCursor):
199
    """
200
    Dictionary cursor returning results as dictionaries.
201
    Column names used as dictionary keys.
202
    """
203
    
204
    def fetchone(self) -> Optional[Dict[str, Any]]:
205
        """Fetch next row as dictionary."""
206
        pass
207
    
208
    def fetchmany(self, size: Optional[int] = None) -> List[Dict[str, Any]]:
209
        """Fetch rows as list of dictionaries."""
210
        pass
211
    
212
    def fetchall(self) -> List[Dict[str, Any]]:
213
        """Fetch all rows as list of dictionaries."""
214
        pass
215
```
216

217
### MySQLCursorBufferedDict
218

219
```python { .api }
220
class MySQLCursorBufferedDict(MySQLCursorBuffered, MySQLCursorDict):
221
    """
222
    Buffered dictionary cursor.
223
    Combines buffering with dictionary result format.
224
    """
225
    pass
226
```
227

228
## Prepared Statement Cursors
229

230
### MySQLCursorPrepared
231

232
```python { .api }
233
class MySQLCursorPrepared(MySQLCursor):
234
    """
235
    Prepared statement cursor for repeated execution.
236
    Offers better performance for repeated queries.
237
    """
238
    
239
    def prepare(self, operation: str) -> None:
240
        """Prepare SQL statement for execution."""
241
        pass
242
    
243
    def execute(self, params: Optional[Union[Sequence, Dict]] = None) -> None:
244
        """Execute prepared statement with parameters."""
245
        pass
246
    
247
    def executemany(self, seq_params: Sequence[Union[Sequence, Dict]]) -> None:
248
        """Execute prepared statement multiple times."""
249
        pass
250
    
251
    @property
252
    def statement(self) -> Optional[str]:
253
        """Prepared SQL statement."""
254
        pass
255
```
256

257
### MySQLCursorPreparedDict
258

259
```python { .api }
260
class MySQLCursorPreparedDict(MySQLCursorPrepared):
261
    """
262
    Prepared statement cursor returning dictionary results.
263
    """
264
    
265
    def fetchone(self) -> Optional[Dict[str, Any]]:
266
        """Fetch next row as dictionary."""
267
        pass
268
    
269
    def fetchmany(self, size: Optional[int] = None) -> List[Dict[str, Any]]:
270
        """Fetch rows as list of dictionaries."""
271
        pass
272
    
273
    def fetchall(self) -> List[Dict[str, Any]]:
274
        """Fetch all rows as list of dictionaries."""
275
        pass
276
```
277

278
## C Extension Cursors
279

280
When the C extension is available (`HAVE_CEXT = True`), high-performance cursor variants are provided:
281

282
```python { .api }
283
class CMySQLCursor(MySQLCursor):
284
    """C extension cursor for improved performance."""
285
    pass
286

287
class CMySQLCursorBuffered(MySQLCursorBuffered):
288
    """C extension buffered cursor."""
289
    pass
290

291
class CMySQLCursorRaw(MySQLCursorRaw):
292
    """C extension raw cursor."""
293
    pass
294

295
class CMySQLCursorBufferedRaw(MySQLCursorBufferedRaw):
296
    """C extension buffered raw cursor."""
297
    pass
298

299
class CMySQLCursorDict(MySQLCursorDict):
300
    """C extension dictionary cursor."""
301
    pass
302

303
class CMySQLCursorBufferedDict(MySQLCursorBufferedDict):
304
    """C extension buffered dictionary cursor."""
305
    pass
306

307
class CMySQLCursorPrepared(MySQLCursorPrepared):
308
    """C extension prepared statement cursor."""
309
    pass
310
```
311

312
## Usage Examples
313

314
### Basic Query Execution
315

316
```python
317
import mysql.connector
318

319
connection = mysql.connector.connect(
320
    host='localhost',
321
    user='myuser',
322
    password='mypassword',
323
    database='mydatabase'
324
)
325

326
# Standard cursor
327
cursor = connection.cursor()
328
cursor.execute("SELECT id, name, email FROM users WHERE age > %s", (25,))
329

330
# Fetch results
331
for (user_id, name, email) in cursor:
332
    print(f"User {user_id}: {name} ({email})")
333

334
cursor.close()
335
connection.close()
336
```
337

338
### Dictionary Cursor Usage
339

340
```python
341
import mysql.connector
342

343
connection = mysql.connector.connect(
344
    host='localhost',
345
    user='myuser',
346
    password='mypassword',
347
    database='mydatabase'
348
)
349

350
# Dictionary cursor
351
cursor = connection.cursor(dictionary=True)
352
cursor.execute("SELECT id, name, email FROM users WHERE age > %s", (25,))
353

354
# Results as dictionaries
355
for row in cursor:
356
    print(f"User {row['id']}: {row['name']} ({row['email']})")
357

358
cursor.close()
359
connection.close()
360
```
361

362
### Buffered Cursor for Row Count
363

364
```python
365
import mysql.connector
366

367
connection = mysql.connector.connect(
368
    host='localhost',
369
    user='myuser',
370
    password='mypassword',
371
    database='mydatabase'
372
)
373

374
# Buffered cursor provides accurate row count
375
cursor = connection.cursor(buffered=True)
376
cursor.execute("SELECT * FROM users")
377

378
print(f"Total users: {cursor.rowcount}")
379

380
# Fetch results
381
users = cursor.fetchall()
382
for user in users:
383
    print(user)
384

385
cursor.close()
386
connection.close()
387
```
388

389
### Prepared Statements
390

391
```python
392
import mysql.connector
393

394
connection = mysql.connector.connect(
395
    host='localhost',
396
    user='myuser',
397
    password='mypassword',
398
    database='mydatabase'
399
)
400

401
# Prepared statement cursor
402
cursor = connection.cursor(prepared=True)
403

404
# Prepare statement once
405
add_user = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)"
406

407
# Execute multiple times with different parameters
408
user_data = [
409
    ('John Doe', 'john@example.com', 30),
410
    ('Jane Smith', 'jane@example.com', 25),
411
    ('Bob Johnson', 'bob@example.com', 35)
412
]
413

414
for data in user_data:
415
    cursor.execute(add_user, data)
416

417
connection.commit()
418
cursor.close()
419
connection.close()
420
```
421

422
### Multi-Statement Execution
423

424
```python
425
import mysql.connector
426

427
connection = mysql.connector.connect(
428
    host='localhost',
429
    user='myuser',
430
    password='mypassword',
431
    database='mydatabase'
432
)
433

434
cursor = connection.cursor()
435

436
# Execute multiple statements
437
sql_statements = """
438
    CREATE TEMPORARY TABLE temp_users (id INT, name VARCHAR(100));
439
    INSERT INTO temp_users VALUES (1, 'Test User');
440
    SELECT * FROM temp_users;
441
"""
442

443
# multi=True returns iterator for multiple results
444
results = cursor.execute(sql_statements, multi=True)
445

446
for result in results:
447
    if result.with_rows:
448
        print(f"Rows produced: {result.fetchall()}")
449
    else:
450
        print(f"Rows affected: {result.rowcount}")
451

452
cursor.close()
453
connection.close()
454
```
455

456
### Stored Procedure Calls
457

458
```python
459
import mysql.connector
460

461
connection = mysql.connector.connect(
462
    host='localhost', 
463
    user='myuser',
464
    password='mypassword',
465
    database='mydatabase'
466
)
467

468
cursor = connection.cursor()
469

470
# Call stored procedure
471
result_args = cursor.callproc('get_user_stats', [2024])
472

473
# Get procedure results
474
for result in cursor.stored_results():
475
    stats = result.fetchall()
476
    print(f"User statistics: {stats}")
477

478
cursor.close()
479
connection.close()
480
```
481

482
### Context Manager Usage
483

484
```python
485
import mysql.connector
486

487
connection = mysql.connector.connect(
488
    host='localhost',
489
    user='myuser', 
490
    password='mypassword',
491
    database='mydatabase'
492
)
493

494
# Automatic cursor cleanup
495
with connection.cursor(dictionary=True) as cursor:
496
    cursor.execute("SELECT COUNT(*) as total FROM users")
497
    result = cursor.fetchone()
498
    print(f"Total users: {result['total']}")
499
    # Cursor automatically closed
500

501
connection.close()
502
```
503

504
### Handling Large Result Sets
505

506
```python
507
import mysql.connector
508

509
connection = mysql.connector.connect(
510
    host='localhost',
511
    user='myuser',
512
    password='mypassword', 
513
    database='mydatabase'
514
)
515

516
cursor = connection.cursor()
517
cursor.execute("SELECT * FROM large_table")
518

519
# Process results in chunks
520
while True:
521
    rows = cursor.fetchmany(1000)  # Fetch 1000 rows at a time
522
    if not rows:
523
        break
524
    
525
    for row in rows:
526
        # Process each row
527
        print(f"Processing row: {row[0]}")
528

529
cursor.close()
530
connection.close()
531
```
532

533
### Raw Cursor for Performance
534

535
```python
536
import mysql.connector
537

538
connection = mysql.connector.connect(
539
    host='localhost',
540
    user='myuser',
541
    password='mypassword',
542
    database='mydatabase'
543
)
544

545
# Raw cursor skips type conversion for better performance
546
cursor = connection.cursor(raw=True)
547
cursor.execute("SELECT id, created_at FROM logs")
548

549
for (raw_id, raw_timestamp) in cursor:
550
    # Values are returned as bytes/raw MySQL types
551
    user_id = int(raw_id)
552
    timestamp = raw_timestamp.decode('utf-8')
553
    print(f"Log entry {user_id} at {timestamp}")
554

555
cursor.close()
556
connection.close()
557
```