CPU load & Temperature Datalog chart

Headlines

Charts with Highcharts, SQLite3 and Python (CPU Temperature)

Objective :

This project goal is to drew data-log form database into a nice looking Front-end Web Page, and that by:

Insert CPU temperature(and CPU Load) into Database SQLite3;
Plot Temperature data logs form Database.

Sources :

https://

Technologies :

– Store data : SQLite3
– Serve content : Python with Flask
– Plot data : canvas.js

1 Database

1.1 Create database

Database structure to create:

Sqlite3 shell script:

$ sqlite3 control.db

CREATE TABLE RoomDetails (ID INTEGER PRIMARY KEY AUTOINCREMENT,
                          Room VARCHAR(25) );
CREATE TABLE Temperature (ID INTEGER PRIMARY KEY AUTOINCREMENT,
                          RoomID INTEGER,
                          FOREIGN KEY(RoomID) REFERENCES RoomDetails(ID));
ALTER TABLE Temperature ADD COLUMN TemperatureC FLOAT(8);
ALTER TABLE Temperature ADD COLUMN Datetime DATETIME;
ALTER TABLE Temperature ADD COLUMN CPU FLOAT(8);

Test:

> INSERT INTO RoomDetails (Room) VALUES ('Library');
> SELECT * FROM RoomDetails;
1|Library
>.quit

>INSERT INTO Temperature (CPU) VALUES (3.1);

Structure verification:

$ sqlite3 control.db
sqlite> .tables
RoomDetails  Temperature
sqlite> .schema
CREATE TABLE RoomDetails (ID INTEGER PRIMARY KEY AUTOINCREMENT,
             Room VARCHAR(25));
CREATE TABLE Temperature (ID INTEGER PRIMARY KEY AUTOINCREMENT,
             RoomID INTEGER,
             TemperatureC FLOAT(8),
             Datetime DATETIME,
             FOREIGN KEY(RoomID) REFERENCES RoomDetails(ID));

2.Acquisition: “insert process”

Rule : insert each minute The data acquisition :

Get the hardware samples : CPU Temperature +, CPU load !	By function: measure _temp()
Store samples	Insert request into the sqliteDB
Launch each minute	ConJob

2.1 cron job : insert CPU Temp

# File name : request_InsertCPUTemp.py
#!/usr/bin/env python
 import sqlite3
 import urllib2
 import json
 import os
 import time
 #function for to get CPU Temperature value
 def measure_temp():
         temp = os.popen("vcgencmd measure_temp").readline()
         return (temp.replace("temp=","").replace("'C",""))
 def main():
     Room = 1
     Temperature = measure_temp() 
     my_query = 'INSERT INTO Temperature(RoomID,TemperatureC,Datetime) \ 
                         VALUES(%s,%s,CURRENT_TIMESTAMP);' %(Room,Temperature)
     try:
         connection = sqlite3.connect('control.db')
         cursor = connection.cursor()
         cursor.execute(my_query)
         query_results = cursor.fetchone()
         my_response = 'Inserted %s for room %s' % (Temperature, Room)
     except sqlite3.Error, e:
         my_response = "There is an error %s:" % (e)
     finally:
         print my_response
         connection.close()
 if <strong>name</strong> == "<strong>main</strong>":
     main()

 $ crontab -e
* * * * * sudo python /home/pi/database/request_InsertCPUTemp.py   >> /home/pi/database/log.txt   # Insert Temp loop

2.2 insert null Once

Insert Null samples each startup to indicate to the chart that its a disrupted graph.

#File name : request_InsertCPUTempOnce.py
#!/usr/bin/env python
 import sqlite3
 import urllib2
 import json
 import os
 import time
 def main():
     Room = 1
     my_query = 'INSERT INTO Temperature(RoomID,TemperatureC,Datetime) VALUES(%s,"",CURRENT_TIMESTAMP);' %(Room)
     try:
         connection = sqlite3.connect('/home/pi/database/control.db',isolation_level=None)
         cursor = connection.cursor()
         cursor.execute(my_query)
         query_results = cursor.fetchone()
         my_response = '_FromReBoot_Inserted Null for room %s' % (Room)
     except sqlite3.Error, e:
         my_response = "_FromReBoot_There is an error %s:" % (e)
     finally:
         print my_response
         connection.close()
 if name == "main":
     main()

