Performing CRUD Operations with SQLAlchemy

Welcome back! In our previous lessons, we set up SQLAlchemy and configured our database. We also transitioned our Todo model from in-memory storage using a list to persistent storage with SQLAlchemy.

In this lesson, we will update our TodoService class to perform CRUD operations using SQLAlchemy. CRUD stands for Create, Read, Update, and Delete, and these operations are fundamental for managing data in any application.

By the end of this lesson, you'll adapt your existing TodoService methods to interact with the database, making your app more functional and responsive to user actions.

Let's update the TodoService class, which will now serve as an intermediary between our database and the rest of our application, ensuring clean and maintainable code.

Here's what we will do:

1. Retrieve Todos from the Database: We'll update the methods to fetch all Todo items and a specific Todo by its ID from the database.
2. Add Todos to the Database: We'll modify the method to create and persist new Todo items in the database.
3. Update Todos in the Database: We'll adjust the method to update existing Todo items and save the changes.
4. Delete Todos from the Database: We'll refactor the method to remove Todo items from the database.

By the end of this section, your TodoService class will be fully integrated with SQLAlchemy, allowing your application to leverage the power of a relational database for persistent storage.

Before diving into the new methods, let's recap how our old TodoService class managed Todo items using in-memory storage with a list:

from models.todo import Todo

class TodoService:
    def __init__(self):
        self._todos = [
            Todo(1, "Sample Todo 1", "Description 1"),
            Todo(2, "Sample Todo 2", "Description 2")
        ]

    def get_all(self):
        return self._todos

    def get_by_id(self, todo_id):
        for todo in self._todos:
            if todo.todo_id == todo_id:
                return todo
        return None

    def add(self, title, description):
        if self._todos:
            new_id = max(todo.todo_id for todo in self._todos) + 1
        else:
            new_id = 1
        new_todo = Todo(new_id, title, description)
        self._todos.append(new_todo)

    def update(self, todo_id, title, description):
        todo = self.get_by_id(todo_id)
        if todo:
            todo.title = title
            todo.description = description
            return True
        return False

    def delete(self, todo_id):
        todo = self.get_by_id(todo_id)
        if todo:
            self._todos.remove(todo)
            return True
        return False

This implementation worked well for in-memory data but didn't persist any changes.