Set up an SQLite database on a Raspberry Pi

Set up an SQLite database on a Raspberry Pi

Whether you're writing code for a data logging application, a multi media archive, or a website, databases are a great way of storing information in a way that can be accessed quickly and easily.

Database transactions

Operations on a database are preformed atomically. This means a group of changes to a database can be written in one operation. If there is an error during the operation, the changes will be undone. If there are no errors during the operation, the changes are permanently commited.

Databases keep a journal of transactions. If there's a power cut or the computer crashes during an operation, and the computer must be restarted, the journal can be checked to see if there were any operations in progress when the failure occurred. If the journal lists any changes that weren't committed, they can be rolled back so that the database is returned to its original state. This ensures that the database won't be corrupted in the event of a failure.

The sequence of events in a transaction is:

• begin the transaction,
• perform some SQL operation,
• if no errors occurred, commit the changes to the database and end the transaction,
• if there were any errors, rollback the changes and end the transaction.

See also: http://en.wikipedia.org/wiki/Database_transaction

SQLite

I'm going to use SQLite in this tutorial because it's compact, reliable and easy to use. Unlike MySQL, no server process is needed to access an SQLite database. SQLite is supported by Python 2.7, so it's easy to use on a Raspberry Pi. You can install SQLite using this command:

$ sudo apt-get install sqlite3

The SQLite libraries are supplied with an SQLite shell. I used this command to invoke the shell and create a database:

$ sqlite3 mydatabase.db

The file mydatabase.db doesn't exist yet. It will be created when I commit changes to the database. After typing the command above, a prompt appears where I can enter commands. The shell supports two types of commands. Commands that start with a '.' are used to control the shell. Try typing these commands:

sqlite> .help sqlite> .show

You can quit from the SQLite shell using the '.quit' command. Note that you can use the up arrow to scroll through previous commands. The other type of command that the shell supports is SQL commands.

Using SQL to access databases

Structured Query Language (SQL) is a language that's used for interacting with databases. It can be used to create tables, insert data and search for data. SQL works with different database solutions such as SQLite, MySQL, Oracle and others. SQL statements must have a ';' on the end.

It's common for SQL commands to be capitalized, but this isn't striclty necessary. Some people argue that using capitals increases readability because it's easier to distinguish between SQL commands and everything else.

I'm going to create a simple database that could be used for logging temperatures in different parts of a home. The first thing I need to do is create a table:

BEGIN; CREATE TABLE temps (tdate DATE, ttime TIME, zone TEXT, temperature NUMERIC); COMMIT;

This creates a table with columns labelled tdate, ttime, zone and temperature. Their respective types are DATE, TIME, TEXT and NUMERIC. Don't forget the semi-colons at the end of each line. Next we need to add some data:

BEGIN; INSERT INTO temps values(date('now', '-1 day'), time('now'), "kitchen", 20.6); INSERT INTO temps values(date('now', '-1 day'), time('now'), "greenhouse", 26.3); INSERT INTO temps values(date('now', '-1 day'), time('now'), "garage", 18.6); INSERT INTO temps values(date('now'), time('now', '-12 hours'), "kitchen", 19.5); INSERT INTO temps values(date('now'), time('now', '-12 hours'), "greenhouse", 15.1); INSERT INTO temps values(date('now'), time('now', '-12 hours'), "garage", 18.1); INSERT INTO temps values(date('now'), time('now'), "kitchen", 21.2); INSERT INTO temps values(date('now'), time('now'), "greenhouse", 27.1); INSERT INTO temps values(date('now'), time('now'), "garage", 19.1); COMMIT;

These commands insert data into the table. The first three INSERT commands insert data with yesterday's date and the current time. The next three lines use today's date, but the time is set to 12 hours ago. The next three lines uses the current data and time. Note that all times are in the UTC/GMT timezone by default. The COMMIT command completes the transaction.

Now we can query the database using the SELECT command:

sqlite> SELECT * FROM temps WHERE zone="garage"; 2013-09-04|01:41:08|garage|18.6 2013-09-05|13:41:52|garage|18.1 2013-09-05|01:42:38|garage|19.1

The '*' means complete records should be returned. The name of the table is used after the word FROM. We can add conditions using the word WHERE. This means that the query will only return records where a condition is true. In this case the condition is the zone must be "garage".

Instead of using an asterisk, this example selects only the temperature field from each record:

sqlite> SELECT temperature FROM temps WHERE zone="garage"; 18.6 18.1 19.1

We can search for records from one day ago:

sqlite> SELECT * FROM temps WHERE tdate=date('now','-1 day'); 2013-09-04|01:40:36|kitchen|20.6 2013-09-04|01:40:51|greenhouse|26.3 2013-09-04|01:41:08|garage|18.6

Queries can use more than one condition. In this example, we search for records where the zone is the kitchen and the date is one day ago:

sqlite> SELECT * FROM temps WHERE zone="kitchen" AND tdate=date('now','-1 day'); 2013-09-04|01:40:36|kitchen|20.6

If we begin a transaction and query the data without a commit operation, the data will be returned. If the ROLLBACK command is used and we run the same query again, the data isn't returned:

BEGIN; INSERT INTO temps values(date('now', '-2 day'), time('now'), "kitchen", 20.6); SELECT * FROM temps WHERE zone="kitchen" AND tdate=date('now','-2 day'); 2013-09-03|01:45:54|kitchen|20.6 ROLLBACK; SELECT * FROM temps WHERE zone="kitchen" AND tdate=date('now','-2 day');

No result is returned from the last query.

In my next article, I'll look at using SQLite with Python.