Bottle micro web services framework 5 : Bucket List App I - sqlite, route, and template

List of Bottle Micro Web Services Tutorials

1. Introduction
2. Static files
3. Template
4. json
5. Bucket List App I - sqlite, route, and template
6. Bucket List App II - get & post
7. Bucket List App III - Editing
8. Bucket List App IV - route validation, regex, and static_file
9. Bucket List App V - json
10. json to html table
11. Forms - Get & Post
12. Forms - Get & Post with editable and checkbox table cells

In this chapter, we'll cover some of the key features of Bottle framework via a Buck list app. This tutorial is based on Tutorial: Todo-List Application, and I'll try to give more code samples by making variations on the ToDo List.

First, we need to make a db using SQLite:

sq.py:

import sqlite3
con = sqlite3.connect('bucket.db')
con.execute("CREATE TABLE bucket (id INTEGER PRIMARY KEY, wish char(100) NOT NULL, status bool NOT NULL)")
con.execute("INSERT INTO bucket (wish,status) VALUES ('Flying over Golden Gate Bridge',0)")
con.execute("INSERT INTO bucket (wish,status) VALUES ('Wind surfing under Golden Gate Bridge',0)")
con.execute("INSERT INTO bucket (wish,status) VALUES ('Bungee jumping from Golden Gate Bridge',0)")
con.execute("INSERT INTO bucket (wish,status) VALUES ('Walking across Golden Gate Bridge',1)")
con.commit()            

Run the server:

$ python bjson.py
Bottle v0.12.7 server starting up (using WSGIRefServer())...
Listening on http://localhost:8080/
Hit Ctrl-C to quit.

127.0.0.1 - - [06/Oct/2014 20:08:03] "GET /api/status HTTP/1.1" 200 53

This will generates a database-file bucket.db with tables called bucket and three columns id, wish, and status. id is a unique id for each row, which is used later on to reference the rows. The column wish holds the text which describes the wish, it can be max 100 characters long. Finally, the column status is used to mark a task as open (value 1) or closed (value 0).

Then we have db created:

$ ls
bucket.db  sq.py

When the page http://localhost:8080/bucket is used as the url for the browser, Bottle takes the call and checks if there is any (Python) function defined for the route 'bucket'. If so, Bottle will execute the corresponding Python code and return its result.

bucket.py:

import sqlite3
from bottle import route, run

@route('/bucket')
def bucket_list():
    conn = sqlite3.connect('bucket.db')
    c = conn.cursor()
    c.execute("SELECT id, wish FROM bucket WHERE status LIKE '1'")
    result = c.fetchall()
    return str(result)

run()

The run() starts the web server included in Bottle. By default, the web server serves the pages on 'localhost' and 'port 8080'. The imported route is responsible for Bottle's routing. Note that we defined one function, bucket_list(), with a few lines of code reading from the database. The important point is the decorator statement @route('/bucket') right before the def bucket_list() statement. By doing this, we bind this function to the route /bucket, so every time the browsers calls http://localhost:8080/bucket, Bottle returns the result of the function bucket_list(). That is how routing within bottle works.

Let's just run python bucket.py and call the page http://localhost:8080/bucket in our browser:

What we see is a list of tuples with each tuple contains one set of results returned from the SQL query.

Actually, we can bind more than one route to a function. So the following code will work:

@route('/bucket')
@route('/my_bucket')
def bucket_list():
...

However, it won't work if we bind one route to more than one function.

Another feature is auto-reloading, which is enabled by modifying the run() statement like this:

run(reloader=True)

This will automatically detect changes to the script and reload the new version once it is called again, without the need to stop and start the server.

A template in bottle is stored as separate file with a .tpl extension. The template can be called from within a function. Template can contain any type of text (which will be most likely HTML mixed with Python statements). Furthermore, templates can take arguments, e.g. the result set of a database query, which will be then formatted within the template.

In this section, we are going to put the result of our query showing the open bucket list items into a simple table with two columns: the first column will contain the id of the item, the second column the list. The result set is, as we've seen in the previous picture, a list of tuples, each tuple contains one set of results.

To incorporate the template in our example, we just add the following lines:

# bucket.py
import sqlite3
from bottle import route, run, template

HOST = 'localhost'
PORT = 8080

@route('/bucket')
def bucket_list():
    conn = sqlite3.connect('bucket.db')
    c = conn.cursor()
    c.execute("SELECT id, wish FROM bucket WHERE status LIKE '1'")
    result = c.fetchall()
    c.close()
    return template('wish_table', rows=result)

run(host=HOST, port=PORT, debug=True)

Two new things here:

1. We imported template from Bottle.
2. We returned the output of the template wish_table. In addition to calling the template, we assign result, which we received from the database query, to the variable rows, which is later on used within the template.

Note that templates always return a list of strings, thus there is no need to convert anything.

If we run the server at this stage and calls http://localhost:8080/bucket, we get the following error:

So, we need to write the wish_table.tpl template, which should like this:

%# wish_table.tpl
<p>The open wish items are as follows:</p>
<table border="1">
%for r in rows:
  <tr>
  %for c in r:
    <td>{{c}}</td>
  %end
  </tr>
%end
</table>

The line starting with % is interpreted as Python code. Please note that, of course, only valid Python statements are allowed, otherwise the template will raise an exception. The other lines are plain HTML.

As we can see, we use Python's for statement two times, in order to iterate over rows. The rows is a variable which holds the result of the database query, so it is a list of tuples. The first for statement accesses the tuples within the list, the second one the items within the tuple, which are put each into a cell of the table.

If we need to access a variable within a non-Python code line inside the template, we need to put it into double curly braces. This tells the template to insert the actual value of the variable right in place.

Since we only have one item, let's add one more by running the following script (sq2.py):

import sqlite3
con = sqlite3.connect('bucket.db')
with con:
    cur = con.cursor()
    con.execute("INSERT INTO bucket (wish,status) VALUES ('Living near Golden Gate Bridge',1)")
    con.commit()

Of course, we can have more items if we select '0' in bucket.py file:

c.execute("SELECT id, wish FROM bucket WHERE status LIKE '0'")