show integer logs from python script as diagram in MM ?

cruunnerr · Feb 5, 2018, 4:31 AM

so i actually edited my python script, so that the results will be automatically loaded up to my MySQL Database on the Synology NAS.

With PHP i can show the Data as a diagram :)
So now i could use an iFrame module.
So far so good ^^ (never thought i get this working)

next days i will take a look at the MMM-Chart Module and the OpenHAB solution. (or a other that maybe works with MySQL)

For those, who are interested so far:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

# import required modules
import time
import datetime
import RPi.GPIO as GPIO
import os
import ftplib
import mysql
import mysql.connector

GPIO.setwarnings(False)

# define server data
# ftpserver = "..."  //optional, when needed uncomment the FTP-Upload further down
# ftpuser = "..."
# ftppassword = "..."
# ftppath = "/web"

sqlhost = "..."
sqlport = "..."
sqluser = "..."
sqlpassword = "..."
sqldb = "Tank"

# define GPIO pins
GPIOTrigger = 18
GPIOEcho    = 24

# function to measure the distance
def MeasureDistance():
  # set trigger to high
  time.sleep(0.2)
  GPIO.output(GPIOTrigger, True)

  # set trigger after 10Ã‚Âµs to low
  time.sleep(0.0001)
  GPIO.output(GPIOTrigger, False)

  # store initial start time
  StartTime = time.time()

  # store start time
  while GPIO.input(GPIOEcho) == 0:
    StartTime = time.time()

  # store stop time
  while GPIO.input(GPIOEcho) == 1:
    StopTime = time.time()

  # calculate distance
  TimeElapsed = StopTime - StartTime
  Distance = (TimeElapsed * 34400) / 2
  
  return Distance

print("Messe Volumen...")

# main function
def main():
  try:
#    while True:
      Distance0 = MeasureDistance()
      Distance01 = MeasureDistance()
      Distance02 = MeasureDistance()
      Distance03 = MeasureDistance()
      Distance04 = MeasureDistance()
      Distance05 = MeasureDistance()
      Distance06 = MeasureDistance()
      Distance07 = MeasureDistance()
      Distance08 = MeasureDistance()
      Distance09 = MeasureDistance()
      Distance10 = MeasureDistance()
      Distance11 = MeasureDistance()
      Distance12 = MeasureDistance()
      Distance13 = MeasureDistance()
      Distance14 = MeasureDistance()
      Distance15 = MeasureDistance()
      Distance16 = MeasureDistance()
      Distance17 = MeasureDistance()
      Distance18 = MeasureDistance()
      Distance19 = MeasureDistance()
      Distance20 = MeasureDistance()
      Distance_sum = Distance01 + Distance02 + Distance03 + Distance04 + Distance05 + Distance06 + Distance07 + Distance08 + Distance09 + Distance10 + Distance11 + Distance12 + Distance13 + Distance14 + Distance15 + Distance16 + Distance17 + Distance18 + Distance19 + Distance20
      Distance = round(Distance_sum / 20,1)
#    Meine Tanks haben Maximal 3.200 Liter bei 150 cm Füllhöhe
#    Zusätzlich 9 cm Offset vom Einbauort des Sensors; 
      Fuelstand = 150 - Distance
      Liter = 3200 / 141 * Fuelstand
      Zeit = time.time()
      ZeitStempel = datetime.datetime.fromtimestamp(Zeit).strftime('%Y-%m-%d_%H:%M:%S')
      print (ZeitStempel),("Entfernung: %.1f cm" % Distance),(" Fuelhoehe: %.1f cm" % Fuelstand),(" Liter: %.0f l" % Liter)
      time.sleep(.1)

      Auslesezeitpunkt = datetime.datetime.fromtimestamp(Zeit).strftime('%d-%m-%Y_%H:%M:%S')
      Tag = datetime.datetime.fromtimestamp(Zeit).strftime('%Y-%m-%d')
      Uhr = datetime.datetime.fromtimestamp(Zeit).strftime('%H:%M:%S')

	# schreibe Langzeitmessung in *.csv Datei
      file = open("longtimelog.csv", "a")
      file.write(str(Tag))
      file.write(", ")
      file.write(str(Liter))
      file.write("\n")
      file.close()

        # schreibe aktuelle Messung in *.csv Datei
      file = open("log.csv", "w")
      file.write(str(Tag))
      file.write(", ")
      file.write(str(Liter))
      file.write("\n")
      file.close()

      print("Logs aktualisiert")
      time.sleep(.1)
      print("Upload Logs auf FTP...")

        # Lädt die longtimelog.csv Datei auf das NAS
