how to create an SQLite database and how to create, read, update and delete data in the database.

We'll also be hooking up our database with a Flask application to serve data whenever needed.

DATA BASE

MODEL
1. Relational: mainstream

• Relational DBMS: Oracle, MySQL, PostgreSQL, SQLite

2. Non-Relational: since 2010 for other forms of data

• Document store: MongoDB
• Key-value store: Redis

To figure out the similarity & differences, strength & weakness.
Compare each to choose the best solution for future problem.

One of the use of DB is web page

---

• Where to store data?
  if We use a list like the example code below,
  Data goes aways once server is refreshed. We need to store them in the DB.

redirect(url_for('function_name'))
-notice that function add() returns redirect url and sends form-data to function home().
-function home() renders template to index.html and gets hold the data with parameter 'all_books', which is from list all_books from add function

main.py

all_books = []

@app.route('/')
def home():
    return render_template("index.html", all_books=all_books)

@app.route("/add", methods=["GET", "POST"])
def add():
    if request.method == "POST":
        new_book = {
            "title": request.form["title"],
            "author": request.form["author"],
            "rating": request.form["rating"]
        }
        all_books.append(new_book)

        return redirect(url_for('home'))

    return render_template("add.html")

---

index.html

for, if statement
When all_books list is empty. it returns 'Library is empty.'

<h1>My Library</h1>
    {% if all_books == []: %}
        <p>Library is empty.</p>
    {% endif %}
    {% for book in all_books: %}
    <ul>
    <li>{{ book['title'] }} - {{ book['author']}} - 
    {{ book['rating']}}/10</li>
    </ul>
    {% endfor %}

---

SQLite Databases

SQL Commands

SQLite databases are expressed as SQL (Structured Query Language) commands.
List of SQL Commands

Docs: https://www.w3schools.com/sql/sql_ref_create_table.asp

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: to modify our SQLite database.

• ( ) - The parts that come inside the parenthesis after CREATE TABLE books ( ) are going to be the fields in this table. (Column headings in an Excel sheet.)

• .execute() - This method will tell the cursor to execute an action. All actions in SQLite databases are expressed as SQL (Structured Query Language) commands. These are almost like English sentences with keywords written in ALL-CAPS. There are quite a few SQL commands. But don't worry, you don't have to memorise them.

• CREATE TABLE - This will create a new table in the database. The name of the table comes after this keyword.

• books - This is the name that we've given the new table we're creating.

• id INTEGER PRIMARY KEY -
  This is the first field, it's a field called "id" which is of data type INTEGER and it will be the PRIMARY KEY for this table. The primary key is the one piece of data that will uniquely identify this record in the table.

• title varchar(250) NOT NULL UNIQUE -
  This is the second field, it's called "title" and it accepts a variable-length string composed of characters. The 250 in brackets is the maximum length of the text.
  -NOT NULL means it must have a value and cannot be left empty.
  -UNIQUE means no two records in this table can have the same title.

DB Browser

In order to view our database.

Download
https://sqlitebrowser.org/dl/

• Insert Value in the table

1. Comment out the CREATE TABLE books part.
2. Close Database. Otherwise, a warning about 'database locked' when you work with the database in PyCharm.
3. Now run the code in main.py and re-open the database in DB Browser

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

SQLAlchemy

• SQLAlchemy is a library that facilitates the communication between Python programs and databases.

(From Reddit)
SQLAlchemy I call a "mapper" that maps the Sqlite3 database data onto python objects. SQLAlchemy is nice because it allows you to work with python objects instead of the direct database data.

For example. If I change a python object's attribute, the value in the database will ALSO change!

Pros: Easy to setup. You learn the backend more using them.
Cons: You have to design and connect all the pieces yourself.

pip3 install flask_sqlalchemy

Poorly Written Docs..
Flask SQLAlchemy (python.org)
Flask-sqlalchemy

so I checked hackersandslackers aricles

SQLAlchemy is ORM Library

ORM: Object Relational Mapping library.

• Most programming language platforms are object-oriented.
• the data in the RDBMS server is stored in tables.

Object-relational mapping is a technique that maps object parameters to the structure of a layer RDBMS table. The ORM API provides a way to perform CRUD operations without writing raw SQL statements.

translates Python classes to tables on relational databases and automatically converts function calls to SQL statements.

-map the relationships in the database into Objects
-Fields(column heading): Object properties
-Tables(worksheet): separate Classes
-each row of data: a new Object.

Task. Creating the same 'book' table above with SQLAlchemy

Create a New Database

Connecting to a Database by specifying URI

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)

