Unit 2.4 Personal Database
Database related to my project from last trimester
import sqlite3
# Connect to the database file
conn = sqlite3.connect('recipes.db')
# Create a new table called "recipes" with columns "id", "name", "ingredients", and "instructions"
cursor = conn.cursor()
cursor.execute('''CREATE TABLE recipes
(id INTEGER PRIMARY KEY,
name TEXT,
ingredients TEXT,
instructions TEXT)''')
conn.commit()
def create(conn, name, ingredients, instructions):
# Insert a new recipe into the database
cursor = conn.cursor()
cursor.execute('''INSERT INTO recipes (name, ingredients, instructions)
VALUES (?, ?, ?)''', (name, ingredients, instructions))
conn.commit()
print(name, "recipe created successfully!")
def read(conn, id):
# Read a recipe from the database by ID
cursor = conn.cursor()
cursor.execute('''SELECT * FROM recipes WHERE id = ?''', (id,))
row = cursor.fetchone()
if row is not None:
print(row)
else:
print("Recipe not found.")
def update(conn, id):
cursor = conn.cursor()
cursor.execute('''SELECT * FROM recipes WHERE id = ?''', (id,))
row = cursor.fetchone()
if row is None:
print("Recipe not found.")
return
while True:
print("What would you like to update?")
print("1. Name")
print("2. Ingredients")
print("3. Instructions")
print("4. Done updating")
choice = input("Enter your choice (1-4): ")
if choice == '1':
name = input("Enter the new recipe name: ")
cursor.execute('''UPDATE recipes SET name = ? WHERE id = ?''', (name, id))
conn.commit()
print("Recipe name updated successfully!")
elif choice == '2':
ingredients = input("Enter the new recipe ingredients: ")
cursor.execute('''UPDATE recipes SET ingredients = ? WHERE id = ?''', (ingredients, id))
conn.commit()
print("Recipe ingredients updated successfully!")
elif choice == '3':
instructions = input("Enter the new recipe instructions: ")
cursor.execute('''UPDATE recipes SET instructions = ? WHERE id = ?''', (instructions, id))
conn.commit()
print("Recipe instructions updated successfully!")
elif choice == '4':
print("Finished updating recipe.")
break
else:
print("Invalid choice. Please try again.")
def delete(conn, id):
# Delete a recipe from the database by ID
cursor = conn.cursor()
cursor.execute('''DELETE FROM recipes WHERE id = ?''', (id,))
conn.commit()
print("Recipe deleted successfully!")
while True:
# Display the options to the user
print("What would you like to do?")
print("1. Add a new recipe")
print("2. View a recipe by ID")
print("3. Update a recipe")
print("4. Delete a recipe")
print("5. Exit")
# Get the user's choice
choice = input("Enter your choice (1-5): ")
if choice == '1':
# Get the recipe details from the user and add the recipe to the database
name = input("Enter the recipe name: ")
ingredients = input("Enter the recipe ingredients: ")
instructions = input("Enter the recipe instructions: ")
create(conn, name, ingredients, instructions)
elif choice == '2':
# Get the ID of the recipe to view and display the recipe details
id = input("Enter the recipe ID: ")
read(conn, id)
elif choice == '3':
# Get the ID of the recipe to update and the updated recipe details, and update the recipe in the database
id = input("Enter the recipe ID: ")
name = input("Enter the new recipe name: ")
ingredients = input("Enter the new recipe ingredients: ")
instructions = input("Enter the new recipe instructions: ")
update(conn, id, name, ingredients, instructions)
elif choice == '4':
# Get the ID of the recipe to delete and delete the recipe from the database
id = input("Enter the recipe ID: ")
delete(conn, id)
elif choice == '5':
# Exit the program
print("Exiting...")
break
else:
# Handle invalid choices
print("Invalid choice. Please try again.")