2.4b Hacks
Database Programming is Program with Data
Each Tri 2 Final Project should be an example of a Program with Data.
Prepare to use SQLite in common Imperative Technique
- Explore SQLite Connect object to establish database connection- Explore SQLite Cursor Object to fetch data from a table within a database
Schema of Users table in Sqlite.db
Uses PRAGMA statement to read schema.
Describe Schema, here is resource Resource- What is a database schema?
- database schema is like a blueprint
- What is the purpose of identity Column in SQL database?
- Identity column defines the variable
- In this case it was UID
- What is the purpose of a primary key in SQL database?
- Identifies the user
"""
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)
""" database dependencies to support sqliteDB examples """
from random import randrange
import os, base64
import json
from sqlalchemy.exc import IntegrityError
''' Tutorial: https://www.sqlalchemy.org/library.html#tutorials, try to get into Python shell and follow along '''
# Define the Recipe class to manage actions in the 'recipes' 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.) Recipe 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 Recipe(db.Model):
__tablename__ = 'recipes' # table name is plural, class name is singular
# Define the User schema with "vars" from object
id = db.Column(db.Integer, unique=True, primary_key=True)
_recipename = db.Column(db.String(255), unique=False, nullable=False)
_recipelink = db.Column(db.String(255), unique=False, nullable=False)
_recipetype = db.Column(db.String(255), unique=False, nullable=False)
_recipecuisine = db.Column(db.String(255), unique=False, nullable=False)
# Defines a relationship between Recipe record and Notes table, one-to-many (one recipe to many notes)
# constructor of a User object, initializes the instance variables within object (self)
def __init__(self, recipename, recipelink, recipetype, recipecuisine):
self._recipename = recipename # variables with self prefix become part of the object,
self._recipelink = recipelink
self._recipetype = recipetype
self._recipecuisine = recipecuisine
# a name getter method, extracts name from object
@property
def recipename(self):
return self._recipename
# a setter function, allows name to be updated after initial object creation
@recipename.setter
def recipename(self, recipename):
self._recipename = recipename
# a getter method, extracts link from object
@property
def recipelink(self):
return self._recipelink
# a setter function, allows link to be updated after initial object creation
@recipelink.setter
def recipelink(self, recipelink):
self._recipelink = recipelink
# a getter method, extracts link from object
@property
def recipetype(self):
return self._recipetype
# a setter function, allows link to be updated after initial object creation
@recipetype.setter
def recipetype(self, recipetype):
self._recipetype = recipetype
# a getter method, extracts link from object
@property
def recipecuisine(self):
return self._recipecuisine
# a setter function, allows link to be updated after initial object creation
@recipecuisine.setter
def recipecuisine(self, recipecuisine):
self._recipecuisine = recipecuisine
@property
# 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 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,
"recipename" : self.recipename,
"recipelink" : self.recipelink,
"recipetype" : self.recipetype,
"recipecuisine" : self.recipecuisine,
}
# CRUD update: updates user name, password, phone
# returns self
def update(self, recipename="", recipelink="", recipetype="", recipecuisine=""):
"""only updates values with length"""
if len(recipename) > 0:
self.recipename = recipename
if len(recipelink) > 0:
self.recipelink = recipelink
if len(recipetype) > 0:
self.recipetype = recipetype
if len(recipecuisine) > 0:
self.recipecuisine = recipecuisine
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 """
# Builds working data for testing
def initRecipes():
with app.app_context():
"""Create database and tables"""
db.create_all()
"""Tester data for table"""
r1 = Recipe(recipename='Avocado Toast', recipelink='link1', recipetype='Breakfast', recipecuisine='American')
r2 = Recipe(recipename='Scrambled Eggs', recipelink='link2', recipetype='Breakfast', recipecuisine='American')
r3 = Recipe(recipename='Pancake', recipelink='link3', recipetype='Breakfast', recipecuisine='American')
r4 = Recipe(recipename='Mac and Cheese', recipelink='link4', recipetype='Lunch', recipecuisine='American')
r5 = Recipe(recipename='Panini Sandwich', recipelink='link5', recipetype='Lunch', recipecuisine='French')
r6 = Recipe(recipename='Salad', recipelink='link6', recipetype='Lunch', recipecuisine='Mediterranean')
r7 = Recipe(recipename='Minestrone Soup', recipelink='link7', recipetype='Dinner', recipecuisine='Italian')
r8 = Recipe(recipename='Lasagna', recipelink='link8', recipetype='Dinner', recipecuisine='Italian')
r9 = Recipe(recipename='Pasta', recipelink='link9', recipetype='Dinner', recipecuisine='Italian')
r10 = Recipe(recipename='Brownies', recipelink='link10', recipetype='Dessert', recipecuisine='German')
r11 = Recipe(recipename='Chocolate Chip Cookies', recipelink='link11', recipetype='Dessert', recipecuisine='American')
r12 = Recipe(recipename='Custard Pudding', recipelink='link12', recipetype='Dessert', recipecuisine='German')
recipes = [r1, r2, r3, r4, r5, r6, r7, r8, r9, r10, r11, r12]
"""Builds sample user/note(s) data"""
for recipe in recipes:
try:
recipe.create()
except IntegrityError:
'''fails with bad or duplicate data'''
db.session.remove()
print(f"Records exist, duplicate email, or error: {recipe.model}")
import sqlite3
database = 'instance/sqlite.db' # this is location of database
def schema():
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL queries
cursor = conn.cursor()
# Fetch results of Schema
results = cursor.execute("PRAGMA table_info('recipes')").fetchall()
# Print the results
for row in results:
print(row)
# Close the database connection
conn.close()
schema()
Reading Users table in Sqlite.db
Uses SQL SELECT statement to read data
- What is a connection object? After you google it, what do you think it does?
- should connect to a database
- Same for cursor object?
- makes the connection for executing SQL
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- special variables
- database
- file
- sqlite3
- Is "results" an object? How do you know?
- results is not an object
- does not show up as an attribute in debugger
import sqlite3
def read():
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL queries
cursor = conn.cursor()
# Execute a SELECT statement to retrieve data from a table
results = cursor.execute('SELECT * FROM recipes').fetchall()
# Print the results
if len(results) == 0:
print("Table is empty")
else:
for row in results:
print(row)
# Close the cursor and connection objects
cursor.close()
conn.close()
read()
import sqlite3
def create():
recipename = input("Enter recipe name:")
recipelink = input("Enter recipe link:")
recipetype = input("Enter recipe type:")
recipecuisine = input("Enter recipe cuisine:")
# 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 insert data into a table
cursor.execute("INSERT INTO recipes (_recipename, _recipelink, _recipetype, _recipecuisine) VALUES (?, ?, ?, ?)", (recipename, recipelink, recipetype, recipecuisine))
# Commit the changes to the database
conn.commit()
print(f"A new recipe record {recipename} has been created")
except sqlite3.Error as error:
print("Error while executing the INSERT:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
create()
Updating a User in table in Sqlite.db
Uses SQL UPDATE to modify password
- What does the hacked part do?
- prevents passwords that don't meet criteria
- Explain try/except, when would except occur?
- except occurs when the user input is an exception to the set rules
- What code seems to be repeated in each of these examples to point, why is it repeated?
- The repeated code seems to be input and close
- done to close the cursor and connection objects
import sqlite3
def update():
recipename = input("Enter recipe name to update")
#recipelink = input("Enter recipe link to update")
recipetype = input("Enter recipe type to update")
#recipecuisine = input("Enter recipe cuisine to update")
# 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 recipes SET _recipetype = ? WHERE _recipename = ?", (recipename, recipetype))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No uid {recipename} was not found in the table")
else:
print(f"The row with recipe name {recipename} the recipe type has updated successfully")
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():
recipename = input("Enter recipe name 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 recipes WHERE _recipename = ?", (recipename,))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No recipe {recipename} was not found in the table")
else:
# The uid was found in the table and the row was deleted
print(f"The row with uid {recipename} 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()
def menu():
operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
if operation.lower() == 'c':
create()
elif operation.lower() == 'r':
read()
elif operation.lower() == 'u':
update()
elif operation.lower() == 'd':
delete()
elif operation.lower() == 's':
schema()
elif len(operation)==0: # Escape Key
return
else:
print("Please enter c, r, u, or d")
menu() # recursion, repeat menu
try:
menu() # start menu
except:
print("Perform Jupyter 'Run All' prior to starting menu")