Transactions

async def commit(self) -> None:
    """
    Commit the current transaction.
    
    Saves all changes made since the last commit or rollback.
    If not in a transaction, this method has no effect.
    After commit, a new transaction may be started automatically
    depending on the isolation_level setting.
    """

async def rollback(self) -> None:
    """
    Roll back the current transaction.
    
    Discards all changes made since the last commit or rollback.
    If not in a transaction, this method has no effect.
    After rollback, a new transaction may be started automatically
    depending on the isolation_level setting.
    """

import aiosqlite

async def transfer_funds(from_account: int, to_account: int, amount: float):
    async with aiosqlite.connect("bank.db") as db:
        try:
            # Start transaction (automatic with first statement)
            await db.execute(
                "UPDATE accounts SET balance = balance - ? WHERE id = ?",
                (amount, from_account)
            )
            
            await db.execute(
                "UPDATE accounts SET balance = balance + ? WHERE id = ?", 
                (amount, to_account)
            )
            
            # Check if first account has sufficient funds
            async with db.execute(
                "SELECT balance FROM accounts WHERE id = ?", 
                (from_account,)
            ) as cursor:
                row = await cursor.fetchone()
                if row and row[0] < 0:
                    raise ValueError("Insufficient funds")
            
            # Commit the transaction
            await db.commit()
            print("Transfer completed successfully")
            
        except Exception as e:
            # Rollback on any error
            await db.rollback()
            print(f"Transfer failed: {e}")
            raise

@property
def in_transaction(self) -> bool:
    """
    True if connection has an uncommitted transaction.
    
    Indicates whether there are pending changes that have not been
    committed or rolled back. Useful for checking transaction state
    before performing operations.
    
    Returns:
    bool: True if in transaction, False otherwise
    """

async with aiosqlite.connect("database.db") as db:
    print(f"In transaction: {db.in_transaction}")  # False initially
    
    # Start a transaction by executing a statement
    await db.execute("INSERT INTO users (name) VALUES (?)", ("Alice",))
    print(f"In transaction: {db.in_transaction}")  # True after INSERT
    
    # Commit to end transaction
    await db.commit()
    print(f"In transaction: {db.in_transaction}")  # False after commit

@property
def isolation_level(self) -> Optional[str]:
    """
    Current isolation level setting.
    
    Controls how transactions interact with concurrent operations:
    - None: Autocommit mode (no transactions)
    - "DEFERRED": Transaction starts on first read/write
    - "IMMEDIATE": Transaction starts immediately with shared lock
    - "EXCLUSIVE": Transaction starts immediately with exclusive lock
    
    Returns:
    Optional[str]: Current isolation level or None for autocommit
    """

@isolation_level.setter
def isolation_level(self, value: IsolationLevel) -> None:
    """
    Set isolation level for transactions.
    
    Parameters:
    - value: Isolation level - "DEFERRED", "IMMEDIATE", "EXCLUSIVE", or None
    """

async with aiosqlite.connect("database.db") as db:
    # Check default isolation level
    print(f"Default isolation: {db.isolation_level}")  # Usually "DEFERRED"
    
    # Set autocommit mode (no transactions)
    db.isolation_level = None
    await db.execute("INSERT INTO users (name) VALUES (?)", ("Bob",))
    # No need to commit in autocommit mode
    
    # Set immediate locking for critical operations
    db.isolation_level = "IMMEDIATE" 
    await db.execute("UPDATE accounts SET balance = balance + 100")
    await db.commit()
    
    # Set exclusive locking for maintenance operations
    db.isolation_level = "EXCLUSIVE"
    await db.execute("VACUUM")
    await db.commit()

@property
def row_factory(self) -> Optional[type]:
    """
    Current row factory for query results.
    
    Controls how result rows are constructed:
    - None: Return rows as tuples (default)
    - sqlite3.Row: Return rows as dict-like objects with column access
    - Custom factory: User-defined row processing function
    
    Returns:
    Optional[type]: Current row factory class or None
    """

@row_factory.setter  
def row_factory(self, factory: Optional[type]) -> None:
    """
    Set row factory for query results.
    
    Parameters:
    - factory: Row factory class (e.g., sqlite3.Row) or None for tuples
    """

@property
def text_factory(self) -> Callable[[bytes], Any]:
    """
    Current text factory for handling TEXT columns.
    
    Controls how TEXT column values are decoded from bytes:
    - str (default): Decode as UTF-8 strings  
    - bytes: Return raw bytes
    - Custom factory: User-defined text processing function
    
    Returns:
    Callable: Function to convert bytes to text
    """

@text_factory.setter
def text_factory(self, factory: Callable[[bytes], Any]) -> None:
    """
    Set text factory for TEXT column handling.
    
    Parameters:
    - factory: Function to convert bytes to desired text type
    """

import sqlite3
import aiosqlite

async with aiosqlite.connect("database.db") as db:
    # Use sqlite3.Row for dict-like access to columns
    db.row_factory = sqlite3.Row
    
    async with db.execute("SELECT id, name, email FROM users LIMIT 1") as cursor:
        row = await cursor.fetchone()
        if row:
            print(f"ID: {row['id']}")        # Access by column name
            print(f"Name: {row['name']}")    # Dict-like access
            print(f"Email: {row[2]}")        # Still supports index access
    
    # Custom row factory for specific processing
    def dict_factory(cursor, row):
        return {description[0]: value 
                for description, value in zip(cursor.description, row)}
    
    db.row_factory = dict_factory
    
    # Configure text handling (usually not needed)
    db.text_factory = str  # Default UTF-8 decoding

@property  
def total_changes(self) -> int:
    """
    Total number of database changes since connection opened.
    
    Includes all INSERT, UPDATE, and DELETE operations.
    Useful for monitoring database activity and change volume.
    
    Returns:
    int: Total change count since connection creation
    """

async with aiosqlite.connect("database.db") as db:
    initial_changes = db.total_changes
    print(f"Initial changes: {initial_changes}")
    
    # Perform some operations
    await db.execute("INSERT INTO users (name) VALUES (?)", ("Charlie",))
    await db.execute("UPDATE users SET name = ? WHERE name = ?", ("Charles", "Charlie"))
    await db.execute("DELETE FROM users WHERE name = ?", ("OldUser",))
    await db.commit()
    
    final_changes = db.total_changes
    print(f"Changes made: {final_changes - initial_changes}")
    print(f"Total changes: {final_changes}")

try:
    async with aiosqlite.connect("database.db") as db:
        await db.execute("BEGIN IMMEDIATE")  # Explicit transaction start
        # ... transaction operations
        await db.commit()
        
except aiosqlite.OperationalError as e:
    if "database is locked" in str(e):
        # Handle database lock contention
        await asyncio.sleep(0.1)  # Brief backoff
        # Retry logic here
    else:
        raise
        
except aiosqlite.IntegrityError as e:
    # Handle constraint violations
    await db.rollback()  # Explicit rollback
    print(f"Data integrity error: {e}")