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.