Tag: sqlite

I wanted to do a Todo List web application that I could pass on to my kids to try. My goal was to give them an introduction to SQL, Web interfaces and Web templating.

For the Todo List application the Python Bottle Web Framework will be used. The Bottle library is a lightweight standalone micro web framework.

To store the Todo list items an SQLite database is used. SQLite is a file based server-less SQL (Structured Query Language) database which is ideal for small standalone applications.

Finally to build Web page, Python web templates will be used. The advantage of using web templates is that is reduces the amount of code written and it separates the presentation component from the backend logic.

Getting Started with Bottle

To install the Python bottle library:

pip install bottle

As a test we can make a program that has a home page (“/”) and a second page, then links can be put on each of the page to move back and forward.

The @route is a decorator that links a URL call, like “/” the home page to a function. In this example a call to the home page “/” will call the home_page() function.

In the home_page() and pages2() functions the return call is used to pass HTML text to the web browser. The anchor tag (<a) is used to define page links.

The run() function will start the Bottle micro web server on: http://127.0.0.1:8080/

The output is below. 

SQLite

The Python SQLite library is one of the base libraries that is installed with Python.

SQLite has a number of tools and utilities that help manage your databases. One useful  light weight application is: DB Browser for SQLite. It is important to note that SQLite data can be view by multiple applications, but for edits/deletes only 1 application can be accessing SQLite.

For the Todo list we’ll start with a simple database structure using three fields:

• Category – this a grouping such as: shopping, projects, activities etc.
• theItem – this is the actual to do item
• id – an unique index for each item. (This will used later for deleting rows)

The Todo database can be created with the SQLite Brower, or in Python code. The code below creates that database, adds a todo table and then inserts some records.

import sqlite3

print("Create a todo list database...")

conn = sqlite3.connect('todo.db') # Warning: This file is created in the current directory
conn.execute("CREATE TABLE todo (category char(50), theitem char(100),id INTEGER PRIMARY KEY )")
conn.execute("INSERT INTO todo (category, theitem) VALUES ('Shopping','eggs')")
conn.execute("INSERT INTO todo (category, theitem) VALUES ('Shopping','milk')")
conn.execute("INSERT INTO todo (category, theitem) VALUES ('Shopping','bread')")
conn.execute("INSERT INTO todo (category, theitem) VALUES ('Activities','snow tires')")
conn.execute("INSERT INTO todo (category, theitem) VALUES ('Activities','rack lawn')")
conn.commit()

print("Database todo.db created")

The DB Browser can be used to view the newly created database.

Viewing the Data in Python

An SQL SELECT command is used to get all the records in the todo database.  A fetchall() method is will return all the database rows in a Python tuple variable. Below is the code to write the raw returned data to a browser page.

# Send to raw SQL result to a Web Page
#
import sqlite3
from bottle import route, run

@route('/')
def todo_list():
    conn = sqlite3.connect('todo.db')
    c = conn.cursor()
    
    c.execute("SELECT * FROM todo")
    result = c.fetchall()
    c.close()
# note: the SQL results are an array of data (tuple)
# send results as a string
    return str(result)

run()

The output formatting can be improved by adding a sort to the SQL SELECT statement and then HTML code are be used to show category heading.

For small applications putting HTML code in the Python code is fine, however for larger web projects it is recommended that the HTML be separated out from the database or backend code.

Web Templates

Web templates allow you to separate the database and back end logic from the web presentation. Bottle has a built-in template engine called Simple Template Engine. I found it did everything that I needed to do. It’s possible to use other Python template libraries, such as Jinja, Mako or Cheetah, if you feel you need some added functionality.

The earlier Python code is simplified by removing the HTML formatting logic. A template object is created with a template name of  sometime.tpl). An example would be:

output = template(‘make_table0‘, rows=result, headings = sqlheadings)

Where rows and headings are variable names that are used in the template file. The template file make_table0.tpl is in the working directory.

#
# Build a To Do List with a Web Template
#
import sqlite3
from bottle import route, run, template


