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} ")
Name: Mountain Bike, Gears: 11 
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} ")
Name: Road Bike, Gears: 13 

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 constructive_ method "init" that takes username and email as _attributes__. 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()
Username: jeffrey, Email: leejeffreysc@gmail.com

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)
Derek Sol
Jagger Klein
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
class Computer:
    def __init__(self, model, year):
        self._model = model
        self._year = year
    def get_model(self):
        return self._model
    def set_model(self, new_model):
        self._model = new_model
    def get_year(self):
        return self._year
    def set_year(self, new_year):
        self._year = new_year
        
c = Computer('Macbook Pro', 2021)
print(c.get_model())

c.set_model('Macbook Air')
print(c.get_model())

print(c.get_year())
c.set_year(2022)
print(c.get_year())
Macbook Pro
Macbook Air
2021
2022

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.

Example of CRUD within an SQLite Database

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.

Create Function:

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()
New basketball with Kobe  is added.

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.

Read Function:

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()
('Kobe ', 'Lakers', 'Shooting Guard', 'he is the GOAT')

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.

Update Function:

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()
Review for the basketball is updated to he sucks

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.

Delete Function

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()
Successfully deleted basketball player Kobe 

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.

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")
No player found.

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:

Making the Table to store our Image Data:

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 pictures
                 (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 (?, ?)", ('image1', 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.

Converting BLOB to Image -> Displaying Image

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')

# Retrieve the image data from the database
result = conn.execute("SELECT data FROM images WHERE name=?", ('image2',)).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)

Hacks

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('dogs')").fetchall()

    # Print the results
    for row in results:
        print(row)

    # Close the database connection
    conn.close()
    
schema()
(0, 'id', 'INTEGER', 1, None, 1)
(1, '_image', 'VARCHAR(255)', 1, None, 0)
(2, '_link', 'VARCHAR(255)', 1, None, 0)
(3, '_name', 'VARCHAR(255)', 1, None, 0)
(4, '_uid', 'VARCHAR(255)', 1, None, 0)
(5, '_breed', 'VARCHAR(255)', 1, None, 0)
(6, '_sex', 'VARCHAR(255)', 1, None, 0)
(7, '_dob', 'DATE', 0, None, 0)
(8, '_price', 'VARCHAR(255)', 1, None, 0)
import sqlite3

def read():
    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL queries
    cursor = conn.cursor()   # open connection object: contains methods and attributes you need to alter data 
    
    # Execute a SELECT statement to retrieve data from a table
    results = cursor.execute('SELECT * FROM dogs').fetchall()  #results is an object 

    # 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()
(1, 'https://do31x39459kz9.cloudfront.net/storage/image/cc7c5dd6a09649e3bf5c6bca96b21daa-1670625496-1670625511-jpg/1024-0-', 'https://haeryny.github.io/teamteam/doginfo/', 'Joe', '81729', 'Labrador Retriever Blend', 'male', '2022-02-11', '215')
(2, 'https://do31x39459kz9.cloudfront.net/storage/image/672cb9b41e7548f68316d4a328c772d2-1673989499-1673989524-jpg/1024-0-', 'https://haeryny.github.io/teamteam/doginfo/', 'Bean', '83792', 'Shepherd-Rottweiler Blend', 'male', '2019-01-31', '180')
(3, 'https://do31x39459kz9.cloudfront.net/storage/image/9f57a9ccb04d489c8e0faeb7a6aaecc1-1671755085-1671755107-jpg/1024-0-', 'https://haeryny.github.io/teamteam/doginfo/', 'Harry', '80032', 'Hound-Terrier Blend', 'male', '2020-04-29', '160')
(4, 'https://do31x39459kz9.cloudfront.net/storage/image/7a0fd8c5107f469a8b6e3ec6db1bc48a-1671827148-1671827194-jpg/1024-0-', 'https://haeryny.github.io/teamteam/doginfo/', 'Honey', '90276', 'Retriever Blend', 'female', '2021-11-01', '200')
(5, 'https://do31x39459kz9.cloudfront.net/storage/image/3b17d9a97b4e41ff984e54467d122820-1670895829-1670895970-jpg/1024-0-', 'https://haeryny.github.io/teamteam/doginfo/', 'George', '90277', 'Retriever Blend', 'male', '2021-11-01', '200')
import sqlite3