$ crontab -e
@reboot sudo python /home/pi/database/request_InsertCPUTempOnce.py >> /home/pi/database/log.txt   # Insert Null once

3. FETCHING/DISPLAYING DATA :

3.1* JSON : Data loading ( Select process )

Rule : Extract all the samples in the DB and return it in a formatted JSON response.

IN :	server.py :	OUT:
<IP:port>	>> SelectDB[JSON] >>	Return Application/json

IN : <IP:port>
>> server.py : SelectDB[JSON] >>
OUT: Return Application/json

#File name : Flask_Data_fetching.py 
#!/usr/bin/python
  
 # Flask server app 
 from flask import Flask, render_template, request, Response
 import sqlite3
 import json
  
 app = Flask(__name__)
  
 # get data from DB 
 # return : JSON
 @app.route("/")
 def select():
     connection = sqlite3.connect("control.db")
     cursor = connection.cursor()
     cursor.execute("SELECT datetime , TemperatureC from Temperature")
     results = cursor.fetchall()
     print(results)
     return Response(json.dumps(results), mimetype='application/json')
  
 # return : Rederection to html "main"
 @app.route("/graph")
 def graph():
     return render_template('CanvasJS-Json-Data-Api-Ajax-Chart - Copy.html')
  
 if __name__ == '__main__':
     app.run(
     debug=True,
     threaded=True,
     host='0.0.0.0',
     port=5000
 )

Code breakdown:

 ...
 # get data from DB 
 # return : JSON
 @app.route("/")
 def select():
     connection = sqlite3.connect("control.db")
     cursor = connection.cursor()
     cursor.execute("SELECT datetime , TemperatureC from Temperature")
     results = cursor.fetchall()
     print(results)
     return Response(json.dumps(results), mimetype='application/json') 
...

Test analysis :

After get into URL : <Server_IP>:5000 from the client side we get the fellowing :

Response Headers :

Content-Length:    160236
Content-Type:      application/json
Date :             Sat, 22 Jun 2019 08:34:43 GMT
Server:            Werkzeug/0.11.15 Python/2.7.13

Request headers :

Accept:            text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
Accept-Encoding:   gzip, deflate
Accept-Language:   en-US,en;q=0.5
Connection:        keep-alive
DNT:               1
Host:              Server_IP>:5000
Upgrade-Insecure-Requests: 1
User-Agent:    Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:67.0) Gecko/20100101 Firefox/67.0

Content :

[
["2019-06-05 09:57:53", null], 
["2019-06-11 15:00:01", 52.1],
["2019-06-11 15:01:02", 51.5],
["2019-06-11 15:02:01", 52.1], 
["2019-06-11 15:03:02", 51.5], 
["2019-06-11 15:21:01", 52.1], 
["2019-06-11 15:22:02", 52.1], 
["2019-06-11 15:23:01", 52.1], 
["2019-06-11 15:24:02", 52.1], 
["2019-06-11 15:25:01", 51.5], 
["2019-06-11 15:26:02", 51.5]]

3.2* /graph : Front End

INPUT : <IP:port>/graph
>> server.py
>> templates/.html : Ajax(server.py, fct(jsonData){Chart})
OUTPUT: Return Application/text

...
# return : Rederection to html "main"
@app.route("/graph")
def graph():
return render_template('CanvasJS-Json-Data-Api-Ajax-Chart - Copy.html')
...

Synthesis

Data flow:

Browser: <Raspberry_iP>:5000/graph
>>   web.py >>   templates/graph.html >> Ajax request(/)
>>   web.py >>   json from DB
>> Ajax return (templates/graph.html) >> Canvas.js

Final result / GUI

Pages: 1 2

N@bil WebSite