db3

How to use Python's PostgreSQL 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 main.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()
    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 "<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.

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