databasesupasafe

dbhandler

A lightweight Python package for managing SQLite, PostgreSQL, and MySQL databases — with a fluent query builder and an ORM-style model layer.

Installation

# SQLite only (no extra dependencies)
pip install .

# With PostgreSQL support
pip install ".[postgresql]"

# With MySQL support
pip install ".[mysql]"

# Everything
pip install ".[all]"

# Development tools
pip install ".[dev]"

Quick Start

DBHandler — direct SQL

from dbhandler import DBHandler

# SQLite (in-memory)
with DBHandler("sqlite", database=":memory:") as db:
    db.execute("""
        CREATE TABLE users (
            id    INTEGER PRIMARY KEY AUTOINCREMENT,
            name  TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE,
            age   INTEGER DEFAULT 0
        )
    """)

    # Insert a single row
    db.insert("users", {"name": "Alice", "email": "[e-mail]", "age": 30})

    # Insert many rows
    db.insert_many("users", [
        {"name": "Bob",   "email": "[e-mail]",   "age": 25},
        {"name": "Carol", "email": "[e-mail]", "age": 35},
    ])

    # Select with a WHERE clause
    adults = db.select("users", where="age >= ?", params=(30,), order_by="name ASC")
    for user in adults:
        print(user["name"], user["age"])

    # Update
    db.update("users", {"age": 31}, where="name = ?", where_params=("Alice",))

    # Delete
    db.delete("users", where="name = ?", params=("Bob",))

    # Raw fetch
    row = db.fetchone("SELECT * FROM users WHERE email = ?", ("[e-mail]",))
    print(row)

PostgreSQL / MySQL

db = DBHandler(
    "postgresql",
    host="localhost",
    port=5432,
    database="mydb",
    user="admin",
    password="secret",
)
db.connect()
# ... same API as SQLite ...
db.disconnect()

QueryBuilder — fluent SQL construction

from dbhandler import DBHandler, QueryBuilder

with DBHandler("sqlite", database="app.db") as db:

    # SELECT
    sql, params = (
        QueryBuilder("users")
        .select("id", "name", "email")
        .where("age > ?", 18)
        .order_by("name ASC")
        .limit(10)
        .build()
    )
    rows = db.fetchall(sql, params)

    # INSERT
    sql, params = QueryBuilder("users").insert(name="Dave", email="[e-mail]", age=22).build()
    db.execute(sql, params)

    # UPDATE
    sql, params = (
        QueryBuilder("users")
        .update(email="[e-mail]")
        .where("name = ?", "Dave")
        .build()
    )
    db.execute(sql, params)

    # DELETE
    sql, params = QueryBuilder("users").delete().where("id = ?", 99).build()
    db.execute(sql, params)

    db.commit()

BaseModel — ORM-style interface

from dbhandler import DBHandler, BaseModel
from dbhandler.models import Field

class User(BaseModel):
    __table__ = "users"

    id    = Field(int, primary_key=True)
    name  = Field(str, nullable=False)
    email = Field(str, nullable=False, unique=True)
    age   = Field(int, default=0)

db = DBHandler("sqlite", database="app.db")
db.connect()

User.__db__ = db
User.create_table()

# Create
alice = User(name="Alice", email="[e-mail]", age=30)
alice.save()

# Read
all_users = User.all()
alice     = User.get(id=1)
adults    = User.filter("age >= ?", (18,))
total     = User.count()

# Update
alice.age = 31
alice.save()

# Delete
alice.delete()

db.disconnect()

Transactions

with DBHandler("sqlite", database="app.db") as db:
    with db.transaction():
        db.insert("orders", {"user_id": 1, "total": 99.99})
        db.insert("order_items", {"order_id": 1, "product_id": 42, "qty": 2})
    # committed automatically; rolled back on any exception

Schema Utilities

db.table_exists("users")       # → True / False
db.get_tables()                # → ["users", "orders", ...]
db.get_columns("users")        # → [{"name": "id", "type": "INTEGER", ...}, ...]

Running Tests

pip install ".[dev]"
pytest tests/ -v --cov=dbhandler

License

MIT