Skip to content

db6

Jump to bottom
pkirlin edited this page Oct 13, 2016 · 24 revisions

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:
        db = get_db()
        rows = db.execute('select id, date, title, content from entries order by date')
        rowlist = rows.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('homepage') }}">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(request.form)
    # Step 1, display form to select which entry to edit
    if "step" not in request.form:
        db = get_db()
        rows = db.execute('select id, date, title, content from entries order by date')
        rowlist = rows.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":
        db = get_db()
        # 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!)
        row = db.execute("select id, date, title, content from entries where id=?", [postid])
        therow = row.fetchone()
        debug("db retrieved: " + str(dict(therow)))
        
        return render_template("edit.html", step="make_edits", entry=therow)

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('homepage') }}">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>

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

Clone this wiki locally