spikes/sqlite

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

• Query for last day/ last week/ last month
• SQLite date/time functions docs
• Simple Python / SQLite tutorial