Database and Table Terms

The foundations of database is defining one or more Tables. In Python, a database can be constructed using the foundations we learned in modeling a Class.

A "Table" is a Model/Schema within a Database.

A "Table" definition in Python/SQLAlchemy is manifested by defining a "Class" and "Attributes" in Python.

A Python Class can inherit database functionality from SQLAlchemy. This is a method Python developers use to turn a Class into a Table within a SQL Database.

Writing methods in the Class for Create, Read, Update, Delete (CRUD) is how a developer initiates database operations.

Reference

Outline for Backend Model and Database

OOP modeling with SQLAlchemy enables CRUD operations.

User Table Schema

The db.Model is inherited into the class User(db.model), Each db.Column is provided properties according to capabilities of SQL. See init.py for db object definition.

db = SQLAlchemy(app)

""" database dependencies to support sqliteDB examples """

from __init__ import app, db
from sqlalchemy.exc import IntegrityError
from werkzeug.security import generate_password_hash, check_password_hash


""" Key additions to User Class for Schema definition """

# Define the User class to manage actions in the 'users' table
# -- Object Relational Mapping (ORM) is the key concept of SQLAlchemy
# -- a.) db.Model is like an inner layer of the onion in ORM
# -- b.) User represents data we want to store, something that is built on db.Model
# -- c.) SQLAlchemy ORM is layer on top of SQLAlchemy Core, then SQLAlchemy engine, SQL
class User(db.Model):
    __tablename__ = 'users'  # table name is plural, class name is singular

    # Define the User schema with "vars" from object
    id = db.Column(db.Integer, primary_key=True)
    _name = db.Column(db.String(255), unique=False, nullable=False)
    _uid = db.Column(db.String(255), unique=True, nullable=False)
    _password = db.Column(db.String(255), unique=False, nullable=False)
    _dob = db.Column(db.Date)

    # Defines a relationship between User record and Notes table, one-to-many (one user to many notes)
    posts = db.relationship("Post", cascade='all, delete', backref='users', lazy=True)

    # constructor of a User object, initializes the instance variables within object (self)
    def __init__(self, name, uid, password="123qwerty", dob=date.today()):
        self._name = name    # variables with self prefix become part of the object, 
        self._uid = uid
        self.set_password(password)
        self._dob = dob

User Table CRUD operations

All these def's are methods of the User Class.

def create(self):
    try:
        # creates a person object from User(db.Model) class, passes initializers
        db.session.add(self)  # add prepares to persist person object to Users table
        db.session.commit()  # SqlAlchemy "unit of work pattern" requires a manual commit
        return self
    except IntegrityError:
        db.session.remove()
        return None

# CRUD read converts self to dictionary
# returns dictionary
def read(self):
    return {
        "id": self.id,
        "name": self.name,
        "uid": self.uid,
        "dob": self.dob,
        "age": self.age,
        "posts": [post.read() for post in self.posts]
    }

# CRUD update: updates user name, password, phone
# returns self
def update(self, name="", uid="", password=""):
    """only updates values with length"""
    if len(name) > 0:
        self.name = name
    if len(uid) > 0:
        self.uid = uid
    if len(password) > 0:
        self.set_password(password)
    db.session.commit()
    return self

# CRUD delete: remove self
# None
def delete(self):
    db.session.delete(self)
    db.session.commit()
    return None

Outline to understand Devops and Databases

DevOps and the SQLite database requires file management and configuring Python to work in local and deployment environment.

Volumes in Deployment
Database URI (deployment)
Review Dockerfile
Database URI (testing)
Exclude/Ignore Testing Database
Port and Host (testing)
Run locally as you develop Select main.py file in VSCode and press Play button, or press down arrow next to Play button to activate Debug testing. The below dialog will appear in Terminal.

