1 - Introduction

In this lesson, you'll extend your Flask app to update and delete records in your SQLite database. This completes the CRUD (Create, Read, Update, Delete) functionality, allowing you to modify existing data.

You'll create forms and routes for editing user details and deleting users, use SQL UPDATE and DELETE commands, add basic error handling, and ensure the displayed data refreshes after changes.

By the end, you'll have a simple CRUD app. Continue working in your 'FlaskSQLiteIntro' project folder from the previous lesson. Make sure you have the users and hobbies tables set up.

Here's what you'll do:

Create an edit form for users.
Implement the update route.
Add a delete feature.
Incorporate error handling.
Test the full CRUD app.

2 - Website Navigation

If you haven't already done this as part of the challenge from the previous lesson, you need to add a navigation bar to all the pages in your Flask app. This will make it easier to move between different parts of your site.

One way to implement the navigation bar is to use Jinja2 template inheritance. This allows you to create a base template with common elements like the navigation, and then extend it in your other templates.

Create a new file called 'base.html' in the 'templates' folder with this content:

<!DOCTYPE html>
<html>
<head>
    <title>{% block title %}{% endblock %}</title>
</head>
<body>
    <nav>
        <a href="/">Home</a> |
        <a href="/add_hobby">Add Hobby</a> |
        <a href="/users">View Users</a> |
        <a href="/hobbies">View Hobbies</a> |
        <a href="/search">Search</a>
    </nav>
    {% block content %}{% endblock %}
</body>
</html>

Now, update your other template files to extend this base.html. For example, in 'form.html', replace the existing content with:

{% extends 'base.html' %}
{% block title %}Add User{% endblock %}
{% block content %}
<h1>Add User</h1>
<form method="post" action="/add_user">
    <label>Name:</label><input type="text" name="name"><br>
    <label>Age:</label><input type="number" name="age"><br>
    <label>Email:</label><input type="text" name="email"><br>
    <label>Hobby:</label>
    <select name="hobby_id">
        <option value="">None</option>
        {% for hobby in hobbies %}
        <option value="{{ hobby[0] }}">{{ hobby[1] }}</option>
        {% endfor %}
    </select><br>
    <button type="submit">Add</button>
</form>
{% endblock %}

Do the same for your other templates like 'hobbies.html', 'hobby_form.html', 'search_results.html', 'search.html', and 'users.html'. Wrap the main content in {% block content %} and {% endblock %}, and set the {% block title %} appropriately.

After updating all templates, run your app with python app.py and visit http://127.0.0.1:5000/. You should see the navigation bar on every page, allowing you to easily switch between sections.

3 - Create Edit Form

To update a user's details, you'll need a form pre-filled with their current data. We'll add an 'Edit' link next to each user in the users list.

First, update 'templates/users.html' to include edit and delete links. Replace the existing content with this full version:

{% extends 'base.html' %}
{% block title %}Users{% endblock %}
{% block content %}
<h1>Users</h1>
<table border="1">
    <tr><th>ID</th><th>Name</th><th>Age</th><th>Email</th><th>Hobby</th><th>Actions</th></tr>
    {% for user in users %}
    <tr><td>{{ user[0] }}</td><td>{{ user[1] }}</td><td>{{ user[2] }}</td><td>{{ user[3] or '' }}</td><td>{{ user[4] or 'None' }}</td>
        <td><a href="/edit_user/{{ user[0] }}">Edit</a> | <a href="/delete_user/{{ user[0] }}" onclick="return confirm('Are you sure?')">Delete</a></td></tr>
    {% endfor %}
</table>
{% endblock %}

Now, create 'templates/edit_user.html':

{% extends 'base.html' %}
{% block title %}Edit User{% endblock %}
{% block content %}
<h1>Edit User</h1>
<form method="post" action="/update_user/{{ user[0] }}">
    <label>Name:</label><input type="text" name="name" value="{{ user[1] }}"><br>
    <label>Age:</label><input type="number" name="age" value="{{ user[2] }}"><br>
    <label>Email:</label><input type="text" name="email" value="{{ user[3] or '' }}"><br>
    <label>Hobby:</label>
    <select name="hobby_id">
        <option value="" {% if not user[5] %}selected{% endif %}>None</option>
        {% for hobby in hobbies %}
        <option value="{{ hobby[0] }}" {% if hobby[0] == user[5] %}selected{% endif %}>{{ hobby[1] }}</option>
        {% endfor %}
    </select><br>
    <button type="submit">Update</button>
