Accessing Databases via Python

In order for our Raspberry Pis to be able to turn off and on their lights according to the state on the website, some sort of interfacing with the database outside of the Django framework will be required. This can easily be accomplished using the same PyMySQL module that allowed the Django framework on Python3 to be compatible with MySQL. What follows is a basic script which we used to access our database and get information about the states of the lights. By modifying this basic script to include code that Arik has been posting about GPIO reading and writing, our application should be nearing a usable state where modifying the database equates to a change "in the real world."

import sys

import time

import pymysql

lights = {'bathroom light 1':0,'bathroom light 2':1,

        'kitchen light':2, 'bedroom light':3}

conn = pymysql.connect(

db='database',

user='user',

passwd='password',

host='123.45.67.890',

)

cur = conn.cursor()

cur.execute("SELECT * FROM lightDB WHERE user_Id = <user_id>") 

for line in cur:

    for key,val in lights.items():

        if key==line[1] and line[3]==1:

            print("turning",key,"on")

        elif key==line[1] and line[3]==0:

            print("turning",key,"off")

Each line in cur is a tuple that has various information about the lights, the 1st (line[1]) item in the tuple is the description of the light, and the 3rd (line[3]) item is the state of whether the light is off or on.

There are some obvious security concerns at this point if this database were a database maintained by the company which contained all of its users lights. For this and other reasons, we are considering restructuring our design regarding where the server for the lights is run and how the information is accessed/backed up. Look for more on this in a later post.

Standing Meeting 6: Return from Break

Let's see what we accomplished since our last meeting.

• Arik:
• Continue to update new pages which we develop to be wrapped in the beautiful environment that is twitter bootstrap.
• Develop the House model for the management app.
• Implement tests for the house model.
• Adding a house to the database.
• Adding a room to the house.
• Removing a room from the house.
• Accessing a house by the user.
• Zach
• Complete tests to simulate Amber's success story. This will be accomplished by checking to make sure the views that are returned are the correct views for a logged in user.
• Develop the Room model for the management app.
• Implement tests for the room model.
• Adding a room to the database.
• Adding a light to a room.
• Removing a light from the room.
• Karl
• Solidify the UML diagrams for the project. Make these in the Violet UML editor.
• Provide users with a view to see what lights are in their house, not necessarily organized by house or room.
• Allow users to change the state of a light.
• Whole group
• Start programming on the Pis, install all necessary software on the devices so that they can function as our "houses".
• Implement Napoleon's success story.

    Arik has the unfortunate task of always updating the website's common look-and-feel, which is never really completely done, so he will never be able to completely check that item off of his list. Arik did not implement tests for the House model, but some of those tests are contained within other tests because houses are required to add rooms and lights. 

    Zach completed all his objectives except for the test to simulate Amber's success story. Since this is proving to be much too difficult to be worth pursuing, he will be abandoning this task. Karl may try implementing using a different method, but for now it is incomplete. 

    Karl failed yet again to make official looking UML documents for our project. The temptation to continue using our whiteboard picture has proven too strong. Karl also failed to implement the user being able to change the state of the light.

    The whole group was successful in installing software on our Pis that we will need in the future. The end goal is to have the Pis run a Django server, which we have not verified is possible yet, but we believe we have all of the required software.

     We were also able to implement Napoleon's success story. When a user logs in, they are now immediately directed to a page which shows all of the lights that they have in their house, arranged by room. We have yet to add a common look and feel, but it is encouraging to see the application really begin to take shape.

    

We held our standing meeting and determined what our goals for the next couple of weeks should be. 

• Arik:
• Develop the way that we will implement having the database of lights control the Pi.
• Karl:
• Develop the method that will allow users to change the states of lights through the website. These changes must also then be reflected in the database. 
• Zach:
• Assist both Arik and Karl in accomplishing their goals
• Whole Group:
• Implement Snowball's success story.

Snowball wants to log in to the system at his house and turn off a light. He logs in to the website, and turns off the bathroom light. The website shows him that he has accomplished this change.

This week we are trying something new by really focusing on using the Team Programming approach. You probably noticed that Zach does not have a specific goal for this week. He will instead be sitting beside Arik or Karl while they program and providing a second pair of eyes to look at the code. If there is some discreet task that Arik or Karl find, they could also pass this task to Zach to complete for them.

We will now leave you with a screenshot of Napolean's success story, all of the lights in his house, organized by room: