Custom SQL Database integration

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.

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.

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.

Take a look at the SQL Sensor.

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.

You can set the db_url with the SQL sensor;

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.

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

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.

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.

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"

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

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