Py SQLite and SQLAlchemy.py

# basic SQLite

    import sqlite3
    
    db = sqlite3.connect("books-collection.db")
    cursor = db.cursor()
    cursor.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title varchar(250) NOT NULL UNIQUE, author varchar(250) NOT NULL, rating FLOAT NOT NULL)")
    
    cursor.execute("INSERT INTO books VALUES(1, 'Harry Potter', 'J. K. Rowling', '9.3')")
    db.commit()
    
    
    

# SQLAlchemy

    
    #SQLAlchemy is defined as an ORM Object Relational Mapping library. This means that it's able to map the relationships in the database into Objects. Fields become Object properties. Tables can be defined as separate Classes and each row of data is a new Object.
    
    
    
    from flask import Flask
    from flask_sqlalchemy import SQLAlchemy
    import os

    here=os.path.dirname(os.path.abspath(__file__))
    os.chdir(here)
    
    app = Flask(__name__)
    
    ##CREATE 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)
    
    
    ##CREATE TABLE
    class Book(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)
    
        #Optional: this will allow each book object to be identified by its title when printed.
        def __repr__(self):
            return f'<Book {self.title}>'
        
    db.create_all()
    
    
    #CREATE RECORD
    new_book = Book(id=1, title="Harry Potter", author="J. K. Rowling", rating=9.3) #primariy key field is optional, as it can be auto generated
    db.session.add(new_book)
    db.session.commit()
    
    
    #Read ALL RECORDS
    all_books = db.session.query(Book).all()
    print(all_books)
    
    #Read A Particular Record By Query
    book = Book.query.filter_by(title="Harry Potter").first()
    print(book)
    
    #Update A Particular Record By Query
    book_to_update = Book.query.filter_by(title="Harry Potter").first()
    book_to_update.title = "Harry Potter and the Chamber of Secrets"
    db.session.commit()
    
    #Update A Record By PRIMARY KEY
    book_id = 1
    book_to_update = Book.query.get(book_id)
    book_to_update.title = "Harry Potter and the Goblet of Fire"
    db.session.commit()
    
    #Delete A Particular Record By PRIMARY KEY
    book_id = 1
    book_to_delete = Book.query.get(book_id)
    db.session.delete(book_to_delete)
    db.session.commit()
    
    # query with order_by example
    ordered_book = Book.query.order_by(Book.rating.desc()).all()
        
        
        
    #SQLAlchemy table relationships
    #https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html