6 Lines of Python to Plot SQLite Data

There are some great tutorials on SQL, SQLite and Matplotlib, however I wanted something super simple that I could use to teach my daughters with.

This blog documents my notes on what I used to teach them SQL, SQLite3 and then how to plot the results in Python. Yes it can only takes 6 lines of Python code to read an SQLite3 file and plot it, if you are doing 2 line charts with a title and legends its 9 lines.

Getting Started with SQL

There are some great SQL databases and SQL tools that can are quite user friendly.

For us I started with SQLite3 which is file based, no server component is required and it runs on all major OS’s and Raspberry Pi’s. There are a number of SQL Query builder tools but we found the DB Browser for SQLite gave us all the functionality we needed.

Check the install documentation for your hardware/OS, to install SQLite3 and DB Browser on Debian/Ubuntu/Raspberry Pi’s :

sudo apt install sqlite3
sudo apt-get install sqlitebrowse

SQLite Databases

For testing there are a lot of excellent databases that can downloaded from Kaggle. These data files are in CSV format and they can be imported into a SQLite database file using the DB Browser.

For our first test we used the Kaggle SARS 2003 data set.

A new database file (sars.db) was created and then the CSV was imported.

Create VIEWS to Simplify

SQL Views can be created to simply the field names, reduce the number of fields or add custom fields.

Use the “Execute SQL” tab to create a view. An example to create a view with simplified field names would be:

Views and SELECT queries can be generated to add custom fields. An example to extract the month from the date field and add 2 other fields (month number and month name):

Test SQL Queries

The DB Browser tool is good for testing out SQL queries and plot the data before moving to Python.

A query to find the worst 5 countries affected by SARS would:

select sum(deaths) sum_deaths, country from v_sars group
  by Country having sum_deaths > 0 order by sum_deaths desc limit 5

Plotting in Python

There are number of ways to plot SQL data in Python. I found that the easiest way was to use Pandas dataframes.

To load the necessary Python libraries:

pip install sqlite3
pip install pandas
pip install matplotlib

The Python code to connect to SQLite3, run the SQL query (of sum of deaths vs. country) and plot the data is:

# sql_bar0.py - Sqlite to Bar Charts
import sqlite3, pandas , matplotlib.pyplot as plt

conn = sqlite3.connect("/media/pete/RED_4GB/sql/sars.db")

sql = """select sum(deaths) sum_deaths, country from v_sars group
  by Country having sum_deaths > 0 order by sum_deaths desc limit 5"""

data = pandas.read_sql(sql, conn)
#x values: data.Country,  y values: data.sum_deaths
plt.bar(data.Country, data.sum_deaths)
plt.title("SARS Death in 2003")

An example with 2 lines to shows the monthly deaths and cases would be:

# sql_line2.py - Sqlite to 2 Line Charts
import sqlite3, pandas , matplotlib.pyplot as plt

conn = sqlite3.connect("/media/pete/RED_4GB/sql/sars.db")

sql = """select s_month, sum(deaths) as sum_deaths, sum(cases) as sum_cases from v_month group by n_month"""

data = pandas.read_sql(sql, conn)

plt.plot(data.s_month,data.sum_deaths, label = "Deaths")
plt.plot(data.s_month,data.sum_cases, label = "Cases")
plt.title("SARS Death in 2003")


By keeping the Python code simple we were able to focus on SQL queries.

Using the basic Python code the SQL connection we later changed from SQLite3 to MySQL or Progresql.

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.

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.


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.


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


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.


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.


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.

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:


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


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.