Exporting Recipes to CSV

Welcome back! So far, you have learned how to make your smart cooking assistant more interactive by adding audio support and new API endpoints for ingredients and reviews. In this lesson, we will take your project one step further by teaching you how to export all your recipes into a CSV file.

Exporting data to CSV is a common feature in many real-world applications. CSV files are easy to open in spreadsheet programs like Microsoft Excel or Google Sheets, making it simple to share, analyze, or back up your recipes. By the end of this lesson, you will know how to create a script that pulls all recipes from your database and writes them into a well-structured CSV file.

Before we start building the export script, let’s quickly remind ourselves how recipes and ingredients are stored in your project.

• Each Recipe has a name, a list of ingredients, and a set of steps.
• Each Ingredient can belong to multiple recipes.
• The relationship between recipes and ingredients is many-to-many, which means a recipe can have many ingredients, and an ingredient can be used in many recipes.

This structure is managed using SQLAlchemy models. Here’s a quick look at the relevant parts:

This reminder will help you understand how to access and organize the data when exporting it.

Let’s start by creating a new script that will handle the export. You should place this script in a scripts directory at the root of your project. This keeps your project organized.

To access your recipes, you need to import your database session and models. This allows the script to use the same database connection as your main application.

Here’s how you can set up the script and import what you need:

import sys
import os

# Ensure project root is importable when running from ./scripts/
CURRENT_DIR = os.path.dirname(os.path.abspath(__file__))
PROJECT_ROOT = os.path.abspath(os.path.join(CURRENT_DIR, "..", "cooking_helper"))
if PROJECT_ROOT not in sys.path:
    os.chdir(PROJECT_ROOT)
    sys.path.insert(0, PROJECT_ROOT)

from sqlalchemy.orm import Session
from app.db.session import SessionLocal
from app.db.models import Recipe

• The script sets up the import path so you can access your models and database session.
• from app.db.session import SessionLocal imports the function to create a database session.
• from app.db.models import Recipe imports the Recipe model so you can query recipes.

This setup is important because it lets your script use the same database and models as your main application.

Now that your script can access the session and models, the next step is to fetch all recipes from the database and prepare the data for export.

First, you need to create a session. This is required for database operations outside your main application.

session: Session = SessionLocal()
try:
    recipes = session.query(Recipe).all()
    # ... process recipes here ...
except Exception as e:
    # ... handle exceptions ...
finally:
    session.close()

• SessionLocal() creates a new database session.
• session.query(Recipe).all() fetches all recipes from the database.

Next, you need to format the data for CSV. For each recipe, you want to collect:

• The recipe ID
• The recipe name
• A comma-separated list of ingredient names
• The steps, flattened into a single line

Here’s how you can do that for each recipe:

for r in recipes:
    ingredient_list = ", ".join(sorted(set(i.name for i in r.ingredients)))
    steps_flat = (r.steps or "").replace("\n", " ")
    # Now you have ingredient_list and steps_flat ready for CSV

• ingredient_list joins all unique ingredient names with commas.
• steps_flat replaces newlines in the steps with spaces, so the steps fit in one CSV cell.

Now that your data is ready, you can write it to a CSV file using Python’s built-in csv module. Here’s how you can do it:

import csv

def export_to_csv(filename: str = "exported_recipes.csv") -> None:
    session: Session = SessionLocal()
    try:
        recipes = session.query(Recipe).all()

        with open(filename, "w", newline="", encoding="utf-8") as csvfile:
            writer = csv.writer(csvfile)
            writer.writerow(["ID", "Name", "Ingredients", "Steps"])

            for r in recipes:
                ingredient_list = ", ".join(sorted(set(i.name for i in r.ingredients)))
                steps_flat = (r.steps or "").replace("\n", " ")
                writer.writerow([r.id, r.name, ingredient_list, steps_flat])

        print(f"✅ Exported {len(recipes)} recipes to '{filename}'")
    except Exception as e:
        print(f"❌ Error exporting recipes: {e}")
        raise
    finally:
        session.close()

if __name__ == "__main__":
    export_to_csv()

• open(filename, "w", ...) opens a new CSV file for writing.
• csv.writer(csvfile) creates a writer object.
• writer.writerow([...]) writes a row to the CSV file.
• The first row is the header, and each following row contains the recipe data.

When you run this script, you will see a message like:

✅ Exported 5 recipes to 'exported_recipes.csv'

And your CSV file will look like this (example):

Note: The script will overwrite any existing exported_recipes.csv file in the current directory. If you want to keep previous exports, consider renaming the file or moving it before running the script.

In this lesson, you learned how to create a script that exports all your recipes from the database into a CSV file. You set up the script environment, queried and formatted the recipe data, and wrote it to a CSV file using Python’s csv module. This feature is useful for sharing, analyzing, or backing up your recipes outside your app.

You are now ready to practice these skills in the exercises that follow. Congratulations on reaching the end of the course and building a complete export feature for your smart cooking assistant!