Tag: json

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

table.insert({"date":"2019-05-06","time":"16:00","moisture":200})
table.insert({"date":"2019-05-06","time":"16:10","moisture":210})
table.insert({"date":"2019-05-06","time":"16:20","moisture":220})

table2 = db.table('Light')

table2.insert({"date":"2019-06-06","time":"16:00","light":30})
table2.insert({"date":"2019-06-06","time":"16:10","light":35})
table2.insert({"date":"2019-06-06","time":"16:20","light":36})

db.close()

#
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')
db.purge_tables()
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")
db.close(

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()))
   print(thedate)
   table = db.table('Pi_1') 
   data1 = table.search(Query()['date'] == thedate)
   print(data1)
   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)
data_list.grid(row=1,column=1)
                     
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)

root.mainloop()

Summary

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.