Custom SQL Database integration

Configuration
1

Hello,
I am using sqlite for HA and I am having mysql/mariadb for my own databases. Now I would like homeassistant to access my own databases for automations. How can this be archieved?

Thanks in advance.

2

By setting up the recorder component… Just create a user, an empty database and grant appropriate permissions to that user on the database. Hass will create the schema and tables on first connection.

3

But this will only change the default database for HA itself. That isnt important for me which db ha is using. I think i have formulated the question wrong. My goal is that HA should access my own custom database with informations about food examples and so on. So that I could use an automation where HA will do a SELECT on my custom database and sends me the information via notification.

4

Take a look at the SQL Sensor.

5

Thanks, I have already viewed the page, but this will only get information from the homeassistant database and not from another one, or am I wrong? I am using an sqlite database for homeassistant and a “food” mysql database for my food informations and want homeassistant to get informations from the food database.

And at the SQL Sensor I would have to create a sensor for all database informations I need? Isnt it possible to trigger an automation (f.e. by time) and then HA will do a SELECT statement once.

6

You can set the db_url with the SQL sensor;

7

Your question is fine, I read it in haste. As @VDRainer said, sql sensor should do the job. If you specify a db_url, you can run any query on it.

8

alternatively if that doesn’t work you can run a commandline sensor with a myql command.

9

Thank you for your informations. The SQL Sensor looks good, but I have one problem.

If I write my bot “food” it should send my a random food title and if I dont like the food I would write food again to get a new one.
Now the sensor will sync every X seconds to get a new random food, which woud be fine, but what if I write food in less than the X seconds interval, then I would recieve the same food again.

10

You’ll need to be a little more specific about your home assistant implementation. We can’t answer questions about some random ‘bot’ that you wrote.

11

So my full question is:
I am using a telegram bot which is connected to homeassistant. Homeassistant will wait for incoming messages.
my current automation looks like this:

  - alias: Telegram Essen
hide_entity: true
initial_state: on
trigger:
  platform: event
  event_type: telegram_command
  event_data:
    command: '/food'
action:
  - service: shell_command.randomfood
  - service: telegram_bot.send_message
    data_template:
      title: 'Gericht heute zubereiten?'
      target: !secret telegram_chat_fabian
      message: ''
      disable_notification: true
      inline_keyboard:
        - 'Ja:/foodyes, Nein:/foodno'

So if i will write /food HA will execute a shell command which runs a mysql SELECT and the shell script sends me the food. I would prefer that HA itself would send me the food information instead of the script.

The shell script looks like this:

ID=$(echo "SELECT ID FROM food WHERE active='y' ORDER BY RAND() ASC LIMIT 0,1" | mysql home -u $user -p$password --disable-column-names)
link=$(echo "SELECT link FROM food WHERE ID='$ID'" | mysql home -u $user -p$password --disable-column-names)
name=$(echo "SELECT name FROM food WHERE ID='$ID'" | mysql home -u $user -p$password --disable-column-names)
duration=$(echo "SELECT duration FROM food WHERE ID='$ID'" | mysql home -u $user -p$password --disable-column-names)
proposed=$(echo "update food set proposed='$daytoday $timenow' WHERE ID='$ID'" | mysql home -u $user -p$password --disable-column-names)
echo "$name - $duration - $link"
curl -s "https://api.telegram.org/bot$telegram_bot_api/sendMessage?chat_id=$telegram_fabian&disable_web_page_preview=1&text=$name - Dauer $duration min. - $link"
12

I am understanding better now thanks. But it is bedtime. Manana.

13

Is it also possible to do an UPDATE SQL Statement with this sensor somehow?