db6

Modifying blog entries with SQL UPDATE

In this lesson we will create a new route to modify an existing blog post. This will be done with the SQL UPDATE command.

Client/server interactions to modify a post

Our workflow will be the following:

Client requests a list of blog posts so that they can select the one they want to modify.
Server sends back a form with this list.
Client selects a blog post, submits form containing their choice.
Server sends back a form with the blog post filled in, allowing edits.
Client edits the blog post however they desire, submits form.
Server receives blog post, runs SQL UPDATE to change the database, sends back a notification to the client.
Client receives notification of successful update.

As compared to writing a blog post, updating needs an extra request-response cycle. From the client's perspective, cycle one displays the webpage with a list of posts to edit, the second cycle displays the webpage with the selected blog post to edit, and the third cycle displays the notification of success.

We will name these steps "display_entries", "make_changes", and "update_database."

Flask & HTML template code, step 1

First, add a route:

@app.route("/edit", methods=['get', 'post'])
def edit():
    # Step 1, display form to select which entry to edit
    if "step" not in request.form:
        conn = get_db()
        cursor = conn.cursor()
        cursor.execute('select id, date, title, content from entries order by date')
        rowlist = cursor.fetchall()
        return render_template('edit.html', step="display_entries", entries=rowlist)

...and a new HTML template, edit.html:

<html>
    <head>
        <title>Edit an entry</title>
    </head>
<body>
    <h1>Edit an entry</h1>
    {% if step == "display_entries" %}
        {% if entries|length > 0 %}
            Choose an entry to edit:
            <form action="{{ url_for('edit') }}" method="post">
            <table border="1">
            <tr><td></td><td>Date</td><td>Title</td></tr>
            {% for entry in entries %}
                <tr><td><input type="radio" name="postid" value="{{ entry.id }}"></td>
                <td>{{ entry.date }}</td><td>{{ entry.title }}</td></tr>
            {% endfor %}
            </table>
            <input type="hidden" name="step" value="make_edits">
            <input type="submit">
            </form>
        {% else %}
            There are no entries.
            <p>
            <a href="{{ url_for('index') }}">Back to homepage</a>
        {% endif %}
    {% endif %}
</body>
</html>

Read this code over carefully. You only major difference between step 1 of edit and our previous browse functionality is that on the webpage, we use a table instead of a list. In the table, we have radio buttons from which we can choose the blog post we want to edit.

Note that the way the HTML form communicates back to the server which blog post we want is through the blog post ID, which we know is the primary key of the entries table. Specifically, look how the radio button uses a variable called postid and the value is {{ entry.id }}, which we know is a blog post ID from the entries table.

Flask/HTML for step 2

Modify your route as follows:

@app.route("/edit", methods=['get', 'post'])
def edit():
    debug("form data=" + str(request.form))
    # Step 1, display form to select which entry to edit
    if "step" not in request.form:
        conn = get_db()
        cursor = conn.cursor()
        cursor.execute('select id, date, title, content from entries order by date')
        rowlist = cursor.fetchall()
        return render_template('edit.html', step="display_entries", entries=rowlist)
    
    # Step 2, get postID from form, SELECT this post from the db, and display
    # a form to edit that post.    
    elif request.form["step"] == "make_edits":
        conn = get_db()
        cursor = conn.cursor()
        # get the postID from the form
        postid = int(request.form["postid"])
        debug("Using postid=" + str(postid))
        
        # query the DB to retrieve that post by ID.  We use fetchone()
        # to retrieve the only row (there can be only one!)
        cursor.execute("select id, date, title, content from entries where id=%s", [postid])
        row = cursor.fetchone()
        debug("db retrieved: " + str(dict(row)))
        
        return render_template("edit.html", step="make_edits", entry=row)

edit.html:

<html>
    <head>
        <title>Edit an entry</title>
    </head>
<body>
    <h1>Edit an entry</h1>
    {% if step == "display_entries" %}
        {% if entries|length > 0 %}
            Choose an entry to edit:
            <form action="{{ url_for('edit') }}" method="post">
            <table border="1">
            <tr><td></td><td>Date</td><td>Title</td></tr>
            {% for entry in entries %}
                <tr><td><input type="radio" name="postid" value="{{entry.id}}"></td>
                <td>{{ entry.date }}</td><td>{{ entry.title }}</td></tr>
            {% endfor %}
            </table>
            <input type="hidden" name="step" value="make_edits">
            <input type="submit">
            </form>
        {% else %}
            There are no entries.
            <p>
            <a href="{{ url_for('index') }}">Back to homepage</a>
        {% endif %}
    
    {% elif step == "make_edits" %}
        Edit your entry:
        <form action="{{ url_for('edit') }}" method="post">
        Title: <input type="text" size="30" name="title" value="{{entry.title}}"><br>
        Text: <textarea name="content" rows="5" cols="40">{{entry.content}}</textarea><br>
        <input type="hidden" name="step" value="update_database">
        <input type="hidden" name="postid" value="{{entry.id}}">
        <input type="submit">
        </form>
    {% endif %}