(base) machine:flask_portfolio user$  cd /Users/user/vscode/flask_portfolio ; /usr/bin/env /Users/user/opt/anaconda3/bin/python /Users/user/.vscode/extensions/ms-python.python-2022.20.2/pythonFiles/lib/python/debugpy/adapter/../../debugpy/launcher 61127 -- /Users/user/vscode/flask_portfolio/main.py 
 * Serving Flask app "__init__" (lazy loading)
 * Environment: production
   WARNING: This is a development server. Do not use it in a production deployment.
   Use a production WSGI server instead.
 * Debug mode: on
 * Running on all addresses.
   WARNING: This is a development server. Do not use it in a production deployment.
 * Running on http://192.168.1.75:8086/ (Press CTRL+C to quit)
 * Restarting with watchdog (fsevents)
 * Debugger is active!
 * Debugger PIN: 403-552-045

Run with Docker prior to commit and deployment It is much simpler to debug Docker issues in VSCode prior to Deployment on AWS. This will create production database sample, which will show up commits. Simply delete file after run session, do not commit it.

(base) machine:flask_portfolio user$ pwd # verify directory
/Users/user/vscode/flask_portfolio
(base) machine:flask_portfolio user$ docker-compose build # run docker build
[+] Building 16.0s (10/10) FINISHED                                                                                                  
 => [internal] load build definition from Dockerfile                                                                            0.0s
 ...
 => [1/6] FROM docker.io/library/python:3.10@sha256:e9f824eec86879b1ffe8da9ef3eb4677dd6e89b63e8bce8cb35c572f550751d8            0.0s
 => CACHED [2/6] RUN apt-get update && apt-get upgrade -y &&     apt-get install -y python3 python3-pip git                     0.0s
 => [3/6] COPY . /                                                                                                              0.2s
 => [4/6] RUN pip install --no-cache-dir -r requirements.txt                                                                    9.7s
 => [5/6] RUN pip install
 ...
 => => writing image sha256:47ccd2a3ca839139727dd8c8f57fc1ad6a36e670962059d960d21b3ad0cba292                                    0.0s 
 => => naming to docker.io/library/flask_port_v1                                                                                0.0s 

(base) machine:flask_portfolio user$ docker-compose up  # enable for browser testing, no -d! This allows ctl-C to stop and all errors will appear in Terminal 
[+] Running 1/1
 ⠿ Container flask_portfolio-web-1  Recreated                                                                                   0.1s
Attaching to flask_portfolio-web-1
flask_portfolio-web-1  | [2023-01-15 13:08:19 +0000] [1] [INFO] Starting gunicorn 20.1.0
flask_portfolio-web-1  | [2023-01-15 13:08:19 +0000] [1] [INFO] Listening at: http://0.0.0.0:8086 (1)
flask_portfolio-web-1  | [2023-01-15 13:08:19 +0000] [1] [INFO] Using worker: sync
flask_portfolio-web-1  | [2023-01-15 13:08:19 +0000] [7] [INFO] Booting worker with pid: 7

docker-compose.yml has a volume mount.

Docker run in a container, its own virtual computer. Outside of the Docker container is where we desire to keep the SQLite database, thus there is a mount to a files accessible by developers. This ensures data is saved after Docker virtual computer up/down. The Left side of volume says ./volumes is a relative location on computer where docker-compose build/up was run, this is were database can always be seen. The Right side /volumes is where the Docker container see the volume.

version:'3'services:
        web:
                image: flask_port_v1
                build: .
                ports:
                        - "8086:8086"
                volumes:
                        - ./volumes:/volumes
                restart: unless-stopped

init.py contains project settings.

This file contains app variables that can be referenced anywhere the app elements is imported (from __init__ import app, db). This is a way of sharing configuration across the many python/flask files in the project. Review the below file and become familiar with the settings ...

from flask import Flask
from flask_login import LoginManager
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

"""
These object can be used throughout project.
1.) Objects from this file can be included in many blueprints
2.) Isolating these object definitions avoids duplication and circular dependencies
"""

# Setup of key Flask object (app)
app = Flask(__name__)
# Setup SQLAlchemy object and properties for the database (db)
dbURI = 'sqlite:////volumes/flask_portfolio.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = dbURI
app.config['SECRET_KEY'] = 'SECRET_KEY'
db = SQLAlchemy(app)
Migrate(app, db)