#CREATE DB 
app.config['SQLALCHEMY_DATABASE_URI'] = "sqlite:///new-books-collection.db"

#Optional: silence the deprecation warning in the console.
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

Create a New Table

• Data models are Python classes representing a SQL table in our database, where attributes of a model translate to columns in a table.
• Each Column has different types(integer, string, text ...etc), unique, nullable and lots of optional parameters.

#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), unique=True, nullable=False)
    rating = db.Column(db.Float, unique=True, nullable=False)
def __init__(self, title, author, rating):
   self.title = title
   self.author = author
   self.rating = rating

#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()

---

• Bonus: __repr__ method explained

  It's best practice to set the value of repr on data models (and Python classes in general) for the purpose of logging or debugging our class instances. The value returned by repr is what we'll see when we print() an instance of User. If you've ever had to deal with [object Object] in Javascript, you're already familiar with how obnoxious it is to debug an object's value and receive nothing useful in return.

(From Reddit) The special __repr__ method should ideally return a string representation 
of an object that you could use to create that same object. 
For instance, given the following class:

    class Animal:
        def __init__(self, age, diet):
            self.age = age
            self.diet = diet

Inside this class you could have a __repr__ method like the following:

        def __repr__(self):
            return f"Animal(age={self.age}, diet='{self.diet}')"


You could then write the following:

    a = Animal(age=50, diet="meat")
    b = a # b is now an Animal object with the same attributes as a

Creating a Session: CRUD operations

• A session is a persistent database connection that lets us add, remove, change, and even undo changes with ease.

Create
Read
Update
Delete

Creating Records with Models & Sessions

• With a model defined and session created, we have the luxury of adding and modifying data purely in Python. SQLAlchemy refers to this as function-based query construction.

With an instance of Book created and saved as a variable book, all it takes to create this book in our database are are two calls to our session: add() queues the item for creation, and commit() saves the change.

1. session.add(): We can pass an instance of a data model into add() to quickly create a new record to be added to our database.
2. session.delete(): Like the above, delete() accepts an instance of a data model. If that record exists in our database, it will be staged for deletion.
3. session.commit(): Changes made within a session are not saved until explicitly committed.
4. session.close(): Unlike SQLAlchemy engines, sessions are connections that remain open until explicitly closed.

---------

Both are fine.

    1. db.session.query(User).filter(...).all()
    2. Books.query.filter(...).all()
     <Class>

# get an instance of the 'Entry' model
entry = Entry.query.get(1)

# change the attribute of the instance; here the 'name' attribute is changed
entry.name = 'New name'

# now, commit your changes to the database; this will flush all changes 
# in the current session to the database
db.session.commit()

---------

SQLAlchemy's ORM Query API

• A database query is a request to access data from a database to manipulate it or retrieve it
• SQLAlchemy session objects have a query() method which accepts the raw class of a data model we've previously defined.
• a query on the book SQL table

book = session.query(Book).FUNCTION()

• Calling .query(Customer) on our session isn't a valid query until we add one more method to the chain. All session queries end with a final method to shape/anticipate the result(s) of our query:

1. all()
will return all records which match our query as a list of objects. If we were to use all on the query above, we would receive all customer records with the Python data type List[Book].
2. first()
returns the first record matching our query, despite how many records match the query (what constitutes "first" depends on how your table is sorted). This is the equivalent of adding LIMIT 1 to a SQL query. As a result, the Python type to be returned would be Book.
3. one()
is extremely useful for cases where a maximum of one record should exist for the query we're executing (think of querying by primary key). This syntax is notably useful when verifying whether or not a record exists prior to creating one.

---

Create a New Record

#CREATE RECORD
book = Book(
	id=1, 	
        title='Harry Porter',
	author='J. K. Rowling', 
        rating=9.3)
db.session.add(book)
db.session.commit()

-the id field is optional, it will be auto-generated.

Read All Records

Returns a list holding objects -> Use 'For loop' to tap into each object

all_books = db.session.query(Books).all()
print(all_books)            #[<Books 2>, <Books 3>]
print(all_books[0].title)   #Harry Porter and Friends

db.session.query(User).filter(...).all()

Filtering Results

filter() is the equivalent of a SQL WHERE clause to return only rows that match the criteria we want:

Read A Particular Record By Query

book = db.session
	.query(Book)
    	.filter_by(title="Harry Potter")
    	.first()

Update A Particular Record By Query

book_to_update = db.session(Book)
		.query
		.filter_by(title="Harry Potter")
		.first()

book_to_update.title = "Harry Potter and Friends"
db.session.commit()  

Update A Record By PRIMARY KEY

book_id = 1
book_to_update = db.session.query(Book).get(book_id)
book_to_update.title = "Harry Potter"
db.session.commit()  

Delete A Particular Record By PRIMARY KEY

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

You can also delete by querying for a particular value e.g. by title or one of the other properties.

• Difference btw get and filter_by : essentially same but has some detailed difference -> here

• Difference btw filter and filter_by:
  filter_by is used for simple queries on the column names using regular kwargs, like db.users.filter_by(name='Joe') The same can be accomplished with filter, not using kwargs, but instead using the '==' equality operator, which has been overloaded on the db.users.name object: db.users.filter(db.users.name=='Joe'))

Project: Library with DB

Goal: Create a library with rating using SQLite

Home(Library)

main.py

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

app = Flask(__name__)

#-------SQLAlchemy----
#CREATE DB
app.config['SQLALCHEMY_DATABASE_URI'] = "sqlite:///new-books-collection.db"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

#CREATE TABLE
class Books(db.Model):
    """Creates db Model (Data Structure)"""
    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 __init__(self, title, author, rating):
   self.title = title
   self.author = author
   self.rating = rating

#Line below only required once, when creating DB. 
# db.create_all()

@app.route('/')
def home():
    """Returns a list of objects from DB and Passes data to template"""
    all_books = db.session.query(Books).all()  #returns a list holding objs
    print(all_books)
    return render_template("index.html", all_books=all_books)

index.html

<h1>My Library</h1>
    {% if all_books == []: %}
        <p>Library is empty.</p>
    {% endif %}

    {% for book in all_books: %}   <!-- all books is a list can tap into with .title / book is an obj -->
    <ul>
    <li>
        <a href="{{ url_for('delete', id=book.id)}}">Delete</a>
        {{ book.title }} - {{ book.author}} - {{ book.rating}}/10
        <a href="{{ url_for('edit', id=book.id) }}">Edit Rating</a> </li>

---

Add

DB

main.py

@app.route("/add", methods=["GET", "POST"])
def add():
    
   """POST: Gets hold of input data and Updates to DB"""
    if request.method == "POST":
        new_book = Books(
            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'))
   
   """GET: Returns 'add' template to get 
   html form input data filled in """   
    return render_template("add.html")

add.html

    <form action="{{url_for('add')}}" method="POST"> 
    <!-- sending form data to add function, Post -->
        <label>Book Name</label>
        <input type="text" name="title">
        <label>Book Author</label>
        <input type="text" name="author">
        <label>Rating</label>
        <input type="text" name="rating">
        <button type="submit">Add Book</button>
    </form>

---

Edit Rating

• Routing:
  url parameter e.g. /edit?id=3
  book_id = request.args.get('id')
  Use 'request.args' to get "parsed contents of query string"

https://flask.palletsprojects.com/en/1.1.x/quickstart/#url-building

https://stackoverflow.com/questions/24892035/how-can-i-get-the-named-parameters-from-a-url-using-flask

Flask will read everything after the question mark into request.args and won't interpret the variables from the route. If you wanted to get to your example route using an HTML form, you would need a bunch of extra JavaScript to make it work. Lastly, route variables are mandatory, request.args can be optional.

@app.route('/edit', methods=["GET", "POST"])
def edit():
    
    """POST: Updates the rating from html form and 
    Redirects to home to show a new rating"""

    if request.method == "POST":
   
        book_id = request.form["id"]
        book_to_update = Books.query.get(book_id)
        book_to_update.rating = request.form["rating"]
        db.session.commit()
        return redirect(url_for('home'))

    """GET: Renders specific book's editing form page"""
    
    book_id = request.args.get('id') 
    #url parameter    e.g. /edit?id=3
    
    book_selected = Books.query.get(book_id)
    return render_template("edit.html", book=book_selected)

edit.html

 <form action="{{url_for('edit')}}" method="POST"> 
 <!-- Sends form data to edit function to update -->
        <p>Book Name: {{book.title}} </p>
        <p>Current Rating: {{book.rating}}/10</p>

        <label>New Rating</label>

        <input hidden="hidden" name="id" value="{{book.id}}"> 
        <!-- ???? -> id  -->
        <input name="rating" type="text" placeholder="New Rating">
        <button type="submit">Change Rating</button>

    </form>

index.html

<a href="{{ url_for('edit', id=book.id) }}">Edit Rating</a> </li>

---

Delete the book

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

<a href="{{ url_for('delete', id=book.id)}}">Delete</a>