People App¶

In the course downloads folder is a simple but complete web-database application called people_app. A reference version of that is available in ~cs304/pub/downloads/people_app

It is fairly simple, though, totalling less than about 100 lines of python, split over three modules. There is also a short html template file.

• It starts a Flask app and configures the cs304dbi
• It responds to requests from a browser
  • all the requests are GET requests
  • some have parameters
• On each request, it
  • connects to the WMDB database
  • extracts information from the request, if necessary
  • queries the database
  • gets data back from the database
  • renders a template, and
  • sends the result to the browser

This complete round-trip from request to response usually takes only a few hundred milliseconds!

Python 102¶

I've found that there are a number of features of Python that many students have either forgotten or never learned. That's fine; now's the time to learn them. If CS 111 was "Python 101"; this is "Python 102". You'll find that information in the Python 102 reading.

One reasonable strategy is to look into the Python102 reading only to learn about features you encounter but don't know about. For example, read the section on the format method when/if you encounter it. Read the section on Exceptions when/if you encounter that.

You might take a few minutes now just to look at the table of contents.

The People App will use the following features:

• import
• formatting strings with the .format method
• keyword arguments

Please make sure you read those sections.

Overview¶

Before digging into the code, let's get a high-level view of the app. Here's a picture, which our Python modules drawn as blue boxes, and the MySQL server drawn as a green box:

Note that the people_app folder does not contain the cs304dbi module or the pymysql module. Those modules have been put in the virtual environment, in the venv sub-folder of the ~cs304/pub/downloads folder.

Running the App¶

You can run the app by activating that virtual environment (or your own; they should be equivalent) and then running app.py:

source ~cs304/pub/downloads/venv/bin/activate
cd ~cs304/pub/downloads/people_app
python app.py

Note that you only have to activate your venv if you haven't already activated it in that terminal. If you have multiple terminals in VS Code, you have to activate your venv in any terminal that you want to run Python in. You can tell if you've activated it by looking at your unix prompt, which is modified to include the name of your venv folder in parentheses.

The subsequent sections will look at each Python module in turn:

The app Module¶

The app.py module contains the routes that receive, process, and respond to requests from the browser. It will import functions from other modules to help it do that work.

• it imports servertime to get the now() function
• it imports cs304dbi to get functions like connect()
• it imports people to get functions that interact with the database
• it imports flask to get functions like render_template

Here's the complete code. It's looks daunting, but it's really only about 50 lines of code.

'''Flask/PyMySQL app to list the people in the WMDB. 

Scott D. Anderson
revised Fall 2019 for Python3
revised Spring 2020 for new dbi module
'''

from flask import (Flask, url_for, render_template)
import cs304dbi as dbi
import servertime
import people

app = Flask(__name__)

@app.route('/')
def hello_world():
    tmpl = '''<h1>Hello Everyone!</h1>
             <p>Click here for <a href="{url1}">people list</a></p>
             <p>Click here for <a href="{url2}">list of people born in June</a></p>
           '''
    page = tmpl.format(url1=url_for('people_list'),
                       url2=url_for('people_born_in_month',month=6))
    return page
    
@app.route('/people/')
def people_list():
    conn = dbi.connect()
    folk = people.get_people(conn)
    now = servertime.now()
    return render_template('people-list.html', 
                          desc='WMDB People as of {}'.format(now), 
                          people=folk)
    
month_names = ['skip', 'January', 'February', 'March',
               'April', 'May', 'June',
               'July', 'August', 'September',
               'October', 'November', 'December' ]

@app.route('/people-born-in/<int:month>')
def people_born_in_month(month):
    conn = dbi.connect()
    folk = people.people_born_in_month(conn,month)
    head = 'WMDB People born in {}'.format(month_names[month])
    return render_template('people-list.html', 
                          desc=head,
                          people=folk)

if __name__ == '__main__':
    import os
    uid = os.getuid()
    dbi.conf('wmdb')
    app.debug = True
    app.run('0.0.0.0',uid)

Notice at the bottom of the file are a few things that run when we start the app. It gets our UID, which we'll use as the port number to open. It reads our CNF¹ file, since that won't change. We also use the wmdb database. It does not connect to the database; each request will do that.

