Database API

def connect(path, adapters=None, adapter_kwargs=None, safe=False, isolation_level=None, apsw_connection_kwargs=None, schema="main"):
    """
    Create a database connection.
    
    Parameters:
    - path (str): Database path (":memory:" for in-memory database)
    - adapters (list, optional): List of adapter entry point names to enable
    - adapter_kwargs (dict, optional): Configuration for specific adapters
    - safe (bool): Enable safe mode (limited SQL operations, default: False)
    - isolation_level (str, optional): Transaction isolation level
    - apsw_connection_kwargs (dict, optional): Additional APSW connection parameters
    - schema (str): Database schema name (default: "main")
    
    Returns:
    APSWConnection: Database connection object
    """

from shillelagh.backends.apsw.db import connect

# Basic connection (in-memory database)
connection = connect(":memory:")

# Connection with specific adapters
connection = connect(
    ":memory:",
    adapters=['CSVFile', 'GSheetsAPI'], 
    adapter_kwargs={
        'gsheetsapi': {'service_account_file': '/path/to/creds.json'}
    }
)

# Safe mode connection (restricted SQL)
safe_connection = connect(":memory:", safe=True)

class Connection:
    """Database connection."""
    
    def cursor(self):
        """
        Return a new Cursor object using the connection.
        
        Returns:
        Cursor object
        """
    
    def execute(self, operation, parameters=None):
        """
        Execute a query on a cursor.
        
        Parameters:
        - operation (str): SQL query string
        - parameters (tuple, optional): Query parameters
        
        Returns:
        Cursor object with results
        """
    
    def close(self):
        """Close the connection."""
    
    def commit(self):
        """Commit any pending transaction to the database."""
    
    def rollback(self):
        """Rollback any transactions."""
    
    # Context manager support
    def __enter__(self): ...
    def __exit__(self, exc_type, exc_val, exc_tb): ...

class Cursor:
    """Connection cursor."""
    
    # Cursor properties
    arraysize: int  # Number of rows to fetch with fetchmany() (default: 1)
    description: tuple  # Column description after execute()
    rowcount: int  # Number of affected/returned rows
    
    def execute(self, operation, parameters=None):
        """
        Execute a query using the cursor.
        
        Parameters:
        - operation (str): SQL query string  
        - parameters (tuple, optional): Query parameters using ? placeholders
        
        Returns:
        Cursor object (self)
        """
    
    def executemany(self, operation, seq_of_parameters=None):
        """
        Execute multiple statements.
        Currently not supported - raises NotSupportedError.
        """
    
    def fetchone(self):
        """
        Fetch the next row of a query result set.
        
        Returns:
        tuple: Single row as tuple, or None when no more data available
        """
    
    def fetchmany(self, size=None):
        """
        Fetch the next set of rows of a query result.
        
        Parameters:
        - size (int, optional): Number of rows to fetch (defaults to arraysize)
        
        Returns:
        list: List of tuples, empty list when no more rows available
        """
    
    def fetchall(self):
        """
        Fetch all remaining rows of a query result.
        
        Returns:
        list: List of tuples containing all remaining rows
        """
    
    def close(self):
        """Close the cursor."""
    
    def setinputsizes(self, sizes):
        """Predefine memory areas for parameters (not supported)."""
    
    def setoutputsizes(self, sizes):
        """Set column buffer size for large columns (not supported)."""
    
    # Iterator support
    def __iter__(self): ...
    def __next__(self): ...

# API compliance level
apilevel = "2.0"

# Thread safety level (2 = threads may share module and connections)
threadsafety = 2

# Parameter style for SQL queries (qmark = ? placeholders)
paramstyle = "qmark"

def Binary(value):
    """Construct binary value."""

def Date(year, month, day):
    """Construct date value."""

def Time(hour, minute, second):
    """Construct time value."""

def Timestamp(year, month, day, hour, minute, second):
    """Construct timestamp value."""

def DateFromTicks(ticks):
    """Construct date from timestamp."""

def TimeFromTicks(ticks):
    """Construct time from timestamp."""

def TimestampFromTicks(ticks):
    """Construct timestamp from timestamp."""

STRING: str     # String column type
BINARY: str     # Binary column type  
NUMBER: str     # Numeric column type
DATETIME: str   # DateTime column type
ROWID: str      # Row ID column type

from shillelagh.backends.apsw.db import connect

# Create connection and cursor
connection = connect(":memory:")
cursor = connection.cursor()

# Execute query
cursor.execute("SELECT * FROM 'data.csv' WHERE age > ?", (25,))

# Fetch results
for row in cursor.fetchall():
    print(row)

# Clean up
cursor.close()
connection.close()

from shillelagh.backends.apsw.db import connect

with connect(":memory:") as connection:
    cursor = connection.cursor()
    cursor.execute("SELECT count(*) FROM 'https://api.example.com/data'")
    count = cursor.fetchone()[0]
    print(f"Total rows: {count}")
    # Connection automatically closed

from shillelagh.backends.apsw.db import connect

connection = connect(":memory:")
try:
    cursor = connection.cursor()
    cursor.execute("INSERT INTO memory_table VALUES (1, 'test')")
    cursor.execute("UPDATE memory_table SET value = 'updated' WHERE id = 1")
    connection.commit()  # Commit changes
except Exception as e:
    connection.rollback()  # Rollback on error
    print(f"Error: {e}")
finally:
    connection.close()