|
2 years ago | |
---|---|---|
.. | ||
README.md | 2 years ago | |
script.py | 2 years ago |
sqlite3 data-table.db
python script.py
And you should see something like
Table created
Values inserted
Query: give all reading for district E2
(34, u'2017-02-25 10:33:26', 80, u'E2')
(45, u'2017-03-12 10:33:26', 76, u'E2')
(34, u'2017-03-11 10:33:26', 70, u'E2')
Query: All data since last week for district E2
(45, u'2017-03-12 10:33:26', 76, u'E2')
(34, u'2017-03-11 10:33:26', 70, u'E2')
Query: Average data since last week for district E2
73.0
CREATE TABLE data (deviceId integer, timestamp integer, sensorReading integer, district text);
INSERT INTO data VALUES (12, datetime('now', '-200 days'), 13, 'E9'),
(12, datetime('now', '-5 days'), 12, 'E9'),
(8, datetime('now', '-6 days'), 14, 'E9'),
(34, datetime('now', '-150 days'), 80, 'E2'),
(45, datetime('now', '-5 days'), 76, 'E2'),
(34, datetime('now', '-6 days'), 70, 'E2');
SELECT * FROM data WHERE district IS 'E9';
SELECT * FROM data WHERE timestamp >= datetime('now', '-7 days') AND district IS 'E2';
SELECT avg(sensorReading) FROM data WHERE timestamp >= datetime('now', '-7 days') AND district IS 'E2';
DROP TABLE data;