def create():
    image = input("Enter image link:")
    link = input("Enter link to dog info:")
    name = input("Enter dog name:")
    uid = input("Enter UID:")
    breed = input("Enter dog breed:")
    sex = input("Enter dog sex:")
    dob = input("Enter dog date of birth:")
    price = input("Enter dog price:")
    
    # 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 dogs (_image, _link, _name, _uid, _breed, _sex, _dob, _price) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", (image, link, name, uid, breed, sex, dob, price))

        
        # Commit the changes to the database
        conn.commit()
        print(f"A new user record {uid} 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()
A new user record 83277 has been created
import sqlite3

def update():
    uid = input("Enter UID to update")
    price = input("Enter updated price")
    if len(price) > 500:
        message = "price is too expensive"
    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 dogs SET _price = ? WHERE _uid = ?", (price, uid))
        if cursor.rowcount == 0:
            # The uid was not found in the table
            print(f"No uid {uid} was found in the table")
        else:
            print(f"The row with user id {uid} the price 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()
The row with user id 83277 the price has been successfully updated
import sqlite3

def delete(id):
    # Validate the input
    try:
        id = int(id)
    except ValueError:
        print("Invalid input: ID must be an integer.")
        return

    # Connect to the database
    conn = sqlite3.connect('instance/sqlite.db')

    try:
        # Use a parameterized query to prevent SQL injection
        c = conn.cursor()
        c.execute("DELETE FROM dogs WHERE id=?", (id,))
        conn.commit()
        print(f"Row with ID {id} has been deleted.")
    except sqlite3.Error as e:
        print(f"Error deleting row with ID {id}: {e}")
    finally:
        # Close the connection
        conn.close()

id = input("Select the ID of the dog you want to remove from our database: ")
delete(id)
Row with ID 7 has been deleted.
def menu():
    operations = [ # cruds placed into a dictionary, keys 
        ('c', create),
        ('r', read),
        ('u', update),
        ('d', delete),
        ('s', schema)
    ]
    while True:
        operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
        if len(operation) == 0: # Escape Key
            return #
        for op in operations:
            if operation.lower() == op[0]: # checks whether the user's input matches the current option being considered in the loop. 
                op[1]()
                break
        else:
            print("Please enter c, r, u, d, or s") # if no match, print prompt 
        
menu()
(1, 'https://do31x39459kz9.cloudfront.net/storage/image/cc7c5dd6a09649e3bf5c6bca96b21daa-1670625496-1670625511-jpg/1024-0-', 'https://haeryny.github.io/teamteam/doginfo/', 'Joe', '81729', 'Labrador Retriever Blend', 'male', '2022-02-11', '215')
(2, 'https://do31x39459kz9.cloudfront.net/storage/image/672cb9b41e7548f68316d4a328c772d2-1673989499-1673989524-jpg/1024-0-', 'https://haeryny.github.io/teamteam/doginfo/', 'Bean', '83792', 'Shepherd-Rottweiler Blend', 'male', '2019-01-31', '180')
(3, 'https://do31x39459kz9.cloudfront.net/storage/image/9f57a9ccb04d489c8e0faeb7a6aaecc1-1671755085-1671755107-jpg/1024-0-', 'https://haeryny.github.io/teamteam/doginfo/', 'Harry', '80032', 'Hound-Terrier Blend', 'male', '2020-04-29', '160')
(4, 'https://do31x39459kz9.cloudfront.net/storage/image/7a0fd8c5107f469a8b6e3ec6db1bc48a-1671827148-1671827194-jpg/1024-0-', 'https://haeryny.github.io/teamteam/doginfo/', 'Honey', '90276', 'Retriever Blend', 'female', '2021-11-01', '200')
(5, 'https://do31x39459kz9.cloudfront.net/storage/image/3b17d9a97b4e41ff984e54467d122820-1670895829-1670895970-jpg/1024-0-', 'https://haeryny.github.io/teamteam/doginfo/', 'George', '90277', 'Retriever Blend', 'male', '2021-11-01', '200')
(6, 'https://puppyintraining.com/wp-content/uploads/2011/12/Dublin-Shopping.jpg', 'https://haeryny.github.io/teamteam/doginfo/', 'Mikey', '83277', 'Retriever Blend', 'male', '2022-11-18', '180')
import sqlite3
from tabulate import tabulate

# Connect to the SQLite database
conn = sqlite3.connect('instance/sqlite.db')
cursor = conn.cursor()

# Select all rows from the "dogs" table
cursor.execute("SELECT id, _name, _uid, _breed, _sex, _dob, _price FROM dogs")
rows = cursor.fetchall()

# Use tabulate to format the rows as a table
table = tabulate(rows, headers=["ID", "Name", "UID", "Breed", "Sex", "DOB", "Price"])

# Print the table
print(table)
  ID  Name      UID  Breed                      Sex     DOB           Price
----  ------  -----  -------------------------  ------  ----------  -------
   1  Joe     81729  Labrador Retriever Blend   male    2022-02-11      215
   2  Bean    83792  Shepherd-Rottweiler Blend  male    2019-01-31      180
   3  Harry   80032  Hound-Terrier Blend        male    2020-04-29      160
   4  Honey   90276  Retriever Blend            female  2021-11-01      200
   5  George  90277  Retriever Blend            male    2021-11-01      200
   6  Mikey   83277  Retriever Blend            male    2022-11-18      180

image

import sqlite3

# Open a connection to the SQLite database
conn = sqlite3.connect('instance/sqlite.db')

# Read the image file as binary data
with open('instance/joeDog.jpg', 'rb') as jpg:
    image_data = jpg.read()

# Insert the image data into the database
conn.execute("INSERT INTO dogimages (name, data) VALUES (?, ?)", ('joeDog', 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('instance/sqlite.db')

# Retrieve the image data from the database
result = conn.execute("SELECT data FROM dogimages WHERE name=?", ('joeDog',)).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()