# Images storage
app.config['MAX_CONTENT_LENGTH'] = 5 * 1024 * 1024  # maximum size of uploaded content
app.config['UPLOAD_EXTENSIONS'] = ['.jpg', '.png', '.gif']  # supported file types
app.config['UPLOAD_FOLDER'] = 'volumes/uploads/'  # location of user uploaded content

Outline of SQL Administration Tools

Learning SQL basics using SQLite tools.

Install SQLite Viewer in VSCode marketplace, click on SQL database to review schema and see rows.
Install SQLite and review SQLite Explorer.
SQLite3 Website describes the command line SQL administration tool. To install on Mac terminal brew install sqlite3, on WSL terminal sudo apt install sqlite3.
SQL Cheat Sheet. These commands can be done using SQLite3 and perhaps in Market Place tooling.

Loading SQLite3 from volumes directory

(base) machine:volumes user$ sqlite3 sqlite.db

Review content in users table

sqlite> select id, _name, _uid, _dob from users;
1|Thomas Edison|toby|1847-02-11
2|Nicholas Tesla|niko|2023-01-14
3|Alexander Graham Bell|lex|2023-01-14
4|Eli Whitney|whit|2023-01-14
5|John Mortensen|jm1021|1959-10-21
6|Hedy Lamar|hedy|2023-01-14
7|Hedy Lamarr|heddy|2023-01-14
8|Marie Currie|marie|2023-01-14
9|Wilma Flintstone|wilma|2023-01-14
10|Fred Flintstone|fred|0010-01-01

Delete content in users table, redisplay

sqlite> delete from users where id=7;
sqlite> select id, _name, _uid, _dob from users;
1|Thomas Edison|toby|1847-02-11
2|Nicholas Tesla|niko|2023-01-14
3|Alexander Graham Bell|lex|2023-01-14
4|Eli Whitney|whit|2023-01-14
5|John Mortensen|jm1021|1959-10-21
6|Hedy Lamar|hedy|2023-01-14
8|Marie Currie|marie|2023-01-14
9|Wilma Flintstone|wilma|2023-01-14
10|Fred Flintstone|fred|0010-01-01

Quit SQLite3 and return to prompt

sqlite> .quit

SQLite Explorer

id	_name	_uid	_password	_dob
1	Thomas Edison	toby	sha256$Yj69CENuKWaRDHw3$dcb731d06dd59b7a316a853944b6603197876aa3a9553a758865d849a2b0a502	1847-02-11
2	Nicholas Tesla	niko	sha256$muQZzhqAFgXLERzC$cc7a02c80fc80190cd0b052d0dd010272cc8bc81971a7fe3cad6cee4a13e55e7	2023-01-14
3	Alexander Graham Bell	lex	sha256$rBKtyle2TI1vY7y9$ace54fb1e44640213f4aa284d6fe05cd27bfbcd1f7ac6d2ba697400d0b78cb7e	2023-01-14
4	Eli Whitney	whit	sha256$j31DNBdcLtDALA4x$d39828e9e016c675b6c27ce1e6a08e196ffa5b7740978d6b313d039e60756375	2023-01-14
5	John Mortensen	jm1021	sha256$5gt0IT2TiQm0UWi5$3d74775d35d2e2b0865deefb8f59e22e2b35d8c19ab4b1c26c7e50a27d3bf9fd	1959-10-21

Hacks

The Big Picture purpose of this hack is to build a database. Being able to create an SQLite table and populate test data within it is the major goal. To do this effectively it is imperative to show the following.

Build Schema for a table, make a new model file and use users.py as an example. Start slow and simple and build up.

Build an initXXXXX() method and use it to add preliminary/test data to the table. Once again use users.py as an example.

Make a 30-60 second video where you show a Debugging session of making new rows in the table. Use sqlite marketplace tools and/or sqlite3 command line tool to show success in creating table and adding data.

Hack Helper

Here are some tips.

