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:

Python
Copy to clipboard
1from models.todo import Todo
2
3class TodoService:
4    def __init__(self):
5        self._todos = [
6            Todo(1, "Sample Todo 1", "Description 1"),
7            Todo(2, "Sample Todo 2", "Description 2")
8        ]
9
10    def get_all(self):
11        return self._todos
12
13    def get_by_id(self, todo_id):
14        for todo in self._todos:
15            if todo.todo_id == todo_id:
16                return todo
17        return None
18
19    def add(self, title, description):
20        if self._todos:
21            new_id = max(todo.todo_id for todo in self._todos) + 1
22        else:
23            new_id = 1
24        new_todo = Todo(new_id, title, description)
25        self._todos.append(new_todo)
26
27    def update(self, todo_id, title, description):
28        todo = self.get_by_id(todo_id)
29        if todo:
30            todo.title = title
31            todo.description = description
32            return True
33        return False
34
35    def delete(self, todo_id):
36        todo = self.get_by_id(todo_id)
37        if todo:
38            self._todos.remove(todo)
39            return True
40        return False

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

Let's now transition our methods to utilize SQLAlchemy for database operations.

We are also making the methods static. Since the methods don't rely on instance-specific data (like an instance variable) and act more like utility functions, using static methods can simplify the method calls from other parts of our application.

In the previous implementation, we needed an instance of TodoService because the _todos list was tied to the instance. Now, the database is a shared resource, so static methods are more appropriate.

We start with the most basic operations: retrieving all Todo items and fetching a specific Todo item by its ID.

Python
Copy to clipboard
1from models.todo import Todo, db
2
3class TodoService:
4    @staticmethod
5    def get_all():
6        # Query all Todo entries from the database
7        return Todo.query.all()
8
9    @staticmethod
10    def get_by_id(todo_id):
11        # Query a specific Todo by its ID
12        return Todo.query.get(todo_id)

Explanation:

• Todo.query.all(): This line retrieves all rows from the Todo table in the database. It converts each row into an instance of the Todo model and returns them as a list.
• Todo.query.get(todo_id): This line retrieves a specific Todo entry by its primary key (ID). If the Todo with the specified todo_id exists, it returns the corresponding Todo object; otherwise, it returns None.

These methods use SQLAlchemy's query interface, which provides a simple yet powerful way to interact with the database. By calling these methods, we can easily fetch data stored in our database, making our application capable of displaying and managing persistent data.

Next, let's look at how to create and add new Todo items to our database.

Python
Copy to clipboard
1from models.todo import Todo, db
2
3class TodoService:
4    # Previous methods ...
5
6    @staticmethod
7    def add(title, description):
8        # Create a new Todo instance with the given title and description
9        new_todo = Todo(title=title, description=description)
10        # Add the new Todo instance to the current session
11        db.session.add(new_todo)
12        # Commit the session to persist the new Todo in the database
13        db.session.commit()
14        return new_todo

Explanation:

• Creating a new Todo instance: new_todo = Todo(title=title, description=description) initializes a new Todo object with the provided title and description.
• Adding to session: db.session.add(new_todo) stages the new Todo object for insertion into the database. Think of this as adding the new object to a queue of database operations.
• Committing the session: db.session.commit() finalizes and applies all staged changes to the database. This ensures the new Todo is actually saved.

By committing the session, we ensure that the new data is persisted in the database. This is crucial for making sure our data changes are saved.

Let’s see how we can update existing Todo items.

Python
Copy to clipboard
1from models.todo import Todo, db
2
3class TodoService:
4    # Previous methods ...
5
6    @staticmethod
7    def update(todo_id, title, description):
8        # Retrieve the Todo instance we want to update
9        todo = TodoService.get_by_id(todo_id)
10        if todo:
11            # Update the fields of the Todo instance
12            todo.title = title
13            todo.description = description
14            # Commit the session to persist the changes in the database
15            db.session.commit()
16            return True
17        return False

Explanation:

• Retrieving the Todo instance: todo = TodoService.get_by_id(todo_id) fetches the Todo object from the database. This object is connected to the database through SQLAlchemy's session.
• Modifying fields: Changing todo.title = title and todo.description = description updates the instance but also signals to SQLAlchemy that these fields should be updated in the database.
• Committing the session: db.session.commit() saves all changes made to any managed objects in this session to the database. This is the step that makes sure your updates are actually stored in the database.

So even though it looks like we're just updating a local instance, these changes are tracked by SQLAlchemy and saved to the database once we commit the session.

Finally, let’s implement the functionality to delete Todo items.

Python
Copy to clipboard
1from models.todo import Todo, db
2
3class TodoService:
4    # Previous methods ...
5
6    @staticmethod
7    def delete(todo_id):
8        # Retrieve the Todo instance we want to delete
9        todo = TodoService.get_by_id(todo_id)
10        if todo:
11            # Mark the Todo instance for deletion
12            db.session.delete(todo)
13            # Commit the session to persist the deletion in the database
14            db.session.commit()
15            return True
16        return False

Explanation:

• Retrieving the Todo instance: todo = TodoService.get_by_id(todo_id) fetches the Todo object that you want to delete by its ID.
• Marking for deletion: db.session.delete(todo) marks the Todo object for deletion from the database.
• Committing the session: db.session.commit() finalizes the deletion, ensuring that the Todo is permanently removed from the database.

In this lesson, we've covered how to perform CRUD operations using SQLAlchemy in our Flask application:

1. Create: Adding new Todo items to the database.
2. Read: Fetching all Todo items or a specific item by its ID.
3. Update: Modifying existing Todo items.
4. Delete: Removing Todo items from the database.

Understanding these operations is crucial for developing dynamic and data-driven applications. Now it’s time for you to practice what you’ve learned. Head over to the practice exercises to solidify your understanding and gain hands-on experience with CRUD operations in SQLAlchemy.