learning from 100 Days of Code: The Complete Python Pro Bootcamp for 2022

SQLite3 model in Python

import sqlite3
db = sqlite3.connect("books-connection.db")

# cursor: modify SQLite database
cursor = db.cursor()

# Create a new table
cursor.execute(
    "CREATE TABLE books ("
    "id INTEGER PRIMARY KEY, "
    "title varchar(250) NOT NULL UNIQUE, "
    "author varchar(250) NOT NULL, "
    "rating FLOAT NOT NULL)")

# Add a new data
cursor.execute(
    "INSERT INTO books "
    "VALUES(1, 'Harry Potter', 'J. K. Rowling', '9.3')")
db.commit()

Flask - SQLAlchemy

00. Install & Connect to / Create a new database

$ pip install -U Flask-SQLAlchemy

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///new-books-collection.db'
db = SQLAlchemy(app)

01. Create a new table

class Books(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(250), unique=True, nullable=False)
    author = db.Column(db.String(250), nullable=False)
    rating = db.Column(db.Float, nullable=False)

    def __repr__(self):
        return '<Books %r>' % self.title

db.create_all()

02. Create A new Record

new_book = Books(title='Harry Potter2', author='J. K. Rowling', rating=9.3)
db.session.add(new_book)
db.session.commit()

03. Read All Records

all_books = db.session.query(Books).all()
all_books = Books.query.all()
print(all_books)

04. Read A Particular Record By Query

# book = Books.query.filter_by(title="Harry Potter2").first()
# print(book)

05. Update A Particular Record By Query

book_to_update = Books.query.filter_by(title="Harry Potter").first()
book_to_update.title = "Harry Potter and the Chamber of Secrets"
db.session.commit()

06. Update A Record By PRIMARY KEY

book_id = 1
book_to_update = Books.query.get(book_id)
book_to_update.title = "Harry Potter and the Goblet of Fire"
db.session.commit()

07. Delete A Particular Record By PRIMARY KEY

book_id = 1
book_to_delete = Books.query.get(book_id)
db.session.delete(book_to_delete)
db.session.commit()

Book Shelf Project

main.py

from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# ---------- SQLAlchemy - Create a new database ---------- #
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///new-books-collection.db'
# Optional: But it will silence the deprecation warning in the console
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)


# ---------- SQLAlchemy - Create a new table ---------- #
class Books(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(250), unique=True, nullable=False)
    author = db.Column(db.String(250), nullable=False)
    rating = db.Column(db.Float, nullable=False)

    def __repr__(self):
        return '<Books %r>' % self.title


@app.route('/')
def home():
    all_books = db.session.query(Books).all()
    return render_template("index.html", books=all_books)


@app.route("/add", methods=["GET", "POST"])
def add():
    if request.method == "POST":
        new_book = Books(
            title=request.form.get("title"),
            author=request.form.get("author"),
            rating=request.form.get("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_rating():
    updating_id = request.args.get('id')
    book_to_update = Books.query.get(updating_id)

    if request.method == "POST":
        book_to_update.rating = request.form.get('new_rating')
        db.session.commit()
        return redirect(url_for('home'))

    return render_template("edit_rating.html", book=book_to_update)


@app.route('/delete')
def delete():
    deleting_id = request.args.get('id')
    book_to_delete = Books.query.get(deleting_id)
    db.session.delete(book_to_delete)
    db.session.commit()
    return redirect(url_for('home'))


if __name__ == "__main__":
    app.run(debug=True)

index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Library</title>
</head>
<body>
<h1>My Library</h1>
{% if books == []: %}
<p>Library is empty.</p>
{% else: %}
<ul>
    {% for book in books: %}
    <li>
        <a href="{{ url_for('delete', id=book.id) }}">Delete</a>
        {{ book.title }} - {{ book.author }} - {{ book.rating }}/10
        <a href="{{ url_for('edit_rating', id=book.id ) }}">Edit Rating</a>
    </li>
    {% endfor %}
    {% endif %}
</ul>

<a href="{{ url_for('add') }}">Add New Book</a>
</body>
</html>

add.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Add Book</title>
</head>
<body>
    <form action=" {{ url_for('add') }} " method="POST">
        <label>Book Name</label>
        <input name="title" type="text">
        <label>Book Author</label>
        <input name="author" type="text">
        <label>Rating</label>
        <input name="rating" type="text">
        <button type="submit">Add Book</button>
    </form>
</body>
</html>

edit_rating.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Edit Rating</title>
</head>
<body>
<h2>Book Name: {{book.title}}</h2>
<p>Current Rating: {{book.rating}}</p>

<form action="{{ url_for('edit_rating', id=book.id ) }}" method="POST">
    <input type="text" name="new_rating" placeholder="New Rating">
    <button type="submit">Change Rating</button>
</form>
</body>
</html>