Become familiar with initUsers(). Observe it is called/activated from main.py. This function activates after you run the main.py and activate the web application in the browser. Observe that the sqlite.db file will appear in the volumes directory in conjunction with home screen of site appearing in browser.
Delete sqlite.db from volumes directory on your development machine. Set breakpoint on initUsers() and run main.py using debug. Use the step into option on the debugger and observe the creation of data.

@app.before_first_request
def activate_job():initJokes()    initUsers()

Make your own XXXXX.py file under model directory. Follow users.py and develop your own schema from the OOP code you did in last Hacks. Follow the pattern in users.py to make a initXXXX() function top populate some test data.
In main.py, add your initXXXX() method to def activate_job() function shown above. Use this as basis of your video debugging session. Debugging is hugely important at this level to understand your database success prior to building an API. Building initXXXX() method, adding database records, and debugging will enable you to verify CRUD operations as you develop.

Completed Hacks

Python Class

""" database dependencies to support sqliteDB examples """
from random import randrange
from datetime import date
import os, base64
import json

from __init__ import app, db
from sqlalchemy.exc import IntegrityError
from werkzeug.security import generate_password_hash, check_password_hash


''' Tutorial: https://www.sqlalchemy.org/library.html#tutorials, try to get into Python shell and follow along '''

# Define the Post class to manage actions in 'posts' table,  with a relationship to 'users' table

class User(db.Model):
    __tablename__ = 'players'  # table name is plural, class name is singular

    # Define the User schema with "vars" from object
    id = db.Column(db.Integer, primary_key=True)
    _name = db.Column(db.String(255), unique=False, nullable=False)
    _uid = db.Column(db.String(255), unique=True, nullable=False)
    _password = db.Column(db.String(255), unique=False, nullable=False)
    _tokens = db.Column(db.Integer)

    # Defines a relationship between User record and Notes table, one-to-many (one user to many notes)
    

    # constructor of a User object, initializes the instance variables within object (self)
    def __init__(self, name, uid, tokens, password="123qwerty"):
        self._name = name    # variables with self prefix become part of the object, 
        self._uid = uid
        self.set_password(password)
        self._tokens = tokens

    # a name getter method, extracts name from object
    @property
    def name(self):
        return self._name
    
    # a setter function, allows name to be updated after initial object creation
    @name.setter
    def name(self, name):
        self._name = name
    
    # a getter method, extracts email from object
    @property
    def uid(self):
        return self._uid
    
    # a setter function, allows name to be updated after initial object creation
    @uid.setter
    def uid(self, uid):
        self._uid = uid
        
    # check if uid parameter matches user id in object, return boolean
    def is_uid(self, uid):
        return self._uid == uid
    
    @property
    def password(self):
        return self._password[0:10] + "..." # because of security only show 1st characters

    # update password, this is conventional setter
    def set_password(self, password):
        """Create a hashed password."""
        self._password = generate_password_hash(password, method='sha256')

    # check password parameter versus stored/encrypted password
    def is_password(self, password):
        """Check against hashed password."""
        result = check_password_hash(self._password, password)
        return result
    
    # dob property is returned as string, to avoid unfriendly outcomes
    @property
    def tokens(self):
        return self._tokens
    
    # dob should be have verification for type date
    @tokens.setter
    def tokens(self, tokens):
        self._tokens = tokens
    
    
    # output content using str(object) in human readable form, uses getter
    # output content using json dumps, this is ready for API response
    def __str__(self):
        return json.dumps(self.read())

    # CRUD create/add a new record to the table
    # returns self or None on error
    def create(self):
        try:
            # creates a person object from User(db.Model) class, passes initializers
            db.session.add(self)  # add prepares to persist person object to Users table
            db.session.commit()  # SqlAlchemy "unit of work pattern" requires a manual commit
            return self
        except IntegrityError:
            db.session.remove()
            return None

    # CRUD read converts self to dictionary
    # returns dictionary
    def read(self):
        return {
            "id": self.id,
            "name": self.name,
            "uid": self.uid,
            "tokens": self.tokens
        }

    # CRUD update: updates user name, password, phone
    # returns self
    def update(self, name="", uid="", password=""):
        """only updates values with length"""
        if len(name) > 0:
            self.name = name
        if len(uid) > 0:
            self.uid = uid
        if len(password) > 0:
            self.set_password(password)
        db.session.commit()
        return self

    # CRUD delete: remove self
    # None
    def delete(self):
        db.session.delete(self)
        db.session.commit()
        return None


