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.

  1. Users Table Schema

  2. Database Properties

  3. Initial Database Setup (call)

  4. Initial Database Setup (add records)

  5. OOP 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, noc, date=date.today(), homeworkName):
        self._noc = noc    # variables with self prefix become part of the object, 
        self._homeworkName = homeworkName
        self._date = date

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

if __name__ == "__main__":
    u1 = User(name='Sean Y', uid='yeung', password='123yeung', dob=date(1995, 2, 3), height='10 feet')
    u2 = User(name='Ellie P', uid='ellie', password='123ellie', dob=date(2007, 11, 1), height='10 feet')
    u3 = User(name='Kaylee H', uid='kaylee', password='123kaylee', dob=date(2005, 10, 30), height='10 feet')
    u4 = User(name='Theo H', uid='theo', password='123theo', dob=date(2006, 1, 31), height='10 feet')

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

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.

  1. Build Schema for a table, make a new model file and use users.py as an example. Start slow and simple and build up.
  2. Build an initXXXXX() method and use it to add preliminary/test data to the table. Once again use users.py as an example.
  3. 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.