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 =['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  (, 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.