0
# Advanced Features
1
2
Extended SQLite functionality including user-defined functions, database backups, progress handlers, extension loading, and database utilities. These features provide advanced capabilities for specialized use cases and database management.
3
4
## Capabilities
5
6
### User-Defined Functions
7
8
Create custom SQL functions that can be called within SQL statements.
9
10
```python { .api }
11
async def create_function(
12
self,
13
name: str,
14
num_params: int,
15
func: Callable,
16
deterministic: bool = False
17
) -> None:
18
"""
19
Create user-defined function that can be later used within SQL statements.
20
21
Must be run within the same thread that query executions take place,
22
so execution is deferred to the connection's worker thread.
23
24
Parameters:
25
- name: Function name to use in SQL statements
26
- num_params: Number of parameters the function accepts (-1 for variable)
27
- func: Python function to execute when SQL function is called
28
- deterministic: If True, function is marked as deterministic for optimization
29
30
Notes:
31
- deterministic flag requires SQLite 3.8.3+, raises NotSupportedError on older versions
32
- Function executes in the database thread, not the main async context
33
"""
34
```
35
36
Usage example:
37
38
```python
39
import aiosqlite
40
import math
41
42
async def setup_custom_functions():
43
async with aiosqlite.connect("database.db") as db:
44
# Simple mathematical function
45
def square(x):
46
return x * x if x is not None else None
47
48
await db.create_function("square", 1, square, deterministic=True)
49
50
# String manipulation function
51
def reverse_string(s):
52
return s[::-1] if s is not None else None
53
54
await db.create_function("reverse", 1, reverse_string, deterministic=True)
55
56
# Aggregate-like function (though SQLite handles aggregation)
57
def distance(x1, y1, x2, y2):
58
if any(v is None for v in [x1, y1, x2, y2]):
59
return None
60
return math.sqrt((x2 - x1)**2 + (y2 - y1)**2)
61
62
await db.create_function("distance", 4, distance, deterministic=True)
63
64
# Test the functions
65
async with db.execute("SELECT square(5) as result") as cursor:
66
row = await cursor.fetchone()
67
print(f"Square of 5: {row[0]}") # 25
68
69
async with db.execute("SELECT reverse('hello') as result") as cursor:
70
row = await cursor.fetchone()
71
print(f"Reverse of 'hello': {row[0]}") # 'olleh'
72
73
# Use in complex queries
74
await db.execute("""
75
CREATE TABLE IF NOT EXISTS points (
76
id INTEGER PRIMARY KEY,
77
x REAL, y REAL,
78
name TEXT
79
)
80
""")
81
82
await db.execute("INSERT INTO points (x, y, name) VALUES (0, 0, 'origin')")
83
await db.execute("INSERT INTO points (x, y, name) VALUES (3, 4, 'point1')")
84
await db.commit()
85
86
async with db.execute("""
87
SELECT name, distance(0, 0, x, y) as dist_from_origin
88
FROM points
89
ORDER BY dist_from_origin
90
""") as cursor:
91
async for row in cursor:
92
print(f"{row[0]}: distance = {row[1]}")
93
```
94
95
### Database Backup
96
97
Create backups of the current database to another database connection.
98
99
```python { .api }
100
async def backup(
101
self,
102
target: Union["Connection", sqlite3.Connection],
103
*,
104
pages: int = 0,
105
progress: Optional[Callable[[int, int, int], None]] = None,
106
name: str = "main",
107
sleep: float = 0.250
108
) -> None:
109
"""
110
Make a backup of the current database to the target database.
111
112
Takes either a standard sqlite3 or aiosqlite Connection object as target.
113
114
Parameters:
115
- target: Destination database connection (aiosqlite.Connection or sqlite3.Connection)
116
- pages: Pages to copy at once (0 = all pages, default: 0)
117
- progress: Optional callback function called during backup progress
118
- name: Database name to backup (default: "main")
119
- sleep: Sleep time between page batches in seconds (default: 0.250)
120
121
Notes:
122
- Progress callback receives (status, remaining, total) parameters
123
- Backup is performed page by page to allow concurrent access
124
- Sleep parameter helps prevent blocking other operations
125
"""
126
```
127
128
Usage example:
129
130
```python
131
import aiosqlite
132
import sqlite3
133
134
async def backup_database():
135
# Source database
136
async with aiosqlite.connect("production.db") as source_db:
137
# Backup to another aiosqlite connection
138
async with aiosqlite.connect("backup.db") as backup_db:
139
def progress_callback(status, remaining, total):
140
print(f"Backup progress: {total - remaining}/{total} pages")
141
142
await source_db.backup(
143
backup_db,
144
pages=100, # Copy 100 pages at a time
145
progress=progress_callback,
146
sleep=0.1 # Brief pause between batches
147
)
148
print("Backup to aiosqlite connection completed")
149
150
# Backup to standard sqlite3 connection
151
sqlite_conn = sqlite3.connect("backup2.db")
152
try:
153
await source_db.backup(sqlite_conn)
154
print("Backup to sqlite3 connection completed")
155
finally:
156
sqlite_conn.close()
157
```
158
159
### Database Dump
160
161
Export database structure and data as SQL statements.
162
163
```python { .api }
164
async def iterdump(self) -> AsyncIterator[str]:
165
"""
166
Return an async iterator to dump the database in SQL text format.
167
168
Generates SQL statements that can recreate the database structure
169
and data. Each iteration yields a single SQL statement.
170
171
Returns:
172
AsyncIterator[str]: Async iterator yielding SQL statements
173
174
Usage:
175
async for line in db.iterdump():
176
print(line)
177
"""
178
```
179
180
Usage example:
181
182
```python
183
import aiosqlite
184
185
async def dump_database():
186
async with aiosqlite.connect("database.db") as db:
187
# Dump to file
188
with open("database_dump.sql", "w") as f:
189
f.write("-- Database dump generated by aiosqlite\n")
190
async for line in db.iterdump():
191
f.write(line + "\n")
192
193
print("Database dumped to database_dump.sql")
194
195
# Print schema only (filter out INSERT statements)
196
print("\nDatabase schema:")
197
async for line in db.iterdump():
198
if not line.strip().startswith("INSERT"):
199
print(line)
200
```
201
202
### Progress Monitoring
203
204
Set up progress callbacks for long-running operations.
205
206
```python { .api }
207
async def set_progress_handler(
208
self,
209
handler: Callable[[], Optional[int]],
210
n: int
211
) -> None:
212
"""
213
Set progress handler callback for long-running operations.
214
215
The handler is called every n virtual machine instructions during
216
SQL statement execution. Can be used to provide progress feedback
217
or to interrupt long-running queries.
218
219
Parameters:
220
- handler: Callback function called during execution
221
- n: Number of VM instructions between handler calls
222
223
Notes:
224
- Handler returning non-zero interrupts the operation
225
- Useful for providing user feedback or implementing timeouts
226
"""
227
```
228
229
Usage example:
230
231
```python
232
import aiosqlite
233
import time
234
235
async def long_running_operation_with_progress():
236
async with aiosqlite.connect("database.db") as db:
237
start_time = time.time()
238
239
def progress_handler():
240
elapsed = time.time() - start_time
241
if elapsed > 30: # Timeout after 30 seconds
242
print("Operation timeout - interrupting")
243
return 1 # Non-zero return interrupts operation
244
245
if int(elapsed) % 5 == 0: # Progress every 5 seconds
246
print(f"Operation running for {elapsed:.1f} seconds...")
247
248
return 0 # Continue operation
249
250
# Set progress handler to be called every 1000 VM instructions
251
await db.set_progress_handler(progress_handler, 1000)
252
253
try:
254
# Long-running operation
255
await db.execute("""
256
CREATE TABLE large_table AS
257
WITH RECURSIVE series(x) AS (
258
SELECT 1 UNION ALL SELECT x+1 FROM series WHERE x < 1000000
259
)
260
SELECT x as id, 'data_' || x as value FROM series
261
""")
262
await db.commit()
263
print("Large table created successfully")
264
265
except aiosqlite.OperationalError as e:
266
if "interrupted" in str(e):
267
print("Operation was interrupted by progress handler")
268
else:
269
raise
270
```
271
272
### Query Tracing
273
274
Set up trace callbacks for debugging and monitoring SQL execution.
275
276
```python { .api }
277
async def set_trace_callback(self, handler: Callable) -> None:
278
"""
279
Set trace callback handler for SQL statement execution.
280
281
The handler is called with each SQL statement before execution,
282
useful for debugging, logging, or performance monitoring.
283
284
Parameters:
285
- handler: Callback function receiving SQL statement as parameter
286
287
Notes:
288
- Handler receives the SQL statement string as its only parameter
289
- Called for every SQL statement executed on this connection
290
- Useful for debugging and performance analysis
291
"""
292
```
293
294
Usage example:
295
296
```python
297
import aiosqlite
298
import time
299
300
async def trace_sql_execution():
301
async with aiosqlite.connect("database.db") as db:
302
# Set up SQL tracing
303
def trace_handler(sql_statement):
304
timestamp = time.strftime("%Y-%m-%d %H:%M:%S")
305
print(f"[{timestamp}] SQL: {sql_statement.strip()}")
306
307
await db.set_trace_callback(trace_handler)
308
309
# Now all SQL statements will be traced
310
await db.execute("CREATE TABLE IF NOT EXISTS test (id INTEGER, name TEXT)")
311
await db.execute("INSERT INTO test VALUES (1, 'Alice')")
312
await db.execute("INSERT INTO test VALUES (2, 'Bob')")
313
await db.commit()
314
315
async with db.execute("SELECT * FROM test ORDER BY name") as cursor:
316
async for row in cursor:
317
print(f"Row: {row}")
318
```
319
320
### Extension Loading
321
322
Load SQLite extensions for additional functionality.
323
324
```python { .api }
325
async def enable_load_extension(self, value: bool) -> None:
326
"""
327
Enable or disable loading of SQLite extensions.
328
329
Parameters:
330
- value: True to enable extension loading, False to disable
331
332
Notes:
333
- Extension loading is disabled by default for security
334
- Must be enabled before calling load_extension()
335
- Should be disabled after loading required extensions
336
"""
337
338
async def load_extension(self, path: str):
339
"""
340
Load an extension from the specified path.
341
342
Parameters:
343
- path: Path to the extension file (.so on Unix, .dll on Windows)
344
345
Notes:
346
- Extension loading must be enabled first with enable_load_extension(True)
347
- Extensions provide additional SQL functions and capabilities
348
- Common extensions include FTS (full-text search), spatial functions
349
"""
350
```
351
352
Usage example:
353
354
```python
355
import aiosqlite
356
357
async def load_extensions():
358
async with aiosqlite.connect("database.db") as db:
359
try:
360
# Enable extension loading
361
await db.enable_load_extension(True)
362
363
# Load extension (example path - actual path varies by system)
364
# await db.load_extension("/usr/lib/sqlite3/libspatialite.so")
365
366
# Extensions would provide additional functions
367
# async with db.execute("SELECT ST_Distance(point1, point2) FROM locations") as cursor:
368
# ...
369
370
except aiosqlite.OperationalError as e:
371
print(f"Extension loading failed: {e}")
372
finally:
373
# Disable extension loading for security
374
await db.enable_load_extension(False)
375
```
376
377
### Connection Interruption
378
379
Interrupt long-running database operations.
380
381
```python { .api }
382
async def interrupt(self) -> None:
383
"""
384
Interrupt pending queries.
385
386
Calls sqlite3.Connection.interrupt() to cancel long-running operations.
387
Operations may still complete normally if interruption occurs too late
388
in the execution process.
389
390
Notes:
391
- Safe to call from any thread or coroutine
392
- May not immediately stop all operations
393
- Interrupted operations raise OperationalError with "interrupted" message
394
"""
395
```
396
397
Usage example:
398
399
```python
400
import aiosqlite
401
import asyncio
402
403
async def interruptible_operation():
404
async with aiosqlite.connect("database.db") as db:
405
# Start a long-running operation
406
async def long_query():
407
try:
408
await db.execute("""
409
WITH RECURSIVE huge_series(x) AS (
410
SELECT 1 UNION ALL
411
SELECT x+1 FROM huge_series WHERE x < 10000000
412
)
413
SELECT COUNT(*) FROM huge_series
414
""")
415
print("Query completed normally")
416
except aiosqlite.OperationalError as e:
417
if "interrupted" in str(e):
418
print("Query was interrupted")
419
else:
420
raise
421
422
# Start the query
423
query_task = asyncio.create_task(long_query())
424
425
# Interrupt after 2 seconds
426
await asyncio.sleep(2)
427
await db.interrupt()
428
429
# Wait for query to complete (either normally or with interruption)
430
await query_task
431
```