I’m trying to query the mariadb with a very simple query. The code I’m using is:
import appdaemon.plugins.hass.hassapi as hass
import datetime
from urllib.parse import urlparse
from typing import List
import sqlalchemy
from sqlalchemy import create_engine, text
import mysql.connector as mariadb
""""
Run from utilities.yaml
"""
class GetSQLData(hass.Hass):
def initialize(self):
fetch_entities = True
try:
mariadb_connection = mariadb.connect(user='user_name', password='password', host='192.168.1.9', database='homeassistant')
self.log("If we made it here, the mariadb.connect command worked")
self.cursor = mariadb_connection.cursor()
self.log("Successfully connected to database {}".format(mariadb_connection))
if fetch_entities:
query = 'SELECT * FROM states WHERE entity_id = "sensor.dark_sky_temperature"'
try:
self.log("Performing query {} with cursor {}".format(query, self.cursor))
response = self.cursor.execute(query, params=None, multi=False)
self.log("Query response {} with cursor {}".format(response, self.cursor))
except:
self.log("Error with query: {}".format(query))
raise
except Exception as exc:
if isinstance(exc, ImportError):
raise RuntimeError(
"The right dependency to connect to your database is "
"missing. Please make sure that it is installed."
)
print(exc)
raise
I also downloaded a database browser, and successfully connected to the mariadb. I can write a line sql code SELECT * FROM states WHERE entity_id = "sensor.dark_sky_temperature" and successfully get data from the database.
However… when I run the above code with the exact same query, I get the following output:
2019-11-14 16:32:45.023301 INFO sql_mariadb: If we made it here, the mariadb.connect command worked
2019-11-14 16:32:45.030726 INFO sql_mariadb: Successfully connected to database <mysql.connector.connection.MySQLConnection object at 0x73a54230>
2019-11-14 16:32:45.037847 INFO sql_mariadb: Performing query SELECT * FROM states WHERE entity_id = "sensor.dark_sky_temperature" with cursor MySQLCursor: (Nothing executed yet)
2019-11-14 16:32:45.050681 INFO sql_mariadb: Query response None with cursor MySQLCursor: SELECT * FROM states WHERE entity_id = "..
Up to the last line, everything is great. And then the query comes up with None… And the query looks truncated in the cursor. There are no errors in the error log. The exact same query works as expected in the database browser (dbForge Studio - free version).
As a bit more info, if I change the query to:
SELECT * FROM states WHERE entity_id="sensor.dark_sky_temperature" AND DATE(last_changed) = "2019-11-14"
The last line of the log changes to (shows two more characters):
2019-11-14 16:49:26.299855 INFO sql_mariadb: Query response None with cursor MySQLCursor: SELECT * FROM states WHERE entity_id="se..
Not sure if that’s relevant or not…
Anyone see what I’m doing wrong?
Any help is greatly appreciated!
