This post describes the last step to have a functional competition system. It will show how to update the python GUI of the central node with the data stored in the database (coming from each of the roommates phones, as explained in the previous post). It is a short entry describing:

• The database and tables use to monitor each participant's progress
• New python functions to include values from the database

All development is done in the central node (Raspberry Pi 3), using Python and SQL queries.

Competition database

It will be hosting two kind of tables:

• Roommate information table - with the current distance, the daily distance and the monthly distance time stamped. In this case, we are 4 people in the house
• Winner information table - with the winner and the month they won

Information transaction

Most of the information will be stored from the Compatition service, as explained in [Pi IoT] Smart Competition Home #8: Competition system III - Android Competition application: communicating with the server (Each roomates distance information). Then, the Python main program will retrieve that information and display the competition in its main GUI. It will also determine who is the monthly winner at the end of each period.

Nevertheless, the main python activity will be the one handling the winners table. Once we change to a new month, it will use the last monthly_distance value of each resident to selectand store that past month winner.

Accessing the database itself - creating a local user for the competition service

Both Competition Service and Main program access the database with an specific user and password. Since it is not very advisable to use the very same root, I will show how to:

• Create a new database user
• Grant permissions to this user
• Check the user its working

Let's begin...  On a command prompt of the central node, we start mysql service as a root user

Create a new database

A step 0, create the database to use:

> CREATE  DATABASE Competitiondb;

And start suing it (~open)

> USE Competitiondb;

Creating a new mySQL user

To create a newlocal  user, we input the following SQL command:

>CREATE USER 'userName'@'localhost' IDENTIFIED BY 'password';

To grant permissions (in my case SELECT, DELETE, CREATE, DROP {table}, INSERT, UPDATE {into table})

> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON Competitiondb.* TO 'userName'@'localhost';

Testing the new user

To test this user, I will create a new mock table and then erase it. We will see the number of tables with SHOW TABLES command (none at this point)

Here is the screenshot:

Updating Central Node GUI

Initial setup: Raspberry Pi 3 - Raspbian SO (Jessie) / SSH Enabled / Mosquitto MQTT Broker installed / MQTT Subsciber client / Console interface / Python GTK interface /  MySQL Server / Apache2 web Server / Competition Service version 1

The GUI is already prepared to host the current competition table (showing each residents progress, and having them organized with the best on top). More details on how it was done, can be found in[PiIoT] Smart Competition Home #5: Central Node Upgrade

(*) last version of the Central Node Code

Read database and display

New File - read_db.py

Existing file - main_gui.py

read_db.py performs has only one function, read_last_sample(table), performing  two main actions:

• Connect to the database - use the created SQL user to open a connection to the database
• Read the last sample of the requested table

def read_last_sample(table_name):
    db = MySQLdb.connect(host="this_host",    # your host, usually localhost
                         user="userName",         # your username
                         passwd="one_password",  # your password
                         db="CompetitionDB")        # name of the data base

    # Cursor to db
    cur = db.cursor()

    # Select table
    cur.execute("SELECT * FROM "+str(table_name))

    # Return last row
    all_rows = cur.fetchall()
    for row in all_rows:
        print row[0]

    last_row = cur.fetchlast()
    db.close()

    return last_row

The main_gui.py will call the function read_last_sample(table) every time a gui label is updated. It will refresh the last values for each of the roommates

Manage competition state and store new winner in database

New File - write_db.py

Existing file - main_gui.py

In this case, the main_gui will detect when a new month starts, and select the best resident during the previous one. It will be stored in winners table, using the file write_db.py (very similar to read_db.py, though it executes an INSERT query)

GUI with the updated competition table

(What a coincidence... I am winning )

Conclusion

For the first time, I can say we have a "Smart Competition House" (yet, very basic one). In the house central node, there will be displayed:

• Smart house information - temperature, pressure, altitude, door state and alarm
• Competition table - current distance traveled by each residence

The platform is lacking a lot of interactivity though (we can not see the competition state in the phone, and there is no current interface for the smart house either )