arduino to mysql database server from scratch

Arduino to mysql database server from scratch code#

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. With 9 power monitor sensors sending data to HA every second, the HA database was being hammered. Here’s what I ended up doing because I could not find an acceptable solution to using the HA database as the primary source. Man, I wish I had the Python skills to fix the HA database. STM32) which would mean 3D printing a new case.

Arduino to mysql database server from scratch code#

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. 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 ( ) and define some SQL sensors in HA to pull that data for display. 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. 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. The long and short of it is that adding 5 power related sensors every second makes the Logbook unusable. Too bad, because it makes the database a liability instead of an asset. If I had the Python skills I would totally rewrite the database component however I just don’t have the knowledge. The way HA stores data as JSON strings instead of a relational database is the Achilles heel in an otherwise awesome project. Posting several sensor values to HA every second is murdering the database. Well, unfortunately it looks like I have to change my approach. Some python devs might shake their heads, but it works for me.

Sql_sh += 'from_battery = '' + entities + ''' Sql_sh += 'to_battery = '' + entities + ''' Sql_sh += 'from_net = '' + entities + ''' Sql_sh += 'pv_total = '' + entities + ''' Sql_sh = 'INSERT INTO e3dc_daily_energy(date,pv_total,house,to_net,from_net,to_battery,from_battery) ' Url = ' + hassip + ':' + hassport + '/api/states/sensor.' + entityĮntities = json_data Yesterday = datetime.now() - timedelta(days=1)ĭate_value = yesterday.strftime('%Y-%m-%d') #!/usr/bin/env pythonĭb_sh = mdb.connect (host='your_db_host_ip', user='username', passwd='passwd', db='database') The script fetches the values (last_period = yesterday) from Utility Meter sensors via HA API and inserts them in a MySQL DB. I’m using a (trial an error made) python shell script for this.