How To Save Sensor Data To MySQL Database?

I have several sensors receiving data from an energy monitor that I would like to record in a more efficient and permanent format than the HA database. Primarily because the HA database saves everything as JSON strings instead of the native DB data types which bloats the database by a factor of 100 or so and makes it inefficient to run queries.

First thought was to just use the SQL Sensor and put the states into the QUERY variable. Doesn’t work as it looks like templates are not accepted in the QUERY variable.

Searched around a bit and couldn’t find a solution. Anyone have an idea?

Thanks!

I’m using a (trial an error made) python shell script for this.
The script fetches the values (last_period = yesterday) from Utility Meter sensors via HA API and inserts them in a MySQL DB.

#!/usr/bin/env python

import json
from requests import get
import MySQLdb as mdb
from datetime import datetime, timedelta

hassip   = '192.168.123.45'
hassport = '8123'
headers = {
    'Authorization': 'Bearer eyJ0eXAi...'
    'content-type': 'application/json'
}

entities = {
    'daily_pv_energy': '',
    'daily_to_net_energy': '',
    'daily_from_net_energy': '',
    'daily_house_energy': '',
    'daily_to_battery_energy': '',
    'daily_from_battery_energy': ''
}

db_sh = mdb.connect (host="your_db_host_ip", user="username", passwd="passwd", db="database")
cursor_sh = db_sh.cursor()

yesterday = datetime.now() - timedelta(days=1)
date_value = yesterday.strftime('%Y-%m-%d')

for entity in entities.keys():
    url = 'http://' + hassip + ':' + hassport + '/api/states/sensor.' + entity

    response = get(url, headers=headers)
    json_data = json.loads(response.text)

    entities[entity] = json_data['attributes']['last_period']

sql_sh = "INSERT INTO e3dc_daily_energy(date,pv_total,house,to_net,from_net,to_battery,from_battery) "
sql_sh += "VALUES ('"
sql_sh += date_value
sql_sh += "','"
sql_sh += entities['daily_pv_energy']
sql_sh += "','"
sql_sh += entities['daily_house_energy']
sql_sh += "','"
sql_sh += entities['daily_to_net_energy']
sql_sh += "','"
sql_sh += entities['daily_from_net_energy']
sql_sh += "','"
sql_sh += entities['daily_to_battery_energy']
sql_sh += "','"
sql_sh += entities['daily_from_battery_energy']
sql_sh += "') "
sql_sh += "ON DUPLICATE KEY UPDATE "
sql_sh += "pv_total = '" + entities['daily_pv_energy'] + "'"
sql_sh += ", "
sql_sh += "house = '" + entities['daily_house_energy'] + "'"
sql_sh += ", "
sql_sh += "to_net = '" + entities['daily_to_net_energy'] + "'"
sql_sh += ", "
sql_sh += "from_net = '" + entities['daily_from_net_energy'] + "'"
sql_sh += ", "
sql_sh += "to_battery = '" + entities['daily_to_battery_energy'] + "'"
sql_sh += ", "
sql_sh += "from_battery = '" + entities['daily_from_battery_energy'] + "'"

try:
    cursor_sh.execute(sql_sh)
except:
    db_sh.rollback()

db_sh.commit()
db_sh.close()

Some python devs might shake their heads, but it works for me. :slightly_smiling_face:

Thank you!

From my limited Pyton experience (which is basically zero) that script looks straightforward. Parameterized queries would be ideal instead of concatenating SQL however it will do the job.

Going to give your script a try with a few modifications and post the results.

Another possibility is that I have been looking at MySQL triggers. Should be able to trigger a stored procedure on every INSERT to the HA database which can run the query to move the data over.

Will update this thread with the eventual solution.

Well, unfortunately it looks like I have to change my approach. Posting several sensor values to HA every second is murdering the database. The way HA stores data as JSON strings instead of a relational database is the Achilles heel in an otherwise awesome project. If I had the Python skills I would totally rewrite the database component however I just don’t have the knowledge. Too bad, because it makes the database a liability instead of an asset.

