Using Shell Command for MySql Commands

[CodeNoMore]

I am using MariaDB and have some custom sensors that write to a second database contained in the MariaDB. The purge function in recorder does not affect my second database unfortunately and only works on the HA created one so my second database continues to grow in size. I was attempting to use the shell command to execute a MySQL command to delete older data in my secondary database and tie it to an automation to delete on a regular schedule. The issue I am having is the shell command for MySQL does not work
.
This is what I am using.
shell_command:
purge_testdb: DELETE FROM DHT22_Test.Temp_Humidity WHERE Date < now() - interval 5 Day; | mysql://user:password@core-mariadb/homeassistant?charset=utf8mb4

The MySQL command itself works correctly when executed within phpMyAdmin so that is OK. When I execute the shell command from the Developer Tools the HA log shows an error return code:2 on the command.

Any help and insight would be appreciated, stuck at this point.

Thanks

[koying (Chris B)]

That won’t work.
If you go the shell command way, you’ll need the mysql command line utility.
If you use a docker installation (all besides “core”), the mysql CLI is not available in the image.

The only forward I see would be to create a python script that do the DELETE via sqlalchemy, which is the python component used by HA to handle SQL.

Python Scripts

[CodeNoMore]

I am using HAOS not a docker install.
Here is the python script I am attempting to get to work. It does not error but does not alter the database either.

My configuration.yaml:

shell_command:
   purge_testdb: "python3 /config/python_scripts/purge_testdb.py"

My /config/python_scripts/purge_testdb.py

python script to delete old data from database

import MySQLdb

database = MySQLdb.connect("core-mariadb","user","password","homeassistant")

cursor = database.cursor()

query = "DELETE FROM DHT22_Test.Temp_Humidity WHERE Date < NOW() - INTERVAL 5 DAY;"

cursor.execute(query)

database.close()

Is there something wrong with this python script or did I miss something elsewhere?

Thanks for any help and insight.

[ondras12345 (Ondra)]

@CodeNoMore

I am using HAOS not a docker install.

Home Assistant OS uses docker containers under the hood.

Test your python script by starting it manually (e.g. using the SSH & Web terminal add-on) to make sure it is working properly. shell_command does not show error messages.

[CodeNoMore]

I have the HA logger enabled and set to debug and I intentionally set the MySQL command to have an error and the logger showed a MySQL syntax error so it seems to be checking somethings but maybe not too deep in python.

I did try the python interactive mode in the SSH & Web Terminal but it gave an error on the
import MySQLdb
command which is the first command in the script so it does not seem to be finding that module.
Not sure.

I have to admit I am struggling here and not sure how to get past these issues at the moment.

Thanks

[VDRainer]

@CodeNoMore

so it does not seem to be finding that module

You need to test your command inside the HA container.

SSH'ing from a command line sensor or shell command

Great, this is realy a guide we’ve been missing. As an alternative, without portainer, it’s possible in the community addon-ssh to open a shell in the HA container if the protection mode is disabled (addon needs to be restarted). [Auswahl_418] [Auswahl_419]

[CodeNoMore]

@VDRainer

You need to test your command inside the HA container.

Great, this is realy a guide we’ve been missing. As an alternative, without portainer, it’s possible in the community addon-ssh to open a shell in the HA container if the protection mode is disabled (addon needs to be restarted). [Auswahl_418] [Auswahl_419]

Thanks for the code tip. Now at least python is finding the files in interactive mode.

I have run the script and it all seems ok except when the query is sent to mariadb then a return code of 1 is returned after the query execution. I have not been able to determine what this number represents in terms of an error and the database is not modified as it should be.
I have tried similar but different queries and none modify the database with the same or sometimes a different error code so I am not sure if it is a permissions issue or something else. I am logging in to the database as a user that is setup with full permissions so it is still a quandary why it is not able to delete old data from the secondary database…

Thanks.

[VDRainer]

@CodeNoMore

then a return code of 1 is returned after the query execution

This is the number of rows that will be deleted, but you are missing the database.commit() before the database.close().

[CodeNoMore]

@VDRainer

This is the number of rows that will be deleted, but you are missing the database.commit() before the database.close().

Thank you for an explanation of what that return number is as that clarifies things and the missing line of code I need to make it all work.
Also thank you to others that provided input and direction to help with this issue.

It works now as desired.

Here is the complete working code to help others that may want to do this.

My configuration.yaml:

shell_command:
   purge_testdb: "python3 /config/python_scripts/purge_testdb.py"

My /config/python_scripts/purge_testdb.py

import MySQLdb

database = MySQLdb.connect("core-mariadb","user","password","homeassistant")

cursor = database.cursor()

query = "DELETE FROM DHT22_Test.Temp_Humidity WHERE Date < NOW() - INTERVAL 5 DAY;"

cursor.execute(query)
database.commit()

database.close()

[splinter]

Hello.
I have Home Assistant 2021.10.6
I would like to save data in a personal database in MariaDB. I tried this solution but it doesn’t work. I started by trying your example; this is the script “prova.py”:

import mariadb

conn = mariadb.connect(
    user="utente",
    password="password",
    host="192.168.1.1",
    database="shelly")

cur = conn.cursor()

cur.execute("DELETE FROM realtime")

conn.commit()
conn.close()

In configuration.yaml I have the following configuration:

shell_command:
  my_first_shell_command: "python3 /config/python_scripts/prova.py"

Infine ho creato il seguente script:

  my_first_shell_command_script:
    sequence:
      service: shell_command.my_first_shell_command

Nothing happens when I call the script.
I also tried to follow the VDRainer guide to run the python script from the terminal but without success.
If I run test.py from the terminal I get an import error on line 1
If before running test.py I use the command:

docker exec -it homeassistant bash

I get the following error:

I can’t figure out what i am doing different than CodeNoMore
Can you help me?

[splinter]

I understood that the problem was on the mariadb connector. I used the one from mysql and was able to run the delete query:

import MySQLdb

conn = MySQLdb.connect(
    user="utente",
    password="password",
    host="192.168.1.1",
    database="shelly")

cur = conn.cursor()

cur.execute("DELETE FROM realtime")

conn.commit()
conn.close()

but now I have another problem that is driving me crazy.
I should put a row in the database. The values I get them via json. This script works perfectly from a raspberry with python installed but I can’t get it to work here on home assistant:

import MySQLdb
import json
import requests
import time
from datetime import datetime

url = 'http://192.168.1.2/emeter/0'

conn = MySQLdb.connect(
    user="utente",
    password="password",
    host="192.168.1.1",
    database="shelly")

cur = conn.cursor()

response = json.loads(requests.get(url).text)

cur.execute("DELETE FROM realtime")

if response["power"] < 0:
  power0 = 0
else:
  power0 = response["power"]
  
query = "INSERT INTO realtime(power0,datetime) VALUES(%s,%s)"
args = (power0,datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
cur.execute(query, args)

conn.commit()
conn.close()

If I delete the insert query, I don’t get an error back so the problem is definitely there but I can’t figure out what’s wrong. Probably a syntax error. Please help me