There is a lot of interest in the amazing Raspberry Pi 3 computer here at Cranfield University. Sometimes, an application we build – for example on the Raspberry Pi – needs to store data – for example we might wish to store data from sensors. For this we need a database. However, again sometimes the overhead of having a full-blown database server up and running is too much. We need a simple, localised way to store and retrieve data. Fortunately there is a great solution to this, ‘SQLite’ (https://www.sqlite.org).
This brief tutorial shows how to setup and configure SQLite on a Raspberry Pi, and gives and example if it in use.
To start with, as with every time we use the Pi, we should update and upgrade the distribution – to ensure all the software within the Debian distribution are up-to-date. As described on the Raspberry Pi pages (https://www.raspberrypi.org/documentation/raspbian/updating.md), this is pretty straight forward.
First, update your system’s package list by entering the following command:
sudo apt-get update
Next, upgrade all your installed packages to their latest versions with the command:
sudo apt-get dist-upgrade
Once this is done, we are ready to install SQLite:
sudo apt-get install sqlite3
We will use the database to store data from within programming code – for example a software application that stores off sensor data. However, in the first instance, we can also run SQLite interactively to ‘create’, ‘read’, ‘update’ and ‘delete’ data directly in SQL tables. SQLite comes with a command line interpreter (CLI), that provides a command prompt to enter in commands. If we create a new database, we can try this out:
sqlite3 sensor_db
This starts up SQLite and will, at the same time, also create a new database – a single file, in this case called ‘sensor_db’ in your current folder.
Now we can create a database structure in our new database:
CREATE TABLE IF NOT EXISTS readings (
'temperature' REAL NOT NULL,
'humidity' REAL NOT NULL,
'datetime_int' INTEGER NOT NULL,
'sensor_id' INTEGER NOT NULL);
Note the semi-colon ending the statement. Now we can check that was created OK:
PRAGMA table_info([readings]);
Let’s place a couple of dummy data items (rows) into the table:
INSERT INTO readings (temperature, humidity, datetime_int, sensor_id) values (18.5,45.3,strftime('%s','now'),1);
INSERT INTO readings (temperature, humidity, datetime_int, sensor_id) values (19.4,42.8,strftime('%s','now'),1);
Now select the data to make sure it was stored correctly. Note we are using one of the two means of storing dates, here using integers not strings (see http://www.sqlitetutorial.net/sqlite-date/).
SELECT temperature, humidity, datetime(datetime_int, 'unixepoch'), sensor_id FROM readings;
The data should be shown…
To quit the interactive mode:
.exit
SQLite is a powerful database solution for small applications. Programming it is just like its larger server-based equivalents. It is worth spending time reading the SQLite tutorials for further information (http://www.sqlitetutorial.net). Another good tutorial is https://www.tutorialspoint.com/sqlite/index.htm.