The long and short of it is that adding 5 power related sensors every second makes the Logbook unusable. And I do notice slowdowns in things like camera refreshes since I added the sensors (note I am running as a virtual environment, Ubuntu, VM, i3-3020 rack mounted server, 500GB spinning HDs in RAID) when in reality, this amount of data input is trivial for a properly designed database. Of course excluding the sensors from the Recorder would fix the issue, and at the same time render the sensors pointless as there would be no history available.

So I’m going to change my sensor code, which is running on an Arduino Uno w/W5100 Ethernet shield to insert the data directly to the MySQL DB using the MySQL_Connector_Arduino library ( https://github.com/ChuckBell/MySQL_Connector_Arduino ) and define some SQL sensors in HA to pull that data for display. Just means that I need to remove MQTT from my existing code as I don’t think there will be enough memory on an Uno to run both. Or swap to another processor (ie. STM32) which would mean 3D printing a new case.

Man, I wish I had the Python skills to fix the HA database.

Subscribed to this thread because I need to save data from a temperature sensor as well, and still have no idea how to do it.

Here’s what I ended up doing because I could not find an acceptable solution to using the HA database as the primary source. With 9 power monitor sensors sending data to HA every second, the HA database was being hammered. It was increasing by about 800MB per day (currently just under 10GB) and rendering the History and Logbook useless due to the forever hang when opening. It was also generally effecting responsiveness. For example, my cameras were often not showing in the UI.

For those curious I am using the Circuit Setup Split Single Phase Real Time Power meter board connected to an Arduino UNO with Ethernet shield. Was sending the data to HA via MQTT as a JSON string.

I rewrote the Arduino firmware to send the data directly to MySQL via the aforementioned MySQL_Connector Arduino library. It saves data to a MySQL DB “energy_monitor” with table “energy_log”. The table just has a primary key incrementing ID column, timestamp column, then a bunch of float columns to hold the data.

Here’s the very basic Arduino code. I still need to clean it up a bit, implement some error checking, etc. But it has been working now for over 24 hours without issue.

/*
 Name:    CircuitSetup_Split_Phase_Power_Meter.ino
 Created: 12/22/2019 10:55:36 AM
 Author:  Aaron Cake

 Energy monitor based on CircuitSetup split phase energy monitor board:
 https://circuitsetup.us/index.php/product/split-single-phase-real-time-whole-house-energy-meter-v1-4/

 Libraries:
 ATM90E32: https://github.com/CircuitSetup/ATM90E32

 Version History:
 1: Basic functionality of sending energy data via MQTT in JSON string
 2: Rewrite to save directly to MySQL database

*/


#include <avr/wdt.h>                    //watch dog timer

#include <MySQL_Connection.h>               //MySQL Connectector
#include <MySQL_Cursor.h>


#include <MemoryFree.h>                   //library with free memory function

//Ethernet library for ENC28J60
//#include <UIPEthernet.h>

//libraries needed for W5500
#include <SPI.h> 
//#include <Ethernet2.h>
#include <Ethernet.h>

#include <ATM90E32.h>                   // for ATM90E32 energy meter

//MySQL variables
IPAddress MySQLServer(192, 168, 107, 11);              // IP of the MySQL *server* here
char MySQLUser[] = "energymon";                   // MySQL user login username
char MySQLPassword[] = "energymon";                 // MySQL user login password
//query source string to format with sprint. Store in PROGMEM to save ~300 bytes
const char InsertQuerySource[] PROGMEM = "INSERT INTO energy_monitor.energy_log (volt_a,volt_c,total_volt,amps_ct1,amps_ct2,total_amps,ct1_watts,ct2_watts,total_watts,pwr_factr,line_freq,temp) VALUES ('%d.%02d','%d.%02d','%d.%02d','%d.%02d','%d.%02d','%d.%02d','%d.%02d','%d.%02d','%d.%02d','%d.%02d','%d.%02d','%d.%02d')";
char InsertQuery[300];                        //SQL query to run

const float FirmwareVersion = 2;                //firmware version

//variables and constants for send timer
unsigned long EnergyMonTelePreviousMillis = 0;                 //the last time the send interval check ran
const int EnergyMonitorTelemetryInterval = 2000;                   //the interval to send, milliseconds

//uptime counter and heartbeat variables
int UptimeSeconds = 0;                      //uptime counter seconds, 0-60
int UptimeMinutes = 0;                      //uptime counter minutes, 0-60
int UptimeHours = 0;                      //uptime counter hours, 0 - 24
int UptimeDays = 0;                       //uptime counter days, 0 - maxint...32,000 day uptime? Unlikely
unsigned long UptimePreviousMillis = 0;             //the last time the uptime counter updated
bool SendHeartbeat = false;                   //flag to send the heartbeat. Set true in uptime loop to send a heartbeat
int UptimeInterval = 1000;                    //how often in mS to run uptime function. Every second.
char HeartBeatPublishString[100];

//ATM90E32 Calibration Variables

/***** CALIBRATION SETTINGS *****/
/*
 * 4485 for 60 Hz (North America)
 * 389 for 50 hz (rest of the world)
 */
unsigned short lineFreq = 4485;

/*
 * 0 for 10A (1x)
 * 21 for 100A (2x)
 * 42 for between 100A - 200A (4x)
 */
unsigned short PGAGain = 21;

/*
 * For meter <= v1.3:
 *    42080 - 9v AC Transformer - Jameco 112336
 *    32428 - 12v AC Transformer - Jameco 167151
 * For meter > v1.4:
 *    37106 - 9v AC Transformer - Jameco 157041
 *    38302 - 9v AC Transformer - Jameco 112336
 *    29462 - 12v AC Transformer - Jameco 167151
 * For Meters > v1.4 purchased after 11/1/2019 and rev.3
 *    7611 - 9v AC Transformer - Jameco 157041
 */
unsigned short VoltageGain = 37495;                       //my 9VAC power adapter, "OEM"

/*
 * 25498 - SCT-013-000 100A/50mA
 * 39473 - SCT-016 120A/40mA
 * 46539 - Magnalab 100A
 */
unsigned short CurrentGainCT1 = 25498;
unsigned short CurrentGainCT2 = 25498;

const int CS_pin = 8;                 //CS pin for ATM90E32

//ATM90E32 Data Variables

float VoltageA = 0;
float VoltageC = 0;
float TotalVoltage = 0;
float CT1Current = 0;
float CT2Current = 0;
float TotalCurrent = 0;
float TotalWattage = 0;
float PowerFactor = 0;
float ATM90E32Temp = 0;
float LineFrequency = 0;
float CT1Wattage = 0;
float CT2Wattage = 0;



unsigned short sys0 = 0;
unsigned short sys1 = 0;
unsigned short en0 = 0;
unsigned short en1 = 0;

//*************************************************
// Make sure to assign a unique MAC to each board!
//*************************************************

byte mac[] = { 0x21, 0xCA, 0x33, 0x4A, 0xED, 0x52 };


//Initialize ethernet client as ethClient
EthernetClient ethClient;

MySQL_Connection MySQLDatabase((Client*)& ethClient);       //initialize MySQL_Connection as MySQLDatabase passing Ethernet client


//initialize the ATM90E32
ATM90E32 eic{};                  //initialize the IC class


void setup() {

	//wdt_enable(WDTO_8S);                //enable the watchdog timer for 8 seconds
	//PetTheDog();                    //pet the watchdog to reset the timer

	//initialize all the pins used


	//open the serial port for debugging
	//Serial.begin(9600);
	Serial.begin(115200);
	delay(100);
	Serial.println(F("BOOT..."));


	// start the Ethernet and obtain an address via DHCP
	Serial.println(F("ETHERNET: Begin..."));
	if (Ethernet.begin(mac) == 0) {
		Serial.println(F("ETHERNET: FAIL. No DHCP."));
		if (Ethernet.hardwareStatus() == EthernetNoHardware) {
			Serial.println(F("ETHERNET: Hardware not found."));
		}
		else if (Ethernet.linkStatus() == LinkOFF) {
			Serial.println(F("ETHERNET: No link."));
		}

		delay(5000);
		//Reboot();
	}

	Serial.print(F("ETHERNET: Success. IP Addresss: "));
	Serial.println(Ethernet.localIP());

	delay(1500);

	Serial.print(F("MYSQL: Attempting connection: "));
	Serial.print(MySQLServer[0]);
	Serial.print(MySQLServer[1]);
	Serial.print(MySQLServer[2]);
	Serial.print(MySQLServer[3]);
	Serial.println(F("..."));

	if (MySQLDatabase.connect(MySQLServer, 3306, MySQLUser, MySQLPassword)) {
		delay(1000);
		Serial.println(F("MYSQL: Connection succeeded."));
	}
	else {
		Serial.println(F("MYSQL: FAIL. Connection failed."));
	}

	/*Initialise the ATM90E32 & Pass CS pin and calibrations to its library -
	*the 2nd (B) current channel is not used with the split phase meter */
	Serial.println(F("ATM90E32: Begin..."));
	eic.begin(CS_pin, lineFreq, PGAGain, VoltageGain, CurrentGainCT1, 0, CurrentGainCT2);
	Serial.println(F("ATM90E32: Success..."));
	delay(2000);


}



void loop() {

	unsigned long CurrentMillis = millis();                 //get the current time count

	//reconnect if connection is lost
	//add reconnect to MYSQL here


	if (CurrentMillis - UptimePreviousMillis >= UptimeInterval) {
		UptimePreviousMillis = CurrentMillis;
		UptimeCounter();
	}


	if (CurrentMillis - EnergyMonTelePreviousMillis >= EnergyMonitorTelemetryInterval) {
		EnergyMonTelePreviousMillis = CurrentMillis;                   //update the time the check last ran

		sys0 = eic.GetSysStatus0();                            //EMMState0
		sys1 = eic.GetSysStatus1();                            //EMMState1
		en0 = eic.GetMeterStatus0();                            //EMMIntState0
		en1 = eic.GetMeterStatus1();                            //EMMIntState1


		Serial.print(F("STATUS: System: S0:0x: "));
		Serial.println(sys0, HEX);
		Serial.print(F("STATUS: System: S1:0x: "));
		Serial.println(sys1, HEX);

		Serial.print(F("STATUS: Meter: E0:0x: "));
		Serial.println(en0, HEX);
		Serial.print(F("STATUS: Meter: E1:0x: "));
		Serial.println(en1, HEX);

		//if true the MCU is not getting data from the energy meter
		if (sys0 == 65535 || sys0 == 0) {
			Serial.println(F("ERROR: Not receiving data from energy meter - Check connection."));
		}

		//get voltage
		VoltageA = eic.GetLineVoltageA();
		VoltageC = eic.GetLineVoltageC();

		if (lineFreq = 4485) {
			TotalVoltage = VoltageA + VoltageC;            //is split single phase, so only 120v per leg
		}
		else {
			TotalVoltage = VoltageA;                //voltage should be 220-240 at the AC transformer
		}

		//get current from both transformers and add for a total current measurement
		CT1Current = eic.GetLineCurrentA();
		CT2Current = eic.GetLineCurrentC();
		TotalCurrent = CT1Current + CT2Current;

		//get power. There seems to be no documumentation however guesses:
		TotalWattage = eic.GetTotalActivePower();               //total wattage
		PowerFactor = eic.GetTotalPowerFactor();
		CT1Wattage = eic.GetActivePowerA();                   //CT1 wattage
		CT2Wattage = eic.GetActivePowerC();                   //CT2 wattage

		ATM90E32Temp = eic.GetTemperature();
		LineFrequency = eic.GetFrequency();

		Serial.print(F("ENERGY: Voltage A: "));
		Serial.println(VoltageA);
		Serial.print(F("ENERGY: Voltage C: "));
		Serial.println(VoltageC);
		Serial.print(F("ENERGY: Total Voltage: "));
		Serial.println(TotalVoltage);
		Serial.print(F("ENERGY: Current CT1: "));
		Serial.println(CT1Current);
		Serial.print(F("ENERGY: Current CT2: "));
		Serial.println(CT2Current);
		Serial.print(F("ENERGY: Total Current: "));
		Serial.println(TotalCurrent);
		Serial.print(F("ENERGY: CT1 Wattage: "));
		Serial.println(CT1Wattage);
		Serial.print(F("ENERGY: CT2 Wattage: "));
		Serial.println(CT2Wattage);
		Serial.print(F("ENERGY: Total Wattage: "));
		Serial.println(TotalWattage);
		Serial.print(F("ENERGY: Power Factor: "));
		Serial.println(PowerFactor);
		Serial.print(F("ENERGY: Temperature: "));
		Serial.println(ATM90E32Temp);
		Serial.print(F("ENERGY: Frequency: "));
		Serial.println(LineFrequency);



		//generate the SQL statement to insert into the database
		sprintf_P(InsertQuery, InsertQuerySource,
			int(VoltageA), int(VoltageA * 100) % 100,                 //sprintf_p doesn't do floats, so need to convert it to two ints to retain decimal
			int(VoltageC), int(VoltageC * 100) % 100,
			int(TotalVoltage), int(TotalVoltage * 100) % 100,
			(int)CT1Current, (int)(CT1Current * 100) % 100,
			(int)CT2Current, (int)(CT2Current * 100) % 100,
			(int)TotalCurrent, (int)(TotalCurrent * 100) % 100,
			(int)TotalWattage, (int)(TotalWattage * 100) % 100,
			(int)CT1Wattage, (int)(CT1Wattage * 100) % 100,
			(int)CT2Wattage, (int)(CT2Wattage * 100) % 100,
			(int)PowerFactor, (int)(PowerFactor * 100) % 100,
			(int)LineFrequency, (int)(LineFrequency * 100) % 100,
			(int)ATM90E32Temp, (int)(ATM90E32Temp * 100) % 100);

		Serial.print(F("MYSQL: Begin INSERT: "));
		Serial.println(InsertQuery);

		MySQL_Cursor* MySQLCursor = new MySQL_Cursor(&MySQLDatabase);
		MySQLCursor->execute(InsertQuery);
		delete MySQLCursor;                           //no results expected so delete cursor

		Serial.println(F("MYSQL: End INSERT"));
	}
}


//function to reset the watchdog timer. Call often
void PetTheDog() {
	wdt_reset();                    //reset the watchdog timer. 
}

//function to use the watch dog timer to reboot the processor immediately
void Reboot() {
	wdt_disable();  //disable any existing watchdogs
	wdt_enable(WDTO_15MS); //enable with a 15MS timeout
	for (;;);   //loop forever so watchdog will reset processor
}

//function to manage the uptime
void UptimeCounter() {
	UptimeSeconds++;                  //increase uptime seconds by 1
	if (UptimeSeconds >= 60) {              //if seconds are greater than 60 that's a minute
		UptimeMinutes++;                //increase minutes by 1
		UptimeSeconds = 0;                //reset seconds to zero
		SendHeartbeat = true;             //set the flag to send the heartbeat publish
	}

	if (UptimeMinutes >= 60) {              //if minutes > 60 that's an hour
		UptimeHours++;                  //increase hour count
		UptimeMinutes = 0;                //reset minutes to zero

	}

	if (UptimeHours >= 24) {
		UptimeDays++;
		UptimeHours = 0;
	}

	if (SendHeartbeat == true) {            //if the heartbeat flag is set (on minute changeover), send heartbeat
		Heartbeat();
		SendHeartbeat = false;              //set the flag false to it doesn't run until next minute
	}
}
void Heartbeat() {

	//ArduinoJSON exists as a library to generate and serialise JSON. Not doing anything complicated so can do it in less memory manually
	//Bunch of sprintfs/strcats to build the heartbeat string.



								//create an uptime in ISO 8601 format:

								/*P is the duration designator(for period) placed at the start of the duration representation.
								Y is the year designator that follows the value for the number of years.
								M is the month designator that follows the value for the number of months.
								W is the week designator that follows the value for the number of weeks.
								D is the day designator that follows the value for the number of days.
								T is the time designator that precedes the time components of the representation.
								H is the hour designator that follows the value for the number of hours.
								M is the minute designator that follows the value for the number of minutes.
								S is the second designator that follows the value for the number of seconds.


								For example, "P3Y6M4DT12H30M5S" represents a duration
								of "three years, six months, four days, twelve hours, thirty minutes, and five seconds".*/




								//generate JSON string with sprintf_p . Format string stored in PROGMEM
	sprintf_P(HeartBeatPublishString, PSTR("{\"ip\":\"%d.%d.%d.%d\",\"uptime\":\"P%dDT%dH%dM%dS\",\"ver\":\"%d.%02d\",\"freemem\":\"%d\"}"),
		Ethernet.localIP()[0], Ethernet.localIP()[1], Ethernet.localIP()[2], Ethernet.localIP()[3],
		UptimeDays, UptimeHours, UptimeMinutes, UptimeSeconds,
		(int)FirmwareVersion, (int)(FirmwareVersion * 100) % 100,         //sprintf_p doesn't do floats, so need to convert it to two ints to retain decimal
		(int)freeMemory());




	Serial.print(F("HEARTBEAT: "));         //print it out to the serial for debugging
	Serial.println(HeartBeatPublishString);


}

The table data looks like this with the log_time being added automatically by MySQL since it is a timestamp() field. ID incrementing and indexed.

(My Watts values are nonsense. I think I have one of the current clamps backwards)

Over 24 hours of data occupies a whole 25MB.

Pulling that into HA is just done with some SQL sensors.

sensor powermonsense:
 - platform: sql
   db_url: mysql://energymon:energymon@localhost/energy_monitor?charset=utf8
   queries:
     - name: Power Voltage Phase 1 Avg 10s
       query: "SELECT ROUND(AVG(volt_a),2) AS avg_volt_a FROM (SELECT volt_a FROM `energy_monitor`.`energy_log` ORDER BY log_time DESC LIMIT 5) t"
       column: 'avg_volt_a'
       unit_of_measurement: V
     - name: Power Voltage Phase 2 Avg 10s
       query: "SELECT ROUND(AVG(volt_c),2) AS avg_volt_c FROM (SELECT volt_c FROM `energy_monitor`.`energy_log` ORDER BY log_time DESC LIMIT 5) t"
       column: 'avg_volt_c'
       unit_of_measurement: V
   scan_interval: 10

Note I perform all the calculations in the query. Since I’m saving data every 2 seconds to MySQL, I do a 10 second average selecting the column ordered descending by log_time, limited to 5 records. Then average the wanted fields, rounded 2 decimal places. HAQ supports only 1 result from a SQL query so this works out well. The data is then available as the sensor state.

MySQL says these queries take between 0.0021 and 0.0031 seconds to complete because all the work is done in the DB engine very efficiently.

2 Likes

Found this a few days ago.

Replying to my own topic here with an update.

Dumping the data directly to the MySQL DB and then using HA SQL sensors has worked quite well.

A month minus a week of energy data (2 phase voltage, 2 phase current, frequency, power factor, temp, 2 phase wattage, total watts) logging every 2 seconds has consumed 49.6MB. So at about 600MB per year I won’t even have to bother trimming old data from the DB for decades.

Updating these sensors every 10 seconds with an average for display in HA has bloated the HA database in the same time period by about 4GB.

1 Like

Thanks for sharing this! Very interesting to see how little space the data takes up in MySQL vs time series. Do you mind if I link to your example from the repository?

No problem. Since there’s no error checking and the code is pretty messy, it should just be used as an example. :slight_smile:

What about using InfluxDB - it’s a time series DB and I believe that data is stored in much better and useful format. Also I’m curious why data is not stored in much useful format in MySQL - those jsons are completely unmanageable. Also it could be very nice if there is an option for different retention period for different kind of sensors / events.

New to Home Assistant, and 2 years user of Domoticz.
My 2 year old db of Domoticz with 2 years of temperature sensors, utilities, rain, wind, etc… 30mb…
Like you said, HA grows really really fast, even after disabling lots of logs and history.
Not to mention that Domoticz makes it really simple to log the sensors and view the data.
Thinking about keeping my Domoticz install to only log the sensors because HA just isn’t practical for it in my view.