Big Idea 2 - Data Related to User Guide
A guide to adding data related to user (Big Idea 2)
Introduction
When building an application that requires users to create accounts or sign in, handling data related to users is crucial. This data can include things like user profiles, preferences, and activity logs, which can be used to personalize the user experience and improve the application's performance.
By learning how to handle data related to users effectively and efficiently, you'll be equipped with the skills and knowledge needed to build helpful applications that meet the needs of your users.
PLEASE DON'T OPEN THE POPCORN HACKS, WE WILL OPEN THEM DURING THE PRESENTATION
Basics of Class and User Data
Establishing Class/User Data and making a new user
Because Python is an object-oriented programming (OOP) language, everything in it represents an object. Lists, dictionaries, functions, etc. are all examples of Python objects. Generally speaking, a Python object is an entity of data items and methods describing the behavior of those items.
A Python class is a template used to create Python objects. We can think of it as a cooking recipe, where all the ingredients and their quantities are listed, and the whole process of cooking is described step-by-step.
In this case, we can compare a cake recipe to a class, and a cake cooked following that recipe to an object (i.e., an instance of that class). Using the same recipe (class), we can create many cakes (objects). This is the essence of creating a class in Python: to define data elements and the rules establishing how these elements can interact and change their state — and then use this framework to build various objects in a set way, instead of creating them from scratch every time.
Let's look at a few examples:
class Bikes:
name = ""
gear = 0
# create objects of class
bike1 = Bikes() # objectName = ClassName()
bike2 = Bikes()
Now that we have our class and object defined, we can now run code to modify certain attributes of our objects.
bike1.gear = 11
bike1.name = "Mountain Bike"
bike2.gear = 13
bike2.name = "Road Bike"
print(f"Name: {bike1.name}, Gears: {bike1.gear} ")
Popcorn Hacks #1:
Add another print command to display the attributes of Bike #2. What output will this code segment produce?print(f"Name: {bike2.name}, Gears: {bike2.gear}")
Example: Defining a User Class
Now we will look into Classes for users of a program or website.
class User:
def __init__(self, username, email):
self.username = username
self.email = email
def display_info(self):
print(f"Username: {self.username}, Email: {self.email}")
For this cell, we define a User class with a constructor method "init" that takes username and email as arguments. The display_info method is used to print the user information.
For backend functionality, this class can be used to create, manipulate, and manage user data. For example, when a new user signs up for an account, you could create a new User object with their username and email. This object can then be used to perform various operations, such as validating the user's input, storing the user's data in a database, or processing user-related requests.
Now let's make a new User:
new_user = User("jeffrey", "leejeffreysc@gmail.com")
new_user.display_info()
Here's a step-by-step breakdown of how the code relates to backend functionality:
new_user = User("jeffrey", "leejeffreysc@gmail.com"): This line creates a new User object, initializing it with the username "jeffrey" and the email "leejeffreysc@gmail.com". This could represent a user who has just signed up for an account, and the input values would typically come from user input, such as a frontend signup form.
new_user.display_info(): This line calls the display_info method on the new_user object, which prints the user's information (username and email) to the console. In a real-world backend application, you might use this method or a similar one to display user information in logs, send a welcome email, or create an API response with the user's data.
User/Class @ Decorators:
A Python Class allows for the use of @ decorators, these allow access to attribute data without the use of functions.
- @property decorator (aka getter). This enables developers to "GET" attribute data in a clean method (object.name versus object.get_name())
- @name.setter decorator (aka setter). This enables developers to "UPDATE"/set attribute data in a clean method (object.name = "Jeffrey" versus object.set_name("Jeffrey"))
The getter and setter methods provide a clean and controlled way to access and modify the attributes of an object. This can be particularly useful when interacting with databases, APIs, or other parts of a web application that require the management and manipulation of object attributes.
class Student:
def __init__(self, student, name):
self._student = student
self._name = name
@property
def name(self):
return self._name
@name.setter
def name(self, new_name):
self._name = new_name
In this example, the Student class has a name attribute, which is accessed and modified through the name property getter and setter methods. The _name attribute uses an underscore prefix, which is a convention to indicate it should not be accessed directly.
Usage of the getter and setter methods are below:
student = Student(1001, "Derek Sol")
print(student.name) # Get the name using the getter method
student.name = "Jagger Klein" # Set the name using the setter method
print(student.name)
Popcorn Hacks #2:
The following code segment for computers does not have getter and setter methods called. Using the missing attributes "model" and "year", add example getter and setters methods for this class. Show us an example output that this code segment might produce.class Computer:
def __init__(self, model, year):
self._model = model
self._year = year
@property
def model(self):
return self._model
@model.setter
def model(self, new_model):
self._model = new_model
@property
def year (self):
return self._year
@year.setter
def year(self, new_year):
self._year = new_year
computer = Computer("ACER", 2022)
print(computer.model) # Get the name using the getter method
computer.model = "MSI" # Set the name using the setter method
print(computer.model)
Databases and SQLite
A database is a structured collection of data that is organized and stored in a way that allows for efficient retrieval, manipulation, and management of that data. In other words, a database is a software application that allows you to store and manage large amounts of data in an organized manner.
Some Examples of Databases:
- Storing and managing customer data, such as contact information, order history, and customer preferences.
- Keeping track of inventory levels and stock movements in a warehouse or retail store.
- Managing financial transactions, such as payments, invoices, and receipts.
SQLite is a software library that provides a user-friendly database management system. Unlike other databases, such as MySQL or PostgreSQL, SQLite is programmed within an application, which means it does not require a separate server process to operate. This makes SQLite great for web applications or for use in situations where you don't need a full database server.
In this lesson, we will be demonstrating how to set up a SQLite database in Flask, a Python web framework. Flask allows programmers to easily interact with SQLite databases, and we'll walk through the process of setting up a new database, creating tables, and adding data. We'll also cover some basic SQL commands that you can use to interact with your database, including CREATE TABLE, INSERT, SELECT, UPDATE, and DELETE.
Setting up a SQLite database in Flask
from flask import Flask
import sqlite3
# Create a Flask application
app = Flask(__name__)
# Connect to the SQLite database using SQLite3
conn = sqlite3.connect('database.db')
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
# Create a table in the database using SQL commands
cursor.execute('''CREATE TABLE students
(id INTEGER PRIMARY KEY, name TEXT, grade INTEGER)''')
# Commit the changes to the database
conn.commit()
# Close the connection
conn.close()
Note: This will create a new file 'database.db' in the _notebooks folder if one doesn't exist.
Breaking down this code segment:
- We imported the necessary libraries and tools
- We created a Flask application
- We connected to the SQLite database using SQLite3
- We created a cursor object to execute SQL commands
- We created tables in the database using SQL commands
Flask provides several extensions and tools for working with SQLite databases, including Flask-SQLAlchemy, Flask-SQLite3, and Flask-Admin. These tools provide a high-level interface for interacting with the database, making it easy to perform common operations such as adding, updating, and deleting records.
CRUD Commands with SQLite
CRUD stands for Create, Read, Update, and Delete, which are the basic operations you can perform on data. SQL is useful library because it allows programmers to create new tables to organize data, add new data to a table, update data that's already there, or delete data that's no longer needed.
C: To create a new record in a database, you must first define the table structure that will store the data. This can be accomplished using SQL commands such as CREATE. Once the table is created, data can be added to it using the INSERT INTO command.
R: To retrieve data from the database, you can use the READ command. You can specify which fields you want to retrieve and the conditions you want to apply using the WHERE clause. There are also several functions available to aggregate and manipulate data.
U: To modify existing data in the database, you can use the UPDATE command. You will need to specify which table and fields you want to update, and the conditions you want to apply using the WHERE clause.
D: To remove data from the database, you can use the DELETE command.
from flask import Flask
import sqlite3
# Create a Flask application
app = Flask(__name__)
# Connect to the SQLite database using SQLite3
conn = sqlite3.connect('database.db')
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
# Create a table in the database using SQL commands
cursor.execute('''CREATE TABLE basketball
(_player TEXT PRIMARY KEY, _team TEXT, _position TEXT, _opinion TEXT)''')
# Commit the changes to the database
conn.commit()
# Close the connection
conn.close()
Hypothetically, our programmer wanted to create an application about basketball players, and created a new table in SQL to store and interact with user-submitted data. Using the same method as the previous table, a new table 'basketball' is created by the cursor and added to the 'database.db' file.
def create():
database = 'database.db'
player = input("Enter the player name")
team = input("Enter team name")
position = input("Enter player position")
opinion = input("Enter your opinion ")
# Connecting to the database, create cursor to execute the SQL command
connection = sqlite3.connect(database)
cursor = connection.cursor()
try:
# Execute SQL to insert record in to db
cursor.execute("INSERT INTO basketball (_player, _team, _position, _opinion) VALUES (?, ?, ?, ?)", (player, team, position, opinion))
# Commit the changes
connection.commit()
print(f"New basketball with {player} is added.")
except sqlite3.Error as error:
print("Error while inserting record", error)
# Closing cursor and connection
cursor.close()
connection.close()
create()
The create() feature allows users to input information about a basketball player and store it in a SQLite table 'basketball' inside the database file we already created 'database.db'. The code prompts the user for the player's name, team name, position, and their opinion about the player, then establishes a connection to the SQLite database and creates a cursor object for executing SQL commands.
The cursor.execute() method is called with an SQL INSERT command to insert a new entry into the 'basketball' table. The connection.commit() method saves the changes to the database, and if the record is inserted successfully, a confirmation message is printed. In case of errors, the except block catches the sqlite3.Error exception and prints an error message. Finally, the cursor and the connection to the database are closed, and the create() function is called to execute the code.
def read():
database = 'database.db'
# Connecting to the database, create cursor to execute the SQL command
connection = sqlite3.connect(database)
cursor = connection.cursor()
# Fetch all the records from basketball table
results = cursor.execute('SELECT * FROM basketball').fetchall()
if len(results) != 0:
for row in results:
print(row)
else:
print("No player found.")
# Closing cursor and connection
cursor.close()
connection.close()
read()
The read() feature retrieves and prints all entries from the 'basketball' table in the specified SQLite database. The script establishes a connection to the SQLite database, creates a cursor object for executing SQL commands, and calls the cursor.execute() method with an SQL SELECT command to fetch all the entires from the 'basketball' table. The fetchall() method retrieves all the rows returned by the SELECT command and stores them in the results variable.
The code then checks if there are any records in the table and, if so, iterates through each row in the results using a 'for loop' and prints the row. If there are no records, the program prints "No player found". Finally, the cursor and the connection to the database are closed, and the read() function is called to execute the code.
import sqlite3
def update():
database = 'database.db'
player = input("Enter a basketball player to update the opinion for:")
opinion = input("Enter new opinion:")
# Connecting to the database, create cursor to execute the SQL command
connection = sqlite3.connect(database)
cursor = connection.cursor()
try:
# Updating review for the basketball
cursor.execute("UPDATE basketball SET _opinion = ? WHERE _player = ?", (opinion, player))
if cursor.rowcount != 0:
print(f"Review for the basketball is updated to {opinion}")
connection.commit()
else:
print(f"Player not found.")
except sqlite3.Error as error:
print("Error occurred", error)
# Closing cursor and connection
cursor.close()
connection.close()
update()
This update feature updates the opinion for a specific basketball player in an SQLite database. The code defines an update() function, which prompts the user to input the basketball player they want to update and the new opinion for that player. The script connects to the SQLite database, creates a cursor object, and handles any potential errors while executing the SQL command. The cursor.execute() method updates the review in the database for the specified basketball item.
If at least one value is changed, a success message is printed and the changes are committed to the database. Otherwise, a "Player not found." message is displayed. In the except block, if an error occurs, an error message along with specific error details is printed. Finally, the cursor and connection to the database are closed, and the update() function is called to execute the review update process.
import sqlite3
def delete():
database = 'database.db'
player = input("Enter basketball player to delete")
# Connecting to the database, create cursor to execute the SQL command
connection = sqlite3.connect(database)
cursor = connection.cursor()
try:
cursor.execute("DELETE FROM basketball WHERE _player = ?", (player,))
if cursor.rowcount == 0:
print(f"{player} does not exist")
else:
print(f"Successfully deleted basketball player {player}")
connection.commit()
except sqlite3.Error as error:
print("Error occurred: ", error)
# Closing cursor and connection
cursor.close()
connection.close()
delete()
The delete feature deletes a specific basketball player from the specified SQLite database. The code defines a delete() function that prompts the user to input the basketball player they want to delete. After connecting to the SQLite database and creating a cursor object, the code handles potential errors while executing the SQL command. The cursor.execute() method is used to delete the basketball item with the specified ID.
If no rows are affected, a message is printed indicating that the basketball player does not exist. Otherwise, a success message is displayed, confirming the basketball player's deletion. The changes are then committed to the database. In the except block, any error that occurs triggers an error message along with specific error details. Finally, the cursor and connection to the database are closed, and the delete() function is called to execute the basketball item deletion process. This code showcases a practical way to remove information from an SQLite database based on user input.
Menu Functions:
Alongside the four CRUD functions, a menu function is designed to manage Create, Read, Update, Delete operations and display the schema. The menu function is responsible for taking in user inputs and making the program call various functions. When running a menu function, users enter their desired operation, whether it be Create, Read, Update, or Delete, and the input is checked by various conditions to determine which function should be called. Many menu functions are called recursively, so the cell will repeat the menu process until the user decides to exit.
Popcorn Hacks #3:
The menu function below is missing some of the CRUD functions for the specified basketball players table. Fix the following code by adding the intended CRUD functions, then perform a READ function on the database.def menu():
operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete")
if operation.lower() == 'c':
create()
elif operation.lower() == 'r':
read()
elif operation.lower() == 'u':
update()
elif operation.lower() == 'd':
delete()
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")
Image Uploading with SQLite
Using various Python Libraries, we are able to upload and customize images using python code, as well as using images to interact with databases.
One way to store images in a database is to INSERT the image data as a binary blob.
To store an image as a blob in SQLite, you can first read the image file and convert it to a binary format using a programming language like Python. Here's an example of how to do this:
from flask import Flask
import sqlite3
# Create a Flask application
app = Flask(__name__)
# Connect to the SQLite database using SQLite3
conn = sqlite3.connect('database.db')
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
# Create a table in the database using SQL commands
cursor.execute('''CREATE TABLE images
(name TEXT PRIMARY KEY, data BLOB)''')
# Commit the changes to the database
conn.commit()
# Close the connection
conn.close()
We use a new data type "BLOB" to store specific data from our .jpg image.
Adding Image Data to our Table:
You can use this example sunset jpg for our code. Download and place it in the same repository as this notebook. Rename image to 'image.jpg'
https://t4.ftcdn.net/jpg/01/04/78/75/360_F_104787586_63vz1PkylLEfSfZ08dqTnqJqlqdq0eXx.jpg
import sqlite3
# Open a connection to the SQLite database
conn = sqlite3.connect('database.db')
# Read the image file as binary data
with open('image.jpg', 'rb') as jpg:
image_data = jpg.read()
# Insert the image data into the database
conn.execute("INSERT INTO images (name, data) VALUES (?, ?)", ('image', image_data))
# Commit the transaction
conn.commit()
# Close the connection
conn.close()
In this example, the image data is read from the "image.jpg" file and inserted into the "images" table in the "database.db" SQLite database. The image data is then stored as a binary blob in the "data" column of the table.
import sqlite3
from PIL import Image
import io
# Open a connection to the SQLite database
conn = sqlite3.connect('database.db')
# Retrieve the image data from the database
result = conn.execute("SELECT data FROM images WHERE name=?", ('image',)).fetchone()
# Convert the binary data to an image object
image_data = io.BytesIO(result[0])
image = Image.open(image_data)
# Display the image
image.show()
# Close the connection
conn.close()
The image data is then retrieved from the "images" table using a SELECT statement with a WHERE clause that correctly matches the image name. The binary image data is then converted to an image object using the PIL library and displayed on the screen.
Popcorn Hacks #4:
Using the same table, find a new .jpg image and add its image data to the database. Display the image using the PIL Library and show us the output image.import sqlite3
from PIL import Image
import io
# Open a connection to the SQLite database
conn = sqlite3.connect('database.db')
# Read the image file as binary data
with open('codehoots.jpg', 'rb') as jpg:
image_data = jpg.read()
# Insert the image data into the database
conn.execute("INSERT INTO images (name, data) VALUES (?, ?)", ('codehoots', image_data))
# Commit the transaction
conn.commit()
# Close the connection
conn.close()
# Open a connection to the SQLite database
conn = sqlite3.connect('database.db')
# Retrieve the image data from the database
result = conn.execute("SELECT data FROM images WHERE name=?", ('codehoots',)).fetchone()
# Convert the binary data to an image object
image_data = io.BytesIO(result[0])
image = Image.open(image_data)
# Display the image
image.show()
# Close the connection
conn.close()
HACKS (Due Sunday 11:59 PM)
Make sure to fill in all blank notes and popcorn hacks, and run each of the interactive coding exercises. (0.45)
- Extra Credit: Winning team for Popcorn Hacks (+0.05)
Create your own SQL database and create an algorithm that can insert, update, and delete data related to your topic of choice. Functionality for adding image data MUST be included. Points will be awarded based on effort and success. (0.45)
- Extra Credit: Connect your backend database to a visible frontend! Screenshots of displayed data is fine. (+0.05)
from flask import Flask
import sqlite3
# Create a Flask application
app = Flask(__name__)
# Connect to the SQLite database using SQLite3
conn = sqlite3.connect('database.db')
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
# Create a table in the database using SQL commands
cursor.execute('''CREATE TABLE water
(_brand TEXT PRIMARY KEY, _water TEXT, _taste TEXT)''')
# Commit the changes to the database
conn.commit()
# Close the connection
conn.close()
def create():
database = 'database.db'
brand = input("Enter the brand name")
water = input("Is it water?")
taste = input("How does it taste")
# Connecting to the database, create cursor to execute the SQL command
connection = sqlite3.connect(database)
cursor = connection.cursor()
try:
# Execute SQL to insert record in to db
cursor.execute("INSERT INTO water (_brand, _water, _taste) VALUES (?, ?, ?)", (brand, water, taste))
# Commit the changes
connection.commit()
print(f"New water with {brand} is added.")
except sqlite3.Error as error:
print("Error while inserting record", error)
# Closing cursor and connection
cursor.close()
connection.close()
create()
def read():
database = 'database.db'
# Connecting to the database, create cursor to execute the SQL command
connection = sqlite3.connect(database)
cursor = connection.cursor()
# Fetch all the records from water table
results = cursor.execute('SELECT * FROM water').fetchall()
if len(results) != 0:
for row in results:
print(row)
else:
print("No water found.")
# Closing cursor and connection
cursor.close()
connection.close()
read()
import sqlite3
def update():
database = 'database.db'
brand = input("Enter a water player to update the taste for:")
taste = input("Enter new taste:")
# Connecting to the database, create cursor to execute the SQL command
connection = sqlite3.connect(database)
cursor = connection.cursor()
try:
# Updating review for the water
cursor.execute("UPDATE water SET _taste = ? WHERE _brand = ?", (taste, brand))
if cursor.rowcount != 0:
print(f"Review for the water is updated to {taste}")
connection.commit()
else:
print(f"Water not found.")
except sqlite3.Error as error:
print("Error occurred", error)
# Closing cursor and connection
cursor.close()
connection.close()
update()
import sqlite3
def delete():
database = 'database.db'
brand = input("Enter water to delete")
# Connecting to the database, create cursor to execute the SQL command
connection = sqlite3.connect(database)
cursor = connection.cursor()
try:
cursor.execute("DELETE FROM water WHERE _brand = ?", (brand,))
if cursor.rowcount == 0:
print(f"{brand} does not exist")
else:
print(f"Successfully deleted water {brand}")
connection.commit()
except sqlite3.Error as error:
print("Error occurred: ", error)
# Closing cursor and connection
cursor.close()
connection.close()
delete()
from flask import Flask
import sqlite3
# Create a Flask application
app = Flask(__name__)
# Connect to the SQLite database using SQLite3
conn = sqlite3.connect('database.db')
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
# Create a table in the database using SQL commands
cursor.execute('''CREATE TABLE waterimages
(name TEXT PRIMARY KEY, data BLOB)''')
# Commit the changes to the database
conn.commit()
# Close the connection
conn.close()
def menu():
operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete")
if operation.lower() == 'c':
create()
elif operation.lower() == 'r':
read()
elif operation.lower() == 'u':
update()
elif operation.lower() == 'd':
delete()
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")
import sqlite3
# Open a connection to the SQLite database
conn = sqlite3.connect('database.db')
# Read the image file as binary data
with open('arrowheadwater.jpg', 'rb') as jpg:
image_data = jpg.read()
# Insert the image data into the database
conn.execute("INSERT INTO waterimages (name, data) VALUES (?, ?)", ('arrowheadwater', image_data))
# Commit the transaction
conn.commit()
# Close the connection
conn.close()
import sqlite3
from PIL import Image
import io
# Open a connection to the SQLite database
conn = sqlite3.connect('database.db')
# Retrieve the image data from the database
result = conn.execute("SELECT data FROM waterimages WHERE name=?", ('arrowheadwater',)).fetchone()
# Convert the binary data to an image object
image_data = io.BytesIO(result[0])
image = Image.open(image_data)
# Display the image
image.show()
# Close the connection
conn.close()
