SQLite Table
SQLite Table
"""
These imports define the key objects
"""
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
"""
These object and definitions are used throughout the Jupyter Notebook.
"""
# Setup of key Flask object (app)
app = Flask(__name__)
# Setup SQLAlchemy object and properties for the database (db)
database = 'sqlite:///cats.db' # path and filename of database
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = database
app.config['SECRET_KEY'] = 'SECRET_KEY'
db = SQLAlchemy()
# This belongs in place where it runs once per project
db.init_app(app)
""" database dependencies to support sqlite examples """
import datetime
from datetime import datetime
import json
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 a Python shell and follow along '''
# 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 Cats(db.Model):
__tablename__ = 'cats' # 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)
_breed = db.Column(db.String(255), unique=True, nullable=False)
_color = db.Column(db.String(255), unique=False, nullable=False)
_age = db.Column(db.Integer)
# constructor of a User object, initializes the instance variables within object (self)
def __init__(self, name, breed, color, age):
self._name = name # variables with self prefix become part of the object,
self._breed = breed
self._color = color
self._age = age
# 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 uid from object
@property
def breed(self):
return self._breed
# a setter function, allows uid to be updated after initial object creation
@breed.setter
def breed(self, breed):
self._breed = breed
# a getter method, extracts uid from object
@property
def color(self):
return self._color
# a setter function, allows uid to be updated after initial object creation
@breed.setter
def color(self, color):
self._color = color
# a getter method, extracts uid from object
@property
def age(self):
return self._age
# a setter function, allows uid to be updated after initial object creation
@breed.setter
def age(self, age):
self._age = age
# output content using str(object) is in human readable form
# 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,
"breed": self.breed,
"color": self.color,
"age": self.age,
}
# CRUD update: updates user name, password, phone
# returns self
def update(self, name="", breed="", color="", age=""):
"""only updates values with length"""
if len(name) > 0:
self.name = name
if len(breed) > 0:
self.breed = breed
if len(color) > 0:
self.color = color
self.age = age
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 initCats():
with app.app_context():
"""Create database and tables"""
db.create_all()
"""Tester data for table"""
cat1 = Cats(name='Bob', breed='calico', color='orange, white, brown', age=2)
cat2 = Cats(name='Mittens', breed='American shorthair', color='gray', age=5)
cats = [cat1, cat2]
"""Builds sample user/note(s) data"""
for cat in cats:
try:
'''add user to table'''
object = cat.create()
print(f"Created new cat {object.name}")
except: # error raised if object nit created
'''fails with bad or duplicate data'''
print(f"Records exist cat name {user.name}, or error.")
initCats()