#      filename = "longtimelog.csv"
#      ftp = ftplib.FTP(ftpserver)
#      ftp.login(ftpuser, ftppassword)
#      ftp.cwd(ftppath)
#      os.chdir(r"/home/pi")
#      myfile = open("longtimelog.csv", 'r')
#      ftp.storlines('STOR ' + "longtimelog.csv", myfile)
#      myfile.close()

        # Lädt die log.csv Datei auf das NAS
#      filename = "log.csv"
#      ftp = ftplib.FTP(ftpserver)
#      ftp.login(ftpuser, ftppassword)
#      ftp.cwd(ftppath)
#      os.chdir(r"/home/pi")
#      myfile = open("log.csv", 'r')
#      ftp.storlines('STOR ' + "log.csv", myfile)
#      myfile.close()

      time.sleep(.1)

      print("Verbinde mit MySQL-Datenbank...")

      time.sleep(.1)

      try:
          connection = mysql.connector.connect(host = sqlhost, port = sqlport, user = sqluser, passwd = sqlpassword, db = sqldb)
      except:
          print "Keine Verbindung zum MySQL-Server"
          exit(0)

      cursor = connection.cursor()
      cursor.execute("INSERT INTO Volumen VALUES (%s,%s)", (Tag,Liter,))
      cursor.close()
      connection.commit()

      time.sleep(.1)

      print("Upload erfolgreich")

  # reset GPIO settings if user pressed Ctrl+C
  except KeyboardInterrupt:
    print("Measurement stopped by user")
    GPIO.cleanup()

if __name__ == '__main__':
  # use GPIO pin numbering convention
  GPIO.setmode(GPIO.BCM)

  # set up GPIO pins
  GPIO.setup(GPIOTrigger, GPIO.OUT)
  GPIO.setup(GPIOEcho, GPIO.IN)

  # set trigger to false
  GPIO.output(GPIOTrigger, False)

  # call main function
  main()

ninjabreadman · Feb 5, 2018, 4:13 AM

@cruunnerr You could use a mysql module to query your database, create a route to output a JSON file, then use MMM-Charts to display.

cruunnerr · Feb 5, 2018, 4:27 AM

@ninjabreadman said in show integer logs from python script as diagram in MM ?:

create a route to output a JSON file

alright, that sound makable for me i think. ^^Will take a look. Thank you

edit: ehm…first question :D

i installed this now (seems to be newer). But where to start? Where i need to create the file, and what type of file, and how will it be loaded?
Is it a javascript file, which must be saved somewhere in the node directory?

doubleT · Feb 5, 2018, 3:09 PM

I have been following this, but I didn’t have time to comment.

You asked about the. json file. Simplyfied, it’s a file that holds a (JS) object for data sharing.

{{date:"2018-01-01", litre:500},{date:"2018-01-02", litre:498},{date:"2018-01-03", litre:495}}

Probably comparable to what you did with your txt file.
For a working solution, that would actually be enough. Grab the json, handle the object, print the result with highcharts.
To me, the SQL db seems a bit “heavy weight” for such a simple use case, but it’s solid if you can make it work. Just my opinion.

ninjabreadman · Feb 5, 2018, 4:46 PM

@cruunnerr @doubleT It would certainly be more easily handled in JSON or even a CSV (easiest to append readings). You can then set up cron to rsync the file to your mirror, load the file with an npm package like csv or fast-csv, and display in MMM-Chart (I would modify MMM-Chart to load the CSV directly).

doubleT · Feb 5, 2018, 5:11 PM

Yes, that’d be the easiest option. Could be running tonigt. ;)
Personally, I’d go with the json, though. I did it like this with Highcharts.js, which seems familiar to MMM-Charts:

node-helper.js:

const fs = require("fs");
//...
    socketNotificationReceived: function(notification, payload) {
        if (notification === "UpdateChart") {
        	this.getChart();
        }
    },
    getChart: function() {

		var rawdata = fs.readFileSync('path/to/my.json');
		var history = JSON.parse(rawdata);
		var history = history.slice(-84); // 12 per day x 7 = I only want the last 84 data points
		this.sendSocketNotification("ChartUpdate", history);
	}

