0
# Cursors and Data Access
1
2
Execute SQL queries and retrieve results using different cursor types optimized for various use cases. aiomysql provides multiple cursor classes to handle different data access patterns efficiently.
3
4
## Capabilities
5
6
### Standard Cursor
7
8
The default cursor for executing queries and fetching results as tuples.
9
10
```python { .api }
11
class Cursor:
12
@property
13
def connection(self) -> Connection:
14
"""Reference to the connection that created this cursor."""
15
16
@property
17
def description(self) -> tuple:
18
"""
19
Sequence of 7-item sequences describing result columns.
20
None if no operation has been performed or no results.
21
"""
22
23
@property
24
def rowcount(self) -> int:
25
"""
26
Number of rows produced or affected by last execute().
27
-1 if no execute() has been performed.
28
"""
29
30
@property
31
def rownumber(self) -> int:
32
"""Current row index (0-based)."""
33
34
@property
35
def arraysize(self) -> int:
36
"""Default number of rows fetchmany() will return."""
37
38
@property
39
def lastrowid(self) -> int:
40
"""Row ID of last modified row (for AUTO_INCREMENT columns)."""
41
42
@property
43
def closed(self) -> bool:
44
"""Whether the cursor is closed."""
45
46
async def execute(self, query: str, args = None) -> int:
47
"""
48
Execute a SQL query.
49
50
Parameters:
51
- query: SQL query string, may contain %s placeholders
52
- args: Parameters for query placeholders
53
54
Returns:
55
Number of affected rows
56
"""
57
58
async def executemany(self, query: str, args) -> int:
59
"""
60
Execute a SQL query multiple times with different parameters.
61
Optimized for bulk INSERT operations.
62
63
Parameters:
64
- query: SQL query string with placeholders
65
- args: Sequence of parameter sequences
66
67
Returns:
68
Number of affected rows
69
"""
70
71
async def callproc(self, procname: str, args = ()) -> tuple:
72
"""
73
Call a stored procedure.
74
75
Parameters:
76
- procname: Name of stored procedure
77
- args: Procedure arguments
78
79
Returns:
80
Modified copy of input arguments
81
"""
82
83
def fetchone(self) -> tuple:
84
"""
85
Fetch next row from query results.
86
87
Returns:
88
Row as tuple, or None if no more rows
89
"""
90
91
def fetchmany(self, size: int = None) -> list:
92
"""
93
Fetch multiple rows from query results.
94
95
Parameters:
96
- size: Number of rows to fetch (default: arraysize)
97
98
Returns:
99
List of rows as tuples
100
"""
101
102
def fetchall(self) -> list:
103
"""
104
Fetch all remaining rows from query results.
105
106
Returns:
107
List of all rows as tuples
108
"""
109
110
def scroll(self, value: int, mode: str = 'relative') -> None:
111
"""
112
Scroll cursor position.
113
114
Parameters:
115
- value: Number of rows to move
116
- mode: 'relative' or 'absolute'
117
"""
118
119
async def nextset(self) -> bool:
120
"""
121
Move to next result set (for multi-result queries).
122
123
Returns:
124
True if another result set is available
125
"""
126
127
def mogrify(self, query: str, args = None) -> str:
128
"""
129
Format query string with parameters for debugging.
130
131
Parameters:
132
- query: SQL query with placeholders
133
- args: Parameters for placeholders
134
135
Returns:
136
Formatted query string
137
"""
138
139
async def close(self) -> None:
140
"""Close the cursor."""
141
```
142
143
### Dictionary Cursor
144
145
Cursor that returns results as dictionaries with column names as keys.
146
147
```python { .api }
148
class DictCursor(Cursor):
149
"""
150
Cursor returning results as dictionaries.
151
Inherits all methods from Cursor with modified return types.
152
"""
153
154
def fetchone(self) -> dict:
155
"""
156
Fetch next row as dictionary.
157
158
Returns:
159
Row as dict with column names as keys, or None
160
"""
161
162
def fetchmany(self, size: int = None) -> list:
163
"""
164
Fetch multiple rows as dictionaries.
165
166
Parameters:
167
- size: Number of rows to fetch
168
169
Returns:
170
List of dictionaries
171
"""
172
173
def fetchall(self) -> list:
174
"""
175
Fetch all rows as dictionaries.
176
177
Returns:
178
List of dictionaries
179
"""
180
```
181
182
### Server-Side Cursor
183
184
Unbuffered cursor that fetches results from server on demand, memory-efficient for large result sets.
185
186
```python { .api }
187
class SSCursor(Cursor):
188
"""
189
Server-side (unbuffered) cursor for large result sets.
190
Fetch methods are async and retrieve data from server.
191
"""
192
193
async def fetchone(self) -> tuple:
194
"""
195
Fetch next row from server.
196
197
Returns:
198
Row as tuple, or None if no more rows
199
"""
200
201
async def fetchmany(self, size: int = None) -> list:
202
"""
203
Fetch multiple rows from server.
204
205
Parameters:
206
- size: Number of rows to fetch
207
208
Returns:
209
List of rows as tuples
210
"""
211
212
async def fetchall(self) -> list:
213
"""
214
Fetch all remaining rows from server.
215
216
Returns:
217
List of all rows as tuples
218
"""
219
```
220
221
### Server-Side Dictionary Cursor
222
223
Combines server-side fetching with dictionary results.
224
225
```python { .api }
226
class SSDictCursor(SSCursor):
227
"""
228
Server-side cursor returning results as dictionaries.
229
Combines unbuffered fetching with dictionary format.
230
"""
231
232
async def fetchone(self) -> dict:
233
"""
234
Fetch next row from server as dictionary.
235
236
Returns:
237
Row as dict, or None if no more rows
238
"""
239
240
async def fetchmany(self, size: int = None) -> list:
241
"""
242
Fetch multiple rows from server as dictionaries.
243
244
Parameters:
245
- size: Number of rows to fetch
246
247
Returns:
248
List of dictionaries
249
"""
250
251
async def fetchall(self) -> list:
252
"""
253
Fetch all rows from server as dictionaries.
254
255
Returns:
256
List of dictionaries
257
"""
258
```
259
260
## Usage Examples
261
262
### Basic Cursor Operations
263
264
```python
265
import asyncio
266
import aiomysql
267
268
async def basic_cursor_example():
269
conn = await aiomysql.connect(
270
host='localhost',
271
user='myuser',
272
password='mypass',
273
db='mydatabase'
274
)
275
276
# Create default cursor (returns tuples)
277
async with conn.cursor() as cur:
278
# Execute query
279
await cur.execute("SELECT id, name, email FROM users WHERE age > %s", (18,))
280
281
# Fetch results
282
print("All users over 18:")
283
rows = cur.fetchall()
284
for row in rows:
285
print(f"ID: {row[0]}, Name: {row[1]}, Email: {row[2]}")
286
287
conn.close()
288
289
asyncio.run(basic_cursor_example())
290
```
291
292
### Dictionary Cursor
293
294
```python
295
async def dict_cursor_example():
296
conn = await aiomysql.connect(
297
host='localhost',
298
user='myuser',
299
password='mypass',
300
db='mydatabase'
301
)
302
303
# Use dictionary cursor
304
async with conn.cursor(aiomysql.DictCursor) as cur:
305
await cur.execute("SELECT id, name, email FROM users LIMIT 5")
306
307
# Results are dictionaries
308
users = cur.fetchall()
309
for user in users:
310
print(f"User: {user['name']} ({user['email']})")
311
312
conn.close()
313
```
314
315
### Server-Side Cursor for Large Results
316
317
```python
318
async def server_side_cursor_example():
319
conn = await aiomysql.connect(
320
host='localhost',
321
user='myuser',
322
password='mypass',
323
db='mydatabase'
324
)
325
326
# Use server-side cursor for large result set
327
async with conn.cursor(aiomysql.SSCursor) as cur:
328
await cur.execute("SELECT * FROM large_table")
329
330
# Process results in batches to avoid memory issues
331
while True:
332
rows = await cur.fetchmany(1000) # Fetch 1000 rows at a time
333
if not rows:
334
break
335
336
print(f"Processing batch of {len(rows)} rows")
337
# Process batch...
338
339
conn.close()
340
```
341
342
### Server-Side Dictionary Cursor
343
344
```python
345
async def ss_dict_cursor_example():
346
conn = await aiomysql.connect(
347
host='localhost',
348
user='myuser',
349
password='mypass',
350
db='mydatabase'
351
)
352
353
# Best of both: server-side + dictionary results
354
async with conn.cursor(aiomysql.SSDictCursor) as cur:
355
await cur.execute("SELECT id, name, created_at FROM logs ORDER BY created_at")
356
357
# Stream results as dictionaries
358
async for row in cur: # Note: this would need to be implemented
359
print(f"[{row['created_at']}] {row['name']}")
360
361
conn.close()
362
```
363
364
### Bulk Insert with executemany
365
366
```python
367
async def bulk_insert_example():
368
conn = await aiomysql.connect(
369
host='localhost',
370
user='myuser',
371
password='mypass',
372
db='mydatabase'
373
)
374
375
# Prepare data for bulk insert
376
user_data = [
377
('Alice Johnson', 'alice@example.com', 25),
378
('Bob Smith', 'bob@example.com', 30),
379
('Carol Davis', 'carol@example.com', 28),
380
('David Wilson', 'david@example.com', 35)
381
]
382
383
async with conn.cursor() as cur:
384
# Bulk insert using executemany
385
await cur.executemany(
386
"INSERT INTO users (name, email, age) VALUES (%s, %s, %s)",
387
user_data
388
)
389
390
print(f"Inserted {cur.rowcount} users")
391
392
# Commit the transaction
393
await conn.commit()
394
395
conn.close()
396
```
397
398
### Stored Procedure Call
399
400
```python
401
async def stored_procedure_example():
402
conn = await aiomysql.connect(
403
host='localhost',
404
user='myuser',
405
password='mypass',
406
db='mydatabase'
407
)
408
409
async with conn.cursor() as cur:
410
# Call stored procedure
411
result_args = await cur.callproc('get_user_stats', (100,)) # user_id = 100
412
413
# Fetch results if procedure returns a result set
414
stats = cur.fetchall()
415
print(f"User stats: {stats}")
416
417
# Check if there are more result sets
418
while await cur.nextset():
419
additional_results = cur.fetchall()
420
print(f"Additional results: {additional_results}")
421
422
conn.close()
423
```
424
425
### Query Debugging with mogrify
426
427
```python
428
async def debug_queries_example():
429
conn = await aiomysql.connect(
430
host='localhost',
431
user='myuser',
432
password='mypass',
433
db='mydatabase'
434
)
435
436
async with conn.cursor() as cur:
437
query = "SELECT * FROM users WHERE age BETWEEN %s AND %s AND city = %s"
438
params = (25, 35, 'New York')
439
440
# Format query for debugging
441
formatted_query = cur.mogrify(query, params)
442
print(f"Executing query: {formatted_query}")
443
444
# Execute the actual query
445
await cur.execute(query, params)
446
results = cur.fetchall()
447
print(f"Found {len(results)} users")
448
449
conn.close()
450
```