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

Take a look at our INSERT statement from the Flask code:

insert into entries (date, title, content) values (datetime('now'), ?, ?)
  • Note that we are leaving out the "id" attribute of entries because SQLite fills it in for us automatically. 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 two question marks in place of literal strings for title and content. When running a query with Python's execute() 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 question marks 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 https://xkcd.com/327/. Using the question mark substitution strategy "sanitizes" the values in the list passed to execute().

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

Clone this wiki locally