Skip to content

db5

Jump to bottom
pkirlin edited this page Oct 12, 2016 · 21 revisions

Writing blog entries and using SQL INSERT

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":
        db = get_db()
        db.execute("insert into entries (date, title, content) values (datetime('now'), ?, ?)",
                   [request.form['title'], request.form['content']])
        db.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 /write route. On the server, Flask runs the write() function. No form has been submitted, so there's no "step" in request.form. We render the write.html template with the compose_entry step.
  • Over in the HTML template, we see the code for the compose_entry step 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, so request.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 localhost:5000/write, and they should then show up at localhost:5000/browse.

Explanation of SQL INSERT

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, ...)

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

Clone this wiki locally