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:implements a WSGI application and acts as the central object. It is passed the name of the module or package of the application. Once it is created it will act as a central registry for the view functions, the URL rules, template configuration and much more. 2. SQLAlchemy db object: SQLAlchemy is a library that facilitates the communication between Python programs and databases.
"""
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:Classes provide a means of bundling data and functionality together. User has many attributes, such as uid, name... - db.Model inheritance: A model where an entity (often called the child entity) is derived (it inherits) from another entity (the parent entity). They get passed down.
- init method: The init function is called every time an object is created from a class. The init method lets the class initialize the object's attributes and serves no other purpose.
-
@property
,@<column>.setter
: Pythonic way to use getters and setters - create, read, update, delete methods: Methods to manipulate the data in a given database.
""" 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
""" database dependencies to support sqliteDB examples """
# Define the Score class to manage actions in the 'score' table
class Scores(db.Model):
__tablename__ = 'scores1'
# Define the User schema with "vars" from object
id = db.Column(db.Integer, primary_key=True)
_name = db.Column(db.String(255), unique=True, nullable=False)
_score = db.Column(db.String(255), unique=False, nullable=False)
_dob = db.Column(db.Date)
# constructor of a User object, initializes the instance variables within object (self)
def __init__(self, name, score, dob=datetime.today()):
self._name = name # variables with self prefix become part of the object,
self._score = score
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 score(self):
return self._score
@score.setter
def score(self, score):
self._score = score
def is_score(self, score):
return self._score == score
@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
@property
def age(self):
today = datetime.today()
return today.year - self._dob.year - ((today.month, today.day) < (self._dob.month, self._dob.day))
@property
def __str__(self):
return json.dumps(self.read())
def create(self):
try:
# creates a person object from Score(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,
"score": self.score,
"dob": self.dob,
"age": self.age,
}
# CRUD update: updates user name, password, phone
# returns self
def update(self, name="", score=""):
"""only updates values with length"""
if len(name) > 0:
self.name = name
if len(score) > 0:
self.score = score
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 """
def initScores():
with app.app_context():
"""Create database and tables"""
# db.init_app(app)
db.create_all()
"""Tester data for table"""
u1 = Score(name='Shruthi', score='2',dob=datetime(2006, 4, 14))
u2 = Score(name='Claire', score='3',dob=datetime(2005, 2, 11))
u3 = Score(name='Grace', score='1',dob=datetime(2005, 9, 11))
u4 = Score(name='Noor', score='5',dob=datetime(2004, 2, 11))
u5 = Score(name='Jiya', score='6',dob=datetime(2004, 8, 9))
u6 = Score(name='Kavya', score='1', dob=datetime(2009, 8, 30))
users = [u1, u2, u3, u4, u5, u6]
"""Builds sample user/note(s) data"""
for user in users:
try: #try to add new user
'''add user to table'''
object = user.create()
print(f"Created new name {object.name}")
except: # error raised if it does not work
'''fails with bad or duplicate data'''
print(f"Records exist name {user.name}, or error.")
initScores()
def find_by_name(name):
with app.app_context():
score = Score.query.filter_by(_name=name).first()
#
return score # returns user object
# Check credentials by finding user and verify password
def check_credentials(name,dob):
user = find_by_name(name)
if user == None:
return False
if (user.is_dob(dob)):
return True
return False
#check_credentials("indi", "123qwerty")
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:Find a specific entry by searching by uid 2. user = User(...): initializes/sets the User object
- user.dob and try/except: Creates the User attribute of dob. If DOB is set to be todays date, except will be run.
- user.create() and try/except: Creates new user. Error will be raised if the user was not build.
def create():
# optimize user time to see if uid exists
name = input("Enter your name:")
user = find_by_name(name)
try:
print("Found\n", user.read())
return
except:
pass # keep going
# request value that ensure creating valid object
score = input("Enter your score:")
# Initialize User object before date
user = Score(name=name,
score=score,
)
# 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 score {score}")
create()
# tested "Shruthi"
# SQLAlchemy extracts all users from database, turns each user into JSON
def read():
with app.app_context():
table = Score.query.all() #
json_ready = [user.read() for user in table] # "List Comprehensions", for each user add user.read() to list
return json_ready
read()
- Add this Blog to you own Blogging site. In the Blog add notes and observations on each code cell.
- Change blog to your own database.
- Add additional CRUD
- Add Update functionality to this blog.
- Add Delete functionality to this blog.