Database/Model, Backend, OOP, Python
The project-based learning objective is to create a purposeful backend, something that can't be done in frontend via JavaScript. A database and storing persistent data is a primary focus of most backend systems. An SQLite Database using the Python SQLAlchemy framework will be the outcome of this lesson. As students learn databases using SQLAlchemy, they will also learn about Object-oriented programming in Python.
- Database and Table Terms
- Hacks
- Init Method
- Schema
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.
""" 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
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
-
Runlocally 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
-
Runwith Docker prior to commit and deployment It is much simpler to debug Docker issues in VSCode prior to Deployment on AWS. This willcreate production databasesample, 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
./volumesis a relative location on computer where docker-compose build/up was run, this is were database can always be seen. The Right side/volumesis 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
appvariables that can be referenced anywhere theappelements 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 terminalsudo 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.dbReview 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
modelfile and useusers.pyas 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
sqlitemarketplace tools and/or sqlite3 command line tool to show success in creating table and adding data.
Init Method
- In programming, the init method is a special method in a class or object that is used to initialize or set up the object's properties when it is first created. It is often used to assign default values to the object's properties, or to perform other setup tasks that need to happen when the object is first created. The method is usually named init() in Python, it's a constructor method that gets called when an object is created.
Schema
- In programming, a schema is a blueprint or structure for organizing and storing data in a database. In the context of tables, a schema defines the structure of the table, including the names and data types of the columns, as well as any constraints or rules that apply to the data stored in the table. A schema can also specify relationships between tables and enforce integrity constraints to ensure the data is consistent and accurate.
Hack Helper
Here are some tips.
-
Become familiar with
initUsers(). Observe it is called/activated frommain.py. This function activates after you run themain.pyand activate the web application in the browser. Observe that thesqlite.dbfile 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
breakpointon initUsers() and runmain.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.pyfile undermodeldirectory. Followusers.pyand develop your own schema from the OOP code you did in last Hacks. Follow the pattern inusers.pyto make a initXXXX() function top populate some test data. -
In
main.py, add your initXXXX() method todef 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.