main.py

from days.day_063.files.helpers import *


def day_063():
    title("VIRTUAL BOOKSHELF")
    load_dotenv()
    template_folder_path = os.path.join(os.path.dirname(__file__), "files", "templates")
    static_folder_path = os.path.join(os.path.dirname(__file__), "files", "static")
    app = Flask(
        __name__, template_folder=template_folder_path, static_folder=static_folder_path
    )

    # CREATE DATABASE before running

    class Base(DeclarativeBase):
        pass

    app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///books.db"
    # Create the extension
    db = SQLAlchemy(model_class=Base)
    # initialise the app with the extension
    db.init_app(app)

    # CREATE TABLE
    class Book(db.Model):
        id: Mapped[int] = mapped_column(Integer, primary_key=True)
        title: Mapped[str] = mapped_column(String(250), unique=True, nullable=False)
        author: Mapped[str] = mapped_column(String(250), nullable=False)
        rating: Mapped[float] = mapped_column(Float, nullable=False)

    # Create table schema in the database. Requires application context.
    with app.app_context():
        db.create_all()

    @app.route("/")
    def home():
        # READ ALL RECORDS
        # Construct a query to select from the database. Returns the rows in the database
        result = db.session.execute(db.select(Book).order_by(Book.title))
        # Use .scalars() to get the elements rather than entire rows from the database
        all_books = result.scalars().all()
        return render_template("index.html", books=all_books)

    @app.route("/add", methods=["GET", "POST"])
    def add():
        if request.method == "POST":
            # CREATE RECORD
            new_book = Book(
                title=request.form["title"],
                author=request.form["author"],
                rating=request.form["rating"],
            )
            db.session.add(new_book)
            db.session.commit()
            return redirect(url_for("home"))
        return render_template("add.html")

    @app.route("/edit", methods=["GET", "POST"])
    def edit():
        if request.method == "POST":
            # UPDATE RECORD
            book_id = request.form["id"]
            book_to_update = db.get_or_404(Book, book_id)
            book_to_update.rating = request.form["rating"]
            db.session.commit()
            return redirect(url_for("home"))
        book_id = request.args.get("id")
        book_selected = db.get_or_404(Book, book_id)
        return render_template("edit_rating.html", book=book_selected)

    @app.route("/delete")
    def delete():
        book_id = request.args.get("id")

        # DELETE A RECORD BY ID
        book_to_delete = db.get_or_404(Book, book_id)
        # Alternative way to select the book to delete.
        # book_to_delete = db.session.execute(db.select(Book).where(Book.id == book_id)).scalar()
        db.session.delete(book_to_delete)
        db.session.commit()
        return redirect(url_for("home"))

    if __name__ == "days.day_063.main":
        app.run(debug=True, use_reloader=False)