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 1: Initializing...

Hot dogs.

That was what was in front of us as we had our first standing meeting for Project HAM. We had previously decided that we would have our standing meeting over dinner, which was an interesting experience to say the least. Thankfully corndogs were being served for dinner, so it made standing and eating relatively easy.

We made a list of things that we wanted to accomplish before the week's end. This is what it included:

• Install Python3 on the server
• Setup a MySQL database for the django framework to access
• Create a website which our potential users could access

We set up another meeting for Wednesday evening where we will make our first steps toward these goals. Arik is the only one in our group who has experience with databases, so setting up the MySQL database will probably be the main topic of Wednesday's meeting. 

We plan on using Django with the MySQL database, which will be a new interface for all of the members of our group. Karl found this resource which might come in handy as we reach that point in our project.

Stay tuned for more info on what exactly Project HAM is and what progress we are making towards our goals.

-Team PorkPI