Databases, ugh!

Posted on Sat 06 April 2019 in Home Automation,

For nearly 15 years, in both professional and personal life I have avoided SQL with a passion.

Databases are what the database teams work with. They ask me to do random AD stuff now and again I ask for database restores every once in a while. No one asks questions, it's a beautiful relationship.

I have had no need to know how databases function, how to enter data in them and how to interact with that data. SQL was not something I've ever needed, or been interested in to be honest.

I had an idea. First mistake.

I use a few scripts to turn appliances on or off dependent on temperature and humidity. I thought it would be a great idea to record and graph the data I'm already able to collect.

So I sat and thought, oblivious to the impending spiral of doom I was approaching, I need some form of table, where I can record the date, time, sensor name and every minute the sensor data. Then somehow query that data and plot it on a graph.

Long story short, I recreated a text based database using python. It was awful.

I have also previously coded a calculator, in Excel. Just throwing that one in there.

I came to my senses accepted I hadn't a clue what I was doing and went to the internet for help.

I found this: https://medium.com/@rovai/from-data-to-graph-a-web-jorney-with-flask-and-sqlite-6c2ec9c0ad0

Exactly what I was trying to do! I STRONGLY suggest you give it a go if you are new to SQL, Python or Flask. I hadn't used Flask before but the basic principal is easy to get. SQL is just Excel on steroids. And Python, just, learn it as you go. Google the hard to read bits and errors.

I had a little trouble at first. I'm using a DHT22 but also a DS18b20 and a BME280. Out of the three, the DHT22 is my least favorite.

I couldn't work with the tutorial verbatim. But it and the scripts are so well written that I was able to cobble together a few scripts that collect data from each sensor and input them in to a database, which I can query and display in graph format as an overlay on a CCTV camera.

Below are my first ventures into SQL…

These are resulting scripts, you first need to have working sensors (you already have knowledge on how to make the sensors push data) and a basic understanding of the conn and curs commands needed to enter data in to the database.

There are three main parts to this.

 The logging scripts
 The web app
 The webpage

LOGGING

The below script is used as a cron to log data from the BME280 sensor It needs the Adafruit_BME280.py and Adafruit_BME280.pyc scripts to be in the same folder

This is the cron entry

* * * * * sudo python /home/pi/scripts/cron.BME280.logger.py

It collects data from the sensor every 60 seconds and inputs the result in to a database

Although the script has been edited, Credits To: http://jorgemoreno.xyz/raspbmesql.php

import sys
sys.path.insert (1, '/home/pi/Adafruit_BME280/')
from Adafruit_BME280 import *
import time
#import Adafruit_GPIO
import datetime
import sqlite3
dbname='/home/pi/BMEdatabase.db'

sensor = BME280(t_mode=BME280_OSAMPLE_8, p_mode=BME280_OSAMPLE_8, h_mode=BME280_OSAMPLE_8)

def getBMEdata():

    degrees = float('{0:.2f}'.format(sensor.read_temperature()))
    pascals = float('{0:.2f}'.format(sensor.read_pressure()))
    hectopascals = pascals / 100
    humidity = float('{0:.2f}'.format(sensor.read_humidity()))
    timenow = datetime.datetime.utcnow()
    return degrees,pascals,humidity

def logData (degrees,pascals,humidity):

    #executes the SQL command in MySQL/MariaDB to insert data.
    conn=sqlite3.connect(dbname)
    curs=conn.cursor()
    curs.execute('''INSERT INTO BM_data VALUES(datetime('now'), (?), (?), (?));''',(degrees,pascals,humidity))
    conn.commit() #commits the data entered above to the table
    conn.close()

def main():

   degrees,pascals,humidity =getBMEdata()
   logData (degrees,pascals,humidity)

main()

   # print 'Time      = ' + str(timenow)
   # print 'Temp      = {0:0.3f} deg C'.format(degrees)
   # print 'Pressure  = {0:0.2f} hPa'.format(hectopascals)
   # print 'Humidity  = {0:0.2f} %'.format(humidity)

WEB APP

