Python has lots of web frameworks. Bottle is one of them and is considered a WSGI Framework. It’s also sometimes called a “micro-framework”, probably because Bottle consists of just one Python file and has no dependencies besides Python itself. I’ve been trying to learn it and I was using the official Todo-list tutorial on their website. In this article, we’re going to go over this application and improve the UI a little bit. Then in a separate follow-up article, we’ll change the application to use SQLAlchemy instead of straight sqlite. You will probably want to go install Bottle if you’d like to follow along.
Getting Started
You always need to start somewhere, so for this application we’ll start with the code to create the database. We’re going to import everything from the get-go though rather than adding additional imports piecemeal. We’ll also be editing the code a little compared to the original. So let’s begin:
import sqlite3
con = sqlite3.connect('todo.db') # Warning: This file is created in the current directory
con.execute("CREATE TABLE todo (id INTEGER PRIMARY KEY, task char(100) NOT NULL, status bool NOT NULL)")
con.execute("INSERT INTO todo (task,status) VALUES ('Read Google News',0)")
con.execute("INSERT INTO todo (task,status) VALUES ('Visit the Python website',1)")
con.execute("INSERT INTO todo (task,status) VALUES ('See how flask differs from bottle',1)")
con.execute("INSERT INTO todo (task,status) VALUES ('Watch the latest from the Slingshot Channel',0)")
con.commit()
This code will create a little database with 4 entries in it. The ones that are set to zero are “done” and the items that are set to one are still to-do. Make sure you run this first before continuing so that the rest of the code will work as we’ll be running queries against this database. Now we’re ready to take a look at the main page’s code.
Creating the Main Page
import sqlite3
from bottle import route, run, debug
from bottle import redirect, request, template, validate
#----------------------------------------------------------------------
@route("/")
@route("/todo")
def todo_list():
"""
Show the main page which is the current 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
if __name__ == "__main__":
debug(True)
run()
Here we import all the necessary pieces for the entire application. Next we create some route decorators. A route in Bottle is a request to function call mapping. Take a look at the code above to see what that means. The first route maps the main page “/” to the todo_list function. Note that we have a second route too. That one maps the “/todo” page to the same function. Yes, Bottle supports multiple route mapping. If you were to run this code, you could go to either http://127.0.0.1:8080/todo or just http://127.0.0.1:8080/ and get to the same page. The SQL command on this page just grabs all the items from the database that still need to be done. We pass that result set, which is a list of tuples, to Bottle’s template function. As you can see, it accepts the name of the template and the result. Finally, we return the rendered template.
Note that we have debug turned on. This will return a full stacktrace to help in debugging issues. You won’t want to leave that enabled should you put this on your production server.
Now, let’s take a look at the template code:
%#template to generate a HTML table from a list of tuples (or list of lists, or tuple of tuples or ...)Your TODO Items:
| {{col}} | %endEdit |
Create New item
Show Done Items
Now templates in Bottle end with the .tpl extension, so this one should be saved as make_table.tpl Anything that starts with a percent sign (%) is Python. The rest is HTML. In this code, we create a table with the row’s id field and the task in the first and second columns. We also add a 3rd column to allow editing. At the end, we add a link to add a new item and another link so show all the “done” or finished items.
Now we’re ready to move on to editing our todo items!
Editing Our Items
Editing the todo items is very easy when using Bottle. However, there is more code involved than the main script because it displays the item to be edited and also handles the save changes request. This code is basically the same as the original:
#----------------------------------------------------------------------
@route('/edit/:no', method='GET')
@validate(no=int)
def edit_item(no):
"""
Edit a TODO item
"""
if request.GET.get('save','').strip():
edit = request.GET.get('task','').strip()
status = request.GET.get('status','').strip()
if status == 'open':
status = 1
else:
status = 0
conn = sqlite3.connect('todo.db')
c = conn.cursor()
c.execute("UPDATE todo SET task = ?, status = ? WHERE id LIKE ?", (edit, status, no))
conn.commit()
redirect("/")
else:
conn = sqlite3.connect('todo.db')
c = conn.cursor()
c.execute("SELECT task FROM todo WHERE id LIKE ?", (str(no)))
cur_data = c.fetchone()
return template('edit_task', old=cur_data, no=no)
You’ll note that the first route decorator is formatted differently than we’ve seen before. The “:no” portion means that we’ll be passing a value to the method that this route is mapped to. In this case, we’ll be passing a number (the id) of the item that is to be edited. We are also setting the method to GET to make the edit form work correctly. You can also set it to POST if you’re wanting to do that operation instead. If the user presses the save button, then the first part of the if statement will execute; otherwise if the user just loads up the edit page, the second part of the statement loads and pre-fills the form. There’s a second decorator called validate which we can use to validate the data that is passed in the URL. In this case, we check to see if the value is an integer. You’ll also notice that we use a redirect to send the user back to the main web page once we’ve saved the changes.
Now, let’s take a moment to look at the template code:
%#template for editing a task %#the template expects to receive a value for "no" as well a "old", the text of the selected ToDo itemEdit the task with ID = {{no}}
As you can see, this creates a simple input control to edit the text and a combobox for changing the status. You should save this code as edit_task.tpl. The next and last piece that we’ll go over is how to create a new item for our todo list.
Creating a New Todo List Item
As you’ve seen so far, Bottle is very easy to use. Adding a new item to our todo list is also extremely simple. Let’s take a look at the code:
#----------------------------------------------------------------------
@route("/new", method="GET")
def new_item():
"""
Add a new TODO item
"""
if request.GET.get("save", "").strip():
new = request.GET.get("task", "").strip()
conn = sqlite3.connect("todo.db")
c = conn.cursor()
c.execute("INSERT INTO todo (task,status) VALUES (?,?)", (new,1))
new_id = c.lastrowid
conn.commit()
c.close()
redirect("/")
else:
return template("new_task.tpl")
This code also uses a GET request in its route definition and we’re using the same idea here that we did with the edit function in that when the page loads, we do the code in the else portion of the if statement and if we save the form, we save the todo item to the database and redirect to the main page, which has been updated appropriately. For completeness, we’ll take a quick look at the new_task.tpl template:
Add a new task to the ToDo list:
Wrapping Up
At this point, you should know how to create a fully functional todo application. If you download the source, you’ll see it includes a couple of other functions for showing individual items or showing a table of “done” (or completed) items. This code should probably have additional error handling added to it and it could use a good web designer to spruce it up a bit with some CSS or images. We will leave those items for the reader to do, if you feel inspired.
Download the Source
Pingback: Mike Driscoll: Bottle – Adding SQLAlchemy to the Todo List Web App | The Black Velvet Room