If you are doing some data logging, sensor reading or other routine task with raspberry Pi, then you probably are thinking of using database. The list of database software choices is quite long, but in most cases you will end with single or few tables in database. First thought might be MySQL – well known database server in WWW. Anyway this is pretty heavy tool to have running on Raspberry Pi.
In my opinion SQLite is probably most suitable choice. Because it is serverless, lightweight, opensource and support most SQL code. Another handy thing is that SQLite stores data in single file which can be stored anywhere.
First of all lets install SQLite to Raspberry Pi using command:
sudo apt-get install sqlite3
After this you are all set. We can start using it by creating a new database:
This creates a new database (same name file in current directory) and prompts for further SQL statements like create tale or other.
SQLite version 3.7.13 2012-06-11 02:05:22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>
Let’s create a simple table temperature:
sqlite> create table temperature (id integer, temp float, date text);
And now we can start adding data to table:
insert into temperature values (1, 22.1, '2013-10-08');
That’s it – we have stored our first record in to database. To check it’s there lets run a select statement:
sqlite> select * from temperature;
Bellow we get results:
Let’s add couple more data points one by one to our table:
insert into temperature values (2, 2.1, '2013-10-09'); insert into temperature values (3, -22.1, '2013-10-10');
And run select statement again:
sqlite> select * from temperature; 1|22.1|2013-10-08 2|2.1|2013-10-09 3|-22.1|2013-10-10
We can see that our data is nicely displayed.
If you ever tried SQL then you can start running more complex queries and find out its capabilities. Next time we will try to access SQLite database using Python.