This work I did for this post was really challenging. It just seemed like nothing was going right for me, and I couldn’t get anything to work properly. More than once I was ready to give up. “No one reads my blog anyway”, I would think to myself. I wanted to quit the whole thing. I tried and it didn’t work. I already put in a solid eight hours a day at work programming. Why keep doing this to myself?
That is what my very selfish, defeating side of my brain was telling me. It often got the best of me in the past. I believed a lot of the lies that it told me about being complacent, that I had done enough, that I would never get anywhere by just trying, and in the worst of times that I wasn’t good enough anyway and didn’t deserve anything better. But those are just that, lies. I could look back over my story of what I had been through and what I have been able to accomplish and know that I could do it. It was just going to take some work. Read some documentation. Yes, I would hit more walls and experience frustration. But if I paid closer attention to what I was trying to do and maybe reevaluate my approach even, I could get through it.
When I finally got the messages from the sensors to save messages to the database, I felt a true sense of accomplishment. Even better, I had proven those dark moments and thoughts of self doubt wrong. I was smart enough to figure it out. It just took a little work and some persistence. So, lets take a look at what I was able to accomplish on Project TMD and getting the messages to save to a PostgreSQL database.
In earlier posts, I went over how I was able to receive messages from door/window sensors to USB ZWave controller. My next step was saving those messages about the sensor opening and closing to a database. I decided to use PostgreSQL for my database. I had never really used it, and I had heard that it was pretty versatile. It was originally designed for Unix based systems, and since my plan was to run in on my Raspberry Pi, it seemed like a good fit. Despite it’s Unix background, I only found instructions to install it for Windows when I looked at the PostgreSQL site!
I was able to find a good primer on setting it up on a Linux system on the Digital Ocean website. I think now is a pretty good time to plug the fine folks at Digital Ocean. I use them for my hosting on this site and have used them for other sites in the past. It is easy to set up a site really quickly and at a good price as well! I have used some other providers in the past and the price, quality, and admin interfaces are by far the best I have used.
My first step was to set up an instance of the database on my machine. The tutorial I found took me through the steps of installing it, creating a user, and creating a database for the application. I then had to decide about the design of my table for recording the message data. To begin with, I am just going to save the opening and closing events of the sensors, but I may want to do other things with the table at a later date. So I wanted to create a design that was going to be general enough to allow me to store other messages. Below is the script I used to create the table in the database.
A little searching led me to a Python library written to interface with Postgres, Psycopg2. This tutorial on the Postgres site walked me through the basic setup for communicating to the database using the library. This library provided me with everything I needed to connect, send, and receive to the database. I knew that I also didn’t want to just have connection information like username and password for the database just hanging out in my code, so I was going to have to figure out how to read from configuration files in Python. The Python documentation was super easy to figure out how to create and parse configuration files.
That above code was all I needed to pull out all of the data I needed to create a connection to the database. I know what you are thinking, “But you just talked about all tough times you had with this stuff!” Yes, despite how simple these 16 lines seem to be, I could not connect to the database with what I was pulling from my config file. This is where two problems came up to triple team me: capitalization, permissions and semicolons!
I was constantly getting failure errors when trying to connect to the database when testing. It was telling me that the login failed at first. After a lot of errors and resetting of passwords and fighting with permissions, I finally got the connection to return the version of Postgres as described in the documentation I found. So now to start to put my data into the table and get to saving some data! I wrote the SQL to insert the information from the signal into the new table in my new database.
Then I was getting an error saying that my database didn’t exist. I went to the terminal and listed it and saw the database there. I thought I was going insane! I dropped and recreated the database and table a couple of times with the same result. I did a little more research and found out the cause of my issue. Turns out that Postgres does some quirky things with capitalization. If you don’t enclose identifiers in double quotes, it converts it to all lower case. This was driving me crazy for a couple of nights while I was working on this. So once I got everything recreated in all lower case, I was able to connect to the database and my insert statement ran without error. I did have to toy with the formatting of the SQL in Python to use parameterization and not use string concatenation which leaves things open to the possibility of some SQL injection issues.
Great, yeah, insert is working all is well with the world. But it wasn’t. Right after my insert was a command to retrieve the last record, but it kept throwing an error saying there were no records in the database. So I looked in the terminal and sure enough, nothing was being inserted. And then I realized that something that had been giving me some trouble earlier had come back to bite me once again. SEMICOLONS!!! When I was setting up the database and creating tables and granting permissions, I would figure out the syntax and commands that I needed and type them in, but nothing would change after I ran the command. After a couple of times I realized that a semicolon was needed for the command to execute. I come from the land of MS SQL Server which is more forgiving about not having semicolons to execute a statement. Sure enough, adding a semicolon to the end of my SQL did the trick. Data was getting saved on every open and close of the sensors. Below you can see how I refactored my calls out into a separate Python file to keep my code cleaner and more organized.
Next step for Project TMD is going to be setting up an API in order to retrieve the data for display in a website to see when there is a bathroom available for use! Remember to keep going when you feel like you are stuck. That those lies our brains tell us some times, are not our truths! Take care.