SQALchemy Database 2.4a related to Tri 2 project
Using Programs with Data is focused on SQL and database actions. Part A focuses on SQLAlchemy and an OOP programming style.
- Notes input
- Create a new User in table in Sqlite.db
- Reading users table in sqlite.db
- Updating users table in sqlite.db
- Deleting users table in sqlite.db
Notes input
- OOP programming: User class with objects and functions built into it (with attributes)
- Imperative: basic level, functions by function (today)
- schema (in a database): columns of a database, how you describe the database
- purpose: differentiate between the columns, easily extract data (organized)
- primary key: should be unique, userid is best (people can share names)
- datatypes in SQL: string, boolean, integer, images, list, dictionary, class
"""
These imports define the key objects
"""
import datetime
from datetime import datetime
import json
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.exc import IntegrityError
from werkzeug.security import generate_password_hash, check_password_hash
"""
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:///sqlite.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)
class User(db.Model):
__tablename__ = 'Trips' # table name is plural, class name is singular
__table_args__ = {'extend_existing': True} # __abstract__ = True
# 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=True, nullable=False)
_destination = db.Column(db.String(255), unique=True, nullable=False)
_budget = db.Column(db.String(255), unique=False, nullable=False)
_dob= db.Column(db.Date, unique=True, nullable=False)
## add duration of trip
# constructor of a User object, initializes the instance variables within object (self)
def __init__(self, name, uid, password="123elly", destination="Dubai", budget="$500", dob=datetime.today()):
self._name = name # variables with self prefix become part of the object,
self._uid = uid
self._password = password
self._destination = destination
self._budget = budget
if isinstance(dob, str): # not a date type
dob = date=datetime.today()
self._dob = dob
# 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
@property
def uid(self):
return self._uid
# a setter function, allows uid to be updated after initial object creation
@uid.setter
def uid(self, uid):
self._uid = uid
# check if uid parameter matches user id in object, return boolean
def is_uid(self, uid):
return self._uid == uid
# a getter method, extracts uid from object
@property
def destination(self):
return self._destination
# a setter function, allows uid to be updated after initial object creation
@destination.setter
def destination(self, destination):
self._destination = destination
@property
def budget(self):
return self._budget
# a setter function, allows name to be updated after initial object creation
@budget.setter
def budget(self, budget):
self._budget = budget
@property
def password(self):
return self._password[0:10] + "..." # because of security only show 1st characters
# update password, this is conventional method used for setter
def set_password(self, password):
"""Create a hashed password."""
self._password = generate_password_hash(password, method='sha256')
# check password parameter against stored/encrypted password
def is_password(self, password):
# """Check against hashed password."""
result = check_password_hash(self._password, password)
return result
# dob property is returned as string, a string represents date outside object
@property
def dob(self):
dob_string = self._dob.strftime('%m-%d-%Y')
return dob_string
# dob setter, verifies date type before it is set or default to today
@dob.setter
def dob(self, dob):
if isinstance(dob, str): # not a date type
dob = date=datetime.today()
self._dob = dob
# age is calculated field, age is returned according to date of birth
# @property
#def age(self):
# today = datetime.today()
# return today.year - self._dob.year - ((today.month, today.day) < (self._dob.month, self._dob.day))
# 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,
"uid": self.uid,
"password": self.password,
"destination": self.destination,
"budget": self.budget,
"date of birth": self.dob
}
# CRUD update: updates user name, password, phone
# returns self
def update(self, name="", uid="", password="", destination="", budget="", dob=""):
"""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)
if len(destination) > 0:
self.destination = destination
if len(budget) > 0:
self.budget = budget
db.session.commit()
return self
# CRUD delete: remove self
# None
def delete(self):
db.session.delete(self)
db.session.commit()
return None
# Builds working data for testing
def initUsers():
with app.app_context():
"""Create database and tables"""
db.create_all()
"""Tester data for table"""
u1 = User(name='Thomas Edison', uid='tommye', password='lightbulb', destination='SeaWorld', budget='$150000', dob=datetime(1847, 2, 11))
u2 = User(name='Nikola Tesla', uid='ntesla2', password='modelx', destination='La Jolla Cove', budget='$50000', dob=datetime(1970, 3, 4))
u3 = User(name='Alexander Graham Bell', uid='grahambalex', password='123bells', destination='Santee Lakes', budget='$1200', dob=datetime(1778, 7, 18))
u4 = User(name='Eli Whitney', uid='eliwhi', password='Greenbay4', destination='Bahia Resort', budget='$30750', dob=datetime(2002, 6, 23))
u5 = User(name='Indiana Jones', uid='indijj', password='adventurous11', destination='San Diego Zoo', budget='none',dob=datetime(1920, 10, 21))
u6 = User(name='Marion Ravenwood', uid='ravenrion', password='blackmagic', destination='Air and Space Museum', budget='$725000', dob=datetime(1921, 10, 21))
users = [u1, u2, u3, u4, u5, u6]
"""Builds sample user/note(s) data"""
for user in users:
try:
'''add user to table'''
object = user.create()
print(f"Created new uid {object.uid}")
except: # error raised if object nit created
'''fails with bad or duplicate data'''
print(f"Records exist uid {user.uid}, or error.")
initUsers()
def is_password(self, password):
# """Check against hashed password."""
result = check_password_hash(self._password, password)
return result
# SQLAlchemy extracts single user from database matching User ID
def find_by_uid(uid):
with app.app_context():
user = User.query.filter_by(_uid=uid).first()
return user # returns user object
# Check credentials by finding user and verify password
def check_credentials(uid, password):
# query email and return user record
user = find_by_uid(uid)
if user == None:
return False
if (user.is_password(password)):
return True
return False
check_credentials("indijii", "adventurous11")
def create():
# optimize user time to see if uid exists
uid = input("Enter your user id:")
user = find_by_uid(uid)
try:
print("Found\n", user.read())
return
except:
pass # keep going
# request value that ensure creating valid object
name = input("Enter your name:")
password = input("Enter your password:")
destination = input("Enter your desired trip destination:")
budget = input("Enter your budget:")
# Initialize User object before date
user = User(name=name,
uid=uid,
password=password,
destination=destination,
budget=budget
)
# create user.dob, fail with today as dob
dob = input("Enter your date of birth 'YYYY-MM-DD'")
try:
user.dob = datetime.strptime(dob, '%Y-%m-%d').date()
except ValueError:
user.dob = datetime.today()
print(f"Invalid date {dob} require YYYY-mm-dd, date defaulted to {user.dob}")
# write object to database
with app.app_context():
try:
object = user.create()
print("Created\n", object.read())
except: # error raised if object not created
print("Unknown error uid {uid}")
create()
def read():
with app.app_context():
table = User.query.all()
json_ready = [user.read() for user in table] # "List Comprehensions", for each user add user.read() to list
return json_ready
read()
def update():
uid=input("Enter user id")
user = find_by_uid(uid)
try:
print("Found\n", user.read())
return
except:
pass
password=input("Enter password")
destination = input("Update destination:")
update()
def delete():
delete()