Sensor log storage and queries · Learning
English translation
Execution conditions
Uses Python's sqlite3; no DB server or account is required.
Topics covered
Use the device log structure to check whether storing the same key again creates a new record or changes the existing record.
Overall workflow
Device and measurement time → key and duplicate handling → SQLite storage → interval queries
Keys that distinguish records
Using (device,t) as the sensor value key distinguishes a repeated reception at the same time from a new measurement. First decide whether to ignore or update duplicates, and enforce the rule with DB constraints.
Define queries before choosing storage
SQL WHERE specifies the target and interval, and ORDER BY specifies the return order. Passing values as parameters keeps inputs separate from SQL syntax.
The key, query, and response rules verified with SQLite can be retained when moving to another storage system.
Worked example
The measurements for device m1 at s are 20,21,22,23. Update entries with the same device and time to the latest value.
import sqlite3
connection=sqlite3.connect(':memory:')
connection.execute('CREATE TABLE readings(device TEXT,t REAL,value REAL,PRIMARY KEY(device,t))')
records=[('m1',0,20.),('m1',1,21.),('m1',1,22.),('m1',2,23.)]
connection.executemany('INSERT INTO readings VALUES(?,?,?) ON CONFLICT(device,t) DO UPDATE SET value=excluded.value',records)
connection.commit()
print(connection.execute('SELECT * FROM readings ORDER BY t').fetchall())
import matplotlib.pyplot as plt
points=connection.execute("SELECT t,value FROM readings ORDER BY t").fetchall()
plt.plot(*zip(*points),marker="o");plt.xlabel("Time [s]");plt.ylabel("Sensor value");plt.grid()
[('m1', 0.0, 20.0), ('m1', 1.0, 22.0), ('m1', 2.0, 23.0)]
Official documentation and data
Reference example
View more examples