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():
    db = get_db()
    rows = db.execute('select id, date, title, content from entries order by date')
    output = ""
    for r in rows:
        debug(str(dict(r)))
        output += str(dict(r))
        output += "\n"
    return "<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.

Visit http://localhost:5000/dump. You should see the sample database entries.

Dissecting the SQL Python functions

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

• We get our database connection object via get_db().
• We call the execute() function which executes one SQL command given as a string. The return value of execute() is a list of rows that we can iterate through.
  • Important! The list that execute() returns is only iterable once!. Once you loop through this list, you can't loop through it again without executing the query a second time. This is for memory/space reasons: execute() doesn't actually make a single list of all the rows that come back, because the number of rows coming back might be very large---possibly too large to store in memory all at once. Instead, we get an iterator object that lets us loop over each row as it comes out of the database, but then that row is gone. The upside of this is that we only have to store a single row in memory at once, rather than all the rows. The downside is that we can't look at more than one row at a time. But if you find yourself needing to look at more than one row of the database at once, you probably should re-write your query, rather than try to save all the rows in a temporary data structure of some sort.
• 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 is sqlite3.Row, which you don't have to know much about other than it lets us treat a row 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.