Skip to content
TheChatArchiveThe Chat Archive
grok-3-preview-02-24· xAIHugging Face Arena Expert 5K

build a aiosqlite wrapper supporting these: 1. utility method: convert *args (python…

  1. User

    build a aiosqlite wrapper supporting these:

    1. utility method: convert *args (python types like UUID, datetime, bool, json-like types (can be stringified with from commons import encode_json)) into sqlite primitives before sending to aiosqlite
    2. get_one and get_many: accept (sql: str, *args, struct: BaseModel) as parameters, also convert the fetched results based on struct with pydantic
    3. execute: accept (sql: str, *args) as parameters
    4. @transactional decorator: 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 of get_one, get_many, execute -> commit/rollback -> close connection
    5. 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
    
  2. 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:

    1. Type Conversion: The _convert_to_sqlite_type method handles conversion of Python types (UUID, datetime, bool, JSON-like types) to SQLite-compatible types.

    2. Query Methods:

      • get_one: Returns a single Pydantic model instance or None
      • get_many: Returns a list of Pydantic model instances
      • Both methods handle row-to-dict conversion and Pydantic model parsing
    3. Execute Method: Handles SQL execution with automatic commit/rollback

    4. 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
    5. Error Handling:

      • Custom SqliteDBError exception
      • Proper connection state checking
      • Automatic rollback on errors
    6. Utility Methods:

      • _convert_args: Handles batch conversion of arguments
      • _row_to_dict and _rows_to_dicts: Handle row-to-dict conversion
      • close: 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 SqliteDB instance across threads (each thread should have its own instance).

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.