This tutorial will guide you through the steps needed to set up an SQL database on your Pi. We will be using MySQL, as it is the most common implementation of the SQL standard.

Pre-requirements

In the terminal on your pi, start by installing MySQL server and the Python bindings needed to interact with it:

sudo apt-get install mysql-server python-mysqldb

Note: During the installation of MySQL server, you will be asked to provide a password for the MySQL root account, this is important to remember.

Database creation

From the terminal, you need to enter the MySQL shell to be able to interact with the database:

mysql -u root -p

You should be prompted for your password, after which you will be in the MySQL shell, from here you can manipulate your databases.

This example will create a database for storing temperature readings, for instance from a temperature sensor on the pi.

Step 1 – Create the database

Every command in the following section is run from the MySQL shell.

CREATE DATABASE temps
USE temps

This creates your database and sets it as the active database in this shell session.

Step 2 – Create admin user

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

This is pretty self-explanatory: You create a user called administrator, with password ‘password’. Then you grant this user all privileges to all tables (temps.*) in the database you created in the previous steps, and flushes the new privileges, so they are stored in the database. The last line quits the MySQL shell, which means you should be back in your regular terminal session.

Now we can re-enter the shell as the new user:

mysql -u administrator -p

Step 3 – Create table

The final part of creating our database is to set up a table in it:

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

This should also be fairly easy to understand if you have touched on SQL before, you create a table called ‘tempdat’ with the columns ‘tdate’, ‘ttime’, ‘zone’ and ‘temperature’.

Connecting to your database through Python

Your SQL database is now up and running, and it’s time to connect to it.

Step 1 – Connect to your database

Create a python file and enter the following contents into it:

#!/usr/bin/env python

import MySQLdb


db = MySQLdb.connect("localhost", "administrator", "password", "temps")
curs=db.cursor()

Step 2 – Fill it with content

Our database is empty, and an empty database is pretty boring, so we will create a python script to populate it:

# 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()

The script will try to execute a bunch of INSERT statements and commit them, and it will roll back the changes if it encounters an error. The following code does exactly the same, but automatically handles commiting and rollbacks:

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)""")

If you want custom logic or logging on exceptions, choose the first example, if not, choose the second.

Step 3 – Reading the content

This can be done with the following python code:

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])

This will fetch all the data in your tempdat table and print it in a pretty-ish format.

If you want to attach conditions to your query, the following example will select all entries in the table where temperature is higher than 20 degrees:

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

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

db.close()