0
# SQL Operations and Cursors
1

2
Traditional SQL operations with multiple cursor types for different data access patterns, including dictionary cursors, buffered cursors, prepared statements, and raw data access.
3

4
## Capabilities
5

6
### Standard Cursor Operations
7

8
Basic cursor functionality for executing SQL statements and retrieving results.
9

10
```python { .api }
11
class MySQLCursor:
12
    """
13
    Standard cursor for executing SQL statements.
14
    
15
    Provides basic functionality for SQL operations including SELECT, INSERT,
16
    UPDATE, DELETE statements with parameter binding and result fetching.
17
    """
18
    
19
    def execute(self, statement, params=None, multi=False):
20
        """
21
        Execute a SQL statement.
22
        
23
        Parameters:
24
        - statement (str): SQL statement to execute
25
        - params (tuple/list/dict): Parameters for statement
26
        - multi (bool): Execute multiple statements
27
        
28
        Returns:
29
        iterator: Iterator over results for multi-statement execution
30
        
31
        Raises:
32
        ProgrammingError: Invalid SQL statement
33
        DatabaseError: Database execution error
34
        """
35
    
36
    def executemany(self, statement, seq_params):
37
        """
38
        Execute statement for each parameter sequence.
39
        
40
        Parameters:
41
        - statement (str): SQL statement with parameter placeholders
42
        - seq_params (list): Sequence of parameter tuples/dicts
43
        """
44
    
45
    def fetchone(self):
46
        """
47
        Fetch next row from result set.
48
        
49
        Returns:
50
        tuple or None: Next row or None if no more rows
51
        """
52
    
53
    def fetchmany(self, size=1):
54
        """
55
        Fetch multiple rows from result set.
56
        
57
        Parameters:
58
        - size (int): Number of rows to fetch
59
        
60
        Returns:
61
        list: List of row tuples
62
        """
63
    
64
    def fetchall(self):
65
        """
66
        Fetch all remaining rows from result set.
67
        
68
        Returns:
69
        list: List of all remaining row tuples
70
        """
71
    
72
    def close(self):
73
        """Close the cursor and free resources"""
74
    
75
    def callproc(self, procname, args=()):
76
        """
77
        Call a stored procedure.
78
        
79
        Parameters:
80
        - procname (str): Stored procedure name
81
        - args (tuple): Procedure arguments
82
        
83
        Returns:
84
        tuple: Modified arguments from procedure
85
        """
86
    
87
    def nextset(self):
88
        """
89
        Move to next result set in multi-result query.
90
        
91
        Returns:
92
        bool: True if more result sets available
93
        """
94
    
95
    def setinputsizes(self, sizes):
96
        """Set input parameter sizes (DB-API compatibility)"""
97
    
98
    def setoutputsize(self, size, column=None):
99
        """Set output buffer size (DB-API compatibility)"""
100
    
101
    # Properties
102
    @property
103
    def description(self):
104
        """Column descriptions for last executed query"""
105
    
106
    @property
107
    def rowcount(self):
108
        """Number of rows affected by last operation"""
109
    
110
    @property
111
    def lastrowid(self):
112
        """Row ID of last inserted row"""
113
    
114
    @property
115
    def statement(self):
116
        """Last executed SQL statement"""
117
```
118

119
**Usage Example:**
120

121
```python
122
import mysql.connector
123

124
connection = mysql.connector.connect(
125
    user='myuser',
126
    password='mypassword',
127
    host='localhost',
128
    database='mydatabase'
129
)
130

131
cursor = connection.cursor()
132

133
# Execute SELECT query
134
cursor.execute("SELECT id, name, email FROM users WHERE age > %s", (18,))
135

136
# Fetch results
137
for (user_id, name, email) in cursor:
138
    print(f"User {user_id}: {name} ({email})")
139

140
# Execute INSERT with parameters
141
cursor.execute(
142
    "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)",
143
    ("John Doe", "john@example.com", 25)
144
)
145

146
connection.commit()
147
cursor.close()
148
connection.close()
149
```
150

151
### Dictionary Cursor
152

153
Returns query results as dictionaries with column names as keys.
154

155
```python { .api }
156
class MySQLCursorDict(MySQLCursor):
157
    """
158
    Dictionary cursor returning rows as dictionaries.
159
    
160
    Each row is returned as a dictionary where keys are column names
161
    and values are the corresponding field values.
162
    """
163
    
164
    def fetchone(self):
165
        """
166
        Fetch next row as dictionary.
167
        
168
        Returns:
169
        dict or None: Row as dictionary or None if no more rows
170
        """
171
    
172
    def fetchmany(self, size=1):
173
        """
174
        Fetch multiple rows as list of dictionaries.
175
        
176
        Returns:
177
        list[dict]: List of row dictionaries
178
        """
179
    
180
    def fetchall(self):
181
        """
182
        Fetch all rows as list of dictionaries.
183
        
184
        Returns:
185
        list[dict]: List of all row dictionaries
186
        """
187

188
class MySQLCursorBufferedDict(MySQLCursorDict):
189
    """Buffered dictionary cursor that fetches all results immediately"""
190
```
191

