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 5: Report from the First Split

This week was the first time that our team tried assigning specific tasks to specific people, let's see how we did.

• Arik:
• Make the website look pretty. All that we have so far is black text displayed on a white background. Arik plans on looking into how Twitter Bootstrap can be integrated into Django.
• 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.
• Karl: 
• Re-organize code so that authentication files are contained in one location. Implement the Light model to the project.
• Entire Group:
• Write tests for the basic model structure that will be implemented for this week.
• Further solidify the UML diagram for the project.

Arik's success of integrating twitter bootstrap into our project is a huge confidence boost. It makes our website look like it wasn't built by a fourth grader. Check out our new homepage!

Zach made significant progress towards attaining his goal, but difficulties with the svn repository due to power outages prevented him from being able to complete his goal for the week. 

Karl successfully reorganized the authentication files after quite a bit of difficulty regarding URLs and templates. A light model has been successfully added so that an administrator can add lights to a specific user.

Our lack of tests added for the models to be implemented is due in part to our unfamiliarity with the test first coding practice and also in part due to ambiguous UML diagrams. Since we aren't familiar with how to write tests and exactly what tests to write, it is difficult to complete the task.

There really isn't much excuse for the lack of a further solidified UML, this will be our first priority for next week, hopefully allowing other pieces of the project to be more easily understood and implemented.

These are our goals for next week (or whenever, since we will be going on Spring Break next week):

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

Napoleon is a registered user of the project HAM website. He wants to see how many lights are currently on in his house. He logs into the website and is immediately greeted by a display which indicates how many lights are controlled by the system in his house and whether those lights are off or on.