0
# Query Operations
1
2
Comprehensive query execution methods for SQL operations including single queries, batch operations, and data retrieval. All methods support async/await syntax and provide automatic cursor management through context managers.
3
4
## Capabilities
5
6
### Single Query Execution
7
8
Execute individual SQL statements with optional parameter binding for safe query execution.
9
10
```python { .api }
11
async def execute(
12
self,
13
sql: str,
14
parameters: Optional[Iterable[Any]] = None
15
) -> Cursor:
16
"""
17
Helper to create a cursor and execute the given query.
18
19
Parameters:
20
- sql: SQL statement to execute
21
- parameters: Optional parameter values for query placeholders
22
23
Returns:
24
Cursor: Async cursor with query results
25
"""
26
```
27
28
Usage example:
29
30
```python
31
async with aiosqlite.connect("database.db") as db:
32
# Simple query without parameters
33
async with db.execute("CREATE TABLE users (id INTEGER, name TEXT)") as cursor:
34
pass
35
36
# Query with parameters (recommended for user input)
37
async with db.execute(
38
"INSERT INTO users (id, name) VALUES (?, ?)",
39
(1, "John Doe")
40
) as cursor:
41
pass
42
43
# Query with named parameters
44
async with db.execute(
45
"SELECT * FROM users WHERE name = :name",
46
{"name": "John Doe"}
47
) as cursor:
48
async for row in cursor:
49
print(row)
50
```
51
52
### Batch Query Execution
53
54
Execute the same SQL statement multiple times with different parameter sets efficiently.
55
56
```python { .api }
57
async def executemany(
58
self,
59
sql: str,
60
parameters: Iterable[Iterable[Any]]
61
) -> Cursor:
62
"""
63
Helper to create a cursor and execute the given multiquery.
64
65
Parameters:
66
- sql: SQL statement to execute multiple times
67
- parameters: Iterable of parameter tuples, one for each execution
68
69
Returns:
70
Cursor: Async cursor after batch execution
71
"""
72
```
73
74
Usage example:
75
76
```python
77
async with aiosqlite.connect("database.db") as db:
78
# Insert multiple records efficiently
79
users_data = [
80
(1, "Alice", "alice@example.com"),
81
(2, "Bob", "bob@example.com"),
82
(3, "Charlie", "charlie@example.com")
83
]
84
85
async with db.executemany(
86
"INSERT INTO users (id, name, email) VALUES (?, ?, ?)",
87
users_data
88
) as cursor:
89
pass
90
91
await db.commit()
92
```
93
94
### Script Execution
95
96
Execute multiple SQL statements from a script string, useful for database initialization and migrations.
97
98
```python { .api }
99
async def executescript(self, sql_script: str) -> Cursor:
100
"""
101
Helper to create a cursor and execute a user script.
102
103
Parameters:
104
- sql_script: String containing multiple SQL statements separated by semicolons
105
106
Returns:
107
Cursor: Async cursor after script execution
108
"""
109
```
110
111
Usage example:
112
113
```python
114
async with aiosqlite.connect("database.db") as db:
115
schema_script = """
116
CREATE TABLE IF NOT EXISTS users (
117
id INTEGER PRIMARY KEY,
118
name TEXT NOT NULL,
119
email TEXT UNIQUE
120
);
121
122
CREATE TABLE IF NOT EXISTS posts (
123
id INTEGER PRIMARY KEY,
124
user_id INTEGER,
125
title TEXT,
126
content TEXT,
127
FOREIGN KEY (user_id) REFERENCES users (id)
128
);
129
130
INSERT OR IGNORE INTO users (name, email)
131
VALUES ('Admin', 'admin@example.com');
132
"""
133
134
async with db.executescript(schema_script) as cursor:
135
pass
136
```
137
138
### Specialized Query Methods
139
140
Convenience methods for common query patterns with optimized execution.
141
142
```python { .api }
143
async def execute_insert(
144
self,
145
sql: str,
146
parameters: Optional[Iterable[Any]] = None
147
) -> Optional[sqlite3.Row]:
148
"""
149
Helper to insert and get the last_insert_rowid.
150
151
Parameters:
152
- sql: INSERT statement to execute
153
- parameters: Optional parameter values for query placeholders
154
155
Returns:
156
Optional[sqlite3.Row]: Row containing the last_insert_rowid, or None
157
"""
158
159
async def execute_fetchall(
160
self,
161
sql: str,
162
parameters: Optional[Iterable[Any]] = None
163
) -> Iterable[sqlite3.Row]:
164
"""
165
Helper to execute a query and return all the data.
166
167
Parameters:
168
- sql: SELECT statement to execute
169
- parameters: Optional parameter values for query placeholders
170
171
Returns:
172
Iterable[sqlite3.Row]: All result rows
173
"""
174
```
175
176
Usage example:
177
178
```python
179
async with aiosqlite.connect("database.db") as db:
180
# Insert and get the new row ID (using context manager)
181
async with db.execute_insert(
182
"INSERT INTO users (name, email) VALUES (?, ?)",
183
("Jane Doe", "jane@example.com")
184
) as new_row:
185
if new_row:
186
print(f"New user ID: {new_row[0]}")
187
188
# Execute query and get all results at once (using context manager)
189
async with db.execute_fetchall("SELECT * FROM users") as all_users:
190
for user in all_users:
191
print(f"User: {user}")
192
```
193
194
### Cursor Management
195
196
Direct cursor creation and management for advanced query operations.
197
198
```python { .api }
199
async def cursor(self) -> Cursor:
200
"""
201
Create an aiosqlite cursor wrapping a sqlite3 cursor object.
202
203
Returns:
204
Cursor: New async cursor instance
205
"""
206
```
207
208
### Cursor Class Methods
209
210
The Cursor class provides fine-grained control over query execution and result fetching.
211
212
```python { .api }
213
class Cursor:
214
"""Async SQLite database cursor for query execution and result iteration."""
215
216
def __init__(self, conn: Connection, cursor: sqlite3.Cursor) -> None: ...
217
218
async def __aenter__(self) -> "Cursor":
219
"""Async context manager entry."""
220
221
async def __aexit__(self, exc_type, exc_val, exc_tb) -> None:
222
"""Async context manager exit with automatic cleanup."""
223
224
def __aiter__(self) -> AsyncIterator[sqlite3.Row]:
225
"""Enable async iteration over result rows."""
226
```
227
228
### Cursor Query Execution
229
230
Execute queries directly on cursor instances.
231
232
```python { .api }
233
async def execute(
234
self,
235
sql: str,
236
parameters: Optional[Iterable[Any]] = None
237
) -> "Cursor":
238
"""
239
Execute the given query.
240
241
Parameters:
242
- sql: SQL statement to execute
243
- parameters: Optional parameter values for query placeholders
244
245
Returns:
246
Cursor: Self for method chaining
247
"""
248
249
async def executemany(
250
self,
251
sql: str,
252
parameters: Iterable[Iterable[Any]]
253
) -> "Cursor":
254
"""
255
Execute the given multiquery.
256
257
Parameters:
258
- sql: SQL statement to execute multiple times
259
- parameters: Iterable of parameter tuples
260
261
Returns:
262
Cursor: Self for method chaining
263
"""
264
265
async def executescript(self, sql_script: str) -> "Cursor":
266
"""
267
Execute a user script.
268
269
Parameters:
270
- sql_script: String containing multiple SQL statements
271
272
Returns:
273
Cursor: Self for method chaining
274
"""
275
```
276
277
### Data Retrieval
278
279
Methods for fetching query results in different patterns.
280
281
```python { .api }
282
async def fetchone(self) -> Optional[sqlite3.Row]:
283
"""
284
Fetch a single row.
285
286
Returns:
287
Optional[sqlite3.Row]: Next result row or None if no more rows
288
"""
289
290
async def fetchmany(self, size: Optional[int] = None) -> Iterable[sqlite3.Row]:
291
"""
292
Fetch up to cursor.arraysize number of rows.
293
294
Parameters:
295
- size: Maximum number of rows to fetch (defaults to cursor.arraysize)
296
297
Returns:
298
Iterable[sqlite3.Row]: List of result rows
299
"""
300
301
async def fetchall(self) -> Iterable[sqlite3.Row]:
302
"""
303
Fetch all remaining rows.
304
305
Returns:
306
Iterable[sqlite3.Row]: List of all remaining result rows
307
"""
308
309
async def close(self) -> None:
310
"""
311
Close the cursor.
312
313
Releases cursor resources. Cursor cannot be used after closing.
314
"""
315
```
316
317
### Cursor Properties
318
319
Properties providing access to cursor state and metadata.
320
321
```python { .api }
322
@property
323
def rowcount(self) -> int:
324
"""
325
Number of rows affected by the last operation.
326
327
Returns:
328
int: Row count (-1 for SELECT statements in some cases)
329
"""
330
331
@property
332
def lastrowid(self) -> Optional[int]:
333
"""
334
Row ID of the last inserted row.
335
336
Returns:
337
Optional[int]: Last inserted row ID or None
338
"""
339
340
@property
341
def arraysize(self) -> int:
342
"""
343
Number of rows to fetch at a time with fetchmany().
344
345
Returns:
346
int: Default fetch size
347
"""
348
349
@arraysize.setter
350
def arraysize(self, value: int) -> None:
351
"""
352
Set number of rows to fetch at a time.
353
354
Parameters:
355
- value: New array size value
356
"""
357
358
@property
359
def description(self) -> tuple[tuple[str, None, None, None, None, None, None], ...]:
360
"""
361
Column description for the current result set.
362
363
Returns:
364
tuple: Tuple of column descriptors (name, type_code, display_size,
365
internal_size, precision, scale, null_ok)
366
"""
367
368
@property
369
def row_factory(self) -> Optional[Callable[[sqlite3.Cursor, sqlite3.Row], object]]:
370
"""
371
Current row factory for this cursor.
372
373
Returns:
374
Optional[Callable]: Row factory function or None
375
"""
376
377
@row_factory.setter
378
def row_factory(self, factory: Optional[type]) -> None:
379
"""
380
Set row factory for this cursor.
381
382
Parameters:
383
- factory: Row factory class or None
384
"""
385
386
@property
387
def connection(self) -> sqlite3.Connection:
388
"""
389
Reference to the underlying sqlite3 connection.
390
391
Returns:
392
sqlite3.Connection: The wrapped sqlite3 connection object
393
"""
394
```
395
396
Usage example:
397
398
```python
399
async with aiosqlite.connect("database.db") as db:
400
async with db.cursor() as cursor:
401
await cursor.execute("SELECT id, name, email FROM users WHERE id > ?", (10,))
402
403
# Check result metadata
404
print(f"Columns: {[desc[0] for desc in cursor.description]}")
405
406
# Fetch results in different ways
407
first_row = await cursor.fetchone()
408
if first_row:
409
print(f"First user: {first_row}")
410
411
next_batch = await cursor.fetchmany(5)
412
print(f"Next {len(next_batch)} users")
413
414
# Or iterate through all results
415
await cursor.execute("SELECT * FROM users")
416
async for row in cursor:
417
print(f"User: {row}")
418
```