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.