Vocab

  • table = a model/schema within a database
  • SQL = structure and query language
    • the way we interact with databases
    • we will do regular coding, and it will allow us to interact with the database
  • persistent data = data that is saved somewhere into a database

User Table Schema

  • Defining the title of the table and what goes into it is the schema
  • db.Model allows us to inherit the properties of db into our user class
  • database = collection of spreadsheets/tables --> should be on our backend
  • db.Model is inhertied into the class User(db.model)
  • each db.Column gets properties according to the capabilities of SQL

How it Works

  • we ask the user for an input
  • it takes the input to the backend --> stores it into a database
  • we can extract information from the database to bring it back to the frontend, if needed
    • ex. setting a password --> when the site tells you your password is incorrect, it's crosschecking with the table created on the backend
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
    # creates multiple columns, one for each type of data (name, uid, password, dob)
    # defining the database
    id = db.Column(db.Integer, primary_key=True) 
    _name = db.Column(db.String(255), unique=False, nullable=False) # associating the properties with the database; allows us to move the infor in and out of the database using the column name
    _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

  • allow us to move data in and out of the database
  • all the defs are methods of the User Class
  • python sessions with the database
  • we are going to try to use these methods to create our own database for our CPT
  • we need to tell our code where our database file is in order to access it
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

DevOps and Databases

  • can create a database which runs locally to work on/actually code on
    • run locally as we develop on a test database
  • create another database which is used to actually deploy the work; copy over from the database which runs locally once you have it working
  • we store our database into the "volumes" directory
    • persistent data is stored into the databse
    • must be careful to ensure that the persistent data isn't erased
  • Link to DevOps section of lecture for refernece
  • there are administrative tools that help us open the database
    • ex. SQLite

SQL Administrative Tools

  • we are the admins
    • use tools to help us figure out what's going on in the backend