SQL Sensor Component UnboundLocalError when no data returned.

[B1tMaster]

Make sure you are running the latest version of Home Assistant before reporting an issue.

You should only file an issue if you found a bug. Feature and enhancement requests should go in the Feature Requests section of our community forum:

Home Assistant release (hass --version):

version 0.64.2
Python release (python3 --version):
bash-4.4# python3 --version
Python 3.6.3

Component/platform:

SQL Sensor

File "/usr/lib/python3.6/site-packages/homeassistant/components/sensor/sql.py", line 137, in update
if data is None:
UnboundLocalError: local variable 'data' referenced before assignment

Description of problem:

When SQL sensor returns NO data, the UnboundLocalError seem to happen.

i suspect there maybe multiple problems here.

1. I can't prove it, but it seems that recorder does not write to MariaDB immediately. I see no new entries there for quite some time, even though new wind data arrives every 5 mins.
   what is very strange is that my sensors on HA GUI are updating.. Both the original Wunderground sensor and my SQL sensor which is based on wunderground sensors. They update in the GUI, which leads me to believe that data is coming and sqlalchemy maybe caching data in memory and does not flush it to MariaDB for some time.. I am not sure about it, but would like to find out if this is actually the case. I had a discussion on this topic on the forum and it's really strange. Maybe recorder is losing some data and does not write it to DB? But that may cause the real issue that I am reporting now.

My discussion on the potential issue with a recorder to MariaDB here:
https://community.home-assistant.io/t/how-often-recorder-flush-data-to-database-seem-like-a-significant-delay/45160/3