192
**Usage Example:**
193

194
```python
195
cursor = connection.cursor(dictionary=True)
196

197
cursor.execute("SELECT id, name, email FROM users LIMIT 5")
198
users = cursor.fetchall()
199

200
for user in users:
201
    print(f"ID: {user['id']}, Name: {user['name']}, Email: {user['email']}")
202
```
203

204
### Named Tuple Cursor
205

206
Returns query results as named tuples with column names as attributes.
207

208
```python { .api }
209
class MySQLCursorNamedTuple(MySQLCursor):
210
    """
211
    Named tuple cursor returning rows as named tuples.
212
    
213
    Each row is returned as a named tuple where field names correspond
214
    to column names, allowing both index and attribute access.
215
    """
216
    
217
    def fetchone(self):
218
        """
219
        Fetch next row as named tuple.
220
        
221
        Returns:
222
        namedtuple or None: Row as named tuple or None
223
        """
224
    
225
    def fetchmany(self, size=1):
226
        """
227
        Fetch multiple rows as list of named tuples.
228
        
229
        Returns:
230
        list[namedtuple]: List of row named tuples
231
        """
232
    
233
    def fetchall(self):
234
        """
235
        Fetch all rows as list of named tuples.
236
        
237
        Returns:
238
        list[namedtuple]: List of all row named tuples
239
        """
240

241
class MySQLCursorBufferedNamedTuple(MySQLCursorNamedTuple):
242
    """Buffered named tuple cursor that fetches all results immediately"""
243
```
244

245
**Usage Example:**
246

247
```python
248
cursor = connection.cursor(named_tuple=True)
249

250
cursor.execute("SELECT id, name, email FROM users LIMIT 5")
251
users = cursor.fetchall()
252

253
for user in users:
254
    print(f"ID: {user.id}, Name: {user.name}, Email: {user.email}")
255
```
256

257
### Buffered Cursors
258

259
Cursors that fetch and buffer all results immediately for improved performance.
260

261
```python { .api }
262
class MySQLCursorBuffered(MySQLCursor):
263
    """
264
    Buffered cursor that fetches all results immediately.
265
    
266
    Improves performance by fetching all results at once and storing
267
    them in memory. Useful for small to medium result sets.
268
    """
269
    
270
    def with_rows(self):
271
        """Check if cursor has result rows"""
272

273
class MySQLCursorBufferedRaw(MySQLCursorBuffered):
274
    """Buffered cursor that returns raw data without type conversion"""
275
```
276

277
### Raw Cursors
278

279
Returns data exactly as received from MySQL server without type conversion.
280

281
```python { .api }
282
class MySQLCursorRaw(MySQLCursor):
283
    """
284
    Raw cursor returning data without type conversion.
285
    
286
    Returns all values as received from MySQL server without Python
287
    type conversion. Useful for performance-critical applications or
288
    when custom type handling is required.
289
    """
290

291
class MySQLCursorBufferedRaw(MySQLCursorRaw):
292
    """Buffered raw cursor combining raw data with buffering"""
293
```
294

295
### Prepared Statements
296

297
Cursor supporting prepared statements for improved performance and security.
298

299
```python { .api }
300
class MySQLCursorPrepared(MySQLCursor):
301
    """
302
    Prepared statement cursor for improved performance and security.
303
    
304
    Uses MySQL's prepared statement protocol for efficient execution
305
    of repeated queries and automatic parameter escaping.
306
    """
307
    
308
    def prepare(self, statement):
309
        """
310
        Prepare a SQL statement for execution.
311
        
312
        Parameters:
313
        - statement (str): SQL statement with parameter markers (?)
314
        """
315
    
316
    def execute(self, statement=None, params=None, multi=False):
317
        """
318
        Execute prepared statement.
319
        
320
        Parameters:
321
        - statement (str): SQL statement or None to use prepared
322
        - params (tuple/list): Parameters for statement
323
        - multi (bool): Execute multiple statements
324
        """
325
```
326

327
**Usage Example:**
328

329
```python
330
cursor = connection.cursor(prepared=True)
331

332
# Prepare statement once
333
stmt = "SELECT id, name FROM users WHERE age > ? AND city = ?"
334

335
# Execute multiple times with different parameters
336
cursor.execute(stmt, (18, 'New York'))
337
results1 = cursor.fetchall()
338

339
cursor.execute(stmt, (25, 'Los Angeles'))
340
results2 = cursor.fetchall()
341
```
342

