How do I call an Insert SQL command to MariaDB addon?

Installation Home Assistant OS
1

I am using hassio with MariaDB addon.

looking for a way to automate / script a SQL “Insert” command to a tables I setup (not one of the HA tables)

Shell command did not seem to help - guess it’s not running in a docker that has mysql installed.

Any idea’s how I can make it work ?

1 Like
2

Looking for the exact same thing. I could use a Shell Command but I’d like to use something nicer. Did you figure something out, @Amir974?

3

I did…
Eventually I created a costum component I called “mysql_utility” that allows me to send up to 3 SQL actions that are executed one after the other

A sample of the configuration:

  - alias: Update Commute
    trigger:
      platform: state
      entity_id: sensor.home_to_work
    action:
      - service: mysql_utility.run_commands
        data_template:
          host : !secret ha_ip
          username : !secret mdb_username
          password : !secret mdb_password
          database : !secret mdb_database
          action1 : "delete from states where entity_id in ('sensor.home_to_work' ,'sensor.work_to_home') and state = 'unknown';"
          action2 : "Insert into commute (state_id, entity_id, state, past10minavg, past30minavg, weekday, wholehr, created) Select p.state_id, p.entity_id, p.state, (SELECT round(avg(c1.state),0) FROM states AS c1 WHERE c1.entity_id = p.entity_id and c1.state != 'unknown' and c1.created between (subtime(p.created, '0 00:10:00')) and (p.created)) AS past10minavg, (SELECT round(avg(c2.state),0) FROM states AS c2 WHERE c2.entity_id = p.entity_id and c2.state != 'unknown' and c2.created  between  (subtime(p.created, '0 00:30:00')) and (p.created)) as past30minavg, weekday (addtime(p.created, '0 03:00:00')), hour(addtime(p.created, '0 03:00:00')),  p.created From states AS p Where p.state != 'unknown' and p.entity_id in ('sensor.home_to_work' ,'sensor.work_to_home') and p.state_id > (SELECT state_id FROM commute order by created desc limit 1);"
#          action3 :

The costum component (mysql_utility.py sitting directly under \custom_components\ directory):

import asyncio
from functools import partial
import logging

import voluptuous as vol
from homeassistant.const import CONF_HOST, CONF_USERNAME, CONF_PASSWORD
import homeassistant.helpers.config_validation as cv

REQUIREMENTS = ['mysql-connector==2.1.6']

DOMAIN = "mysql_utility"
CONF_DATABASE = 'database'
CONF_ACTION1 = 'action1'
CONF_ACTION2 = 'action2'
CONF_ACTION3 = 'action3'

import mysql.connector

_LOGGER = logging.getLogger(__name__)

""" key's expected from user configuration"""
SERVICE_RUN_COMMANDS = 'run_commands'

MYSQL_UTILITY_RUN_COMMAND = vol.Schema({
    vol.Required(CONF_HOST): cv.string,
    vol.Required(CONF_USERNAME): cv.string,
    vol.Required(CONF_PASSWORD): cv.string,
    vol.Required(CONF_DATABASE): cv.string,
    vol.Required(CONF_ACTION1): cv.string,
    vol.Optional(CONF_ACTION2): cv.string,
    vol.Optional(CONF_ACTION3): cv.string
})

@asyncio.coroutine
def async_setup(hass, config):

    @asyncio.coroutine
    def run_commands(call):
       _LOGGER.debug("fakeSQL: Before commands")

       host = call.data.get(CONF_HOST)
       username = call.data.get(CONF_USERNAME)
       password = call.data.get(CONF_PASSWORD)
       database = call.data.get(CONF_DATABASE)
       action1 = call.data.get(CONF_ACTION1)
       action2 = call.data.get(CONF_ACTION2)
       action3 = call.data.get(CONF_ACTION3)

       cnx = mysql.connector.connect(user=username, password=password, host=host, database=database)
       cursor = cnx.cursor()
   
       cursor.execute(action1)
       something = cursor.lastrowid
       _LOGGER.debug("fakeSQL: After Action 1, Somthing =%s", something)

       if action2 is not None:
         cursor.execute(action2)
         something = cursor.lastrowid
         _LOGGER.debug("fakeSQL: After Action 2, Somthing =%s", something)

       if action3 is not None:
         cursor.execute(action3)
         something = cursor.lastrowid
         _LOGGER.debug("fakeSQL: After Action 3, Somthing =%s", something)

       cnx.commit()
       cursor.close()
       cnx.close()
       _LOGGER.debug("fakeSQL: Done with commands")

    hass.services.async_register(
         DOMAIN, SERVICE_RUN_COMMANDS, run_commands,
         schema=MYSQL_UTILITY_RUN_COMMAND)

    return True

Hope that helps!

3 Likes
4

Wow, that is a great solution.

Still - as I was looking for a minimal solution - I ended up creating a shell_command. It works fine, allthough I wasnt able to get it working using secrets:

sql_insert: >
  /usr/bin/mysql --host=[HOST] --user=[USER] --password=[PASSWORD] home --execute "INSERT INTO BLABLABLA...;"

The mysql client needs to be installed on the home assistant machine, of course.

5

This looks awesome. Sadly it doesnt work for me:

Error while executing automation automation.test. Service not found for call_service at pos 1: (ServiceNotFound(…), ‘Service mysql_utility.run_commands not found’)

6

I quite using Maria DB and and this custom component unfortunately so I can’t look into it at the moment.

Also I believe they’ve changed the way custom components are implemented recently which may be the cause - not sure.

1 Like
7

Hey guys. I grew sick of the fact that there was no easy way to insert data from HA to an external MySQL DB. So now I created my own custom component. Many Thanks to @Amir974!

Check it out over here:

1 Like