App.py Step by Step¶

If you're not feeling comfortable with the code of app.py, let's go through it step by step.

Imports¶

Lines 1-4 import some stuff. If you don't remember much about import in Python, you can read more about import in Python

from flask import (Flask, url_for, render_template)
import cs304dbi as dbi
import servertime
import people

The first line imports three names from the Flask module (which we installed into our venv using pip). The list of names that we import from Flask will get longer as the semester goes on, so be prepared for that.

The second line imports the cs304dbi module and gives it a short nickname, just for brevity in our code.

The next two lines import two other modules, both of which correspond to files in this folder. We'll discuss servertime and people later in this page.

The app Variable¶

Line 6 is:

app = Flask(__name__)

This creates a global variable named app using the Flask class that we imported on line 1. We pass the name of the module or package of the application, which is the same as the file we are in.

You will always pass __name__ to the Flask class, so it's best to just think of this as a magic incantation and not think about it further.

You can choose a different name for the global variable, but app is traditional, and it's also clear and succinct, so I see no need to vary this.

The hello_world Route¶

Lines 8-16 contain a function definition. I've copied it here with new line numbers for easy reference:

@app.route('/')
def hello_world():
    tmpl = '''<h1>Hello Everyone!</h1>
             <p>Click here for <a href="{url1}">people list</a></p>
             <p>Click here for <a href="{url2}">June birthdays</a></p>
           '''
    page = tmpl.format(url1=url_for('people_list'),
                       url2=url_for('people_born_in_month',month=6))
    return page

The function is called hello_world. It's defined the same way as you would define any Python function, just like you learned in CS 111. The body of the function is similarly ordinary Python code. We'll get to that in a moment, but before that, let's talk about the @app.route decorator.

The app.route Decorator¶

Again, these first two lines:

@app.route('/')
def hello_world():

This is bit of Flask magic involving a decorator. (If you want to know more in general about decorators, you can read about decorators, but this is optional.)

Here's what's happening: our flask app is going to get requests. When a request comes in, the app has to figure out what function to call to handle the request. That decision is made based on the url or route that is in the request. I like to call this the routing algorithm. You can think of the routing algorithm as an enormous but invisible if statement:

if route == '/':
    h1()
elif route == '/about/':
    h2()
elif route == '/contact/':
    h3()
...
else:
    raise Error('no handler for route {}'.format(route))

The various functions (h1, h2 and h3) are handlers. A handler is just a python function that is associated with a route (a URL or a pattern of URLs).

Who defines the handlers? We do! We are coding the Flask app, so we write the handler functions.

Who decides the routes/URLs? We do! It's still our app, and we decide whether the route is called /about/ or /about-us/ or whatever.

Now, the routing algorithm is probably not implemented as an enormous if statement. It's probably implemented using a data structure that stores pairs of routes and handler functions. If it were implemented using Python dictionaries, we might do something like:

handler = routing_dictionary.get(route)
if handler is None:
    raise Error('no handler for route {}'.format(route))
else:
   handler()

Much easier! But you have to wrap your head around the idea of storing functions in a dictionary. But you can easily do so (see function dictionary).

So, how do the handlers get into the dictionary? Flask needs a way to let us put a handler function into the routing dictionary for our app. Enter the decorator magic:

@app.route(some_route)
def some_handler():
   ...

The route decorator stores the function that we define into the dictionary associated with the route that we specify.

So, now we understand what line 8 does:

@app.route('/')
def hello_world():

This tells Flask to associate the hello_world function with the / route, and so when that request comes in, our app will call that function.

Whew! Hard to believe I can write so much about one line of code! However, we'll use this decorator about 1,000 times this semester, so we might as well take a few minutes to understand it.

The hello_world Handler Function¶

Now let's return to the code of the hello_world handler function. Here it is again:

@app.route('/')
def hello_world():
    tmpl = '''<h1>Hello Everyone!</h1>
             <p>Click here for <a href="{url1}">people list</a></p>
             <p>Click here for <a href="{url2}">June birthdays</a></p>
           '''
    page = tmpl.format(url1=url_for('people_list'),
                       url2=url_for('people_born_in_month',month=6))
    return page

