Unit 2.4a Using Programs with Data, SQLAlchemy
Using Programs with Data is focused on SQL and database actions. Part A focuses on SQLAlchemy and an OOP programming style,
Database and SQLAlchemy
In this blog we will explore using programs with data, focused on Databases. We will use SQLite Database to learn more about using Programs with Data. Use Debugging through these examples to examine Objects created in Code.
-
College Board talks about ideas like
- Program Usage. "iterative and interactive way when processing information"
- Managing Data. "classifying data are part of the process in using programs", "data files in a Table"
- Insight "insight and knowledge can be obtained from ... digitally represented information"
- Filter systems. 'tools for finding information and recognizing patterns"
- Application. "the preserve has two databases", "an employee wants to count the number of book"
-
PBL, Databases, Iterative/OOP
- Iterative. Refers to a sequence of instructions or code being repeated until a specific end result is achieved
- OOP. A computer programming model that organizes software design around data, or objects, rather than functions and logic
- SQL. Structured Query Language, abbreviated as SQL, is a language used in programming, managing, and structuring data
Imports and Flask Objects
Defines and key object creations
-
Comment on where you have observed these working? Provide a defintion of purpose.
-
Flask app object
Acts as an instance for flask and can be used to control flask. It is useful for creating web applications that can handle HTTP requests and responses.
-
SQLAlchemy db object
A python library which can be used to interact with relational databases. Can be used to manage database connection and database schema.
-
"""
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:///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)
Model Definition
Define columns, initialization, and CRUD methods for users table in sqlite.db
- Comment on these items in the class, purpose and defintion.
- class User Represents a user in the application. It contains various properties and methods related to the user.
- db.Model inheritance Relationship between the User class and the db.Model class. The db.Model class is a class provided by the SQLAlchemy library that provides a set of methods and properties for interacting with the database.
- init method
-
@property,@<column>.setterMethod in Python class. It's called when an object of the class is created. In this situation the init method initializes the properties of the User class. - create, read, update, delete methods Create method creates a new user in the database, read method retrieves a user from the database, update method updates an existing user in the database, and the delete method deletes a user from the database.
import datetime
import json
from sqlalchemy import create_engine, Column, Integer, String, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
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 '''
# Set up SQLite database engine and session
engine = create_engine('sqlite:///users.db', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
# Define the User class to manage actions in the 'users' table
Base = declarative_base()
class User(Base):
__tablename__ = 'users' # table name is plural, class name is singular
# Define the User schema with "vars" from object
id = Column(Integer, primary_key=True)
_name = Column(String(255), unique=False, nullable=False)
_uid = Column(String(255), unique=True, nullable=False)
_house = Column(String(255), unique=False, nullable=False)
_dateofarrival = Column(Date)
# constructor of a User object, initializes the instance variables within object (self)
def __init__(self, name, uid, house="123qwerty", dateofarrival=None):
self._name = name
self._uid = uid
self.set_house(house)
if dateofarrival is None:
dateofarrival = datetime.date.today()
self._dateofarrival = dateofarrival
# 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 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
@property
def house(self):
return self._house[0:10] + "..." # because of security only show 1st characters
# update house, this is conventional method used for setter
def set_house(self, house):
"""Create a hashed house."""
self._house = generate_password_hash(house, method='sha256')
# check house parameter against stored/encrypted house
def is_house(self, house):
"""Check against hashed house."""
result = check_password_hash(self._house, house)
return result
# dateofarrival property is returned as string, a string represents date outside object
@property
def dateofarrival(self):
dateofarrival_string = self._dateofarrival.strftime('%m-%d-%Y')
return dateofarrival_string
# dateofarrival setter, verifies date type before it is set or default to today
@dateofarrival.setter
def dateofarrival(self, dateofarrival):
if isinstance(dateofarrival, str): # not a date type
dateofarrival = datetime.date.today()
self._dateofarrival = dateofarrival
# age is calculated field, age is returned according to date of birth
# age is calculated field, age is returned according to date of birth
@property
def age(self):
today = datetime.today()
return today.year - self._dateofarrival.year - ((today.month, today.day) < (self._dateofarrival.month, self._dateofarrival.day))
Initial Data
Uses SQLALchemy db.create_all() to initialize rows into sqlite.db
-
Comment on how these work?
-
Create All Tables from db Object Function that generates a database based off of schema definitions.
-
User Object Constructors Function creates instance that is like a blueprint for creating user data structures that contain certain properties and methods.
-
Try / Except Atlternative code paths can be taken if an error or exception ever occurs during the execution of a program.
-
from datetime import datetime
# 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='toby', house='123toby', dateofarrival=datetime(1847, 2, 11))
u2 = User(name='Nikola Tesla', uid='niko', house='123niko', dateofarrival=datetime.today())
u3 = User(name='Alexander Graham Bell', uid='lex', house='123lex', dateofarrival=datetime.today())
u4 = User(name='Eli Whitney', uid='whit', house='123whit', dateofarrival=datetime.today())
u5 = User(name='Indiana Jones', uid='indi', dateofarrival=datetime(1920, 10, 21))
u6 = User(name='Marion Ravenwood', uid='raven', dateofarrival=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.")
from datetime import datetime
from sqlalchemy import create_engine, Column, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite:///users.db')
Session = sessionmaker(bind=engine)
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
name = Column(String, nullable=False)
uid = Column(String, primary_key=True)
house = Column(String, nullable=False)
dateofarrival = Column(DateTime, default=datetime.now)
def __repr__(self):
return f"<User(uid='{self.uid}', name='{self.name}', house='{self.house}', dateofarrival='{self.dateofarrival}')>"
Base.metadata.create_all(engine)
def initUsers():
session = Session()
try:
"""Tester data for table"""
u1 = User(name='Thomas Edison', uid='toby', house='123toby', dateofarrival=datetime(1847, 2, 11))
u2 = User(name='Nikola Tesla', uid='niko', house='123niko')
u3 = User(name='Alexander Graham Bell', uid='lex', house='123lex')
u4 = User(name='Eli Whitney', uid='whit', house='123whit')
session.add_all([u1, u2, u3, u4])
session.commit()
except:
"""fails with bad or duplicate data"""
session.rollback()
raise
finally:
session.close()
def find_by_uid(uid):
session = Session()
try:
user = session.query(User).filter(User.uid == uid).first()
except:
user = None
finally:
session.close()
return user
def check_credentials(uid, house):
user = find_by_uid(uid)
if user == None:
return False
if user.house == house:
return True
return False
Create a new User in table in Sqlite.db
Uses SQLALchemy and custom user.create() method to add row.
- Comment on purpose of following
- user.find_by_uid() and try/except Method that takes a uid parameter and returns the corresponding user instance from the database if it exists. The try/except block is used to output no results if the user doesn't exist.
- user = User(...) Method which creates a new instance of the User class and initializes its attributes with the values passed as arguments.
- user.dateofarrival and try/except Method that represents the date of birth of the user. The try/except block is used to catch any ValueError exceptions that might be raised if the dateofarrival argument passed to the User Constructor if the date isn't valid.
- user.create() and try/except Method that inserts a new row into the users table in the database with the values of the attributes of the User instance. The try/except block is used to catch any IntegrityError exceptions that might be raised if there is a unique constraint violation when inserting the new row.
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:")
house = input("Enter your house")
# Initialize User object before date
user = User(name=name,
uid=uid,
house=house
)
# create user.dateofarrival, fail with today as dateofarrival
dateofarrival = input("Enter your date of birth 'YYYY-MM-DD'")
try:
user.dateofarrival = datetime.strptime(dateofarrival, '%Y-%m-%d').date()
except ValueError:
user.dateofarrival = datetime.today()
print(f"Invalid date {dateofarrival} require YYYY-mm-dd, date defaulted to {user.dateofarrival}")
# 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()
Reading users table in sqlite.db
Uses SQLALchemy query.all method to read data
- Comment on purpose of following
- User.query.all SQalchemy method that returns all user information from table.
- json_ready assignment, google List Comprehension The json_ready variable is assigned the result of the list comprehension. The list comprehension iterates over each User object in the list returned by User.query.all(), and creates a dictionary for each user.
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///users.db'
db = SQLAlchemy(app)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50), nullable=False)
uid = db.Column(db.String(50), nullable=False)
house = db.Column(db.String(50), nullable=False)
dateofarrival = db.Column(db.DateTime, default=datetime.utcnow)
def read(self):
return {
'name': self.name,
'uid': self.uid,
'house': self.house,
'dateofarrival': self.dateofarrival.strftime('%Y-%m-%d %H:%M:%S')
}
with app.app_context():
db.create_all()
def read_users():
with app.app_context():
users = []
for user in User.query.all():
users.append(user.read())
return users
users = read_users()
print(users)