Data Logging and Batch Automation Workflow

So one thing I’ve been interested in for a while is home automation and, in a more general sense, how technology can be used to make day-to-day life more simple. A necessary part of this idea is that data is needed both to understand current processes and to test any optimizations that are put in place. Therein lies the problem.

For more trivial applications such as monitoring energy consumption, controlling temperature, or even just making sure you didn’t leave the lights on there are numerous commercial solutions available. Not only would equipping your home with these products cost a small fortune, they also lack flexibility and the integration that may be needed when your tasks becomes more niche. Now the purpose of this post is not to explore any specific situation but to document a cheap, general purpose, DIY-type solution whose implementation is robust, distributed, and easily integrated with web or local applications (there’s some buzzwords for you).

The hardware of this project is open-ended as what worked for me may not work for you. But, with the abundance of Pis I have sitting around as well as the release of the $5 Raspberry Pi Zero, the decision was easy. Not only is the Pi universe well documented and equipped, they offer a much more sophisticated processing environment out of the box than something like an Arduino. More specifically to this project, Pis also come better equipped to serve data logging purposes with integrated SD card slots and the ability to easily hookup an external display to monitor or debug. My connectivity method of choice was WiFi which not only makes it easy to share data with other devices on the network, but also would allow a node to independently host or push directly to the web. Using a Pi 2 Model B and a cheap USB WiFi adapter the generic hardware setup was complete.

I started with a clean install of Raspbian Jesse (2015-11-21)  and installed mysql-server, python-mysqldb, apache, and php. I won’t go over all the steps needed to get everything up and running but I’ll leave some write ups that I found helpful. I setup a separate 2.4 GHz network for the nodes and made sure the db was accessible to the other devices. Normally I would have struggled with the SQL but that’s what I do for my job (setting up a db did give nice insight into the life of a DBA). I created a reader/writer user which is shared by the nodes, and a reader user for external applications. I gave both access to the automator_test table I created (datetime DATETIME, val INT) and was ready to start logging data.

I wrote a quick Python script to insert two rows of dummy data (I unfortunately lack interesting sensors at the moment):

The next step was to create a cron job to run the Python code at a specified interval. Again, I won’t go into too much detail but will instead leave a helpful link. And that’s the first half of the project. We now have a host node that can read, write, and send data in and out of the network.

The next part is also open-ended, OS reliant, and dependent on your needs. I wanted to be able to access the data across a variety of applications and not limit myself to the Pi software universe (but instead the OSX universe). While manually querying the database daily sounded like a lot of fun, I decided to go with a more automator approach (Automator). Here’s where someone more knowledgeable might remark: “But Connor, Automator already supports SQLite queries and not MySQL.” The choice to use MySQL over SQLite was intended to make the network more flexible and distributed, at the sacrifice of Automator convenience. There are several workarounds to the problem (reading from a MySQL db) and the one I chose mirrors the insert process: python-mysqldb. By default this library and its dependencies are missing from OSX however, all can be remedied with brew install mysql (+ any dependencies this may be missing), followed by pip install mysql-python (+ any dependencies this, too, may be missing). Once the MySQL library has been installed, we can access the db through shell or a script.

The Automator task I had in mind was fairly straightforward: poll the database and return daily data. While there are several caveats to having this run on a computer that isn’t always awake, I will not cover possible workarounds in this post. I, instead, assume that the computer is capable of running this workflow at a specific time or that data with a one day lag is not an issue. While I am by no means an Automator expert I was able to create a process that performed as expected. The first step is to create a new Calendar Alarm workflow, which we will have run daily. Next, the file which we want our data to be written to must be selected using the Get Specified Finder Items action. We then open this file with TextEdit and run our Python script to get the latest data to be added. Using a Set Contents of TextEdit Document action we can append the results of our database query to a local .txt file. Here’s a picture of the complete workflow:

As well as the script I’m using to generate the output:And there we have it. An automation for dumping data from a MySQL server into a pipe delimited text file we which can use in Excel, MATLAB, or any program of our choice.

I skipped over quite a bit of how the data can be used locally or on the web by the Pi however, there are many great resources for doing so and I’m sure some of that information will appear on this site in the future. I hope you found this a somewhat interesting read. Please reach out with any questions or concerns.