Line 3 defines a string, using the Python triple-quote syntax of a multi-line string. Multi-line strings are nice when we have long strings, and it allows us to nicely format them, particularly when they are in another programming language. Here the other language is HTML, but often in our Python code it'll be SQL.

The tmpl variable holds a template for some HTML code. There are two named placeholders in it, url1 and url2. The format method on line 7 will replace those placeholders with two actual URLs.

Lines 7 and 8 create urls for the routes that match two of the handler functions that are defined later in this file. One handler function is named people_list and the other is named people_born_in_month. That function name is long but clear and descriptive.

The people_list function takes no arguments, so the url_for call on line 7 needs no other information. The other function with the long name takes an argument, so the call to url_for needs a value for that argument. The value is defined keyword-style, so we supply month=6 to get a list of people born in June.

Those two URLs are then substituted into the tmpl string, resulting in a small bit of complete HTML. We store that momentarily in a local variable called page.

Finally, the last line returns that page to the browser, and the handler function has finished its work of responding to the request.

The people_list Handler Function¶

Now let's turn to the next chunk of code:

@app.route('/people/')
def people_list():
    conn = dbi.connect()
    folk = people.get_people(conn)
    now = servertime.now()
    return render_template('people-list.html', 
                          desc='WMDB People as of {}'.format(now), 
                          people=folk)

We now understand the first two lines. Those lines associate the URL /people/ with the handler function people_list. Not how the URL doesn't have to be identical to the name of the function. (Though in practice, it often is, but that's a coincidence or a habit, not a rule.)

Note that url_for() takes the name of the function as its argument, not the URL. If we change the URL, nothing else in our program needs to change. We don't have to track down all the references to that URL and update those. That's a big advantage of url_for() over hard-coding the route in other places that might re-direct to here, for example.

The code of the handler is just a few steps.

• We connect to the database using the connect function in cs304dbi. We don't need to read the CNF file, because the app will read that (and cache the info) when the app starts. See the script block.
• We use the helper function get_people() defined in our people module, passing in the database connection that we just created. It returns a list of people, which we put in a local variable called folk.
• We use the helper function now defined in our servertime to get a string representing the current time on the server.
• Finally, we render a template, filling in the two local variables into placeholders in the template. Conceptually, this is the same as the .format method and the multi-line string that we saw in the hello_world route, except that
  • The template is stored in a file, rather than a multi-line string
  • The rendering is done by Jinja2 via the render_template function, instead of by the format method

The result of render_template is a complete web page, which we return to the browser.

The people_born_in_month Route¶

The next two chunks of code is are another route:

month_names = ['skip', 'January', 'February', 'March',
               'April', 'May', 'June',
               'July', 'August', 'September',
               'October', 'November', 'December' ]

@app.route('/people-born-in/<int:month>')
def people_born_in_month(month):
    conn = dbi.connect()
    folk = people.people_born_in_month(conn,month)
    head = 'WMDB People born in {}'.format(month_names[month])
    return render_template('people-list.html', 
                          desc=head,
                          people=folk)

The first part is just storing a fixed, static list of English month names in a Python variable. We set skip as the name of element 0, so that 'January' is in element 1 and so forth. We could omit this, but we'd end up with month-1 indexes later which is confusing and error-prone.

This route is a little different from our last two. This one is parameterized.

Parameterized Routes¶

Let's focus on the parameterized route:

@app.route('/people-born-in/<int:month>')
def people_born_in_month(month):

It takes a parameter which we have called month, which is an integer. So the route is actually a pattern and matches a set of URLs, from /people-born-in/1 to /people-born-in/12.

(Actually, it also matches /people-born-in/789 and infinitely many other URLs, so ideally we should put some error checking into our code. Some other day.)

Because the URL has information in it that the function needs, namely the month number, Flask parses² the URL, and associates the number in the URL, like the 6 we saw in the hello_world function, with a name, like month.

The name is passed into the handler function when it gets invoked.

If we have a more complex URL with multiple parameters, Flask is smart enough to handle this. The following works, despite the fact that we changed the order of arguments to the handler function.

@app.route('/article_by_year/<year>/<month>/')
def lookup_article(month,year):
    return '<p>year: {year} month: {month}'.format(month=month, year=year)

