Jump to content
  • 0

Fibaro stats in OpenHab3/SQL?


Question

I'm looking for a solution to export fibaro data (lux, energy readings etc) to an SQL server. I'm also using Openhab3 to collect energy/climate related statistics and want to combine that with fibaro stats in the (MariaDB) SQL server. 

 

So how can I export data from fibaro to MariaDB-SQL? OpenHab3 can also be used as a collector, OH3 is also feeding MariaDB. 

OH3 is running on Raspberry, MariaDB on Windows. 

Link to post
Share on other sites

1 answer to this question

Recommended Posts

  • 0

run by cron python script, for example

 

#!/usr/bin/python
# -*- coding: utf-8 -*-.
# by tender

import logging
import logging.handlers
import argparse
import sys
import errno
import time
import math
import json
import requests
import datetime
import mysql.connector
from mysql.connector import errorcode 

hc2IP = "192.168.1.36"
hc2auth = ('admin', 'HERE-IS-PASSWORD-FOR-HCx')

def centered_average(nums):
    return (sum(nums) - max(nums) - min(nums)) / (len(nums) - 2)

def get_device_value(id, prop):
	try:
		r = requests.get('http://' + hc2IP + '/api/devices/?id='+str(id), auth=hc2auth)
		if r.status_code==200:
			data = r.json()
			return data['properties'][prop] 
		else:
			return None
	except: # catch *all* exceptions
                return None

def get_device_history(id, from_time, into_time):
	try:
                url = 'http://' + hc2IP + '/api/panels/event?deviceID='+ str(id) +'&from=' + str(from_time) + '&to=' + str(into_time) + '&type=time'
                r = requests.get(url, auth= hc2auth)
                if r.status_code==200:
                   data = r.json()
                   return data 
                else:
                   return None
	except: # catch *all* exceptions
                return None


def get_device_values_history(id):
    now = datetime.datetime.now()
    d1 = datetime.datetime.strftime(now, "%d/%m/%Y %H:%M")
    d0 = datetime.datetime.strftime(now - datetime.timedelta(minutes=15), "%d/%m/%Y %H:%M")

    from_time = int(time.mktime(datetime.datetime.strptime(d0, "%d/%m/%Y %H:%M").timetuple()))
    into_time = int(time.mktime(datetime.datetime.strptime(d1, "%d/%m/%Y %H:%M").timetuple()))

    history = get_device_history(id, from_time, into_time)           
    history = filter(lambda x: x["type"] == "DEVICE_EVENT", history)
    history = filter(lambda k: 'DevicePropertyUpdatedEvent' in k['event']['type'], history)
    return [( float(i["event"]["data"]["newValue"]), i["timestamp"]) for i in history]

def get_device_value_avg(id):
    list = get_device_values_history(id)
    if len(list) != 0:
       return sum(c[0] for c in list) / float(len(list))
    else:
       return get_device_value(id)

def if_set_None(value, check):
    #print(value)

    if value != None:
       if int(float(value)) == check:
          return None

    return value

def savetodb(sensorName, value):
    if value != None:
       cnx = mysql.connector.connect(user='root', password='root', host='localhost', database='sensors') 
       cursor = cnx.cursor()
       add_data = "INSERT INTO `power` (`name`, `value`) VALUES (%s, %s)"
       data = (sensorName, value)
       cursor.execute(add_data, data)
       cnx.commit()
       cursor.close()
       cnx.close()


savetodb("PhaseA", get_device_value(1149, 'value'))
savetodb("PhaseB", get_device_value(1152, 'value'))
savetodb("PhaseC", get_device_value(1146, 'value'))

savetodb("PhaseA_Energy", get_device_value(1148, 'power'))
savetodb("PhaseB_Energy", get_device_value(1151, 'power'))
savetodb("PhaseC_Energy", get_device_value(1145, 'power'))

 

  • Like 1
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...