Using SQLite in Raspberry Pi

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.

raspi_sqlite3

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:

sqlite3 my.db

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:

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 then you can start running more complex queries and find out its capabilities. Next time we will try to access 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

Your email address will not be published. Required fields are marked *