In practice, changing the order of parameters and arguments is asking for trouble, but you can do it if you need to. I recommend keeping the order the same.

Now let's return to the handler function.

The people_born_in_month Handler Function¶

As a reminder, here's our handler function:

@app.route('/people-born-in/<int:month>')
def people_born_in_month(month):
    conn = dbi.connect()
    folk = people.people_born_in_month(conn,month)
    head = 'WMDB People born in {}'.format(month_names[month])
    return render_template('people-list.html', 
                          desc=head,
                          people=folk)

This function gets called with a number from the URL. It then does the following steps, which you'll recognize as being very similar to the people_list handler function:

• We connect to the database.
• We use the helper function people_born_in_month() defined in our people module, passing in the database connection and the month that we want to use in the search.
• We generate a nice descriptive header using the array of English month names and store it in a local variable called head.
• Finally, we render the same template as before, filling in the two local variables into the same placeholders in the template. One is a string and the other a list of dictionaries, just as before.

Finally, the finished page is returned to the browser.

That's all the routes in this app. For your projects, you'll define many routes.

The Script Block¶

The end of our app is short and simple:

if __name__ == '__main__':
    import os
    uid = os.getuid()
    dbi.conf('wmdb')
    app.debug = True
    app.run('0.0.0.0',uid)

When we run the app.py file as a python script, that code executes. It imports the os module from the Python library and uses it to determine our UID. (It asks the operating system.)

Next, we read use the dbi module to read our MySQL credentials from our ~/.my.cnf file, saving them in memory so that we only have to do this once, and get ready to connect to the database. That makes the process of dbi.connect(), which we do a zillion times, much easier and more efficient.

Finally, we turn on debugging in our app, and start the infinite loop, using the run method of our app. That run method is essentially the run function that we imagined in the last section.

That's it for the app.py module! Now let's turn to our co-star, the people module.

The people Module¶

The people.py module contains functions that do queries and allow any clients (people who use this module) to succinctly get data from the database. It defines two functions (though it could define many more). It defines:

• getPeople which returns a list of all people in the person table
• peopleBornInMonth which returns a list of all people born in a given month

We saw these functions called from the handler functions in app.py, above.

Both functions take a database connection as their first argument. They require the caller to connect to the DBMS, including using the correct database. For this application, we'll use WMDB. That's all done by code in the app.py module.

Here's the complete code for the file. Most of it is pretty short. In fact, almost half the file is some code at the bottom that runs only when we run this file as a script, as opposed to importing it as a module.

import cs304dbi as dbi

# ==========================================================
# The functions that do most of the work.

def get_people(conn):
    '''Returns the name and birthdate of all the entries in
the person table, as a list of dictionaries.
    '''
    curs = dbi.dict_cursor(conn)
    curs.execute('select name,birthdate from person')
    return curs.fetchall()

def people_born_in_month(conn,month):
    '''Returns the name and birthdate of people born in given month,
    as a list of dictionaries
    '''
    curs = dbi.dict_cursor(conn)
    # this uses a prepared query, since the 'month' comes
    # from an untrustworthy source
    curs.execute('''
        select name,birthdate from person 
        where month(birthdate) = %s''',
                 [month])
    return curs.fetchall()

# ==========================================================
# This starts the ball rolling, *if* the file is run as a
# script, rather than just being imported.    

if __name__ == '__main__':
    dbi.conf('wmdb')
    conn = dbi.connect()
    pl = get_people(conn)
    for person in pl:
        print('{name} born on {date}'
              .format(name=person['name'],
                      date=person['birthdate']))
    print('September People')
    pl = people_born_in_month(conn,9)
    for person in pl:
        print('{name} born on {date}'
              .format(name=person['name'],
                      date=person['birthdate']))
    

You're welcome to run it as a script, just to see this code work. This is also a nice way to test your database functions before incorporating them in your app.py:

python people.py

Let's discuss this file piece by piece.

The get_people Function¶

The get_people function is pretty short:

def get_people(conn):
    '''Returns the name and birthdate of all the entries in
the person table, as a list of dictionaries.
    '''
    curs = dbi.dict_cursor(conn)
    curs.execute('select name,birthdate from person')
    return curs.fetchall()

