A SQL Database To Store The Data

Creating The Database

I used a copy of the Mac app SQLite Manager to create my database.  I chose a simple relational model with four tables.  The first table is to store details of the "run" ie a sequence of sensor readings.  There are then three tables to store the actual readings from the gyro, accelerometer and compass respectively.  Each of the three sensor tables includes a column that indicates the run that the sensor reading was associated with.

Each of the sensor tables contains six integer fields (X0, X1, Y0, Y1, Z0, Z1) to store the values that were read from the sensors.  They also have three float fields (X, Y, Z) to store the numeric values after the readings have been processed and calibrated (more to follow on that later). 

Writing Data to The Database

First a word of caution, close the SQLite Manager app before programmatically writing to a database.  It has a nasty habit of crashing.

I wrote the Python code in Python 2.7.4 using the PyCharm Community Edition (ie free) IDE.

I have included python code below to write to the database.  It is pretty straightforward.  It uses three libraries:

• The json library to parse the data that is stored in the data file.
• The sqlite3 library to make the database connection to the sqlite database.
• The os library allows interaction with the file system to change directory.

The python code then performs the following tasks:

• Create the database connection
• Open the JSON file that was created on the Raspberry Pi containing the sensor data
• Read the JSON file and write it to the database tables
• Read the header information to create a run record, and capture the RUN ID
• Loop through the readings data, processing each in turn
• Read Gyro data and write to DB
• Read Accel data and write to DB
• Read Compass data and write to DB

A few words on the sqlite code:

• You initiate a connection to the database, that returns a connection object
• The connection has a cursor object.
• The cursor object is used to execute SQL commands against the database
• The connection object is used to commit the SQL commands.

This website is very helpful on how to use the sqlite from Python.

import json
import os
import sqlite3


def init_db(db_file):
    conn = sqlite3.connect(db_file)
    return conn


def load_file(filename, filedir):
    os.chdir(filedir)
    f = open(filename, "r")
    d = json.load(f)
    return d


def process_header(header, db):
    c = db.cursor()
    # expects dict of the form {"type": "header", 
                             #  "Month": 12, 
                             #  "Day": 4, 
                             #  "Year": 2016}
    c.execute("INSERT INTO RUN (Year, Month, Day) 
              "values ({yr}, {mth}, {day})".
        format(yr=header["Year"], 
               mth=header["Month"], 
               day=header["Day"]))
    db.commit()
    return c.lastrowid  # return the PK of the last row entered

def process_reading(reading, db, header):
    c = db.cursor()
    accel = reading["accel"]
    c.execute("INSERT INTO ACCEL (RUN_ID, x0, x1, y0, y1, z0, z1) "
              "values ({hdr}, {x0}, {x1}, {y0}, {y1}, {z0}, {z1})".
        format(hdr=header,
               x0=accel["x0"],
               x1=accel["x1"],
               y0=accel["y0"],
               y1=accel["y1"],
               z0=accel["z0"],
               z1=accel["z1"]))
    db.commit()

    gyro = reading["gyro"]
    c.execute("INSERT INTO GYRO (RUN_ID, x0, x1, y0, y1, z0, z1) "
              "values ({hdr}, {x0}, {x1}, {y0}, {y1}, {z0}, {z1})".
        format(hdr=header,
               x0=gyro["x0"],
               x1=gyro["x1"],
               y0=gyro["y0"],
               y1=gyro["y1"],
               z0=gyro["z0"],
               z1=gyro["z1"]))

    db.commit()

    compass = reading["magno"]
    c.execute("INSERT INTO COMPASS (RUN_ID, x0, x1, y0, y1, z0, z1)"
              " values ({hdr}, {x0}, {x1}, {y0}, {y1}, {z0}, {z1})".
        format(hdr=header,
               x0=compass["x0"],
               x1=compass["x1"],
               y0=compass["y0"],
               y1=compass["y1"],
               z0=compass["z0"],
               z1=compass["z1"]))
    db.commit()


def json_to_sql(readings, db):
    header_id = process_header(readings["header"], db)
    for element in readings:
        item = readings[element]
        if item["type"] == "reading":
            process_reading(item, db, header_id)

db_conn = init_db("/sqlite3/sensors.sqlite")
sensor_data = load_file("readings.data", 

             "/scratch/Raspberry.Pi/sensor_data")
json_to_sql(sensor_data[0], db_conn)

The results stored in the SQLITE database are shown below

More to follow later on this as I explore the meaning of the captured data.