The below is a python web app that queries and posts the data

It collects the most recent data form the database, formats it, does some error correcting and posts it ready for our index.html to display

Although the script has been edited, credits to: Marcelo Rovai https://mjrobot.org/author/mjrovai

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
#!/usr/bin/env python

'''
    RPi WEb Server for DHT captured data with Gage and Graph plot
'''

from datetime import datetime

from matplotlib.backends.backend_agg import FigureCanvasAgg as FigureCanvas
from matplotlib.figure import Figure
import io

from flask import Flask, render_template, send_file, make_response, request
app = Flask(__name__)

import sqlite3
conn=sqlite3.connect('/home/pi/BMEdatabase.db')
curs=conn.cursor()

# Retrieve LAST data from database
def getLastData():
    for row in curs.execute("SELECT * FROM BM_data ORDER BY date_time DESC LIMIT 1"):
        time = str(row[0])
        temp = row[1]
        hum = row[3]
    #conn.close()
    return time, temp, hum

# Get 'x' samples of historical data
def getHistData (numSamples):
    curs.execute("SELECT * FROM BM_data ORDER BY date_time DESC LIMIT "+str(numSamples))
    data = curs.fetchall()
    dates = []
    temps = []
    hums = []
    for row in reversed(data):
        dates.append(row[0])
        temps.append(row[1])
        hums.append(row[3])
        temps, hums = testeData(temps, hums)
    return dates, temps, hums

# Test data for cleanning possible "out of range" values
def testeData(temps, hums):
    n = len(temps)
    for i in range(0, n-1):
        if (temps[i] < -10 or temps[i] >50):
            temps[i] = temps[i-2]
        if (hums[i] < 0 or hums[i] >100):
            hums[i] = temps[i-2]
    return temps, hums


# Get Max number of rows (table size)
def maxRowsTable():
    for row in curs.execute("select COUNT(temperature) from  BM_data"):
        maxNumberRows=row[0]
    return maxNumberRows

# Get sample frequency in minutes
def freqSample():
    times, temps, hums = getHistData (2)
    fmt = '%Y-%m-%d %H:%M:%S'
    tstamp0 = datetime.strptime(times[0], fmt)
    tstamp1 = datetime.strptime(times[1], fmt)
    freq = tstamp1-tstamp0
    freq = int(round(freq.total_seconds()/60))
    return (freq)

# define and initialize global variables
global numSamples
numSamples = maxRowsTable()
if (numSamples > 101):
        numSamples = 100

global freqSamples
freqSamples = freqSample()

global rangeTime
rangeTime = 100


# main route
@app.route("/")
def index():
    time, temp, hum = getLastData()
    templateData = {
      'time'        : time,
      'temp'        : temp,
      'hum'         : hum,
      'freq'        : freqSamples,
      'rangeTime'       : rangeTime
    }
    return render_template('index.html', **templateData)


@app.route('/', methods=['POST'])
def my_form_post():
    global numSamples
    global freqSamples
    global rangeTime
    rangeTime = int (request.form['rangeTime'])
    if (rangeTime < freqSamples):
        rangeTime = freqSamples + 1
    numSamples = rangeTime//freqSamples
    numMaxSamples = maxRowsTable()
    if (numSamples > numMaxSamples):
        numSamples = (numMaxSamples-1)

    time, temp, hum = getLastData()

    templateData = {
      'time'        : time,
      'temp'        : temp,
      'hum'         : hum,
      'freq'        : freqSamples,
      'rangeTime'   : rangeTime
    }
    return render_template('index.html', **templateData)


@app.route('/plot/temp')
def plot_temp():
    times, temps, hums = getHistData(numSamples)
    ys = temps
    fig = Figure()
    axis = fig.add_subplot(1, 1, 1)
    axis.set_title("Temperature [°C]")
    axis.set_xlabel("Samples")
    axis.grid(True)
    xs = range(numSamples)
    axis.plot(xs, ys)
    canvas = FigureCanvas(fig)
    output = io.BytesIO()
    canvas.print_png(output)
    response = make_response(output.getvalue())
    response.mimetype = 'image/png'
    return response

