database_manager.py

import sqlite3


class DatabaseManager:
    """
    Handles SQLite connection management and common database operations.
    Returns rows as dictionary-like sqlite3.Row objects.
    """

    def __init__(self, db_name="rides.db"):
        self.db_name = db_name
        self.conn = None

    def connect(self):
        """
        Open a database connection if one is not already open.
        Enables foreign key enforcement and dictionary-like row access.
        """
        if self.conn is None:
            self.conn = sqlite3.connect(self.db_name)
            self.conn.row_factory = sqlite3.Row
            self.conn.execute("PRAGMA foreign_keys = ON;")

    def close(self):
        """
        Close the database connection if it is open.
        """
        if self.conn is not None:
            self.conn.close()
            self.conn = None

    def execute(self, query, params=()):
        """
        Execute a query that changes the database
        (INSERT, UPDATE, DELETE, CREATE, etc.).
        Returns the cursor so caller can inspect lastrowid if needed.
        """
        self.connect()
        cursor = self.conn.cursor()
        cursor.execute(query, params)
        self.conn.commit()
        return cursor

    def fetchone(self, query, params=()):
        """
        Execute a SELECT query and return one row.
        Returns None if no row matches.
        """
        self.connect()
        cursor = self.conn.cursor()
        cursor.execute(query, params)
        return cursor.fetchone()

    def fetchall(self, query, params=()):
        """
        Execute a SELECT query and return all matching rows.
        """
        self.connect()
        cursor = self.conn.cursor()
        cursor.execute(query, params)
        return cursor.fetchall()

    def create_tables(self, schema_file="schema.sql"):
        """
        Create all tables using SQL statements stored in a schema file.
        Uses executescript because the schema file contains multiple
        CREATE TABLE statements.
        """
        self.connect()
        with open(schema_file, "r", encoding="utf-8") as f:
            schema_sql = f.read()

        self.conn.executescript(schema_sql)
        self.conn.commit()

    def row_to_dict(self, row):
        """
        Convert a sqlite3.Row into a regular Python dictionary.
        Returns None if row is None.
        """
        if row is None:
            return None
        return dict(row)

    def rows_to_dicts(self, rows):
        """
        Convert a list of sqlite3.Row objects into a list of dictionaries.
        """
        return [dict(row) for row in rows]

    def __enter__(self):
        """
        Support use in a context manager:
        with DatabaseManager() as db:
            ...
        """
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """
        Always close the connection when leaving a with-block.
        """
        self.close()