This is similar to the examples we saw when we learned PyMySQL.

To recap, that function does the following:

• it creates a cursor so that it can execute a query. This one is a dictionary cursor, meaning that each row of the data will be represented as a Python dictionary, as opposed to a tuple.
• it executes a SQL query that retrieves all the rows of the person table. Each row will be represented as a dictionary. The data is stored in the cursor object.
• it gets a list of all the rows as a list, using the fetchall() method.

The database connection is an object that can create cursors but doesn't execute queries by itself.

Recall that a cursor is an object that can execute queries and fetch the results. The results are temporarily stored in the cursor object itself, and you can then get them one at a time (say, in a Python for loop) or you can get a list of all of them.

Once this function has the list of all the people, it returns the list to the caller.

The people_born_in_month Function¶

The next function is a bit more complicated, but we've seen it before when we learned about prepared queries.

def people_born_in_month(conn,month):
    '''Returns the name and birthdate of people born in given month,
    as a list of dictionaries
    '''
    curs = dbi.dict_cursor(conn)
    # this uses a prepared query, since the 'month' comes
    # from an untrustworthy source
    curs.execute('''
        select name,birthdate from person 
        where month(birthdate) = %s''',
                 [month])
    return curs.fetchall()

This function takes two arguments, a connection object and a month, which should be an integer. Again, I've omitted any error checking code, such as checking that the month is a number (like 6) rather than a string (like 'june') and that the number is in the correct range. Since we have discussed the connection object in the PyMySQL reading and in the previous section, I won't repeat that here.

Getting a dictionary cursor is exactly the same as in get_people, so I won't repeat that either.

The query is more complicated because there is static stuff and dynamic data. The static stuff is some SQL code in a triple-quoted string. The dynamic data is the month variable.

Combining dynamic data with static SQL can open our app up to a kind of web vulnerability called SQL injection unless we are careful. The best approach to foiling SQL injection attacks is to keep the dynamic data separate from the static SQL and hand them to the cursor separately, using a prepared query.

Therefore, the execute method on cursors allows either one or two arguments. The two-argument version is for prepared queries. The first argument is some static SQL code as a string and the second argument is the dynamic data as a list of values. Here, there is only one dynamic value, so we have a list of just one value: [month].

To properly combine the static SQL with the dynamic data, the execute method needs to know where to drop the values into the static string. You mark the places in the string to be filled in using %s. This technique is reminiscent of an obsolescent form of formatting strings in Python, and that's no accident, but it is not string formatting, and you must not use string formatting instead of the prepared query.

Once the prepared query is executed, the function gets a list of results and returns it, exactly like the get_people function.

The Script Block¶

The last chunk of the file is the code that is ignored when app.py imports this file as a module, but is executed if we were to run this file as a script on the command line. Here it is again:

if __name__ == '__main__':
    dbi.conf('wmdb')
    conn = dbi.connect()
    pl = get_people(conn)
    for person in pl:
        print('{name} born on {date}'
              .format(name=person['name'],
                      date=person['birthdate']))
    print('September People')
    pl = people_born_in_month(conn,9)
    for person in pl:
        print('{name} born on {date}'
              .format(name=person['name'],
                      date=person['birthdate']))

Lines 2 and 3 read and cache the CNF file and makes sure that our database connections will use the WMDB. We do the same thing in the script block of app.py.

Line 3 connects to the database, just like our handlers did in app.py.

Line 4 calls get_people to get a list of people and lines 6-9 iterate over the list and print each row, using the .format() method on strings.

Line 11 calls people_born_in_month with an argument of 9 to get people born in September. The rest of the code prints that list of people using the same code as lines 6-9.

That brings us to the end of this file. There's only one other file in the app, and that's the template that we rendered in two different routes in app.py.

The people-list.html Template File¶

The templates are all put in a folder called templates. This example app only has one template, but a larger app would probably have a half-dozen or more. A larger app would probably use template inheritance as well.

In the app, the two routes always produce a list of people and this template renders a list of people, so we can use the same template for both routes. Very convenient. Yet another example of modularity and abstraction. Here it saves us the work of writing a second HTML file.