343
### Stored Procedures
344

345
Execute MySQL stored procedures and handle multiple result sets.
346

347
```python { .api }
348
def callproc(self, procname, args=()):
349
    """
350
    Execute a stored procedure.
351
    
352
    Parameters:
353
    - procname (str): Name of stored procedure to call
354
    - args (tuple): Input parameters for procedure
355
    
356
    Returns:
357
    tuple: Modified arguments (including OUT parameters)
358
    
359
    Example:
360
    cursor.callproc('GetUserStats', (user_id, 0, 0))
361
    # Returns tuple with IN/OUT parameter values
362
    """
363

364
def stored_results(self):
365
    """
366
    Get iterator over result sets from stored procedure.
367
    
368
    Returns:
369
    iterator: Iterator over MySQLCursor objects for each result set
370
    """
371
```
372

373
**Usage Example:**
374

375
```python
376
cursor = connection.cursor()
377

378
# Call stored procedure
379
result_args = cursor.callproc('GetUserStats', (123, 0, 0))
380
print(f"Total users: {result_args[1]}, Active users: {result_args[2]}")
381

382
# Process multiple result sets
383
for result in cursor.stored_results():
384
    rows = result.fetchall()
385
    print(f"Result set: {rows}")
386
```
387

388
### Batch Operations
389

390
Efficient execution of multiple similar operations.
391

392
```python { .api }
393
def executemany(self, statement, seq_params):
394
    """
395
    Execute statement multiple times with different parameters.
396
    
397
    Parameters:
398
    - statement (str): SQL statement with parameter placeholders
399
    - seq_params (sequence): Sequence of parameter tuples/dicts
400
    
401
    Example:
402
    data = [
403
        ('John', 'john@example.com', 25),
404
        ('Jane', 'jane@example.com', 30),
405
        ('Bob', 'bob@example.com', 35)
406
    ]
407
    cursor.executemany(
408
        "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)",
409
        data
410
    )
411
    """
412
```
413

414
### C Extension Cursors
415

416
High-performance cursor implementations using the optional C extension for improved performance.
417

418
```python { .api }
419
class CMySQLCursor(MySQLCursor):
420
    """
421
    C extension cursor providing improved performance over pure Python implementation.
422
    
423
    Requires the C extension to be available. Provides the same interface as
424
    MySQLCursor but with optimized execution and data handling.
425
    """
426

427
class CMySQLCursorDict(MySQLCursorDict):
428
    """C extension dictionary cursor with improved performance"""
429

430
class CMySQLCursorBuffered(MySQLCursorBuffered):
431
    """C extension buffered cursor with improved performance"""
432

433
class CMySQLCursorBufferedDict(MySQLCursorBufferedDict):
434
    """C extension buffered dictionary cursor with improved performance"""
435

436
class CMySQLCursorRaw(MySQLCursorRaw):
437
    """C extension raw cursor with improved performance"""
438

439
class CMySQLCursorBufferedRaw(MySQLCursorBufferedRaw):
440
    """C extension buffered raw cursor with improved performance"""
441

442
class CMySQLCursorNamedTuple(MySQLCursorNamedTuple):
443
    """C extension named tuple cursor with improved performance"""
444

445
class CMySQLCursorBufferedNamedTuple(MySQLCursorBufferedNamedTuple):
446
    """C extension buffered named tuple cursor with improved performance"""
447

448
class CMySQLCursorPrepared(MySQLCursorPrepared):
449
    """C extension prepared statement cursor with improved performance"""
450
```
451

452
**Usage Example:**
453

454
```python
455
import mysql.connector
456

457
# Connect using C extension (when available)
458
connection = mysql.connector.connect(
459
    user='myuser',
460
    password='mypassword',
461
    host='localhost',
462
    database='mydatabase',
463
    use_pure=False  # Enable C extension
464
)
465

466
# C extension cursors are used automatically when use_pure=False
467
cursor = connection.cursor()  # Returns CMySQLCursor
468
dict_cursor = connection.cursor(dictionary=True)  # Returns CMySQLCursorDict
469
buffered_cursor = connection.cursor(buffered=True)  # Returns CMySQLCursorBuffered
470
```
471

472
## Types
473

474
```python { .api }
475
CursorConfig = {
476
    'buffered': bool,
477
    'raw': bool,
478
    'prepared': bool,
479
    'cursor_class': type,
480
    'dictionary': bool,
481
    'named_tuple': bool
482
}
483

484
ColumnDescription = tuple[
485
    str,  # name
486
    int,  # type_code
487
    int,  # display_size
488
    int,  # internal_size
489
    int,  # precision
490
    int,  # scale
491
    bool  # null_ok
492
]
493
```