We have already seen how to use Python to run SQL commands from an external file. In this lesson, we will see how to run individual SQL SELECT queries directly from our application.

First, we will add a route that will simply retrieve all the rows in our entries table and "dump" them to a webpage. We say "dump" to mean that we're not going to make them look pretty --- we'll just use this for debugging.

Add the following route to your main.py file. I put it near the top, underneath the / route.

@app.route("/dump")
def dump_entries():
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('select id, date, title, content from entries order by date')
    rows = cursor.fetchall()
    output = ""
    for r in rows:
        debug(str(dict(r)))
        output += str(dict(r))
        output += "\n"
    return "Should see database dump here:\n<pre>" + output + "</pre>"

Notice how this route uses our debug() function to print things to the command-line (useful for debugging) as well as builds an output string using concatenation. The <pre> tag is used in HTML for "pre-formatted" text, which means it preserves spacing and line breaks, which usually HTML ignores. It's nice in this case for a quick piece of debugging code, which is what this dump route is.

You should be able to run your app with the green run button, then add /dump to the end of your URL. You should see the sample database entries.

Let's break down the dump_entries() function line by line.

• We get our database connection object via get_db().
• We get a database cursor object by calling cursor(). Cursors are useful, but we'll just gloss over this for now.
• We call the execute() function which executes one SQL command given as a string.
• We then call fetchall() which gives us a list of rows that we can iterate through.
• We make an output string that we will concatenate everything with.
• We start a loop over the rows iterator, using the variable r to represent a single row. r's datatype we can treat much like a Python dictionary. In other words, if you want to access a specific attribute, you can use r["name-of-attribute"].
• We convert r to a Python dictionary explicitly, then a string (for pretty-printing reasons), then print it with debugging and also concatenate it with our output.
• We finally send output to the web browser with return.