Using SQLite in Raspberry Pi

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.

raspi_sqlite3

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:

sqlite3 my.db

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:

1|22.1|2013-10-08

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.

3 Comments:

  1. After this you are all set. We can start using it by creating a new database:
    sqlite3 my.db

    This gives me:
    >>> sqlite3 my.db
    SyntaxError: invalid syntax

    What am I missing?

  2. did your setup went smooth:
    sudo apt-get install sqlite3

    Then it should look like:
    pi@raspberrypi:~$ sqlite3 test.db
    SQLite version 3.7.13 2012-06-11 02:05:22
    Enter “.help” for instructions
    Enter SQL statements terminated with a “;”
    sqlite>

  3. I’m very new at using the Pi, and didn’t realise that the command had to be entered at a system prompt, not from within IDLE3.

    Works now,

    Thaks

Leave a Reply