MySQL Database Creation

Using the libraries available in python creating timers using a MySQL database to manage relays connected to 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 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 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 setup.

The benefit of using the database over just hard coding the timers is that we can make changes to the database at anytime without have 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 before continuing.

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 login 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.

The “pk” column is defined to provide a primary key that we can use 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 of our first relay as follows

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 it 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 pairs 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 it’s comparisons.

Repeat the above process to insert table data for relay_3_timer and relay_4_timer.

Finally if you wanted to for example 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.

The Python Code

So now that we have our database ready we can go ahead and start creating our pyhton code to read the database and control the relays.

To start we need to import some modules and setup our database connection details

#!/usr/bin/env python

Next we will configure the GPIO pins to be outputs, set the relays to off and setup 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
GPIO.setmode(GPIO.BCM)
GPIO.setwarnings(False)
# Global Variables
outputpins = [22, 23, 24, 25]  # Set the GPIO pin numbers that are required
relaynum = ["relay_1", "relay_2", "relay_3", "relay_4"]  # Relay names in MySQL
relaycount = range(1, 5)  # Number of relays to be controlled
numdtpairs = 4  # Number of Start/Stop pairs per relay
#Set our 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)
sleep(2)

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

def read_override_data():
    conn = MySQLdb.connect(servername, username, password, yourdbname)
    curs = conn.cursor()
    curs.execute("SELECT * FROM timer_override WHERE pk=(1)")
    override_timer_values = curs.fetchone()
    conn.close()
    return override_timer_values

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

def get_relay_timer_data(tablename, row):
    conn = MySQLdb.connect(servername, username, password, dbname)
    curs = conn.cursor()
    curs.execute("SELECT * FROM {0} WHERE pk={1}".format(tablename, row))
    relay_timer_values = curs.fetchone()
    conn.close()
    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"
    else:
        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"
            else:
                return "False"
        else:
            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.

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 + "_timer", dtpair)
                relayon = timercheck(timer_data, relay)
                if relayon == "True":
                    GPIO.output(relay, True)
                    break
                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
    sleep(1)

All going well you should now have all of your relays controlled via your MySQL database, any changes made to the database should be immediately reflected by your relays without the need to stop the program running in the background.

All the python code shown here is available for both 2.x and 3.x 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.