@app.route('/plot/hum')
def plot_hum():
    times, temps, hums = getHistData(numSamples)
    ys = hums
    fig = Figure()
    axis = fig.add_subplot(1, 1, 1)
    axis.set_title("Humidity [%]")
    axis.set_xlabel("Samples")
    axis.grid(True)
    xs = range(numSamples)
    axis.plot(xs, ys)
    canvas = FigureCanvas(fig)
    output = io.BytesIO()
    canvas.print_png(output)
    response = make_response(output.getvalue())
    response.mimetype = 'image/png'
    return response

if __name__ == "__main__":
   app.run(host='0.0.0.0', port=99, debug=False)

HTML

The below is the index.html I am using to display two cameras, one with a graph overlay and plot the temperature and humidity below.

<!<!DOCTYPE html>
<html>
<head>
  <title> GroControl v.01</title>
  <link rel="stylesheet" type="text/css" href="../static/styleGrid.css">
  <link href="https://fonts.googleapis.com/css?family=PT+Sans" rel="stylesheet">
  <script src="./static/raphael-2.1.4.min.js"></script>
  <script src="./static/justgage.js"></script>
  </head>

<body>

  <div class="wrapper">

      <div class="header">
        <div class="imglogo" >
          <img src="../static/images/logoPage.png" width="150px" height="150px" />
        </div>
      </div>


      <div class="cam1">
        <div class ="cam1Iframe">
          <iframe src="http://192.168.10.100:8081" width="450" height="950" scrolling="no"  frameborder="0" marginwidth="0" marginheight="0" gesture="media" allow="encrypted-media" allowfullscreen></iframe>
        </div>

      <div id="g1"></div>
      <div id="g2"></div>

        <script>
var g1, g2;
document.addEventListener("DOMContentLoaded", function(event) {
    g1 = new JustGage({
  id: "g1",
  value: {{temp}},
  valueFontColor: "red",
  min: -10,
  max: 50,
  title: "Temperature",
  label: "Celcius"
});
    g2 = new JustGage({
  id: "g2",
  value: {{hum}},
  valueFontColor: "red",
  min: 0,
  max: 100,
  title: "Humidity",
  label: "%"
});
    });
  </script>
      </div>

      <div class="cam2">
        <div class ="cam1Iframe">
          <iframe src="http://192.168.10.102:8081" width="450" height="950" scrolling="no"  frameborder="0" marginwidth="0" marginheight="0" gesture="media" allow="encrypted-media" allowfullscreen></iframe>
        </div>
        <div id="g3"></div>
        <div id="g4"></div>

      <script>
  var g3, g4;
  document.addEventListener("DOMContentLoaded", function(event) {
    g3 = new JustGage({
  id: "g3",
  value: {{temperature}},
  valueFontColor: "yellow",
  min: -10,
  max: 50,
  title: "Temperature",
  label: "Celcius"
  });
    g4 = new JustGage({
  id: "g4",
  value: {{humidity}},
  valueFontColor: "yellow",
  min: 0,
  max: 100,
  title: "Humidity",
  label: "%"
  });
    });
  </script>

      </div>

      <div class="temp1">
        <div class="temp1Plot">
        <img src="./plot/temp" alt="Image Placeholder" width="400" height="350" >
      </div>
      </div>

      <div class="temp2">
        <div class="temp1Plot">
        <img src="./plot/hum" alt="Image Placeholder" width="400" height="350" >
      </div>
    </div>

      <div class="footer">
    <h4> Last Reading: {{ time }} ==> <a href="/"class="button">REFRESH</a></h4>
    <h5> HISTORICAL DATA </h6>
        <p> 60 is 1 hour (Sample Frequency: {{ freq }} minutes)
        <form method="POST">
            <input name="rangeTime" value= {{rangeTime}}>
            <input type="submit">
        </form></p>
    </div>

  </div>



  </body>

  </html>

![webpage] (images/webpage.png "WebPage")

The cameras are in dark mode so not showing an image, but you get the idea :)