If you are doing some data logging, sensor reading, or another routine task with Raspberry Pi, you probably think of using the database. The list of database software choices is quite long, but you will end with a single or few tables in the database in most cases. The first thought might be MySQL – a well-known database server in WWW. Anyway, this is a pretty heavy tool to have running on Raspberry Pi.
In my opinion, SQLite is probably the most suitable choice. Because it is serverless, lightweight, opensource, and supports most SQL code. Another handy thing is that SQLite stores data in a 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 the 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, you can start running more complex queries and finding out its capabilities. Next time we will try to access the SQLite database using Python.