-
Notifications
You must be signed in to change notification settings - Fork 4
db5
Now that we know how to get information out of a database, in this lesson we will see how to add a Flask route for inserting information into a database.
Let's add a route for writing blog posts. Let's look at the different steps here, broken down by what the client (web browser) is doing, and what the server (Python/Flask) is doing:
- Client: Send request to server for the blog post blank form.
- Server: Receive request for blank form. Send back blank form.
- Client: Receive blank form. Users fills out form with blog entry details. User submits form. Send request to server with contents of the form.
- Server: Receive blog post data from form. Add the blog post to the database. Send a notification back to the client.
- Client: Receive notification from server that entry was posted.
This is two cycles of client-request/server-respond. To distinguish these, we will call the first cycle "compose_entry" and the second cycle "add_entry."
Let's begin by making a new route:
@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":
conn = get_db()
cursor = conn.cursor()
cursor.execute("insert into entries (title, content) values (%s, %s)",
[request.form['title'], request.form['content']])
conn.commit()
return render_template("write.html", step="add_entry")And let's add a new template, write.html:
<html>
<head>
<title>Browse entries</title>
</head>
<body>
<h1>Write an entry</h1>
{% if step=="compose_entry" %}
<form action="{{ url_for('write') }}" method="post">
Title: <input type="text" size="30" name="title"><br>
Text: <textarea name="content" rows="5" cols="40"></textarea><br>
<input type="hidden" name="step" value="add_entry">
<input type="submit">
</form>
{% elif step == "add_entry" %}
Your post has been submitted.
{% endif %}
</body>
</html>Let's analyze this in the order of the client/server requests.
- Client requests the
/writeroute. On the server, Flask runs thewrite()function. No form has been submitted, so there's no "step" in request.form. We render thewrite.htmltemplate with thecompose_entrystep. - Over in the HTML template, we see the code for the
compose_entrystep displays a blank form. Notice how we use a hidden field to tell the server what step to do next. The user fills out the form, presses submit, and we start another request... - Back on the server, Flask runs
write()again, but now the form has been submitted, sorequest.form["step"] == "add_entry"is true. We get our database connection, run a SQL INSERT query (explained further below), commit the results, then render the template again, but with a different step. - Back in the HTML template, our step is now
add_entry, so we display a simple confirmation message.
Note that at this point, you should be able to add blog entries in the web browser by going to the URL /write, and they should then show up at the URL /browse.
The SQL INSERT statement syntax is (we looked at it earlier, but here it is again):
INSERT INTO table_name (attrib1, attrib2, ...) VALUES (val1, val2, ...)
Take a look at our INSERT statement from the Flask code:
insert into entries (title, content) values (%s, %s)
- Note that we are leaving out the "id" attribute of entries because PostgreSQL fills it in for us automatically, as well as the date attribute. In general, when using SQL INSERT, if you leave out a column, SQL will either insert a NULL or the "default value" for that attribute. When you create a table, you can optionally specify a default value other than NULL.
- Also note that in the VALUES section, we are using
%ssymbols in place of literal strings for title and content. When running a query with Python'sexecute()function, you may optionally pass a list as the second argument to this function. The number of question marks must match the number of items in the list. Notice how we make a list of the form's title and content that were submitted from the client ([request.form['title'], request.form['content']]) and we pass this as the second argument.
When this happens, SQLite will replace the %s part with corresponding entries from the list. This is the appropriate way to get information from a form and use it in an SQL query. Never use string concatenation or Python's string variable formatting/substitution to build an SQL query and from variables taken from a web form. This leaves you open to an SQL injection attack. See this comic. Using the question mark substitution strategy "sanitizes" the values in the list passed to execute().
Since we have two routes now, one for browsing and one for editing, it would be nice if our website had a homepage from which we could access both of these routes.
Edit the homepage route back in main.py:
@app.route("/")
def index():
return render_template('home.html') Create a home.html webpage template with two hyperlinks. One will take you go the /browse route, and the other
will take you to /write. Do not manually link them to the /browse URL and the /write URL. Instead, use url_for() to retrieve those URLs.
Hint: use code like <a href="{{ put something here }}">Browse entries</a>
To the bottom of the browse template, add a hyperlink to this new home.html page you just created. This way, after a user clicks "browse," they can go back to the homepage when they have read all the entries. Again, use url_for().
In the write.html template, add a hyperlink to the homepage that only appears after a blog entry has been submitted. In other words, don't display it at the same time as the blank form; only have it show up on the notification page that says "Your post has been submitted."
How to do it is below (but try yourself first!): .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Here are the edits we've made to our files:
In main.py:
@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":
conn = get_db()
cursor = conn.cursor()
cursor.execute("insert into entries (title, content) values (%s, %s)",
[request.form['title'], request.form['content']])
conn.commit()
return render_template("write.html", step="add_entry")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>
</ul>
</body>
</html>browse.html:
<html>
<head>
<title>Browse entries</title>
</head>
<body>
<h1>Browse entries</h1>
{% if entries|length > 0 %}
<ul>
{% for entry in entries %}
<li><b>{{ entry.title }}</b> ({{ entry.date }})
<p>
{{ entry.content }}
<hr>
{% endfor %}
</ul>
{% else %}
This blog is empty.
{% endif %}
<a href="{{ url_for('homepage') }}">Back to homepage</a>
</body>
</html>write.html:
<html>
<head>
<title>Browse entries</title>
</head>
<body>
<h1>Write an entry</h1>
{% if step=="compose_entry" %}
<form action="{{ url_for('write') }}" method="post">
Title: <input type="text" size="30" name="title"><br>
Text: <textarea name="content" rows="5" cols="40"></textarea><br>
<input type="hidden" name="step" value="add_entry">
<input type="submit">
</form>
{% elif step == "add_entry" %}
Your post has been submitted.
<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.