Using Databases from Applications

Updating Data in Applications

Learning Objectives

  • You can implement a basic update flow from a web application.
  • You can explain why updating one row usually takes more than one request.

At this point, the simplest way to change a tag would be to delete it and create a new one with the desired name. That works, but it is awkward: the new tag gets a different identifier, and any place in the application that already referenced the old tag would lose its reference. It is also inconvenient for the user, because the current value is not carried over into the new form.

The next question is: how can users change one chosen row without deleting it?

An update flow involves:

  • identifying an existing row,
  • showing its current values,
  • saving the changed values,
  • and returning to a stable page.

Updating One Chosen Row

The starting question in an update flow is: which exact row should be changed?

The answer is the same as in the delete chapter: the row that matches a given identifier. In the tags table, the id column is a good identifier because it uniquely identifies one row.

Update adds one more step compared with delete. Before the updated values can be stored, the current values need to be shown first so that they can be modified.

That is why a basic update flow uses two requests:

  • one GET request to show the current row in an edit form,
  • and one POST request to save the updated values.

In app/main.py, the route that loads the edit page might look like this:

@app.get("/tags/{tag_id}/edit")
def show_edit_tag(request: Request, tag_id: int):
    with get_connection() as conn:
        tag = conn.execute(
            t"SELECT id, name FROM tags WHERE id = {tag_id}"
        ).fetchone()

    return render(request, "tag_edit.html", tag=tag)

The route receives one identifier from the path, runs a parameterized query to load the matching row, and passes that row to the template so the current values can be shown before anything is changed.

If the query finds no matching row, fetchone() returns None. In a fuller application, the route would usually return a “not found” response or redirect somewhere safe. For simplicity, we assume here that the row always exists.

Loading Exercise...

Linking to the Edit Page

Before the edit form can be shown, the list page needs a way to choose one row.

By this point, app/templates/tags.html already has the delete form from the previous chapter. The same loop can now also include an edit link for each tag. This is again only the relevant snippet inside the existing loop:

<ul>
  {% for tag in tags %}
    <li>
      <strong>{{ tag.name }}</strong>
      <a href="/tags/{{ tag.id }}/edit">Edit</a>
      <form method="post" action="/tags/{{ tag.id }}/delete">
        <button type="submit">Delete</button>
      </form>
    </li>
  {% endfor %}
</ul>

The placement matters here. The link belongs inside the loop because tag.id comes from one rendered row at a time. When the user clicks one of these links, the browser sends a GET request to /tags/{tag_id}/edit for that specific tag. The delete form stays on the page as well, because the update flow extends the earlier delete flow rather than replacing it.

Showing the Edit Form

Next, we need a form for editing content. Create app/templates/tag_edit.html, which contains a form with the current tag name pre-filled. This way, the user can see the current value and change it as needed:

<!doctype html>
<html lang="en">
  <head>
    <meta charset="utf-8" />
    <title>Study Tracker</title>
  </head>
  <body>
    <h1>Study Tracker</h1>
    <h2>Edit Tag</h2>

    <form method="post" action="/tags/{{ tag.id }}/edit">
      <label for="name">Tag Name</label>
      <input id="name" name="name" type="text" value="{{ tag.name }}" />

      <button type="submit">Save</button>
    </form>
  </body>
</html>

This template assumes the GET /tags/{tag_id}/edit route above passed tag=tag to render(). That is why tag.id and tag.name can be used directly.

This is another place where the identifier matters:

  • the form action embeds tag.id,
  • the browser sends the form to /tags/{tag_id}/edit,
  • and FastAPI passes that value into the matching route parameter.
Loading Exercise...

Saving the Update

Back in app/main.py, the route that saves the changed row looks like this:

@app.post("/tags/{tag_id}/edit")
def update_tag(tag_id: int, name: str = Form(...)):
    with get_connection() as conn:
        conn.execute(t"""
            UPDATE tags
            SET name = {name}
            WHERE id = {tag_id}
            """)

    return RedirectResponse("/tags", status_code=303)

The key parts are:

  • the update uses a parameterized t-string,
  • the WHERE id = {tag_id} clause keeps the change narrow to one row,
  • and the route redirects back to /tags after success.

With this, the overall update flow looks like this:

Figure 1 — The update flow uses a GET request to load the current values and a POST request to save the changes.

What to Check After Updating

Update is easier to reason about when the visible state is checked deliberately:

  • before the update, the tag has its old name,
  • after the update, the list page should show the new name.

That is a simple check, but it catches many mistakes:

  • the wrong row was targeted,
  • the form field name did not match the route parameter,
  • the update query ran, but the page is still showing stale data.

The same practical habit from the delete chapter still applies:

  • make the intended target clear,
  • make the before/after state visible,
  • then verify that the page matches the intended change.

The Tags Template After This Chapter

After adding the edit link next to the delete button, the full app/templates/tags.html looks like this:

<!doctype html>
<html lang="en">
  <head>
    <meta charset="utf-8" />
    <title>Study Tracker</title>
  </head>
  <body>
    <h1>Study Tracker</h1>
    <h2>Tags</h2>

    <ul>
      {% for tag in tags %}
        <li>
          <strong>{{ tag.name }}</strong>
          <a href="/tags/{{ tag.id }}/edit">Edit</a>
          <form method="post" action="/tags/{{ tag.id }}/delete">
            <button type="submit">Delete</button>
          </form>
        </li>
      {% endfor %}
    </ul>

    <h2>Create a Tag</h2>
    <form method="post" action="/tags">
      <label for="name">Tag Name</label>
      <input id="name" name="name" type="text" />

      <button type="submit">Create</button>
    </form>
  </body>
</html>

Together with the new tag_edit.html, the project now supports the full create, read, update, and delete flow for tags.

Check Your Understanding

  1. Why does an update flow usually need both a GET route and a POST route?
  2. Why does the edit form include the current stored value first?
  3. Why is it worth checking the list page again after saving an update?

Programming Exercise

This chapter’s programming exercise builds on the working delete flow from the previous chapter. It asks you to add the edit link on the tags page, the edit page, the save route, and the template needed to update one chosen tag.

Loading Exercise...
← Deleting Data in Applications
Organizing a Small Application →