-
Notifications
You must be signed in to change notification settings - Fork 4
db6
Phil Kirlin edited this page Oct 9, 2019
·
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.
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()
cursor = db.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('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=" + str(request.form))
# Step 1, display form to select which entry to edit
if "step" not in request.form:
db = get_db()
cursor = db.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":
db = get_db()
cursor = db.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=?", [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('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'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:
db = get_db()
cursor = db.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":
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!)
cursor.execute("select id, date, title, content from entries where id=?", [postid])
row = row.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":
db = get_db()
cursor = db.cursor()
# get the postID from the form
postid = int(request.form["postid"])
# run our UPDATE
cursor.execute("update entries set title=?, content=? where id=?",
[request.form['title'], request.form['content'], postid])
db.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('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>
{% elif step == "update_database" %}
Thanks for making your changes!
<p>
<a href="{{ url_for('homepage') }}">Back to homepage</a>
{% endif %}
</body>
</html>- First, add a link on the homepage for the
/editroute (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 testif "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 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
datetime('now', 'localtime')technique from thewriteroute. Do not add a third question mark! This is easier than trying to get the current date in Python.
Solution below:
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
blog.py:
# Blog Flask application
import os
import sqlite3
from flask import Flask, request, render_template, g
# Create the application
app = Flask(__name__)
####################################################
# Routes
@app.route("/")
def homepage():
return render_template("home.html")
@app.route("/dump")
def dump_entries():
db = get_db()
rows = db.execute('select id, date, title, content from entries order by date')
output = ""
for r in rows:
debug(str(dict(r)))
output += str(dict(r))
output += "\n"
return "<pre>" + output + "</pre>"
@app.route("/browse")
def browse():
db = get_db()
rows = db.execute('select id, date, title, content from entries order by date')
rowlist = rows.fetchall()
return render_template('browse.html', entries=rowlist)
@app.route("/write", methods=['get', 'post'])
def write():
# Step 1, display form
if "step" not in request.form:
return render_template('write.html', step="compose_entry")
# Step 2, add blog post to database.
elif request.form["step"] == "add_entry":
db = get_db()
db.execute("insert into entries (date, title, content) values (datetime('now', 'localtime'), ?, ?)",
[request.form['title'], request.form['content']])
db.commit()
return render_template("write.html", step="add_entry")
@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:
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)
# Step 3, user has changed post, now update the DB with changes
elif request.form["step"] == "update_database":
db = get_db()
# 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:
db.execute("update entries set title=?, content=?, date=datetime('now', 'localtime') where id=?",
[request.form['title'], request.form['content'], postid])
else:
db.execute("update entries set title=?, content=? where id=?",
[request.form['title'], request.form['content'], postid])
db.commit()
return render_template("edit.html", step="update_database")
#####################################################
# Database handling
def connect_db():
"""Connects to the database."""
debug("Connecting to DB.")
conn = sqlite3.connect(os.path.join(app.root_path, 'blog.db'))
conn.row_factory = sqlite3.Row
return conn
def get_db():
"""Opens a new database connection if there is none yet for the
current application context.
"""
if not hasattr(g, 'db_connection'):
g.db_connection = connect_db()
return g.db_connection
@app.teardown_appcontext
def close_db(error):
"""Closes the database automatically when the application
context ends."""
debug("Disconnecting from DB.")
if hasattr(g, 'db_connection'):
g.db_connection.close()
######################################################
# Command line utilities
def init_db():
db = get_db()
with app.open_resource('schema.sql', mode='r') as f:
db.cursor().executescript(f.read())
db.commit()
@app.cli.command('initdb')
def init_db_command():
"""Initializes the database."""
print("Initializing DB.")
init_db()
def populate_db():
db = get_db()
with app.open_resource('populate.sql', mode='r') as f:
db.cursor().executescript(f.read())
db.commit()
@app.cli.command('populate')
def populate_db_command():
"""Populates the database with sample data."""
print("Populating DB with sample data.")
populate_db()
#####################################################
# Debugging
def debug(s):
"""Prints a message to the screen (not web browser)
if FLASK_DEBUG is set."""
if app.config['DEBUG']:
print(s)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>
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('homepage') }}">Back to homepage</a>
{% endif %}
</body>
</html>Use the main wiki page to navigate, not the list of pages directly above, because those are out of order.