Raspberry Pi Relay Controller

by | Jul 3, 2016

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:


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.


mysql -u username -p

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.


mysql>CREATE DATABASE yourdbname;


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.


Note: All of the commands below have been written for 4 relays with 4 start and stop times each, depending on how many relays you are trying to control you will need to add or remove tables and columns as required.


mysql>USE yourdbname;
Database changed
mysql>CREATE TABLE timer_override (
->relay_1 TEXT,
->relay_2 TEXT,
->relay_3 TEXT,
->relay_4 TEXT);


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.


mysql> INSERT INTO timer_override (pk, relay_1, relay_2, relay_3, relay_4)
->VALUES (1,"on","off","auto","on");
mysql>SELECT * FROM timer_override;


MySQL Database and Table Summary


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. 


mysql>CREATE TABLE relay_1_timer (
->starttime DATETIME,
->stoptime DATETIME);


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’


mysql>INSERT INTO relay_1_timer (pk, starttime, stoptime)
->VALUES (1, '2019-01-01 08:00:00', '2019-03-31 15:30:00'),
->(2, '2019-04-01 10:00:00', '2019-06-30 13:00:00'),
->(3, '2019-07-01 13:00:00', '2019-09-30 19:00:00'),
->(4, '2019-10-01 02:30:00', '2019-12-31 06:00:00');
mysql>SELECT * FROM relay_1_timer;


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.


mysql>INSERT INTO relay_2_timer (pk, starttime, stoptime)
->VALUES (1, '2019-01-01 08:00:00', '2019-03-31 09:30:00'),
->(2, '2019-01-01 15:00:00', '2019-03-31 18:00:00'),
->(3, NULL, NULL),
->(4, NULL, NULL);
mysql>SELECT * FROM relay_2_timer;


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:


mysql>UPDATE relay_2_timer SET starttime='2019-04-01 11:00:00', stoptime='2019-12-31 13:00:00' WHERE pk=3;
mysql>SELECT * FROM relay_2_timer;


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. 


sudo apt install python-pip
sudo pip install mysql-connector-python


Next, we need to import the required modules and set up the database connection details.


#!/usr/bin/env python

import mysql.connector as mariadb
from time import sleep
import datetime
import RPi.GPIO as GPIO

servername = "localhost"
username = "YourMySQLusername"
password = "YourMySQLpassword"
dbname = "YourDatabaseName"

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.


# Set up GPIO ports


# Global Variables

# Set the GPIO pin numbers that are required
outputpins = [22, 23, 24, 25]
# Relay names in MySQL
relaynum = ["relay_1_timer", "relay_2_timer", "relay_3_timer", "relay_4_timer"]
 # Number of relays to be controlled
relaycount = range(1, 5)
# Number of Start/Stop pairs per relay
numdtpairs = 4

#Set the GPIO pins to outputs and set them to off then wait 2 seconds

for i in outputpins:
    GPIO.setup(i, GPIO.OUT)
    GPIO.output(i, False)


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


# Read the manual setting from the database
def read_override_data():
    conn = mariadb.connect(user=username,
    curs = conn.cursor()
        curs.execute("SELECT * FROM timer_override WHERE pk=(1)")
    except mariadb.Error as error:
        print("Error: {}".format(error))
    override_timer_values = curs.fetchone()
    return override_timer_values


This definition reads in the start/stop pairs we have set one row at a time.


#Get the start/stop pairs from the SQL database

def get_relay_timer_data(tablename, row):
    conn = mariadb.connect(user=username,
    curs = conn.cursor()
        curs.execute("SELECT * FROM {0} WHERE pk={1}"
                     .format(tablename, row))
    except mariadb.Error as error:
        print("Error: {}".format(error))
    relay_timer_values = curs.fetchone()
    return relay_timer_values


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.


def timercheck(timer_data, relay):
    if timer_data[1] is None:  # Python reads a "NULL" value as None
        return  "False"
        cdt = datetime.datetime.now()
        starttimer = timer_data[1]
        starttimer = starttimer.replace(year=cdt.year)
        stoptimer = timer_data[2]
        stoptimer = stoptimer.replace(year=cdt.year)
        if (cdt.date() >= starttimer.date()
            and cdt.date() <= stoptimer.date()): if (cdt.time() >= starttimer.time()
                and cdt.time() <= stoptimer.time()):
                return "True"
                return "False"
            return "False"


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.


# Main Program

    while True:  # Repeat the code indefinitely
        override = read_override_data()
        for i, j, z in zip(relaycount, outputpins, relaynum):
            if override[i] == "auto":
                relay = j
                relayon = "False"
                dtpair = 1
                while relayon == "False" and dtpair <= numdtpairs:
                    timer_data = get_relay_timer_data(z, dtpair)
                    relayon = timercheck(timer_data, relay)
                    if relayon == "True":
                        GPIO.output(relay, True)
                    elif relayon == "False":
                        dtpair += 1
                    if dtpair == (numdtpairs + 1):
                        GPIO.output(relay, False)
            elif override[i] == "on":
                GPIO.output(j, True)  # turn relay on
            elif override[i] == "off":
                GPIO.output(j, False)  # turn relay off
except KeyboardInterrupt:
    # catches the ctrl-c command, breaks the loop above
    # and turns off the relays
        for i in outputpins:
            GPIO.output(i, False) 


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.

Pin It on Pinterest

Share This