Python and SQLite: Building a Recipe App Exercise

Introduction
Prerequisites
Setup
Creating the SQLite Database
Building the Recipe App
Conclusion

Introduction

In this tutorial, we will build a recipe app using Python and SQLite. The app will allow users to store and retrieve recipes from a database, as well as perform basic CRUD (Create, Read, Update, Delete) operations on the recipes. By the end of this tutorial, you will have a functioning recipe app that you can further customize and enhance to meet your needs.

Prerequisites

Before starting this tutorial, you should have a basic understanding of Python programming concepts, including variables, functions, and loops. Familiarity with SQLite and SQL queries is also beneficial. You will need to have Python installed on your machine.

Setup

To get started, let’s set up our project environment. Follow these steps:

Open your terminal or command prompt.
Create a new directory for your project:
```
 mkdir recipe-app
```
Navigate into the project directory:
```
 cd recipe-app
```
Create a virtual environment:
```
 python -m venv env
```
Activate the virtual environment:

On Linux or macOS:
```
  source env/bin/activate
```
On Windows:
```
  env\Scripts\activate
```
1. Install the required dependencies:
```
  pip install sqlite3
```
  Great! Now that we have our project set up, let’s move on to creating the SQLite database.

Creating the SQLite Database

Create a new Python file called database.py in your project directory.
Import the sqlite3 module:
```
 import sqlite3
```
Create a connection to the SQLite database:
```
 conn = sqlite3.connect("recipes.db")
```
This line establishes a connection to the recipes.db database file. If the file doesn’t exist, SQLite will create it for us.
Create a cursor object to interact with the database:
```
 cursor = conn.cursor()
```
The cursor object allows us to execute SQL queries and retrieve results.
Create a table to store recipes:
```
 cursor.execute('''CREATE TABLE IF NOT EXISTS recipes
                   (id INTEGER PRIMARY KEY AUTOINCREMENT,
                    title TEXT,
                    ingredients TEXT,
                    instructions TEXT)''')
```
This SQL query creates a table called recipes with four columns: id, title, ingredients, and instructions. The id column serves as the primary key, and it will automatically increment whenever a new row is inserted.
Commit the changes and close the connection:
```
 conn.commit()
 conn.close()
```
The commit method saves the changes to the database, and the close method closes the connection.

Now that we have our database set up, let’s move on to building the recipe app.

Building the Recipe App

Create a new Python file called app.py in your project directory.
Import the required modules:
```
 import sqlite3
```

Create a class called RecipeApp:

 class RecipeApp:
     def __init__(self, db_name):
         self.conn = sqlite3.connect(db_name)
         self.cursor = self.conn.cursor()

The __init__ method initializes the connection to the SQLite database.

Define a method to add a recipe:

     def add_recipe(self, title, ingredients, instructions):
         self.cursor.execute('INSERT INTO recipes (title, ingredients, instructions) VALUES (?, ?, ?)',
                             (title, ingredients, instructions))
         self.conn.commit()

This method inserts a new row into the recipes table with the provided title, ingredients, and instructions.

Define a method to retrieve all recipes:

     def get_all_recipes(self):
         self.cursor.execute('SELECT * FROM recipes')
         return self.cursor.fetchall()

This method executes a select query to fetch all rows from the recipes table.

Define a method to update a recipe:

     def update_recipe(self, recipe_id, new_title, new_ingredients, new_instructions):
         self.cursor.execute('UPDATE recipes SET title = ?, ingredients = ?, instructions = ? WHERE id = ?',
                             (new_title, new_ingredients, new_instructions, recipe_id))
         self.conn.commit()

This method updates the title, ingredients, and instructions of a recipe identified by its ID.

Define a method to delete a recipe:

     def delete_recipe(self, recipe_id):
         self.cursor.execute('DELETE FROM recipes WHERE id = ?', (recipe_id,))
         self.conn.commit()

This method deletes a recipe from the recipes table based on its ID.

Create an instance of the RecipeApp class and test the functionalities:

 app = RecipeApp("recipes.db")
	
 # Add a recipe
 app.add_recipe("Banana Bread", "2 ripe bananas, 1 cup flour, 1/2 cup sugar", "1. Preheat the oven...")
	
 # Retrieve all recipes
 recipes = app.get_all_recipes()
 for recipe in recipes:
     print(recipe)
	
 # Update a recipe
 app.update_recipe(1, "Modified Banana Bread", "2 ripe bananas, 1 cup flour, 1/2 cup sugar, 1/2 cup walnuts", "1. Preheat the oven...")
	
 # Delete a recipe
 app.delete_recipe(1)
	
 # Retrieve all recipes after deletion
 recipes = app.get_all_recipes()
 for recipe in recipes:
     print(recipe)
	
 app.conn.close()

This code creates an instance of the RecipeApp class, adds a recipe, retrieves all recipes, updates the first recipe, deletes the first recipe, and retrieves all recipes again.

Congratulations! You’ve built a recipe app using Python and SQLite. Feel free to further enhance and customize the app according to your needs.

Conclusion

In this tutorial, we learned how to build a recipe app using Python and SQLite. We covered the process of creating a database, defining tables, and performing basic CRUD operations on the recipes. You are now equipped with the knowledge to build your own database-backed applications using Python and SQLite. Start exploring different ways to extend the functionality of the app and further improve your Python skills.

If you have any questions, feel free to refer to the Frequently Asked Questions (FAQ) section below.

FAQs

Q: Can I use a different database instead of SQLite? A: Yes, you can use other databases such as MySQL or PostgreSQL. However, you will need to install the corresponding Python libraries and modify the code accordingly.
Q: How can I add more columns to the recipes table? A: To add more columns, you can modify the CREATE TABLE statement in the database.py file. Make sure to update the corresponding INSERT, UPDATE, and SELECT queries in the RecipeApp class as well.
Q: How can I add validation or error handling to the app? A: You can add input validation and error handling to the methods in the RecipeApp class. For example, you can check if a recipe with the same title already exists before adding a new recipe.
Q: What are some other features I can add to the app? A: Some ideas for additional features include searching for recipes by title or ingredients, categorizing recipes, and adding user authentication. These enhancements can make your recipe app more powerful and user-friendly.

Stay curious and keep experimenting with new ideas and concepts!

Published: 21 June 2020