SQLite is an embedded relational database management system is contained in a small C programming library and is an integral part of client-based applications. SQLite uses a dynamic SQL syntax and performs multitasking to do reads and writes at the same time. The reads and writes are done directly to ordinary disk files.
An SQLite library is called dynamically and application programs use SQLite functionality through simple function calls, reducing latency in database access. These programs store entire databases as single cross-platform files on host machines. This simple design is implemented by locking the entire database file during a write.
SQLite implements the SQL-92 standard for SQL and uses an unusual system for SQL compatible database management systems. Types are assigned to individual values, adding flexibility to columns when bound to dynamic scripting languages. Full unicode support in SQLIte is optional.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.
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
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:
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: ( Type the following , in the terminal after “sqlite>”)
CREATE TABLE temps (tdate DATE, ttime TIME, zone TEXT, temperature NUMERIC);
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:
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);
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”;
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”;
We can search for records from one day ago:
sqlite> SELECT * FROM temps WHERE tdate=date(‘now’,’-1 day’);
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’);