2. When my SQL sensor does a query against database (basically selecting wind data for the last 20 mins and trying to average it out, no data may come back.. if it was not written?
   And then this SQL sensor error potentially happening.

The SQL queries I am running:

--Average Wind over last 20 mins
select avg(state) as avr_wind_20m from states
where  entity_id = 'sensor.pws_wind_kph' and state != '0' and created >= ADDDATE( UTC_TIMESTAMP(), INTERVAL -20 minute)
group by entity_id

--Average Gust over last 20 mins
select avg(state) avg_wind_gust_20m from states
where  entity_id = 'sensor.pws_wind_gust_kph' and state != '0' and created >= ADDDATE( UTC_TIMESTAMP(), INTERVAL -20 minute)
group by entity_id

This is the SQL sensor I have:

- platform: sql
  db_url: !secret mdb_url
  queries:
    - name: Avr_Wnd
      query: 'select avg(state) as avr_wind_20m from states where  entity_id = "sensor.pws_wind_kph" and state != "0" and created >= ADDDATE( UTC_TIMESTAMP(), INTERVAL -20 minute) group by entity_id'
      column: 'avr_wind_20m'
      unit_of_measurement: kph

    - name: Avr_Wnd_Gust
      query: 'select avg(state) as avr_wind_gust_20m from states where  entity_id = "sensor.pws_wind_gust_kph" and state != "0" and created >= ADDDATE( UTC_TIMESTAMP(), INTERVAL -20 minute) group by entity_id'
      column: 'avr_wind_gust_20m'
      unit_of_measurement: kph

Expected:

empty or None values should be handled by the SQL sensor.

Problem-relevant configuration.yaml entries and steps to reproduce:

- platform: sql
  db_url: !secret mdb_url
  queries:
    - name: Avr_Wnd
      query: 'select avg(state) as avr_wind_20m from states where  entity_id = "sensor.pws_wind_kph" and state != "0" and created >= ADDDATE( UTC_TIMESTAMP(), INTERVAL -20 minute) group by entity_id'
      column: 'avr_wind_20m'
      unit_of_measurement: kph

    - name: Avr_Wnd_Gust
      query: 'select avg(state) as avr_wind_gust_20m from states where  entity_id = "sensor.pws_wind_gust_kph" and state != "0" and created >= ADDDATE( UTC_TIMESTAMP(), INTERVAL -20 minute) group by entity_id'
      column: 'avr_wind_gust_20m'
      unit_of_measurement: kph

- platform: wunderground
  api_key: !secret wunder_api_key
  pws_id: !secret  wunder_station_id
  monitored_conditions:
    - wind_kph
    - wind_dir
    - wind_gust_kph
    - pressure_trend
    - wind_string
    - weather_1h

Traceback (if applicable):

018-03-03 13:05:19 INFO (MainThread) [homeassistant.core] Bus:Handling <Event state_changed[L]: entity_id=sensor.pws_wind_string, old_state=<state sensor.pws_wind_string=Calm; attribution=Data provided by the WUnderground weather service, date=Last Updated on March 3, 12:59 PM +08, friendly_name=Wind Summary, icon=mdi:weather-windy @ 2018-03-03T12:44:40.823798+08:00>, new_state=<state sensor.pws_wind_string=From the North at 6.5 MPH Gusting to 10.1 MPH; attribution=Data provided by the WUnderground weather service, date=Last Updated on March 3, 1:04 PM +08, friendly_name=Wind Summary, icon=mdi:weather-windy @ 2018-03-03T13:05:19.811356+08:00>>
2018-03-03 13:05:19 ERROR (MainThread) [homeassistant.helpers.entity] Update for sensor.avr_wnd fails
Traceback (most recent call last):
  File "/usr/lib/python3.6/site-packages/homeassistant/helpers/entity.py", line 204, in async_update_ha_state
    yield from self.async_device_update()
  File "/usr/lib/python3.6/site-packages/homeassistant/helpers/entity.py", line 327, in async_device_update
    yield from self.hass.async_add_job(self.update)
  File "/usr/lib/python3.6/asyncio/futures.py", line 332, in __iter__
    yield self  # This tells Task to wait for completion.
  File "/usr/lib/python3.6/asyncio/tasks.py", line 250, in _wakeup
    future.result()
  File "/usr/lib/python3.6/asyncio/futures.py", line 245, in result
    raise self._exception
  File "/usr/lib/python3.6/concurrent/futures/thread.py", line 56, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/usr/lib/python3.6/site-packages/homeassistant/components/sensor/sql.py", line 137, in update
    if data is None:
UnboundLocalError: local variable 'data' referenced before assignment
2018-03-03 13:05:19 INFO (MainThread) [homeassistant.core] Bus:Handling <Event system_log_event[L]: timestamp=1520053519.9030633, level=ERROR, message=Update for sensor.avr_wnd fails, exception=Traceback (most recent call last):
  File "/usr/lib/python3.6/site-packages/homeassistant/helpers/entity.py", line 204, in async_update_ha_state
    yield from self.async_device_update()
  File "/usr/lib/python3.6/site-packages/homeassistant/helpers/entity.py", line 327, in async_device_update
    yield from self.hass.async_add_job(self.update)
  File "/usr/lib/python3.6/asyncio/futures.py", line 332, in __iter__
    yield self  # This tells Task to wait for completion.
  File "/usr/lib/python3.6/asyncio/tasks.py", line 250, in _wakeup
    future.result()
  File "/usr/lib/python3.6/asyncio/futures.py", line 245, in result
    raise self._exception
  File "/usr/lib/python3.6/concurrent/futures/thread.py", line 56, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/usr/lib/python3.6/site-packages/homeassistant/components/sensor/sql.py", line 137, in update
    if data is None:
UnboundLocalError: local variable 'data' referenced before assignment
, source=components/sensor/sql.py>
2018-03-03 13:05:20 INFO (MainThread) [homeassistant.core] Bus:Handling <Event state_changed[L]: entity_id=sensor.avr_wnd_gust, old_state=<state sensor.avr_wnd_gust=6 @ 2018-03-03T13:04:52.159797+08:00>, new_state=<state sensor.avr_wnd_gust=16.3; avr_wind_gust_20m=16.3, unit_of_measurement=kph, friendly_name=Avr Wnd Gust @ 2018-03-03T13:05:20.020915+08:00>>

Additional info:

[dgomes]

I've fixed the error in the logs only.

We use an ORM (sqlalchemy) that can do some caching between python and the actual database.

The issue at hand is clearly that your SQL query returns no results. With the fix, it will return None.

[B1tMaster]

@dgomes Thank you very much for replying and fixing.. What did you mean fixed in logs only?
Do you mean you just don't print this error in logs anymore?

Is there a way to test SQL queries quickly against SQLAlchemy cache rather than directly against MariaDB? (without writing python code ).

What is a general problem with query that return no results? I guess some queries will return results sometimes and sometimes will not. Do we need to make sure that query always return results for SQL sensor?

What I am basically trying to do is to get the avg numerical value of a sensor for the last 20 mins. If SQLAlchemy does not flush this data to DB quick enough, does that means that my query will ALWAYS be empty and it is not possible to use SQL sensor in the way I intent?

--Average Wind over last 20 mins
select avg(state)  from states
where  entity_id = 'sensor.pws_wind_kph' and state != '0' and created >= ADDDATE( UTC_TIMESTAMP(), INTERVAL -20 minute)
group by entity_id

What are the caching parameters at the moment? Do you configure to buffer by memory size or by time? ie: what are the approximate rules that guide when it will flush data to DB?

Is there a command-line tool I could use to:
a. View content of SQLAlchemy cache
b. Force SQLAlchemy to flush cache to DB without stoping HA? That would be helpful for debugging.

Is moving MariaDB to external location like AWS cloud, be a bad idea? Since it seem SQLAlchemy maintain cache locally inside the container running on Pi and would have to talk to a remote database.

[dgomes]

@B1tMaster SQL sensor was not intended as a tool to query HASS internals, but to monitor other databases, so it's not except that this use case would work.

What I fixed: it will no longer raise an exception and show an error message (it will return None and show a warning in the logs). This means that it is now acceptable to have queries which return no results.

All your questions about SQLalchemy are out of scope of HASS, they are internal to SQLalchemy. In HASS code we are actually flushing things quite fast.

To solve your use case: there is currently a PR for a history_average sensor (#10717) and the filter_sensor was recently accepted for next version (but is not currently time bound).