Sensor log storage and queries

Learning objectives

Use the device log structure to check whether storing the same key again creates a new record or changes the existing record.

Execution conditions

Uses Python's sqlite3; no DB server or account is required.

Store duplicate measurements only once

Starting conditions

The measurements for device m1 at $t\in\{0,1,1,2\}$ s are 20,21,22,23. Update entries with the same device and time to the latest value.

Procedure

1. Check SQLite's composite primary key and store the four provided measurements.
2. Check the number of stored rows and the value of $t=1$, and explain whether the duplicate handling rule is correct.

Results to retain

• Storage results and 1 duplicate handling example.

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

Record results and self-check

Input count:

Stored row count:

Duplicate key:

Final value:

Points to check

• Did you distinguish measurement time from reception time?
• Did you use parameters instead of appending values directly to SQL strings?

Query by time interval and device

Starting conditions

Add $t\in\{0,1,2\}$ with values 30,31,32 for device m2. Query only m1 for 1≤t≤2.

Procedure

1. Write a query function that takes a device, start time, and end time as arguments.
2. Test the specified interval and an interval with no values once each.
3. Plot query results as time versus measurement.

Results to retain

• A query function, two query results, and a time series plot.

connection.executemany('INSERT INTO readings VALUES(?,?,?)',[('m2',0,30.),('m2',1,31.),('m2',2,32.)])
def query(device,start,end):
    return connection.execute('SELECT t,value FROM readings WHERE device=? AND t BETWEEN ? AND ? ORDER BY t',(device,start,end)).fetchall()
print(query('m1',1,2));print(query('m1',10,20))
# TODO: Plot the time series.

Record results and self-check

Device:

Query interval:

Row count:

Value range:

Points to check

• Did you specify the sort order in SQL?
• Did you avoid replacing empty results with errors or a value of 0?

Connect sensors to an API

Starting conditions

Assume the HTTP API in the next unit calls the query function. The request is device=m1,start=0,end=2.

Procedure

1. Define the field structure for passing query results as JSON.
2. Define response rules that distinguish a nonexistent device from start>end.
3. Identify the data and query rules to retain when switching to an external storage system such as DynamoDB.

Results to retain

• Two response examples for valid and invalid requests, and rules to retain when replacing storage.

import json
print(json.dumps({'device':'m1','readings':[{'time_s':t,'value':v} for t,v in query('m1',0,2)]}))
# TODO: Implement input validation and error responses.

Record results and self-check

Request:

Validation results:

Response fields:

Expected row count:

Points to check

• Did you verify storage and query principles without an external service account?
• Did you preserve the meanings of device ID and measurement time in the API?

Official examples and data

• Python · SQLite