source: bottlepy.org
Now let’s have a look at casting the output of the script into a proper format.
Actually Bottle expects to receive a string or a list of strings from a function and returns them by the help of the built-in server to the browser. Bottle does not bother about the content of the string itself, so it can be text formatted with HTML markup, too.
Bottle brings its own easy-to-use template engine with it. Templates are stored as separate files having a .tpl extension. The template can be called then from within a function. Templates can contain any type of text (which will be most likely HTML-markup mixed with Python statements). Furthermore, templates can take arguments, e.g. the result set of a database query, which will be then formatted nicely within the template.
Right here, we are going to cast the result of our query showing the open ToDo items into a simple table with two columns: the first column will contain the ID of the item, the second column the text. The result set is, as seen above, a list of tuples, each tuple contains one set of results.
To include the template in our example, just add the following lines:
import sqlite3
from bottle import route, run, debug, template
@route('/todo')
def todo_list():
conn = sqlite3.connect('todo.db')
c = conn.cursor()
c.execute("SELECT id, task FROM todo WHERE status LIKE '1'")
result = c.fetchall()
c.close()
output = template('make_table', rows=result)
return output
run()
So we do here two things: first, we import template from Bottle in order to be able to use templates. Second, we assign the output of the template make_table to the variable output, which is then returned. 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. If necessary, you can assign more than one variable / value to a template.
Templates always return a list of strings, thus there is no need to convert anything. We can save one line of code by writing return template('make_table', rows=result), which gives exactly the same result as above.
Now it is time to write the corresponding template, which looks like this:
%#template to generate a HTML table from a list of tuples (or list of lists, or tuple of tuples or ...)
<p>The open items are as follows:</p>
<table border="1">
%for row in rows:
<tr>
%for col in row:
<td>{{col}}</td>
%end
</tr>
%end
</table>
Save the code as make_table.tpl in the same directory where todo.py is stored.
Let’s have a look at the code: every line starting with % is interpreted as Python code. Because it is effectively Python, only valid Python statements are allowed. The template will raise exceptions, just as any other Python code would. The other lines are plain HTML markup.
As you can see, we use Python’s for statement two times, in order to go through rows. As seen above, 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. It is important that you close all for, if, while etc. statements with %end, otherwise the output may not be what you expect.
If you need to access a variable within a non-Python code line inside the template, you need to put it into double curly braces. This tells the template to insert the actual value of the variable right in place.
Run the script again and look at the output. Still not really nice, but at least more readable than the list of tuples. You can spice-up the very simple HTML markup above, e.g. by using in-line styles to get a better looking output.
