No Description

README.md 2.0KB

SQLite Spike

Setting up

  • Open a terminal and start SQLite with the name of the database:
sqlite3 data-table.db
  • You can then in another terminal run the script
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 a table:
CREATE TABLE data (deviceId integer, timestamp integer, sensorReading integer, district text);
  • Add data to table:
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');

Simple query examples with SQL

  • Query: give all reading for district E2
SELECT * FROM data WHERE district IS 'E9';
  • Query: All data since last week in E2
SELECT * FROM data WHERE timestamp >= datetime('now', '-7 days') AND district IS 'E2';
  • Query: Average data since last week in district 'E2'
SELECT avg(sensorReading) FROM data WHERE timestamp >= datetime('now', '-7 days') AND district IS 'E2';

Clean up

  • Dropping the table:
DROP TABLE data;

Resources