0
# aiosqlite
1

2
Asyncio bridge to the standard sqlite3 module. Provides async/await compatible interface for SQLite database operations while maintaining compatibility with the standard sqlite3 module API patterns and enabling non-blocking database operations in async applications.
3

4
## Package Information
5

6
- **Package Name**: aiosqlite
7
- **Package Type**: pypi
8
- **Language**: Python
9
- **Installation**: `pip install aiosqlite`
10

11
## Core Imports
12

13
```python
14
import aiosqlite
15
```
16

17
For specific components:
18

19
```python
20
from aiosqlite import connect, Connection, Cursor
21
```
22

23
## Basic Usage
24

25
```python
26
import aiosqlite
27
import asyncio
28

29
async def main():
30
    # Connect using context manager (recommended)
31
    async with aiosqlite.connect("example.db") as db:
32
        # Create table
33
        await db.execute('''
34
            CREATE TABLE IF NOT EXISTS users (
35
                id INTEGER PRIMARY KEY,
36
                name TEXT NOT NULL,
37
                email TEXT
38
            )
39
        ''')
40
        
41
        # Insert data
42
        await db.execute(
43
            "INSERT INTO users (name, email) VALUES (?, ?)",
44
            ("John Doe", "john@example.com")
45
        )
46
        
47
        # Commit transaction
48
        await db.commit()
49
        
50
        # Query data using cursor
51
        async with db.execute("SELECT * FROM users") as cursor:
52
            async for row in cursor:
53
                print(f"User: {row[1]}, Email: {row[2]}")
54

55
# Run the async function
56
asyncio.run(main())
57
```
58

59
## Architecture
60

61
The library implements a thread-based architecture to prevent blocking the event loop:
62

63
- **Connection Thread**: Each connection runs in a separate thread, executing SQLite operations
64
- **Queue-based Communication**: Async methods queue operations and await results through futures
65
- **Context Managers**: Automatic resource cleanup for connections and cursors
66
- **sqlite3 Compatibility**: Full API compatibility with the standard sqlite3 module
67

68
## Capabilities
69

70
### Database Connection Management
71

72
Core connection functionality including connection creation, transaction management, and resource cleanup. Provides async context managers for automatic resource management.
73

74
```python { .api }
75
def connect(database: Union[str, Path], *, iter_chunk_size=64, **kwargs) -> Connection: ...
76
```
77

78
[Connection Management](./connection.md)
79

80
### Query Execution and Data Retrieval
81

82
Comprehensive query execution methods including single queries, batch operations, and data fetching patterns. Supports all standard SQL operations with async/await syntax.
83

84
```python { .api }
85
async def execute(self, sql: str, parameters: Optional[Iterable[Any]] = None) -> Cursor: ...
86
async def executemany(self, sql: str, parameters: Iterable[Iterable[Any]]) -> Cursor: ...
87
async def fetchone(self) -> Optional[sqlite3.Row]: ...
88
async def fetchall(self) -> Iterable[sqlite3.Row]: ...
89
```
90

91
[Query Operations](./queries.md)
92

93
### Transaction and State Management
94

95
Transaction control, isolation levels, and connection state management. Includes commit, rollback operations and transaction properties.
96

97
```python { .api }
98
async def commit(self) -> None: ...
99
async def rollback(self) -> None: ...
100
@property
101
def in_transaction(self) -> bool: ...
102
@property  
103
def isolation_level(self) -> Optional[str]: ...
104
```
105

106
[Transactions](./transactions.md)
107

108
### Advanced Database Features
109

110
Extended SQLite functionality including user-defined functions, database backups, progress handlers, and extension loading.
111

112
```python { .api }
113
async def create_function(self, name: str, num_params: int, func: Callable, deterministic: bool = False) -> None: ...
114
async def backup(self, target: Union[Connection, sqlite3.Connection], **kwargs) -> None: ...
115
async def iterdump(self) -> AsyncIterator[str]: ...
116
```
117

118
[Advanced Features](./advanced.md)
119

120
## Constants and Utilities
121

122
Package metadata and SQLite configuration constants re-exported from the sqlite3 module.
123

124
```python { .api }
125
__version__: str  # Package version string (e.g., "0.21.0")
126
paramstyle: str  # Parameter style supported by sqlite3 ("qmark")
127
sqlite_version: str  # SQLite library version string
128
sqlite_version_info: tuple[int, int, int]  # SQLite version as tuple (major, minor, micro)
129
```
130

131
### Type Adapters and Converters
132

133
Functions for registering custom type handling between Python and SQLite.
134

135
```python { .api }
136
def register_adapter(type_: type, adapter: Callable) -> None:
137
    """
138
    Register an adapter callable for a Python type.
139
    
140
    Parameters:
141
    - type_: Python type to register adapter for
142
    - adapter: Function to convert Python type to SQLite-compatible type
143
    """
144

145
def register_converter(typename: str, converter: Callable) -> None:
146
    """
147
    Register a converter callable for a database type.
148
    
149
    Parameters:
150
    - typename: SQLite type name to register converter for  
151
    - converter: Function to convert SQLite type to Python type
152
    """
153
```
154

155
Usage example:
156

157
```python
158
import aiosqlite
159
import json
160
from datetime import datetime
161

162
# Register JSON adapter/converter
163
def adapt_json(obj):
164
    return json.dumps(obj).encode('utf-8')
165

166
def convert_json(s):
167
    return json.loads(s.decode('utf-8'))
168

169
aiosqlite.register_adapter(dict, adapt_json)
170
aiosqlite.register_converter("json", convert_json)
171

172
# Register datetime adapter/converter  
173
def adapt_datetime(dt):
174
    return dt.isoformat()
175

176
def convert_datetime(s):
177
    return datetime.fromisoformat(s.decode('utf-8'))
178

179
aiosqlite.register_adapter(datetime, adapt_datetime)
180
aiosqlite.register_converter("timestamp", convert_datetime)
181
```
182

183
## Types
184

185
```python { .api }
186
class Connection:
187
    """Async SQLite database connection."""
188
    
189
class Cursor:
190
    """Async SQLite database cursor."""
191

192
class Row:
193
    """
194
    Dict-like row object for query results (re-exported from sqlite3).
195
    
196
    Provides both index-based and name-based access to column values.
197
    Available when using sqlite3.Row as row_factory.
198
    """
199
    def __getitem__(self, key: Union[int, str]) -> Any: ...
200
    def keys(self) -> list[str]: ...
201

202
# Type aliases
203
IsolationLevel = Optional[Literal["DEFERRED", "IMMEDIATE", "EXCLUSIVE"]]
204
```
205

206
## Exceptions
207

208
All exceptions are re-exported from the sqlite3 module:
209

210
```python { .api }
211
# Base exceptions
212
class Error(Exception): ...
213
class Warning(Exception): ...
214
class DatabaseError(Error): ...
215

216
# Specific exceptions  
217
class IntegrityError(DatabaseError): ...
218
class ProgrammingError(DatabaseError): ...
219
class OperationalError(DatabaseError): ...
220
class NotSupportedError(DatabaseError): ...
221
```