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.

todo_code_1

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.

make_table

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

todo_raw

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.

todo_code_2

web_rbr

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:

tpl_make_table

todo_tmp_table

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

tpl_make_list

web_tpl_rbr

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:

tpl_show_todo

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:

tpl_newtask

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:

todo_final

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.

Sqlite and Node-Red

Sqlite is an extremely light weight database that does not run a server component.

In this blog I wanted to document how I used Node-Red to create, insert and view SQL data on a Raspberry Pi. I also wanted to show how to reformat the SQL output so that it could be viewed in a Node-Red Dashboard line chart.

Installation

Node-Red is pre-installed on the Pi Raspian image. I wasn’t able to install the Sqlite node using the Node-Red palette manager. Instead I did a manual install as per the directions at: https://flows.nodered.org/node/node-red-node-sqlite .

cd ~/.node-red 
npm i --unsafe-perm node-red-node-sqlite 
npm rebuild

Create a Database and Table

It is possible to create a database and table structures totally in Node-Red.

I connected a manual inject node to a sqlite node.

sqlite_create_table

In the sqlite node an SQL create table command is used to make a new table. Note: the database file is automatically created.

For my example I used a 2 column table with a timestamp and a value

sqlite_db_config

Insert Data into Sqlite

Data can be inserted into Sqlite a number of different ways. A good approach for a Rasp Pi is to pass some parameters into an SQL statement.

sqlite_insert_flow

The sqlite node can use a “Prepared Statement” with a msg.params item to pass in data. For my example I created two variable $thetime and $thevalue.

sqlite_insert_conf

A function node can be used to format a msg.params item.


// Create a Params variable
// with a time and value component
//
msg.params = { $thetime:Date.now(), $thevalue:msg.payload }
return msg;

Viewing Sqlite Data

A “select” statement is used in an sqlite node to view the data.

A simple SQL statement to get all the data for all the rows in this example would be:

select * from temps;

A debug node can used to view the output.
sqlite_select

Custom Line Chart

Node-Red has a nice dashboard component that is well formatted for web pages on mobile devices.

To add the dashboard components use the Node-Red palette manager and search for: node-red-dashboard.

By default the chart node will create its own data vs. time storage. For many applications this is fine however if you want long term storage or customized historical plots then you will need to pass all the trend data to the chart node.

For some details on passing data into charts see: https://github.com/node-red/node-red-dashboard/blob/master/Charts.md#stored-data

Below is an example flow for creating a custom chart with 3 values with times.custom_chart_data

The JavaScript code needs to create a structure with: series, data and labels definitions


msg.payload = [{
"series": ["A"],
"data": [
[{ "x": 1577229315152, "y": 5 },
{ "x": 1577229487133, "y": 4 },
{ "x": 1577232484872, "y": 6 }
]
],
"labels": ["Data Values"]
}];

return msg;

This will create a simple chart:

custom_chart_image

For reference, below is an example of the data structure for three I/O points with timestamps:


// Data Structure for: Three data points with timestamps

msg.payload = [{
"series": ["A", "B", "C"],
"data": [
[{ "x": 1577229315152, "y": 5 },
{ "x": 1577229487133, "y": 4 },
{ "x": 1577232484872, "y": 2 }
],
[{ "x": 1577229315152, "y": 8 },
{ "x": 1577229487133, "y": 2 },
{ "x": 1577232484872, "y": 11 }
],
[{ "x": 1577229315152, "y": 15 },
{ "x": 1577229487133, "y": 14 },
{ "x": 1577232484872, "y": 12 }
]
],
"labels": ["Data Values"]
}];

Sqlite Data in a Line Chart

To manually update a line chart with some Sqlite data I used the following nodes:

sqlite_2_chartThe SQL select statement will vary based on which time period or aggregate data is required. For the last 8 values I used:

select * from temps LIMIT 8 OFFSET (SELECT COUNT(*) FROM temps)-8;

The challenging part is to format the SQL output to match the required format for the Line Chart. You will need to iterate over each data row (payload object) and format a JSON string.

 //  
 // Create a data variable   
 //  
 var series = ["temp DegC"];  
 var labels = ["Data Values"];  
 var data = "[[";  
   
 for (var i=0; i < msg.payload.length; i++) {  
   data += '{ "x":' + msg.payload[i].thetime + ', "y":' + msg.payload[i].thetemp + '}';  
   if (i < (msg.payload.length - 1)) {  
     data += ","  
   } else {  
     data += "]]"  
   }  
 }  
 var jsondata = JSON.parse(data);  
 msg.payload = [{"series": series, "data": jsondata, "labels": labels}];  
   
   
 return msg;  

 

To view the Node-Red Dashboard enter: http://pi_address:1880/ui

Screen_chart_sqlite

Final Comments

For a small standalone Raspberry Pi project using sqlite as a database is an excellent option. Because a Pi is limited in data storage I would need to include a function to limit the amount of data stored.