module.js:

    getScripts: function() {
        return [
            this.file("highcharts/highcharts.js"),
            this.file("highcharts/series-label.js"),
            this.file("highcharts/exporting.js")
        ]
    },
    socketNotificationReceived: function(notification, payload) {
        if (notification === "ChartUpdate") {
        	this.getChart(payload);
        }
    },
    getChart: function(history) {
        var chart = "";
            for(i = 0; i < history.length; i++) {
                if (i === 0) {
	    		chart = history[i].litre;
	    	}
	    	else {
	    		chart = chart + ", " + history[i].price;
	    	}
	    }
	    var maxi = Math.max.apply(Math, JSON.parse("[" + chart + "]"));
	    var highest = maxi.toString() + " L";
	    var mini = Math.min.apply(Math, JSON.parse("[" + chart + "]"));
	    var lowest  = mini.toString() + " L";

	    Highcharts.chart('module-chart', { // the id of the div to contain the chart!
	    	chart: {
		    	height: 175, 
		    	margin: 0,
		    	left: 0
	    	},
	        plotOptions: {
	            series: {
	                marker: {
	                    enabled: false
	                }
	            }
	        },
	        tooltip: {
	            pointFormat: "Value: {point.y:.2f}"
	        },
	        yAxis: {
	            min: mini-0.05,
	            max: maxi+0.05,
	            tickInterval: 0.05,
	            plotLines: [{
	            	value: this.price,
	            	dashStyle: 'solid',
	            	width: 1,
	            	color: {
		                linearGradient: [0, 0, 900, 0],
		                stops: [
		                    [0, 'rgba(255, 255, 255, 0.1)'],
		                    [1, 'rgba(255, 255, 255, 0.2)']
		                ]
		            }
	            }, {
	            	value: Math.max.apply(Math, JSON.parse("[" + pricelist + "]")),
	            	dashStyle: 'dot',
	            	width: 1,
	            	color: {
                            linearGradient: [0, 0, 900, 0],
                            stops: [
                                [0, 'rgba(255, 255, 255, 0.2)'],
                                [1, 'rgba(255, 255, 255, 0.3)']
                            ]
                        },
                        label: {
                            text: highest,
                            x: -2,
                            y: -7
                        }
	            }, 
                    {
                    value: Math.min.apply(Math, JSON.parse("[" + pricelist + "]")),
                    dashStyle: 'dot',
                    width: 1,
                    color: {
                        linearGradient: [0, 0, 900, 0],
                        stops: [
                            [0, 'rgba(255, 255, 255, 0.2)'],
                            [1, 'rgba(255, 255, 255, 0.3)']
                        ]
                    },
                    label: {
                        text: lowest,
                        x: -2,
                        y: 13
                    }
                }]
            },
            series: [{
                data: JSON.parse("[" + pricelist + "]"), // here's the action
                step: 'center',
                lineWidth: 2,
                color: {
	            linearGradient: [0, 0, 900, 0],
	            stops: [
	                [0, 'rgba(255, 255, 255, 0.4)'],
	                [1, 'rgba(255, 255, 255, 1)']
                    ]
                }
            }]
        });
    }

cruunnerr · Feb 5, 2018, 6:49 PM

wow, thank u guys for your effort :)

personally for me this project is not that easy, because i need to learn and understand several languages at the same time. So pls be patient ^^

While u posted here i was able to create a JSON file from the mysql database. But the results were looking like this:

[{"Datum":"2018-02-03T23:00:00.000Z","Volumen":2488},{"Datum":"2018-02-03T23:00:00.000Z","Volumen":2488},]

thats probably because the columns in the database are defined as “date” (not datetime) and “decimal”
I think i would get this right and could load this into the MMM-Chart.

But now u guys posted several solutions. The easiest would be to create a CSV and log this directly with the MMM-Chart @ninjabreadman said.
To create the CSV is no problem for me, but how changing the MMM-Chart… i need to pass a.t.m. ^^

Actually i’m not sure how node.js and npm works so i need to read and try a little bit more to understand it better :)

@doubleT
How exactly would highcharts work together with the MM? Is it just like a web page or what? :D
How to start it, where to put the files, and how to implement to MM?
For me it looks nearly like an own module for the MM.

Sorry for the much questions, but i am trying not just to use a finished solution. I want to nearly understand that whole stuff XD

Thank you guys, u are great

edit:
So i changed the columns definition from “date” to “text”.
Now my with the javascript using node i can get a JSON file looks correct:

[{"Datum":"2018-02-04","Volumen":2490},{"Datum":"2018-02-04","Volumen":2488},]