Here's the template:

<!DOCTYPE html>
<html>
    <head>
        <title>Database People</title>
    </head>
    <body>
        
    <h1>List of {{ desc }}</h1>

    <article>
        <ol>
            {% for person in people %}
            <li>{{ person.name }} born on
                {{ person.birthdate }}</li>
            {% endfor %}
        </ol>
    </article>
    </body>
</html>

This is a pretty short chunk of HTML code, but it's still nice to have it in a separate file rather than incorporated into our Python files using triple-quoted strings. Besides, the triple-quoted strings might tempt us to copy/paste the HTML code from one route to another, losing some modularity and abstraction, as well as bloating our code. Finally, it's nicely indented (which would be annoying and awkward in a triple-quoted string in our Python file).

The code isn't just HTML code, though. Like our triple-quoted template strings, this contains placeholders where we can drop in dynamic data. Once again, we encounter the notion of combining static strings and dynamic data.

The placeholders are desc (line 8) and people (line 12).

The first placeholder is easy. It expects desc to be a string, and the string is just dropped into the file to replace {{ desc }}.

The second placeholder is a bit more advanced. Here are lines 11-15 again:

        <ol>
            {% for person in people %}
            <li>{{ person.name }} born on
                {{ person.birthdate }}</li>
            {% endfor %}
        </ol>

It's going to generate an HTML ordered list (ol). It expects people to be a list, and the template uses a for loop to iterate over the list, binding person to each element of the list.

This for loop syntax is similar to Python's loop syntax. The person variable is a new variable, created just for this loop. The people variable is an existing variable that should contain a list. This is Jinja2 language, but it mimics Python so that the Jinja language is easier to learn if you know Python. However, it ends with endfor instead of outdenting, because we can't rely on outdenting in an HTML file.

The loop body will create a list item (li) like this:

<li>{{x}} born on {{y}}</li>

However, the two places to be filled in are not given names like x and y but instead are filled in with Jinja2 expressions, namely person.name and person.birthdate.

Remember that person is a variable that holds an element of the list, and we know that the list is a list of dictionaries, so person holds a dictionary.

In many languages, including C, JavaScript, Java and others, you can access an element of a dictionary using dot notation: dict.key. In Python, we would have to say dict['key'], which is not hard, but is a bit more cumbersome. Jinja2 allows us to use dot notation when accessing an element of a dictionary. Here, we are pulling out the name and birthdate elements of each dictionary.

So, in a few lines of code, we can iterate over a list of dictionaries containing names and birthdates of people and create an HTML ordered list with a list item for each dictionary, giving the person's name and birthdate. Very powerful!

Allow me to draw your attention to some long-distance connections though.

• The columns in the wmdb.person table are name and birthdate.
• The SQL queries in people.py use those column names in the two queries, and they request results as dictionaries, so the dictionaries have keys of name and birthdate.
• The list of dictionaries flow to the handler functions in app.py which in turn pass those lists of dictionaries to render_template and to the people-list.html file.
• The people-list.html file then refers to the name and birthdate keys in elements of the list it was given in people.

If you were to rename one of the columns in the database, say full_name instead of name, we'd have to update all of these places. (Well, not the handler functions, which just pass the data from one place to another, without referring to the column names.)

Conclusion¶

Take some time to see how all these pieces fit together. Flask is nice because different kinds of things (e.g. html) can be hidden in separate files instead of cluttering up our Python code, but the downside is that we have to know how all these parts fit together.

Appendices¶

The following information is just for the interested or curious student.

Function Dictionary¶

In Python, functions are objects that can be stored in variables and in data structures. Later, they can be pulled out and called/invoked.

This code defines two functions, stores them in a dictionary associated with two routes, and then uses exactly the code I sketched out to look up a route and invoke the associated handler.

Try it! You can literally copy/paste it into a Python shell.

def hello():
    print('hello!')

def bye():
    print('goodbye!')

dictfun = {'/start/': hello, '/stop/':bye}
route = '/start/'  # or anything else

# here's the code from above
handler = dictfun.get(route)
if handler is None:
    raise Error('no handler for route {}'.format(route))
else:
    handler()