db3

How to use Python's SQLite interface to query a database

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.

Add a route to dump the entries table

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 blog.py file. I put it near the top, before all the database and command-line functions.

####################################################
# Routes

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