build a aiosqlite wrapper supporting these: 1. utility method: convert *args (python…
- User
build a aiosqlite wrapper supporting these:
- utility method: convert
*args(python types like UUID, datetime, bool, json-like types (can be stringified withfrom commons import encode_json)) into sqlite primitives before sending to aiosqlite get_oneandget_many: accept(sql: str, *args, struct: BaseModel)as parameters, also convert the fetched results based onstructwith pydanticexecute: accept(sql: str, *args)as parameters@transactionaldecorator: wrap an async function with this preserving original function signature. use the decorator as a side effect to spawn a new connection dedicated for that transaction. start new connection -> sequences ofget_one,get_many,execute-> commit/rollback -> close connection- encapsulate everything in a class:
T = TypeVar("T", bound=BaseModel) class SqliteDB: def __init__(self, path: str): self.path = path self.conn = None async def create(self): self.conn = await connect(self.path) self.conn.row_factory = Row async def get_one(self, sql, *args, struct: Type[T]) -> T | None: ... async def get_many(self, sql, *args, struct: Type[T]) -> list[T]: ... async def execute(self, sql, *args): ... def transactional(self, func: Callable[..., Awaitable]) -> Callable[..., Awaitable]: ... # feel free to add any utility classes or functions - utility method: convert
- Assistant
Here's a complete implementation of the aiosqlite wrapper with all the requested functionality. I've added type hints, error handling, and utility methods to make it robust and production-ready:
import aiosqlite from typing import TypeVar, Type, Callable, Awaitable, Any, Optional, List from pydantic import BaseModel from datetime import datetime import uuid import json from functools import wraps from aiosqlite import Row, Connection # Assuming this is your JSON encoder from commons def encode_json(obj: Any) -> str: return json.dumps(obj) T = TypeVar("T", bound=BaseModel) class SqliteDBError(Exception): """Custom exception for database errors""" pass class SqliteDB: def __init__(self, path: str): self.path = path self.conn: Optional[Connection] = None async def create(self) -> None: """Initialize the database connection""" try: self.conn = await aiosqlite.connect(self.path) self.conn.row_factory = Row except aiosqlite.Error as e: raise SqliteDBError(f"Failed to create database connection: {str(e)}") async def close(self) -> None: """Close the database connection""" if self.conn: await self.conn.close() self.conn = None def _convert_to_sqlite_type(self, value: Any) -> Any: """Convert Python types to SQLite-compatible types""" if isinstance(value, uuid.UUID): return str(value) elif isinstance(value, datetime): return value.isoformat() elif isinstance(value, bool): return 1 if value else 0 elif isinstance(value, (dict, list)): return encode_json(value) return value def _convert_args(self, args: tuple) -> tuple: """Convert all arguments to SQLite-compatible types""" return tuple(self._convert_to_sqlite_type(arg) for arg in args) def _row_to_dict(self, row: Row) -> dict: """Convert a SQLite row to a dictionary""" return dict(row) if row else {} def _rows_to_dicts(self, rows: list[Row]) -> list[dict]: """Convert multiple SQLite rows to list of dictionaries""" return [self._row_to_dict(row) for row in rows] async def get_one(self, sql: str, *args, struct: Type[T]) -> Optional[T]: """Execute a query and return a single result parsed into the specified Pydantic model""" if not self.conn: raise SqliteDBError("Database connection not initialized") try: converted_args = self._convert_args(args) async with self.conn.execute(sql, converted_args) as cursor: row = await cursor.fetchone() if row is None: return None return struct(**self._row_to_dict(row)) except aiosqlite.Error as e: raise SqliteDBError(f"Query execution failed: {str(e)}") async def get_many(self, sql: str, *args, struct: Type[T]) -> List[T]: """Execute a query and return multiple results parsed into the specified Pydantic model""" if not self.conn: raise SqliteDBError("Database connection not initialized") try: converted_args = self._convert_args(args) async with self.conn.execute(sql, converted_args) as cursor: rows = await cursor.fetchall() return [struct(**row_dict) for row_dict in self._rows_to_dicts(rows)] except aiosqlite.Error as e: raise SqliteDBError(f"Query execution failed: {str(e)}") async def execute(self, sql: str, *args) -> None: """Execute a SQL statement with the given arguments""" if not self.conn: raise SqliteDBError("Database connection not initialized") try: converted_args = self._convert_args(args) await self.conn.execute(sql, converted_args) await self.conn.commit() except aiosqlite.Error as e: await self.conn.rollback() raise SqliteDBError(f"Execute failed: {str(e)}") def transactional(self, func: Callable[..., Awaitable]) -> Callable[..., Awaitable]: """Decorator to wrap a function in a database transaction with a dedicated connection""" @wraps(func) async def wrapper(*args, **kwargs): # Create a new database instance for the transaction tx_db = SqliteDB(self.path) try: await tx_db.create() # Replace the original db instance in args/kwargs if present new_args = [] for arg in args: if isinstance(arg, SqliteDB): new_args.append(tx_db) else: new_args.append(arg) new_kwargs = {} for k, v in kwargs.items(): if isinstance(v, SqliteDB): new_kwargs[k] = tx_db else: new_kwargs[k] = v # Execute the wrapped function result = await func(*new_args, **new_kwargs) # Commit the transaction if tx_db.conn: await tx_db.conn.commit() return result except Exception as e: # Rollback on any error if tx_db.conn: await tx_db.conn.rollback() raise finally: # Always close the transaction connection await tx_db.close() return wrapper # Example usage: if __name__ == "__main__": from pydantic import BaseModel import asyncio class User(BaseModel): id: int name: str created_at: str is_active: bool metadata: dict async def example(): # Initialize main database db = SqliteDB(":memory:") await db.create() # Create a sample table await db.execute(""" CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT, created_at TEXT, is_active INTEGER, metadata TEXT ) """) # Example of transactional operation @db.transactional async def create_user(tx_db: SqliteDB, name: str, metadata: dict): await tx_db.execute( "INSERT INTO users (name, created_at, is_active, metadata) VALUES (?, ?, ?, ?)", name, datetime.now(), True, metadata ) user = await tx_db.get_one( "SELECT * FROM users WHERE name = ?", name, struct=User ) return user # Use the transactional function try: user = await create_user( name="John Doe", metadata={"role": "admin"} ) print(f"Created user: {user}") # Get multiple users users = await db.get_many("SELECT * FROM users", struct=User) print(f"All users: {users}") except SqliteDBError as e: print(f"Database error: {e}") finally: await db.close() asyncio.run(example())This implementation includes:
-
Type Conversion: The
_convert_to_sqlite_typemethod handles conversion of Python types (UUID, datetime, bool, JSON-like types) to SQLite-compatible types. -
Query Methods:
get_one: Returns a single Pydantic model instance or Noneget_many: Returns a list of Pydantic model instances- Both methods handle row-to-dict conversion and Pydantic model parsing
-
Execute Method: Handles SQL execution with automatic commit/rollback
-
Transactional Decorator:
- Creates a new database connection for each transaction
- Handles commit/rollback automatically
- Preserves the original function signature
- Replaces the original database instance in args/kwargs with the transaction-specific one
-
Error Handling:
- Custom
SqliteDBErrorexception - Proper connection state checking
- Automatic rollback on errors
- Custom
-
Utility Methods:
_convert_args: Handles batch conversion of arguments_row_to_dictand_rows_to_dicts: Handle row-to-dict conversionclose: Proper connection cleanup
The example usage demonstrates how to use all the features, including the transactional decorator, type conversion, and Pydantic model parsing.
To use this in production, you might want to add:
- Connection pooling
- More sophisticated error handling
- Logging
- Connection timeout handling
- Additional type conversions as needed
- Connection retry logic
The implementation is thread-safe as long as you don't share the same
SqliteDBinstance across threads (each thread should have its own instance). -
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.