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.

Backend Model

Below is a sample backend model using the User class from the OOP notes. It inherets the attributes of User and uses various imported programs.

""" 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

# CRUD TIME

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