</body>
</html>

Let's examine this code.

In the Flask route, for step 2, we grab the postid from the hidden field in the form. (Note the debug code that has been added. It helps when things start to crash!)
Next, we query the DB to retrieve the single post with that postid. Note how since we are only retrieving one row, we call fetchone() instead of fetchall(). We pass our data off to the template.
In the template, step 2 replicates the code from write, but we add in some extra pieces. We use the post's existing title and content as default values for those fields so they appear already filled in. We keep the ID as a hidden field because we will need to use that in step 3 to do the actual SQL UPDATE (long story short: always make sure you keep passing around the primary key). We don't need to pass the date as a hidden field because we won't change that.

On to step 3:

Before we look at the Flask or HTML code, let's examine the syntax for an SQL UPDATE:

UPDATE table_name
SET attrib1=value1, attrib2=value2, ...
WHERE some_column=some_value;

An SQL UPDATE works like a SELECT in that we include a WHERE clause to choose only certain rows from a specific table. After the rows are chosen, all of those rows are modified by changing the attributes as shown in the SET clause. In every chosen row, attrib1 is changed to value1, attrib2 is changed to value2, and so on. Often the WHERE clause is designed to select only a single row (often by asking for a specific value of a primary key), but it doesn't have to be.

Our UPDATE clause will look like:

UPDATE entries 
SET title=[the new title], content=[the new content] 
WHERE id=[our post id]",

Note that UPDATE will not complain if we change an attribute to the same value it already has. For instance, if the user chooses to edit the content of a blog post but not the title, we can still update the title to the same value it already has with no issues. UPDATE will even work if all the values stay the same as they already are.

Here's our final Flask route and HTML template edit.html: [exercise below]

@app.route("/edit", methods=['get', 'post'])
def edit():
    debug("form data=" + str(request.form))
    # Step 1, display form to select which entry to edit
    if "step" not in request.form:
        conn = get_db()
        cursor = conn.cursor()
        cursor.execute('select id, date, title, content from entries order by date')
        rowlist = cursor.fetchall()
        return render_template('edit.html', step="display_entries", entries=rowlist)
    
    # Step 2, get postID from form, SELECT this post from the db, and display
    # a form to edit that post.    
    elif request.form["step"] == "make_edits":
        conn = get_db()
        cursor = conn.cursor()
        # get the postID from the form
        postid = int(request.form["postid"])
        debug("Using postid=" + str(postid))
        
        # query the DB to retrieve that post by ID.  We use fetchone()
        # to retrieve the only row (there can be only one!)
        cursor.execute("select id, date, title, content from entries where id=%s", [postid])
        row = cursor.fetchone()
        debug("db retrieved: " + str(dict(row)))
        
        return render_template("edit.html", step="make_edits", entry=row)
        
    # Step 3, user has changed post, now update the DB with changes
    elif request.form["step"] == "update_database":
        conn = get_db()
        cursor = conn.cursor()
        
        # get the postID from the form
        postid = int(request.form["postid"])
        
        # run our UPDATE
        cursor.execute("update entries set title=%s, content=%s where id=%s", 
                       [request.form['title'], request.form['content'], postid])
        conn.commit()
        return render_template("edit.html", step="update_database")

edit.html:

<html>
    <head>
        <title>Edit an entry</title>
    </head>
<body>
    <h1>Edit an entry</h1>
    {% if step == "display_entries" %}
        {% if entries|length > 0 %}
            Choose an entry to edit:
            <form action="{{ url_for('edit') }}" method="post">
            <table border="1">
            <tr><td></td><td>Date</td><td>Title</td></tr>
            {% for entry in entries %}
                <tr><td><input type="radio" name="postid" value="{{ entry.id }}"></td>
                <td>{{ entry.date }}</td><td>{{ entry.title }}</td></tr>
            {% endfor %}
            </table>
            <input type="hidden" name="step" value="make_edits">
            <input type="submit">
            </form>
        {% else %}
            There are no entries.
            <p>
            <a href="{{ url_for('index') }}">Back to homepage</a>
        {% endif %}
    
    {% elif step == "make_edits" %}
        Edit your entry:
        <form action="{{ url_for('edit') }}" method="post">
        Title: <input type="text" size="30" name="title" value="{{entry.title}}"><br>
        Text: <textarea name="content" rows="5" cols="40">{{entry.content}}</textarea><br>
        <input type="hidden" name="step" value="update_database">
        <input type="hidden" name="postid" value="{{entry.id}}">
        <input type="submit">
        </form>
        
    {% elif step == "update_database" %}
        Thanks for making your changes!
        <p>
        <a href="{{ url_for('index') }}">Back to homepage</a>
    {% endif %}
</body>
</html>

You try it

