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.

The Registration Page

Wilbur's success story was implementing a view that would allow a user to create an account on our website. Like a large majority of the things that we do with our website, the way to implement this came from a very helpful post on Stack Overflow.

Unfortunately, while this provided all the details for how to log the user in behind the scenes, it doesn't provide the method of allowing the user to input their data. Thankfully, this webpage provided a template that can be used for logging a user in. Using this template with the code from the previous post should work right?

Wrong! It was at this point that I was introduced to one of Django's security features, csrf tokens. Cross Site Request Forgery Tokens are used to prevent malicious websites from accessing our websites data. Sounds like a good idea to me, as long as I don't have to spend valuable hours of sleep figuring out how to make it work with our software engineering project. The fix was simple enough, I just had to add one line of code:

{% extends "base.html" %}
{% block title %}Create an account{% endblock %} 
{% block content %} 
<h1>Create an account</h1> 
<form action="" method="post"> 
    {% csrf_token %} <--------------This line needs to be added.
    {{ form.as_p }} 
    <input type="submit" value="Create the account"> 
</form> 
{% endblock %}

Now I don't know if my site will be more protected because I added this one line of code, but I do know that it lets me experience success logging in, and that's what's most important to me right now!