Raspberry Pi Relay Controller
Using the MySQL libraries that are available in python to create a relay timer using a MySQL database on a Raspberry Pi is fairly straight forward. Creating a facility to override the timers and turn the relays on or off manually is also possible.
The timers in this tutorial will be aimed at longer-term settings over months and days. When I created this code I was looking to manage a pool so I have configured it to take the start and stop times for each relay spread out over an entire year. The timers can also be configured to turn on and off the relays multiple times a day either all year round or say just over the summer. As we get further into the code I will show some examples of how the timers can be set up.
The benefit of using the database over just hard coding the timers is that we can make changes to the database at any time without having to stop the program from running. Another advantage is that if you have a web server installed then you can access the database from your own webpage or if installed, PHPMyAdmin.
This tutorial follows on from others showing you how to connect relays to your Raspberry Pi, how to install a web server and optionally, how to install PHPMyAdmin, if you haven’t already, I would suggest reading these tutorials before continuing.
In this tutorial I will be using the following materials:
- Raspberry Pi (2, 3 or 4)
- Micro SD Card
- Power Supply
- Ethernet Cable or Wi-Fi Dongle (Wi-Fi is onboard the Pi 3 and 4)
- Raspberry Pi Case (Optional)
In This Article
MySQL Database Creation via the CLI
The first thing we need to do is create a MySQL database and add some tables, there are 2 ways to do this, we can log in to the command-line interface (CLI) of the Pi or via PHPMyAdmin on a web browser. First, we log into MySQL from the Pi command prompt.
Enter your password and you will be met with a new prompt – mysql>. Next, we will create a new database, note that all MySQL commands need to be terminated with a semi-colon.
Replace “yourdbname” with what you want to call your database and if successful you should now see that your database has been added
Now that we have a database we can start adding our tables and columns, the first one we need is timer_override, this table will hold values of on, off and auto for each of the relays and allows you to control the relays manually.
The “pk” column is defined to provide a primary key, the values in this column must be unique, we can use this column later to address the data that we want to change, all other columns are set to be of type “TEXT”. We now need to add some values to our row.
Using the same process we now need to add a table for the timers, this table will contain the start (activate) and stop (deactivate) times for the first relay.
Again we define a “pk” column and set the other 2 columns to be “datetime” types. Repeat this step to create tables for relay_2_timer, relay_3_timer, and relay_4_timer, while we could put all of our values into one table it becomes more readable in the python code if we separate them out.
Next, we will assign 4 start and stop times to each of our relays. Dates and times need to be entered in the following format: ‘YYYY-MM-DD HH:MM:SS’
In the above example I have divided the year up into 4 equal parts so the first start/stop pair will activate the relay between the start of January and end of March between 8 am and 3:30 pm each day, pairs 2-4 will do the same for the rest of the year during their specified times. It is not necessary to fill in all 4 timer pairs, the following entry could also be used.
In this example, the first-timer pair activates the relay between 8 am and 9:30 am from January to March, the 2nd pair also activates the timer from January to March but from 3 pm to 6 pm (effectively the relay is activated twice each day). The final 2 start/stop times are not used, there is one restriction, while you can set the timers to span multiple years this would require a modification to the python code and also the necessity to update the timers each year. The python code has been written to basically ignore the year when making its comparisons.
Repeat the above process to insert table data for relay_3_timer and relay_4_timer.
Finally, as an example, if you wanted to update the 3rd row in table “relay_2_timer” from NULL, then we need to use the MySQL UPDATE command as follows:
That’s it we now have our database set up and ready to start controlling the relays.
MySQL Database Creation via phpMyAdmin (GUI)
Another way of creating the database is to use the Graphical User Interface (GUI) provided by PHPMyAdmin. In the short video below I will show you how to create the same database and tables that we made above through the CLI.
Relay Timer Python Code
So now that we have our database ready we can go ahead and start creating our python code to read the database and control the relays.
The MySQL connector python library is not part of the standard set of installed Python packages so we will need to add it using the following two commands.
Next, we need to import the required modules and set up the database connection details.
Next, we will configure the GPIO pins to be outputs, set the relays to off and set up our variables. The relays that I am using are “active low” so we need to set the output value to “True” to deactivate the relay. If your relays are active high then you will need to change the settings to “False” throughout the code.
The variables describe which GPIO pins we are using, assigns the MySQL table names, sets the number of relays and sets how many start /stop pairs we have defined in the database. Here I have shown settings for 4 relays, and 4 start/stop pairs, if you have a different number of relays or are using different pins you can add and remove them in the 4 variables above. In order to read the information that we have saved in our database, we will use the following three pieces of code: This definition will connect to the MySQL database and read in the row that specifies our manual override information. ie On/Off/Auto
This definition reads in the start/stop pairs we have set one row at a time.
If the read_override_data definition returns “Auto” for a particular relay then the start/stop pairs will be passed to this definition to decide if the time right now is within one of the set date/time ranges and if so, tell the main program to activate the relay.
The main program consists of a series of loops that call the definitions every second checking the database for manual On/Off changes and comparing the start/stop times to the current time when the relay is set to auto. The main program is contained in a while loop that will repeat indefinitely.
All going well you should now have all of your relays controlled via your MySQL database, any changes made to the database should be reflected by your relays (after the sleep period) without the need to stop the program running in the background.
All the python code shown here is available on my Hydropi Github Repository.
If you have any thought’s about this article, improvements or errors let me know in the comments below and if you found this helpful, why not share it with others.