Accessing database from a python script

Installation Home Assistant OS
1

Hi, how can I run queries on my MariaDB database from inside a Python script if I cannot import python libraries in HASS?

2

Impossible. Checkout the SQL component

3

I’m using the new SQL component, but it only allows me to get 1 value. I would need more complex queries to the DB.

4

What’s the query? I was assuming you were talking about the python scripts component in my earlier answers. Otherwise you can import sqlalchemy

5

That’s right, I want to run queries from the python scripts component, but I cannot import anything in python scripts (in HASS).

I want to get daily statistics from my energy counter so later I can work the data. For example gather all the data at the end of the day and send it somewhere.

Example Query:

select
DATE(created) Date,
ROUND(SUM(state)/1000, 1) Consumption
from states
where entity_id = ‘sensor.energy_counter’
group by Date
order by Date

6

You have a couple of options:

  1. Write a normal python script (not using the python_scripts component of course) then use a command line sensor
  2. Use the API - but some processing of the returned data would be required, perhaps a python_script could be used for the processing
  3. Try this suggestion and just use a restful_sensor
    For processing data checkout my data-detective, which you could import to do some processing.
    Please post your progress
    Cheers
2 Likes
7

Thanks robmarkcole! I went for the first option and it worked!

My configuration.yaml:

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

My /config/python_scripts/script1.py:

import MySQLdb

logfile = open("script1.log","w")

database = MySQLdb.connect("core-mariadb","MY_USER","MY_PASSWORD","homeassistant")

cursor = database.cursor()

query = """select 
DATE(created) `Day`,
ROUND(SUM(state)/1000, 1) `Consumption`
from states
where entity_id = 'sensor.energy_counter'
group by `Day`
order by `Day`"""

cursor.execute(query)

for row in cursor.fetchall():
  day = row[0]
  consumption = row[1]
  
  logfile.write("%s -> %s\n" % (day, consumption))

database.close()
logfile.close()

Cheers!

3 Likes
8

Hello, you can use this script execution to show in a card template?