SQLAlchemy: Python & Databases

python -m pip install sqlalchemy
# For PostgreSQL later: python -m pip install "psycopg[binary]"

from datetime import datetime
from sqlalchemy import DateTime, String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column

class Base(DeclarativeBase):
    pass

class Task(Base):
    __tablename__ = "tasks"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200))
    done: Mapped[bool] = mapped_column(default=False)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.now)

engine = create_engine("sqlite:///tasks.db", echo=False)
Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add_all([Task(title="Learn SQLAlchemy"), Task(title="Build an app")])
    session.commit()

with Session(engine) as session:
    statement = select(Task).where(Task.done.is_(False)).order_by(Task.created_at)
    for task in session.scalars(statement):
        print(task.id, task.title)

def complete_task(task_id: int) -> bool:
    with Session(engine) as session:
        task = session.get(Task, task_id)
        if task is None:
            return False
        task.done = True
        session.commit()
        return True

def delete_completed() -> int:
    with Session(engine) as session:
        completed = list(session.scalars(select(Task).where(Task.done.is_(True))))
        for task in completed:
            session.delete(task)
        session.commit()
        return len(completed)