Building CRUD Applications in Flask

# CHAPTER 10

Building CRUD Applications in Flask #

1. Introduction #

2. Learning Objectives #

• Integrate SQLAlchemy queries directly inside Flask View functions.

• Render dynamic database data to an HTML template (Read).

• Process HTML forms to save new records to the database (Create).

• Query and modify existing records (Update).

• Remove records from the database securely (Delete).

3. Beginner-Friendly Explanation #

• Create: A user fills out a registration form. The server takes that form and creates a new file folder in the cabinet.

• Read: A user asks to see a list of all employees. The server opens the cabinet, counts all the folders, and writes a list on a whiteboard (The HTML Template).

• Update: An employee gets married and changes their last name. The server finds their specific folder and rewrites the name tag.

• Delete: An employee leaves the company. The server finds their folder and throws it in the shredder.

These four operations (CRUD) form the backbone of 90% of all software engineering.

4. Step 1: READ (Displaying Data) #

In app.py:

@app.route('/')
def home():
    # 1. Query the database for all users
    all_users = User.query.all()
    
    # 2. Pass the list of users into the Jinja2 template
    return render_template('home.html', users=all_users)

In templates/home.html:

{% extends "base.html" %}
{% block content %}
    <h1>Registered Users</h1>
    <ul>
        {% for user in users %}
            <!-- Access the database columns using dot notation! -->
            <li>{{ user.username }} ({{ user.email }})</li>
        {% endfor %}
    </ul>
{% endblock %}

5. Step 2: CREATE (Inserting Data) #

In app.py:

from flask import request, redirect, url_for

@app.route('/add', methods=['GET', 'POST'])
def add_user():
    if request.method == 'POST':
        # 1. Extract data from the HTML form
        uname = request.form.get('username')
        uemail = request.form.get('email')
        
        # 2. Create the Python object
        new_user = User(username=uname, email=uemail)
        
        # 3. Save to the database
        db.session.add(new_user)
        db.session.commit()
        
        # 4. Redirect back to the homepage (PRG Pattern)
        return redirect(url_for('home'))
        
    return render_template('add_user.html')

*(You will need an add_user.html template with a POST form containing name="username" and name="email" inputs).*

6. Step 3: UPDATE (Modifying Data) #

In app.py:

# The URL captures the ID of the specific user to update
@app.route(&#039;/update/<int:id>', methods=['GET', 'POST'])
def update_user(id):
    # Fetch the user, or automatically return a 404 error if they don't exist
    user_to_update = User.query.get_or_404(id)
    
    if request.method == &#039;POST':
        # Overwrite the Python object's attributes with the new form data
        user_to_update.username = request.form.get(&#039;username')
        user_to_update.email = request.form.get(&#039;email')
        
        # Commit the changes (No need to db.session.add() again)
        db.session.commit()
        return redirect(url_for(&#039;home'))
        
    return render_template(&#039;update_user.html', user=user_to_update)

7. Step 4: DELETE (Removing Data) #

In app.py:

@app.route(&#039;/delete/<int:id>')
def delete_user(id):
    user_to_delete = User.query.get_or_404(id)
    
    # Shred the folder
    db.session.delete(user_to_delete)
    db.session.commit()
    
    return redirect(url_for(&#039;home'))

8. Backend Workflow: The get_or_404 Method #

9. Best Practices #

• POST for Deletions: In Step 4, we made the delete route a simple GET request. In a professional application, this is a severe security risk. A web crawler (like Googlebot) visiting every link on your site would accidentally click every /delete/id link and wipe your entire database! Deletions should ALWAYS be protected behind a POST request form.

10. Common Mistakes #

• Unique Constraint Failures: In Chapter 9, we set email to unique=True. If a user fills out the Create form with an email that already exists in the database, db.session.commit() will crash with an IntegrityError. You must use try/except blocks (Chapter 15) to catch this error and show a polite message to the user ("Email already in use") instead of crashing the server.

11. Exercises #

1. 1. Explain the chronological flow of data starting from the moment a user clicks a "Delete User" link, to the moment they are redirected back to the homepage.

12. Coding Challenges #

• Challenge: Update your home.html template. Inside the loop that displays each user, add two HTML hyperlink tags (<a>). One link should point to {{ url_for('update_user', id=user.id) }} with the text "Edit". The other should point to delete_user with the text "Delete".

13. MCQs with Answers #

14. Interview Questions #

• Q: Explain the Post/Redirect/Get (PRG) pattern. Why must a developer return a redirect() rather than render_template() after successfully processing a Create or Update operation?

• Q: Why is it considered a critical security vulnerability to execute database deletion operations (db.session.delete) via a standard GET HTTP request?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #