Quantcast
submit to reddit       
       

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.


Comments

Accessing an SQLite database with Python

In the last tutorial, I set up an SQLite database, and performed a few basic operations on it using the SQLite shell. This time I'm going to use Python to manage my database.

Using Python to query a database

The following script connects to the database that I made in the last tutorial, and does a couple of queries:

#!/usr/bin/env python import sqlite3 conn=sqlite3.connect('mydatabase.db') curs=conn.cursor() print "\nEntire database contents:\n" for row in curs.execute("SELECT * FROM temps"): print row print "\nDatabase entries for the garage:\n" for row in curs.execute("SELECT * FROM temps WHERE zone='garage'"): print row conn.close()

Save this as database.py and make it executable with this command:

chmod +x database.py

The call to sqlite.connect() opens the database file, and creates a cursor. The cursor object is then used to execute SQL commands. In the example above, I have used the execute command to run a couple of queries, first to get all records in the table temps and then to get all records where the zone is 'garage'. In each case, the execute function returns a list of records, so we can use a for loop to iterate through each record. Finally, the database connection is closed.

The output from this code is as follows:

Entire database contents: (u'2013-09-04', u'01:40:36', u'kitchen', 20.6) (u'2013-09-04', u'01:40:51', u'greenhouse', 26.3) (u'2013-09-04', u'01:41:08', u'garage', 18.6) (u'2013-09-05', u'13:41:27', u'kitchen', 19.5) (u'2013-09-05', u'13:41:38', u'greenhouse', 15.1) (u'2013-09-05', u'13:41:52', u'garage', 18.1) (u'2013-09-05', u'01:42:09', u'kitchen', 21.2) (u'2013-09-05', u'01:42:26', u'greenhouse', 27.1) (u'2013-09-05', u'01:42:38', u'garage', 19.1) (u'2013-09-05', u'07:22:05', u'garage', 18.3) (u'2013-09-05', u'07:22:44', u'garage', 18.3) Database entries for the garage: (u'2013-09-04', u'01:41:08', u'garage', 18.6) (u'2013-09-05', u'13:41:52', u'garage', 18.1) (u'2013-09-05', u'01:42:38', u'garage', 19.1) (u'2013-09-05', u'07:22:05', u'garage', 18.3) (u'2013-09-05', u'07:22:44', u'garage', 18.3)

Each record is printed as a tuple of values, and text is in unicode. Each item in the tuple can be accessed using tuple notation (for example, to access the date from a row in the code above, you would use row[0]. You can convert from unicode using the Python str() function.

The example above simple returns database records in the order in they were found. Sometimes you might want to display the data in a particular order. This example selects all entries sorted in ascending order of temperature:

print "\nDatabase entries in order of temperature\n" for row in curs.execute("SELECT * FROM temps ORDER BY temperature ASC"): print row

This code's output is as follows:

Database entries in order of temperature (u'2013-09-05', u'13:41:38', u'greenhouse', 15.1) (u'2013-09-05', u'13:41:52', u'garage', 18.1) (u'2013-09-05', u'07:22:05', u'garage', 18.3) (u'2013-09-05', u'07:22:44', u'garage', 18.3) (u'2013-09-04', u'01:41:08', u'garage', 18.6) (u'2013-09-05', u'01:42:38', u'garage', 19.1) (u'2013-09-05', u'13:41:27', u'kitchen', 19.5) (u'2013-09-04', u'01:40:36', u'kitchen', 20.6) (u'2013-09-05', u'01:42:09', u'kitchen', 21.2) (u'2013-09-04', u'01:40:51', u'greenhouse', 26.3) (u'2013-09-05', u'01:42:26', u'greenhouse', 27.1)

To sort in descending order, replace ASC with DESC. ASC is used by default if neither is specified.

Subsituting SQL parameters

Sometimes it's useful to use the same query with different parameters. For example, we might want to select records where the temperature is above a certain value.

print "\nDatabase entries where the temperature is above x:\n" for row in curs.execute("SELECT * FROM temps WHERE temperature>19.0"): print row

Suppose we plan on using this query repeatedly, but we might want to check for different temperature thresholds. An alternative is to pass a variable to the execute function and let sqlite subsititute that variable in the query:

print "\nDatabase entries where the temperature is above x:\n" for row in curs.execute("SELECT * FROM temps WHERE temperature>(?)",(x,)): print row

Now the query will return any record where the temperature is greater than x. Note that x has to be passed to the execute command as a tuple, not a single variable. In this case we're using a tuple with a single entry, and x is equal to 19.0. Here's the output from this code:

Database entries where the temperature is above x: (u'2013-09-04', u'01:40:36', u'kitchen', 20.6) (u'2013-09-04', u'01:40:51', u'greenhouse', 26.3) (u'2013-09-05', u'13:41:27', u'kitchen', 19.5) (u'2013-09-05', u'01:42:09', u'kitchen', 21.2) (u'2013-09-05', u'01:42:26', u'greenhouse', 27.1) (u'2013-09-05', u'01:42:38', u'garage', 19.1)

Use python to insert data into a database

Using substitution is useful when writing code to insert a record into a database. In this function, the zone and temperature are passed to the query as variables, and the 'date' and 'time' SQL function is used to generate the current date and time:

def add_temp_reading (zonestr, temp): # I used triple quotes so that I could break this string into # two lines for formatting purposes curs.execute("""INSERT INTO temperatures values(date('now'), time('now'), (?), (?))""", (zonestr,temp)) # commit the changes conn.commit()

Selecting records by date

In many applications it's useful to select records by date. We can use less than/greater than operators to search for records before or after a specific date. The next examples select records from two days ago, and one month ago respecitvely:

print "\nDatabase entries for the garage:\n" for row in curs.execute("SELECT * FROM temperatures WHERE tdate>=date('now','-2 day')"): print row print "\nDatabase entries for the garage:\n" for row in curs.execute("SELECT * FROM temperatures WHERE tdate>=date('now','-1 month')"): print row
See also: http://docs.python.org/2/library/sqlite3.html
Comments

Using MySQL on a Raspberry Pi

SQLite is a great database for many situations, but there are times when it's not quite up to the job. SQLite can be used in web sites, but it's much more common to use MySQL. This is because

  • MySQL is more scalable,
  • MySQL can be tuned more easily,
  • it supports user management and permissions,
  • MySQL is better for sites with heavy traffic,
  • it can be used in client server architectures where a database client must access a database remotely.

Setting up MySQL on a Raspberry Pi

Before we get started, we need to install MySQL server and the Python bindings for MySQL:

sudo apt-get install mysql-server python-mysqldb

During the installation of MySQL server, you will be prompted to enter a password for the MySQL root account.

I'm going to create a database to log temperatures as I did with the SQLite posts. As before, I'm going to create a database named temps to store records with fields for a date, time, zone and temperature.

Like SQLite, MySQL comes with a shell that can be used for configuration. We can use the the MySQL shell to create a database:

$ mysql -u root -p Enter password: mysql> CREATE DATABASE temps mysql> USE temps

The 'USE temps' command tells the shell to use that database in future operations in this shell session. MySQL supports users accounts, so we need to create a database user and give it access to the database:

mysql> CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'password'; mysql> GRANT ALL PRIVILEGES ON temps.* TO 'monitor'@'localhost' mysql> FLUSH PRIVILEGES; mysql> quit

This creates a user called monitor (because the database is going to be accessed by a program that monitors temperatures), and assigns it a pass word 'password'. This user is allowed to connect to the database from 'localhost'.

Initially, the new user has no privileges, so it must be granted some access rights using the 'GRANT' command. I have used 'ALL' in this example, but in real world applications it would be better to grant users more limited rights. A complete list of privilege options is available at http://dev.mysql.com/doc/refman/5.1/en/grant.html.

The last command quits the shell so that we can re-enter the shell as the user that we just created:

mysql -u monitor -p

Now I'm going to create a table with the fields needed for storing data:

CREATE TABLE tempdat (tdate DATE, ttime TIME, zone TEXT, temperature NUMERIC);

Accessing a MySQL database with Python

I'm going to use Python to populate the database. As in the previous examples, I'm going to create sample data from one day ago, 12 hours ago and now, and I'm going to record temperatures in three different zones.

This Python code is the start of my script:

#!/usr/bin/env python import MySQLdb db = MySQLdb.connect("localhost", "monitor", "password", "temps") curs=db.cursor()

I imported the MySQLdb python module and connected to it with the user name and password that I set up in the shell. The following code then inserts records into the database:

# note that I'm using triplle quotes for formatting purposes # you can use one set of double quotes if you put the whole string on one line try: curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE() - INTERVAL 1 DAY, NOW(), 'kitchen', 21.7)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE() - INTERVAL 1 DAY, NOW(), 'greenhouse', 24.5)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE() - INTERVAL 1 DAY, NOW(), 'garage', 18.1)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW() - INTERVAL 12 HOUR, 'kitchen', 20.6)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW() - INTERVAL 12 HOUR, 'greenhouse', 17.1)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW() - INTERVAL 12 HOUR, 'garage', 16.2)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW(), 'kitchen', 22.9)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW(), 'greenhouse', 25.7)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW(), 'garage', 18.2)""") db.commit() print "Data committed" except: print "Error: the database is being rolled back" db.rollback()

If there's an error during any of these SQL commands, or if there's an error while committing the changes, the changes will be rolled back. This can be simplified using a Python context manager:

with db: curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE() - INTERVAL 1 DAY, NOW(), 'kitchen', 21.7)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE() - INTERVAL 1 DAY, NOW(), 'greenhouse', 24.5)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE() - INTERVAL 1 DAY, NOW(), 'garage', 18.1)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW() - INTERVAL 12 HOUR, 'kitchen', 20.6)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW() - INTERVAL 12 HOUR, 'greenhouse', 17.1)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW() - INTERVAL 12 HOUR, 'garage', 16.2)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW(), 'kitchen', 22.9)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW(), 'greenhouse', 25.7)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW(), 'garage', 18.2)""")

This will automatically handle commit and rollback operations.

Getting data from the database

Once data has been inserted into the database, we need to be able to retrieve it. We can do this using the SQL select command. The execute function runs the SQL query, and the fetchall function returns a list of records that matched the query:

curs.execute ("SELECT * FROM tempdat") print "\nDate Time Zone Temperature" print "===========================================================" for reading in curs.fetchall(): print str(reading[0])+" "+str(reading[1])+" "+\ reading[2]+" "+str(reading[3])

The for loop iterates through the list of results. Each record is a list of values. Note that the time and the temperature values have to be converted to a string before they can be appended to the result string. This code prints the contents of the entire database as a table:

Date Time Zone Temperature =========================================================== 2013-09-09 14:41:46 kitchen 21.7 2013-09-09 14:41:46 greenhouse 24.5 2013-09-09 14:41:46 garage 18.1 2013-09-10 2:41:46 kitchen 20.6 2013-09-10 2:41:46 greenhouse 17.1 2013-09-10 2:41:46 garage 16.2 2013-09-10 14:41:46 kitchen 22.9 2013-09-10 14:41:46 greenhouse 25.7 2013-09-10 14:41:46 garage 18.2

We can use the WHERE keyword to attach conditions to a query. In this example, I'm going to search for records where the temperature is above 20.0 degrees:

curs.execute ("SELECT * FROM tempdat WHERE temp>%s", (str(20.0),))

Note that it's important not to use string substitution to insert parameters into the query as this makes it easier for people to inject malicious SQL code into a query. This query does the same thing, but it's less secure:

curs.execute ("SELECT * FROM tempdat WHERE temp>%s" % str(20.0))

In this example, the variable is unconditionally inserted into the query. In the previous one, the query and the parameter are passed to the MySQL library, which checks to see if the parameter is safe before inserting it.

The output from this query is

Date Time Zone Temperature =========================================================== 2013-09-09 14:41:46 kitchen 21.7 2013-09-09 14:41:46 greenhouse 24.5 2013-09-10 2:41:46 kitchen 20.6 2013-09-10 14:41:46 kitchen 22.9 2013-09-10 14:41:46 greenhouse 25.7

At the end of the script, we close the connection to the database:

db.close()

See also: http://dev.mysql.com/doc/refman/5.5/en/sql-syntax.html.


Comments



Follow me