but i am using a very very simple script i think and i am just able to execute it manually by typing in terminal “node script.js”
also i don’t know in what direction this has to be saved.

var mysql      = require('mysql');
var fs = require('fs');
var connection = mysql.createConnection({
  host: 'xxx.xxx.xxx.xxx',
  port: '3307',
  user: '...',
  password: '...',
  database: 'Tank'
});

connection.connect();

connection.query('SELECT * from Volumen', function(err, results, fields) {
    if(err) throw err;

    fs.writeFile('results.json', JSON.stringify(results), function (err) {
      if (err) throw err;
      console.log('Saved!');
    });
});

connection.end();

doubleT · Feb 5, 2018, 6:49 PM

Sorry, I didn’t want to confuse you. I’d suggest Highcharts if you want to build your own module (which I thought you were going for in the beginning). It’s a library that you load (you load the scripts) and use. You address it, tell it the id of the element where the chart should sit and the data it should show along with some parameters on how to show it.

But maybe in your case you really want to use MMM-Chart. On first look it seems like the JSON you have there should be totally enough! No CSV needed, as far as I can see from the readme.

What he has is similar to your JSON:

[["2017-04-21 15:58:00",8.3,95.5],["2017-04-21 14:55:00",9.3,90.5],["2017-04-21 12:56:00",10.7,87.7],["2017-04-21 11:53:00",10.5,87.7],["2017-04-21 11:01:00",10.6,88.8]]

It’s just that he has arrays in an array and you have objects with keys and values in an array.
I didn’t check, but maybe MMM-Chart works with your style, too. Else, you could try to get your JSON to write as arrays in an array:

[["2018-02-03T23:00:00.000Z",2488],["2018-02-03T23:00:00.000Z",2488]]

From then on, MMM-Chart should print your chart.

BTW. please check if you can edit your process/function that writes your JSON so there is no comma after the last object (or array, if you change it).

cruunnerr · Feb 5, 2018, 7:49 PM

@doubleT
i edited my post while u wrote this ^^

for the first i didn’t want to create a own module, but maybe later, when i understand the stuff good.

as u can read in my edit, i can create a JSON with the script i posted. but i don’t know where to put the script to execute it automatically every 24 hours or something like that.

because i think using cronjob would be the wrong way, right? :D

edit:

i just tried the MMM-Charts module, but i even cannot get this to work with a sample JSON.
i created a test.json and loaded it up to my NAS.

[["2018-02-04",2490],["2018-02-05",2488]]

in the MM config.js i used this config:

{
                        module: 'MMM-Chart',
                        position: 'middle_center',
                        header: 'Tankvolumen',
                        config: {
                                name: "test",
                                url: "http://192.168.178.220/test.json",
                                xaxisLabelTicks: true,
                                maintainAspectRatio: false,
                                graphLabel0: "Volumen",
                                graphLineColor0: "rgba(200, 200, 200, 1)",
                                graphTickColor0: "rgba(200, 200, 200, 0.8)",
                                graphFillColor0: "rgba(200, 200, 200, 0.4)",
                                xaxisTimeUnit: "hour",
                                xaxisTimeFormatLabels: "HH:mm",
                        }
                },

and modified the custom.css as said in the readme like this:

.test {
	width: 1060px;
	height: 180px;
}

But it just shows the header and a long line below.
i am sure i don’t use the module the correct way. ^^

doubleT · Feb 5, 2018, 8:43 PM

@cruunnerr said in show integer logs from python script as diagram in MM ?:

as u can read in my edit, i can create a JSON with the script i posted. but i don’t know where to put the script to execute it automatically every 24 hours or something like that.
because i think using cronjob would be the wrong way, right? :D

That’s exactly what a cronjob is for. ;)

But yeah, that’s on the serving side. You have to make sure the file is updated (read, altered, saved) or overwritten entirely.

I checked MMM-Chart and have the same issue, not even his own example is working. Tried some debugging but didn’t get very far. I don’t know what’s wrong.

Let’s take a step back because I think there are some things that are not necessarry.
You read the volume with a RasPi and it’s logged to a file on your NAS. Regular updates to that file work already?
It’s also saved to a MySQL database?
You have a Script that makes a JSON from the database? But it’s not yet regularly updated? Does this Script run on the measuring RasPi or the Mirror?

I’d make the measuirng Pi save a JSON to the NAS. Make sure it’s working and the format is clean. MySQL database and another script are not necessarry that way. Then I’d make the Mirror read the JSON.

Shoot me a PM (in German), I think I have an idea.