Using Databases from Applications

Reading Tag Data from Code

Learning Objectives

  • You can explain when a route should use fetchall() and when it should use fetchone().
  • You can create a route and a page for showing one selected tag.
  • You can modify a simple read query and predict how the visible tag page changes.

The previous chapter added the /tags page that lists all tags. This chapter adds a second read-only page that shows the newest tag.

Fetching Many Rows or One Row

When thinking of a page that shows data from the database, a useful question is: which kind of query result does the page need?

If the page should show a list, the route needs several rows. If the page should show one item, the route needs one row. The psycopg library has two methods for those cases:

  • fetchall() returns all rows produced by the query as a list of rows,
  • and fetchone() returns one row from the query result, or None if there are no rows.

For example, the /tags page needs several rows, so it uses fetchall():

@app.get("/tags")
def list_tags(request: Request):
    with get_connection() as conn:
        tags = conn.execute(
            """
            SELECT id, name
            FROM tags
            ORDER BY created_at DESC, id DESC
            """
        ).fetchall()

    return render(request, "tags.html", tags=tags)

If the question is “Which tags exist right now?”, this route answers it by selecting several rows and returning them as a list. If the route used fetchone() instead, only one row would be returned, and the page could not show the whole list.

The psycopg documentation on cursor methods is a useful reference if you want to see these fetch methods in more detail.

Loading Exercise...

Finding the Newest Tag

Let us next build functionality for a page that shows the newest tag. The question is: which tag was created most recently?

To answer that, the query needs to order the tags by their creation time, with the newest tag first. That way, the newest tag is the first row in the query result. To get only that row, the query can use LIMIT 1, which keeps only the first row. Then, the route can use fetchone() to get that one row.

Add a new route to app/main.py for /tags/newest that implements that logic. The route should pass the tag to a new template called newest_tag.html, which shows the tag name:

@app.get("/tags/newest")
def newest_tag(request: Request):
    with get_connection() as conn:
        tag = conn.execute(
            """
            SELECT id, name
            FROM tags
            ORDER BY created_at DESC, id DESC
            LIMIT 1
            """
        ).fetchone()

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

Read the route from top to bottom:

  • @app.get("/tags/newest") says which request the function handles,
  • ORDER BY created_at DESC moves the newest rows to the top,
  • id DESC is a useful tie-breaker if two rows have the same timestamp,
  • LIMIT 1 keeps only the first row,
  • and fetchone() says the route expects one row rather than a whole list.

That combination is a common pattern for one-row pages:

  • first decide which row should come first,
  • then keep one row,
  • then fetch one row.
Loading Exercise...

A Template for One Row

The route above calls render(request, "newest_tag.html", tag=tag). This means that the template should expect one variable named tag, not a list named tags.

Create a new template file called newest_tag.html in the app/templates/ directory:

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

    {% if tag %}
      <p>
        <strong>{{ tag.name }}</strong>
      </p>
    {% else %}
      <p>No tags found.</p>
    {% endif %}
  </body>
</html>

The page shows the tag name in bold if there is a tag, and shows a fallback message if there are no tags. The {% if tag %} check is important because if the tags table is empty, the query returns no rows and fetchone() returns None. The check makes sure the template handles that case safely and does not try to access tag.name when there is no tag.

This page is simpler than tags.html because it does not loop over several rows. It either shows one tag or, if the table is empty, a fallback message.

This also shows another practical difference between the two read pages:

  • tags.html needs a list and a loop,
  • newest_tag.html needs one row and no loop.

Later in the part, we will extract the shared HTML boilerplate (<html>, <head>, the top heading) into one common base template so that individual pages only contain their own content. Until then, each page keeps its own full HTML shell.

Check Your Understanding

  1. Why is fetchall() a good fit for /tags but fetchone() a good fit for /tags/newest?
  2. Which part of the /tags/newest query makes sure only one row is returned?
  3. Why is the {% if tag %} check useful in newest_tag.html?

Programming Exercise

This chapter’s programming exercise continues from the previous chapter’s finished /tags page. It asks you to add /tags/newest and its template, and then adjust the /tags list page so it shows the same rows in alphabetical order.

Loading Exercise...
← Using Databases from Web Applications
Parameterized Queries and SQL Injection →