</form>
{% endblock %}

This form uses POST for updates, pre-filling fields with user data and selecting the current hobby.

4 - SQL UPDATE Statement

The SQL UPDATE statement allows you to modify existing data in a database table. It's a key part of managing information, letting you change details without deleting and re-adding records.

Here's the basic structure:

UPDATE table_name
SET column1 = new_value1, column2 = new_value2
WHERE condition;

UPDATE table_name: Specifies which table you want to update.
SET column = new_value: Defines which columns to change and their new values. You can update multiple columns at once, separated by commas.
WHERE condition: Tells the database which rows to update. This is crucial – without it, every row in the table would be updated!

For example, imagine you have a 'users' table and want to change a user's age:

UPDATE users
SET age = 18
WHERE id = 1;

This updates the age to 18 for the user with id 1.

Another example: Update both name and email for a user:

UPDATE users
SET name = 'Alex Smith', email = 'alex@example.com'
WHERE id = 2;

Note: If you don't include a WHERE clause then all the records in the users table would be updated!:

UPDATE users
SET age = 18;

5 - Implement Update Route

In this step, you'll add a route to handle updating user records using SQL UPDATE.

Update 'app.py' by adding these routes after the /users route. This is the full code to add:

@app.route('/edit_user/<int:user_id>', methods=['GET'])
def edit_user(user_id):
    conn = sqlite3.connect('users.db')
    c = conn.cursor()
    c.execute("SELECT u.id, u.name, u.age, u.email, h.name, u.hobby_id FROM users u LEFT JOIN hobbies h ON u.hobby_id = h.id WHERE u.id = ?", (user_id,))
    user = c.fetchone()
    c.execute("SELECT * FROM hobbies")
    hobbies = c.fetchall()
    conn.close()
    if user:
        return render_template('edit_user.html', user=user, hobbies=hobbies)
    return 'User not found', 404

@app.route('/update_user/<int:user_id>', methods=['POST'])
def update_user(user_id):
    name = request.form['name']
    age = int(request.form['age'])
    email = request.form.get('email', '')
    hobby_id = request.form.get('hobby_id')
    hobby_id = int(hobby_id) if hobby_id else None
    conn = sqlite3.connect('users.db')
    c = conn.cursor()
    c.execute("UPDATE users SET name = ?, age = ?, email = ?, hobby_id = ? WHERE id = ?", (name, age, email, hobby_id, user_id))
    conn.commit()
    conn.close()
    return redirect('/users')

You'll also need to import redirect at the top, so that you can redirect to other routes after operations.

from flask import Flask, render_template, request, redirect

Run python app.py. Visit http://127.0.0.1:5000/users, click 'Edit' for a user, update details, and submit. You should be redirected to the users list with updated data.

Let's break down some key parts of this code:

Routing with the ID: The routes use /edit_user/<int:user_id> and /update_user/<int:user_id>. The <int:user_id> is a dynamic part of the URL that captures the user's ID as an integer. This ID is passed to the function (e.g., def edit_user(user_id):) so you can use it to query or update the specific user in the database.

Getting values from the form: In the update_user function, we use request.form['name'], request.form['age'], etc., to get the data submitted from the edit form. These match the name attributes in the form fields. We convert types where needed, like int(request.form['age']), and handle optional fields with request.form.get('email', '').

The UPDATE statement: The SQL command UPDATE users SET name = ?, age = ?, email = ?, hobby_id = ? WHERE id = ? modifies the existing record in the 'users' table. It sets the new values for the specified columns, but only for the row where the id matches the provided user_id. The question marks (?) are placeholders for the values, which are safely inserted from the tuple (name, age, email, hobby_id, user_id) to prevent SQL injection.

Unlock the Full Learning Experience

Get ready to embark on an incredible learning journey! Get access to this lesson and hundreds more in our Digital Skills Curriculum.

Get started

Updating and Deleting Records

Live Class Feed

1 - Introduction

2 - Website Navigation

3 - Create Edit Form

4 - SQL UPDATE Statement

5 - Implement Update Route

Unlock the Full Learning Experience

Updating and Deleting Records

Live Class Feed

1 - Introduction

2 - Website Navigation

3 - Create Edit Form

4 - SQL UPDATE Statement

5 - Implement Update Route

Unlock the Full Learning Experience

XP