Skip to main content

​
Introduction

The database module provides async database operations for the CS Library Kiosk system. It uses SQLite with bcrypt for password hashing and provides a clean async interface for all database operations.

​
Database Connection

The module uses a centralized connection pattern with SQLite: 
import sqlite3
from pathlib import Path

DB_PATH = Path(__file__).parent / "cs_library.db"

def _connect() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn

​
Key Features

  • Row Factory: Returns rows as sqlite3.Row objects for dict-like access
  • Foreign Keys: Enforces referential integrity with PRAGMA foreign_keys = ON
  • Async Execution: All blocking SQLite calls run off the event loop thread

​
Async/Await Pattern

All database operations are async to prevent blocking the event loop: 
async def _run(fn):
    """Run a blocking SQLite call off the event loop thread."""
    loop = asyncio.get_event_loop()
    return await loop.run_in_executor(None, fn)
This ensures the UI remains responsive during database operations.

​
Database Initialization

def init_db() -> None:
    """Initialize database schema and create tables if they don't exist."""

​
Returns

This function returns None and is called once at application startup.

​
Schema Details

The init_db() function creates three tables:

​
Users Table

CREATE TABLE IF NOT EXISTS users (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id    TEXT    NOT NULL UNIQUE,
    name          TEXT    NOT NULL,
    email         TEXT    NOT NULL UNIQUE COLLATE NOCASE,
    password_hash TEXT    NOT NULL,
    active        INTEGER NOT NULL DEFAULT 1,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP
);
​
id
INTEGER
Primary key, auto-incremented
​
student_id
TEXT
Unique student identifier used for barcode scanner login
​
name
TEXT
Full name of the user
​
email
TEXT
Email address (case-insensitive unique constraint)
​
password_hash
TEXT
Bcrypt-hashed password
​
active
INTEGER
Account status (1 = active, 0 = inactive)

​
Books Table

CREATE TABLE IF NOT EXISTS books (
    isbn    TEXT PRIMARY KEY,
    title   TEXT NOT NULL,
    author  TEXT NOT NULL,
    cover   TEXT NOT NULL DEFAULT "",
    status  TEXT NOT NULL DEFAULT "Available",
    shelf   TEXT NOT NULL DEFAULT ""
);
​
isbn
TEXT
Primary key, ISBN-10 or ISBN-13 identifier
​
title
TEXT
Book title
​
author
TEXT
Author name
​
cover
TEXT
URL to book cover image
​
status
TEXT
Either “Available” or “Checked Out”
​
shelf
TEXT
Physical shelf location (optional)

​
Loans Table

CREATE TABLE IF NOT EXISTS loans (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id       INTEGER NOT NULL REFERENCES users(id),
    isbn          TEXT    NOT NULL REFERENCES books(isbn),
    checked_out   DATETIME DEFAULT CURRENT_TIMESTAMP,
    due_date      DATETIME NOT NULL,
    returned      INTEGER  NOT NULL DEFAULT 0,
    returned_date DATETIME
);
​
id
INTEGER
Primary key, auto-incremented
​
user_id
INTEGER
Foreign key to users.id
​
isbn
TEXT
Foreign key to books.isbn
​
checked_out
DATETIME
Timestamp when book was checked out
​
due_date
DATETIME
When the book is due back (14 days from checkout)
​
returned
INTEGER
Return status (0 = active loan, 1 = returned)
​
returned_date
DATETIME
Timestamp when book was returned (NULL if not returned)

​
Configuration

​
Database Path

The SQLite database file is created in the same directory as the module: 
DB_PATH = Path(__file__).parent / "cs_library.db"

​
Open Library API

Toggle live Open Library lookups: 
USE_LIVE_API = True  # Set False for offline/testing mode
When False, only books already in the local database are available.

​
Error Handling

The module uses Python’s Optional type hints to indicate when operations may fail:
  • Functions returning Optional[dict] return None on failure
  • Functions returning bool return False on failure
  • IntegrityError exceptions are caught for duplicate key violations

Build docs developers (and LLMs) love

Get started for freeTalk to us