@route('/')
def todo_list():
    # Send the output from a select query to a template
    conn = sqlite3.connect('todo.db')
    c = conn.cursor()    
    c.execute("SELECT * FROM todo order by category, theitem")
    result = c.fetchall()
    # define a template to be used, and pass the SQL results
    output = template('make_table0', rows=result)
    return output
        
run()

Templates are HTML pages with inline Python code. Python code can either be in blocks with a <% to start the block and a %> to end the block, or each line can start with %.

Two of the major differences of inline template Python code are:

• indenting the line of Python is not required or used
• control statements like : if and for need an end statement

A template that takes the SQL results and writes each row in a table would look like:

A template that take the SQL results and writes a category heading and lists the items would look like:

Include ADD and DELETE to the Templates

The next step is to include ADD and DELETE functionality.

For the DELETE functionality, a form is added to the existing template. A ‘Delete’ button is placed beside all the Todo items and the button passes the unique index id (row[2]) of the item. The form has a POST request  with the action going to the page ‘/delete‘ on the Bottle Web Server.

For the ADD functionality, a new template is created and a %include call is put at the bottom of the main template. (You could also put everything in one file).

The main template now looks like:

The new item template uses a dropdown HTML element with some predefined category options (Activities, Projects and Shopping). The item text will displace 25 characters but more can be entered. Pressing the save button will generate a POST request to the “/new” URL on the Bottle server.

The new_todo.tpl file is:

Bottle Python Code with /add and /delete Routes

The final step is to include routes for the /add and /delete URL references.

The new_item() function gets the category and Todo item from the form in the new_todo template. If the request passes a non-blank item (request.forms.get(“theitem”) then an SQL INSERT command will add a row. The unique item ID is automatically included because this field is defined as an index key.

The delete_item() function reads the unique item id that is passed from the button to issue an SQL DELETE statement.

At the end of the new_item() and delete_item() function the user is redirected back to the home (“/”) page.

#
# Build a Todo List 
#
import sqlite3
from bottle import route, run, template, request, redirect, post  

# The main page shows the Todo list, /new and /delete references are called from this page
@route('/')
def todo_list():
    conn = sqlite3.connect('todo.db')
    c = conn.cursor()
    
    c.execute("SELECT * FROM todo order by category,theitem ")
    result = c.fetchall()
    # in case column names are required
    colnames = [description[0] for description in c.description]
    numcol = len(colnames)
    # for now only the rows=result variables are used
    output = template('show_todo', rows=result, headings=colnames, numcol = numcol)
    return output

# Add new items into the database
@route('/new', method='POST')
def new_item():

    print("New Post:", request.body.read())
    theitem = request.forms.get("theitem")
    newcategory = request.forms.get("newcategory")

    if theitem != "":        
        
        conn = sqlite3.connect('todo.db')
        c = conn.cursor()
        c.execute("INSERT INTO todo (category,theitem) VALUES (?,?)", (newcategory,theitem))
        conn.commit()
        c.close()

    redirect("/") # go back to the main page   

# Delete an item in the database
@route('/delete', method='POST')
def delete_item():

    print("Delete:", request.body.read() )
    theid = request.forms.get("delitem").strip()
    print("theid: ", theid)
               
    conn = sqlite3.connect('todo.db')
    c = conn.cursor()
    sqlstr = "DELETE FROM todo WHERE id=" + str(theid)
    print(sqlstr)
    c.execute(sqlstr)
    conn.commit()
    c.close()

    redirect("/") # go back to the main page   
        
run()

The application will look something like:

Final Clean-up

Some of the final clean-up could include:

• enlarge the database to include fields like: status, due date, who is responsible etc.
• add an “Are you sure?” prompt before doing adds and deletes
• verify double entries aren’t included
• include an edit feature
• make the interface slicker

If you want to speed up the performance PyPy can be used instead of the Python interpreter. To use Pypy (after you’ve installed it), you will need to install the pip and bottle:

pypy3 -m ensurepip --user
pypy3 -mpip install bottle --user

Final Comments

As I was working on this I found a good BottleTutorial: Todo-List Application. This tutorial approaches the Todo list project differently but it is still a worthwhile reference.