First, add a link on the homepage for the /edit route (so you'll now have three links there: browse, write, and edit).
Add a checkbox to the form where the user can edit the blog post (step 2). The checkbox should let the user choose if they want to update the date on the post to the current date, or keep the date of the original post.
Modify your Python code to obey the logic of the checkbox.
Hint Use HTML code like: <input type="checkbox" name="changedate" value="yes">. In Flask, use the test if "changedate" in request.form: This will detect whether the checkbox was checked. Because there's only one checkbox, we don't need to check if the value of the variable actually equals "yes," we can just check whether the variable exists. If there were multiple checkboxes, each with the same name, then we would need to examine the variable values.
Hint 2: Write two separate SQL UPDATES for this and use the test above to pick which one to use. One query should be the same as the existing (old) query, and the new one should use the PostgreSQL now() function. You can use now() in an UPDATE query like this (for example):

UPDATE entries SET title=[whatever], content=[whatever], date=now() WHERE....

This is easier than trying to get the current date in Python.

Solution below:

.

main.py:

@app.route("/edit", methods=['get', 'post'])
def edit():
    debug("form data=" + str(request.form))
    # Step 1, display form to select which entry to edit
    if "step" not in request.form:
        conn = get_db()
        cursor = conn.cursor()
        cursor.execute('select id, date, title, content from entries order by date')
        rowlist = cursor.fetchall()
        return render_template('edit.html', step="display_entries", entries=rowlist)
    
    # Step 2, get postID from form, SELECT this post from the db, and display
    # a form to edit that post.    
    elif request.form["step"] == "make_edits":
        conn = get_db()
        cursor = conn.cursor()
        # get the postID from the form
        postid = int(request.form["postid"])
        debug("Using postid=" + str(postid))
        
        # query the DB to retrieve that post by ID.  We use fetchone()
        # to retrieve the only row (there can be only one!)
        cursor.execute("select id, date, title, content from entries where id=%s", [postid])
        row = cursor.fetchone()
        debug("db retrieved: " + str(dict(row)))
        
        return render_template("edit.html", step="make_edits", entry=row)
        
    # Step 3, user has changed post, now update the DB with changes
    elif request.form["step"] == "update_database":
        conn = get_db()
        cursor = conn.cursor()
        
        # get the postID from the form
        postid = int(request.form["postid"])
        
        # figure out if we update the date
        if "changedate" in request.form:
            changedate = True
        else:
            changedate = False
        
        # run our UPDATE
        if changedate:
            cursor.execute("update entries set title=%s, content=%s, date=now() where id=%s",
                       [request.form['title'], request.form['content'], postid])
        else:
            cursor.execute("update entries set title=%s, content=%s where id=%s", 
                       [request.form['title'], request.form['content'], postid])
        conn.commit()
        return render_template("edit.html", step="update_database")

edit.html:

<html>
    <head>
        <title>Edit an entry</title>
    </head>
<body>
    <h1>Edit an entry</h1>
    {% if step == "display_entries" %}
        {% if entries|length > 0 %}
            Choose an entry to edit:
            <form action="{{ url_for('edit') }}" method="post">
            <table border="1">
            <tr><td></td><td>Date</td><td>Title</td></tr>
            {% for entry in entries %}
                <tr><td><input type="radio" name="postid" value="{{entry.id}}"></td>
                <td>{{ entry.date }}</td><td>{{ entry.title }}</td></tr>
            {% endfor %}
            </table>
            <input type="hidden" name="step" value="make_edits">
            <input type="submit">
            </form>
        {% else %}
            There are no entries.
            <p>
            <a href="{{ url_for('index') }}">Back to homepage</a>
        {% endif %}
    
    {% elif step == "make_edits" %}
        Edit your entry:
        <form action="{{ url_for('edit') }}" method="post">
        Title: <input type="text" size="30" name="title" value="{{entry.title}}"><br>
        Text: <textarea name="content" rows="5" cols="40">{{entry.content}}</textarea><br>
        Update date and time of post? <input type="checkbox" name="changedate" value="yes"><br>
        <input type="hidden" name="step" value="update_database">
        <input type="hidden" name="postid" value="{{entry.id}}">
        <input type="submit">
        </form>
        
    {% elif step == "update_database" %}
        Thanks for making your changes!
        <p>
        <a href="{{ url_for('index') }}">Back to homepage</a>
    {% endif %}
</body>
</html>

home.html:

<html>
    <head>
        <title>Blog</title>
    </head>
<body>
    <h1>Blog Homepage</h1>
    <ul>
        <li><a href="{{ url_for('browse') }}">List all entries</a>
        <li><a href="{{ url_for('write') }}">Write a new entry</a>
        <li><a href="{{ url_for('edit') }}">Edit an existing entry</a>
    </ul>
</body>
</html>

Use the main wiki page to navigate, not the list of pages directly above, because those are out of order.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

db6

Modifying blog entries with SQL UPDATE

Client/server interactions to modify a post

Flask & HTML template code, step 1

Flask/HTML for step 2

On to step 3:

You try it

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Clone this wiki locally