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
This has been used in other notebooks, and allows for a database to creates without having seperate api and python files. Like in our flask for our CPT, we needed to have one file to create an API and the other to create the SQL database.
-
SQLAlchemy db object
Allows for communication between python and databased, which is needed for CRUD. We also used this in our CPT when we iterated through databases, and displayed (read) from frontend as well.
-
"""
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 :defines the object that we will be using CRUD operations on - db.Model inheritance: we are able to organize our objects into a database, and display them in our sqlite.db folder
- init method: allows class to initialize object's attributes
-
@property: creates and returns object,@<column>.setter: sets column for sqlite.db table - create (creates new object(reads existing database), update(changes object in existing database), delete methods(removes object from 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 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 Client(db.Model):
__tablename__ = 'clients' # table name is plural, class name is singular
# Define the Client schema with "vars" from object
id = db.Column(db.Integer, primary_key=True)
_product = db.Column(db.String(255), unique=False, nullable=False)
_ingredients = db.Column(db.String(255), unique=False, nullable=False)
_skinType = db.Column(db.String(255), unique=False, nullable=False)
_date = db.Column(db.String(255), unique=False, nullable=False)
_pid = db.Column(db.String(255), unique=False, nullable=False)
def __init__(self, product, pid, ingredients, date, skinType):
self._product = product # variables with self prefix become part of the object,
self._ingredients = ingredients
self._date = date
self._pid = pid
self._skinType = skinType
# a name getter method, extracts name from object
@property
def product(self):
return self._product
# a setter function, allows name to be updated after initial object creation
@product.setter
def product(self, product):
self._product = product
# a name getter method, extracts name from object
@property
def pid(self):
return self._pid
# a setter function, allows name to be updated after initial object creation
@pid.setter
def pid(self, pid):
self._pid = pid
# a getter method, extracts email from object
@property
def ingredients(self):
return self._ingredients
# a setter function, allows name to be updated after initial object creation
@ingredients.setter
def ingredients(self, ingredients):
self._ingredients = ingredients
# a getter method, extracts email from object
@property
def skinType(self):
return self._skinType
# a setter function, allows name to be updated after initial object creation
@skinType.setter
def skinType(self, skinType):
self._skinType = skinType
# a getter method, extracts email from object
@property
def date(self):
return self._date
# a setter function, allows name to be updated after initial object creation
@date.setter
def date(self, date):
self._date = date
# output content using str(object) in human readable form, uses getter
# 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 Client(db.Model) class, passes initializers
db.session.add(self) # add prepares to persist person object to 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 {
"product": self._product,
"ingredients": self._ingredients,
"date": self._date,
"skinType": self._skinType,
"pid": self._pid,
}
# CRUD update: updates product
# returns self
def update(self, product=""):
"""only updates values with length"""
if len(product) > 0:
self.product = product
db.session.commit()
return self
# CRUD delete: remove self and product
# None
def delete(self, product):
db.session.delete(self)
db.session.delete(product)
db.session.commit()
return None
"""Database Creation and Testing """
# Builds working data for testing
def initClients():
with app.app_context():
"""Create database and tables"""
db.create_all() # uses the database model to put objects (defined below) into a table
"""Tester data for table""" # uses earlir defined class to create objects
u1 = Client(product='Cetaphil Gentle Skin Cleanser', pid='001', skinType='dry', ingredients='Water, Glycerin, Cocamidopropyl Betaine, Disodium Laureth Sulfosuccinate, Sodium Cocoamphoacetate, Panthenol, Niacinamide, Pantolactone, Acrylates/C10-30 Alkyl Acrylate Crosspolymer, Sodium Benzoate, Masking Fragrance, Sodium Chloride, Citric Acid', date='2023')
u2 = Client(product='Alaffia Everyday Coconut Face Toner', pid='002', skinType='dry', ingredients='Azadirachta indica (neem) leaf aqueous extract, Carica Papaya (Papaya) Leaf Aqueous Extract, Cocos Nucifera (Coconut) Water, Glycerin, Lavandula Hybrida (Lavender) Oil, Phenoxyethanol, Potassium Sorbate, Ascorbic Acid, Cocos Nucifera (Coconut) Extract', date='2023')
u3 = Client(product='Laurel Skin Deep Clarity Oil Cleanser', pid='003', skinType='combination', ingredients='Safflower Seed Oil, Sunflower Seed Oil, Sesame Seed Oil, Tamanu Oil, Black Cumin Seed Oil, Whole Plant Extracts of Rosemary, Calendula, Turmeric, Essential Oils of Green Mandarin, Ylang Ylang, Neroli, Rosemary', date='2023')
u4 = Client(product='Glow Recipe Avocado Ceramide Redness Relief Serum', pid='004', skinType='combination', ingredients='Water/Aqua/Eau, Glycerin, Jojoba Esters, Persea Gratissima (Avocado) Oil, Persea Gratissima (Avocado) Fruit Extract, Niacinamide, Ceramide NP, Ceramide NS, Ceramide AP, Ceramide EOP, Ceramide EOS, Hydrolyzed Rice Protein, Allantoin, Oryza Sativa (Rice) Bran Oil, Bisabolol, Zingiber Officinale (Ginger) Root Extract, Curcuma Longa (Turmeric) Root Extract, Tocopherol, Palmitoyl Tripeptide-8, Vitis Vinifera (Grape) Fruit Extract, Potassium Palmitoyl Hydrolyzed Wheat Protein, Boswellia Serrata Extract, Populus Tremuloides Bark Extract, Caprylic/Capric Triglyceride, Sodium Phytate, Cetyl Alcohol, Melia Azadirachta Flower Extract, Melia Azadirachta Leaf Extract, Ocimum Sanctum Leaf Extract, Behenic Acid, Cholesterol, Ethylhexylglycerin, Ocimum Basilicum (Basil) Flower/Leaf Extract, Elettaria Cardamomum Seed Extract, Jasminum Officinale (Jasmine) Flower/Leaf Extract, Corallina Officinalis Extract, Sodium Carbonate, Sodium Chloride, Chlorophyllin-Copper Complex (CI 75810), Camellia Sinensis Leaf Extract, Cananga Odorata Flower Extract, Caprooyl Phytosphingosine, Caprooyl Sphingosine, Cucumis Melo (Melon) Fruit Extract, Cucumis Sativus (Cucumber) Fruit Extract, Pyrus Malus (Apple) Fruit Extract, Rose Extract, Rubus Idaeus (Raspberry) Leaf Extract, Ascorbyl Palmitate, Dextran, Hydroxyacetophenone, Xanthan Gum, Butylene Glycol, Carbomer, Cetearyl Olivate, Sorbitan Olivate, 1,2-Hexanediol, Caprylyl Glycol, Hydrogenated Vegetable Oil, Lavandula Angustifolia (Lavender) Flower/Leaf/Stem Extract, Santalum Album (Sandalwood) Wood Extract, Tocopheryl Acetate, Ceteareth-25, Sodium Hydroxide, Phenoxyethanol, Sodium Benzoate', date='2023')
u5 = Client(product='Paula’s Choice Pore-Reducing Toner', pid='005', skinType='oily', ingredients='Water, Glycerin, Butylene Glycol, Niacinamide, Adenosine Triphosphate, Anthemis Nobilis (Chamomile) Flower Extract (anti-irritant), Arctium Lappa (Burdock) Root Extract, Hydrolyzed Jojoba Esters, Hydrolyzed Vegetable Protein, Sodium PCA, Panthenol, Sodium Hyaluronate, Sodium Chondroitin Sulfate, Ceramide 3, Ceramide 6 II, Ceramide 1, Phytosphingosine, Cholesterol, Tetrahexyldecyl Ascorbate, Oleth-10, DEA-Oleth-10 Phosphate, Sodium Lauroyl Lactylate, Polysorbate-20, Caprylyl Glycol, Hexylene Glycol, Sodium Citrate, Xanthan Gum, Trisodium EDTA, Phenoxyethanol', date='2023')
u6 = Client(product='Drunk Elephant Beste No. 9 Jelly Cleanser', pid='006', skinType='oily',ingredients='Water/Aqua/Eau, Glycerin, Cocamidopropyl Betaine, Coco-Glucoside, Sodium Lauroyl Methyl Isethionate, Cocamidopropyl Hydroxysultaine, Sodium Methyl Oleoyl Taurate, Propanediol, Aloe Barbadensis Leaf Extract, Glycolipids, Linoleic Acid, Linolenic Acid, Lauryl Glucoside, Cucumis Melo Cantalupensis Fruit Extract, Sclerocarya Birrea Seed Oil, Dipotassium Glycyrrhizate, Tocopherol, Citric Acid, Phenoxyethanol, Sodium Hydroxide, Sodium Benzoate, Sodium Chloride, Polylysine', date='2023')
clients = [u1, u2, u3, u4, u5, u6]
"""Builds sample user/note(s) data"""
for client in clients:
try: # tries to create the new object in the table
'''add user to table'''
object = client.create()
print(f"Created new product {object.product}")
except: # error raised if object nit created, doesn't create if duplicate
'''fails with bad or duplicate data'''
print(f"Records exist product {client.product}, or error.")
initClients()
Check for given Credentials in users table in sqlite.db
Use of ORM Query object and custom methods to identify user to credentials uid and password
- Comment on purpose of following
- Client.query.filter_by:filters through existing database to determine whether a product already exists 2. client.pid: uses product id to verify or authenticate before completing functions
# uid to product, password to pid
def find_by_product(product):
with app.app_context():
client = Client.query.filter_by(_product=product).first()
return client # returns user object
# Check credentials by finding user and verify password
def check_credentials(product, pid):
# query email and return user record
client = find_by_product(product)
if client == None:
return False
if (client.is_pid(pid)):
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
- client.find_by_product() and try/except:finds object that includes user inputted product, then outputs whether it is found or not (keep going) 2. client = Client(...): initializes object to "client"
- client.create() and try/except: allows the create function to add an object to the database
def create():
# optimize user time to see if uid exists
product = input("Enter product name:")
client = find_by_product(product)
try:
print("Found\n", client.read())
return
except:
pass # keep going
# request value that ensure creating valid object
product = input("Enter product name:")
ingredients = input("Enter product ingredients:")
skinType = input("Enter skin type best for product:")
date = input("Enter production year:")
pid = input("Enter product ID:")
# Initialize User object before date
client = Client(product=product,
ingredients=ingredients,
skinType=skinType, date=date, pid=pid
)
# write object to database
with app.app_context():
try:
object = client.create()
print("Created\n", object.read())
except: # error raised if object not created
print("Unknown error product {product}")
create()
# SQLAlchemy extracts all users from database, turns each user into JSON
def read():
with app.app_context():
table = Client.query.all()
json_ready = [client.read() for client in table] # "List Comprehensions", for each user add user.read() to list
return json_ready
read()
import sqlite3
database = 'instance/sqlite.db'
def update():
product = input("Enter product name to update:")
pid = input("Enter pid to update:")
if len(pid) < 2:
message = "hacked"
pid = 'gothackednewpassword123'
else:
message = "successfully updated"
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to update data in a table
cursor.execute("UPDATE clients SET _pid = ? WHERE _product = ?", (pid, product))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No product {product} was not found in the table")
else:
print(f"The row with product name {product} the pid has been {message}")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the UPDATE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
update()
import sqlite3
def delete():
product = input("Enter product to delete")
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
cursor.execute("DELETE FROM clients WHERE _product = ?", (product,))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No product {product} was not found in the table")
else:
# The uid was found in the table and the row was deleted
print(f"The row with product {product} was successfully deleted")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the DELETE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
delete()