Store and visualize sensor data in SQLite

Store sensor data with SQLite

Very often as part of an Orange Pi or Raspberry Pi project involving sensors, it is useful to store the sensor data in some kind of way or another. A good way to store the data is in a database that you can manage and query. SQLite is a popular embedded database management system. SQLite comes in the form of a C library. Due to it’s low footprint on resources, it fits well with a device such as a Raspberry Pi or the Orange Pi.

In the first part of the post I will show you how to read and store the data in a SQLite database and in the second part we will look at plotting the data in a chart using Python and JavaScript. In this example, I will use the output of a DHT22 temperature an humidity sensor, but any sensor will do. I showed how to connect the temperature and humidity sensor and the C code for reading measurements in a previous post.

First, we need to install SQLlite on our Orange Pi, if you don’t already have it:

sudo apt-get install sqlite3

Then we need to create a database:

sqlite3 dht22.db

Finally, in this database we need to create a table to store the data sent from the sensor:

Now, we need to make the Orange Pi read the sensor and store the readings in the table we’ve just created. Most of the code below is recycled from one of my previous posts. Have a look there for the basic DHT22 set-up. Regarding sending data from C to the database, there is a nice introduction on how to work with SQLlite on the official SQLite website. So this is the code with the new database writing functionality:

Then let it run for a while so you get some readings. If all went well, we should have some new records in our database. To check that, start SQLlite and do a simple select from our table:

SELECT * from temps;

The output should look like this:

Next, let’s have a look at how to visualize the data with Python and D3.js.

Visualize sensor data with D3.js

The way I am going to do this is by using Flask, a Python web micro framework, to read data from the SQLite database, store it in a pandas DataFrame. Next we will pass this data to a HTML template that Flask will use to render the chart dynamically, updating as new data comes in. For a intro on Flask, please see my previous post. As before, I will use the SQLAlchemy module to read data in Python from the SQLite database:

Comments

Thanks for one marvelous posting! I enjoyed reading it; you are a great author. I will make sure to bookmark your blog and may come back someday. I want to encourage that you continue your great postsHadoop Training in Bangalore