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:

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.

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.

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.

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.

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.