SQLite/Bottle Todo List

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:

The output is below. 


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')")

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

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



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

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

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
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))

    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)

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

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.

Small Standalone Databases for IoT

For many IoT projects you want to historically save your data, however if you’re only saving a small amount of data using a database server such as MySQL can be overkill.

I wanted to come up with a way to save my data and be able to port it between projects and hardware.  My goal was to use Python because it has the best Raspberry Pi support. I looked at some options like:

  • CSV files – simple but filtering and sorting isn’t so easy
  • dBase files – good “old school” approach, however it’s no longer support in Excel and MS Access so viewing the data offline is a little ugly
  • Python Pickle and PickleDB – great for saving game scores but no real sorting
  • SQLite – excellent light weight database

For my light weight Python applications I was really happy with TinyDB. TinyDB offers:

  • a JSON storage interface, so you could easily migrate to MongoDB (or equivalent)
  • pure Python with no dependencies,  (so it runs on all OS’s and versions of Python)
  • supports queries and searches

Getting Started with TinyDB

To install TinyDB :

pip install tinydb

TinyDB is standalone so there are no server components. Your Python code talks directly to one of more JSON files, or you can also create multiple tables in one JSON.

Below is a Python example that create a JSON file with two tables.

# Use TinyDB to create a JSON file with 2 tables
from tinydb import TinyDB, Query

db = TinyDB('db2.json')
db.purge_tables() # Clear out the file's old data

table = db.table('Moisture')


table2 = db.table('Light')



import json

with open('db2.json') as f:
data = json.load(f)

print("JSON file with 2 tables\n")
print(json.dumps(data, indent = 4, sort_keys=True))

The JSON file will have a format like:


A Test Example

For a test example I wanted to simulate a month’s worth of 5 minute data. Then I would try some queries to show the data.

# Create a month's worth of 10 minute simulated JSON data
# Use a format of:
#    "Pi_1": {
#        "1": {
#            "date": "2019-04-01",
#            "time": "01:00",
#            "moisture": 577,
#            "light": 30
#        }

from tinydb import TinyDB, Query
import random

db = TinyDB('/writing/blog/tinydb/db1.json')
table = db.table('Pi_1')

for iday in range(1,31):
    print("day: ",iday)
    for ihour in range(0,24):
        print("\thour: ", ihour)
        for imin in range(0,56,5):
            thedate = "2019-04-{:02}".format(iday)
            thetime = "{:02}:{:02}".format(ihour, imin)
            table.insert({"date": thedate,"time": thetime ,"moisture": random.randint(200,600),"light":  random.randint(20,100)})

print ("Finished updating JSON file\n\n")

Once the JSON data file was created I used a simple Python Tkinter application to query the data by day. My goal was to see how easy it was to query the data and return results. Below is a my TinyDB Tkinter viewing application and code. The bottom slider is used to select the day in the month.


# Use a Tkinter 
# Create a dialog that uses TinyDB to query 1 day of data
# from a JSON file
from tinydb import TinyDB, Query
import tinydb 
import tkinter as tk

db = TinyDB('db1.json')

# get a selected days data
def sel():
   thedate = "2019-04-{:02}".format(int(var.get()))
   table = db.table('Pi_1') 
   data1 = table.search(Query()['date'] == thedate)
   if data_list.size() > 0:
       data_list.delete(1,data_list.size() -1 )
   for items in data1:
       data_list.insert(data_list.size()+ 1,
                        items['date'] + " " + items['time'] +
                        " -  Moisture : " + str(items['moisture']) +
                        " -  Light : " + str(items['light']) )

root = tk.Tk()
root.title("DBtiny - Daily Data")

var = tk.DoubleVar()
data_list = tk.Listbox(root,height=20, width= 60)
day_scale = tk.Scale( root,variable = var, from_=1,to=30,orient='horizontal',length=300 ).grid(row=2,column=1)

button1 = tk.Button(root, text="Get Daily Values", command=sel).grid(row=3,column=1)



TinyDB is a good solution for small IoT projects where I’m using Python and I’m not collecting a lot of data.

My next step would be to look at presenting the JSON data in Node-Red.


Android Wearable

You can create some simple wearable projects with just your Android phone, an arm band and a set of headphones with a microphone. A wearable solution like this could be used by anyone that has their hands busy, for example recipes for chefs, instruction manuals for mechanics or  directions for a cyclist.

In this article we will look at creating an Android application that does speech recognition of a keyword. The keyword will be searched in a simple CSV file and text-to-speech will be used for the result.  We will create the Android app using MIT’s AppInventor package. AppInventor is a free Web based Android app development tool, that allows you to create applications in a graphical environment.


To get started with AppInventor, you will need a Google user account and a PC, Mac or Linux computer. AppInventor is a Web based Android app creation tool  (http://appinventor.mit.edu), that uses a graphical programming environment.

AppInventor has two main screens. The Designer screen is used for the layout of the Android app and the Blocks screen is used to build the logic.


On the Designer screen, an app is created by dragging a component from the Palette window onto the Viewer window.

For the visuals on this application we will a Button, a Label and a ListView from the User Interface Palette window. The button will be used to initiate the speech recognition. The label will show the result from the speech recognition, and the listview component will show the CSV file data.

Also some non-visual components will as be used. In the Media section of the Palette window, select the SpeechRecognizer and TextToSpeech components and drag them into the Viewer window. As well select add the File component from the Storage heading .


The Components window is used to rename or delete components.  When a component is selected the Properties window is used to change its editable features. In this example we renamed the button to BT_Speak, and we changed the backgroundColor, FontSize, Width and Text.


The Logic

Once the layout design is complete, logic can be added by clicking on the Designer  button (on the top menu bar).

Logic is built by selecting an object in the Blocks window, and then click on the specific block that you would like to use.


The entire program only requires 1 variable and 4 when blocks.


The first step is to load the text file when Screen1.Initialize block is called. The when File1.GotText block loads the text file data into the global variable, (THELIST), and it populates the ListView component.

The when BT_Speak.Click block is activated on a button push and it starts the speech recognition block.

The final block, when SpeechRecognizer1.AfterGettingText, shows the result of the speech in a label and it checks if the result is in the global variable. If the result is found a text-to-speech message is generated with the full line of text.

The Data File

For our test file we placed the key words at the starting of each line.

"Hope Bay has a sandy beach with good hiking and..."
"The Glen is a horseshoe shaped valley with ..."
"Isaac Lake is a bird sanctuary with ..." 
"Oliphant is a great for kite surfing..."

Our data file used some local landmarks, but there are lots of other choices like: friends addresses, recipes ingredients or favorite restaurants.

The file was saved as places.txt in the phones download directory, this should match up with the File1.ReadFrom block definition (/Download/places.txt).

Compiling and Running the App

After the screen layout and logic is complete, the menu item Build will compile the app. The app can be made available as an APK downloadable file or as a QR code link.


Once the app is install in the phone, pushing the “Talk” button will open the Google speech recognition dialog.  If you’ve spoken a valid keyword then you should hear the line from the data file. The data file can be updated without any changes to the app.


Final Thoughts

This example used a simple text file, but it could be enhanced to support multi-field CSV files, Cloud Services, HTTP requests or Google Maps.