"""Database Creation and Testing """


# Builds working data for testing
def initPlayers():
    """Create database and tables"""
    db.create_all()
    """Tester data for table"""
    u1 = User(name='Azeem Khan', uid='azeemK', password='prodlilxeem', tokens=45)
    u2 = User(name='Ahad Biabani', uid='ahadB', password='daha4tw', tokens=41)
    u3 = User(name='Akshat Parikh', uid='akshatP', password='akshlatt!!', tokens=40)
    u4 = User(name='Josh Williams', uid='joshW', password='tripleAJfrfr', tokens=38)
    # u5 = User(name='Billy Bob', uid='billyB', password='thagoatfrfr', tokens=54)

    users = [u1, u2, u3, u4, u5]

    """Builds sample user/note(s) data"""
    for user in users:
        try:
            user.create()
        except IntegrityError:
            '''fails with bad or duplicate data'''
            db.session.remove()
            print(f"Records exist, duplicate email, or error: {user.uid}")

Class Implementation (API)

from flask import Blueprint, request, jsonify
from flask_restful import Api, Resource # used for REST API building

from model.arcades import User

# Change variable name and API name and prefix
players_api = Blueprint('players_api', __name__,
                   url_prefix='/api/players')

# API docs https://flask-restful.readthedocs.io/en/latest/api.html
api = Api(players_api)

class UserAPI:        
    class _Create(Resource):
        def post(self):
            ''' Read data for json body '''
            body = request.get_json()
            
            ''' Avoid garbage in, error checking '''
            # validate name
            name = body.get('name')
            if name is None or len(name) < 2:
                return {'message': f'Name is missing, or is less than 2 characters'}, 210
            # validate uid
            uid = body.get('uid')
            if uid is None or len(uid) < 2:
                return {'message': f'User ID is missing, or is less than 2 characters'}, 210
            # look for password and dob
            password = body.get('password')
            tokens = body.get('tokens')

            ''' #1: Key code block, setup USER OBJECT '''
            uo = User(name=name, 
                      uid=uid,
                      tokens=tokens,)
            
            ''' Additional garbage error checking '''
            # set password if provided
            if password is not None:
                uo.set_password(password)            
            
            ''' #2: Key Code block to add user to database '''
            # create user in database
            player = uo.create()
            # success returns json of user
            if player:
                return jsonify(player.read())
            # failure returns error
            return {'message': f'Processed {name}, either a format error or User ID {uid} is duplicate'}, 210

    class _Read(Resource):
        def get(self):
            players = User.query.all()    # read/extract all users from database
            json_ready = [player.read() for player in players]  # prepare output in json
            return jsonify(json_ready)  # jsonify creates Flask response object, more specific to APIs than json.dumps

    class _Update(Resource):
        def update(self):
            body = request.get_json() # get the body of the request
            id = body.get('id') # get the ID (Know what to reference)

            # Set up (just in case they do not work out)
            name = ''
            uid = ''
            password = ''

            try:
                name = body.get("name") # get and update the name
            except:
                pass
            try:
                uid = body.get("uid") # get and update the uid
            except:
                pass
            try: 
                password = body.get("password") # get and update password
            except:
                pass
            
            user = User.query.get(id) # get the user (using the uid in this case)
            user.update(name, uid, password)
            return f"{user.read()} Updated"

    class _Delete(Resource):
        def delete(self):
            body = request.get_json()
            uid = body.get('uid')
            user = User.query.get(uid)
            user.delete()
            return f"{user.read()} Has been deleted"


    # building RESTapi endpoint
    api.add_resource(_Create, '/create')
    api.add_resource(_Read, '/')
    api.add_resource(_Delete, '/delete')

video