-
Notifications
You must be signed in to change notification settings - Fork 4
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.
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."
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.
Modify your route as follows:
@app.route("/edit", methods=['get', 'post'])
def edit():
debug("form data=" + 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>Let's examine this code.
- In the Flask route, for step 2, we grab the
postidfrom the hidden field in the form. (Note thedebugcode 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 callfetchone()instead offetchall(). 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.
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'
Use the main wiki page to navigate, not the list of pages directly above, because those are out of order.