8000 Support for queries with no results (fix for #12856) by dgomes · Pull Request #12888 · home-assistant/core · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

Support for queries with no results (fix for #12856) #12888

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 6 commits into from
Mar 6, 2018
Merged

Support for queries with no results (fix for #12856) #12888

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
6 commits
Select commit Hold shift + click to select a range
b3fc007
Addresses issue #12856
dgomes Mar 3, 2018
9447d75
error -> warning
dgomes Mar 3, 2018
97755a2
added edge case and test
dgomes Mar 4, 2018
ab9a842
uff uff
dgomes Mar 4, 2018
17c1b9f
Added SELECT validation
dgomes Mar 5, 2018
09ede3d
Improved tests
dgomes Mar 6, 2018
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
24 changes: 17 additions & 7 deletions homeassistant/components/sensor/sql.py
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -24,9 +24,17 @@
CONF_QUERY = 'query'
CONF_COLUMN_NAME = 'column'


def validate_sql_select(value):
"""Validate that value is a SQL SELECT query."""
if not value.lstrip().lower().startswith('select'):
raise vol.Invalid('Only SELECT queries allowed')
return value


_QUERY_SCHEME = vol.Schema({
vol.Required(CONF_NAME): cv.string,
vol.Required(CONF_QUERY): cv.string,
vol.Required(CONF_QUERY): vol.All(cv.string, validate_sql_select),
vol.Required(CONF_COLUMN_NAME): cv.string,
vol.Optional(CONF_UNIT_OF_MEASUREMENT): cv.string,
vol.Optional(CONF_VALUE_TEMPLATE): cv.template,
Expand Down Expand Up @@ -129,14 +137,16 @@ def update(self):
finally:
sess.close()

if not result.returns_rows or result.rowcount == 0:
10000
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

returns_rows -> I would expect this to be validated in the PLATFORM_SCHEMA by checking if the query starts with SELECT ?

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

this is me being extra careful not to raise any exception :)

http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=returns_rows#sqlalchemy.engine.ResultProxy.returns_rows

How would I validade the query string using voluptuous ?

  • Should I create a new "ensure_sql_query" in home-assistant/homeassistant/helpers/config_validation.py ?

Copy link
Member
@balloob balloob Mar 5, 2018 •
edited
Loading

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

No need to add things to config validation if only used once. You can add a one off validator to this file and use that.

def validate_sql_select(value):
    if not value.lstrip().lower().startswith('select'):
        raise vol.Invalid('Only select queries allowed')
    return value

vol.Schema({
  'query': vol.All(cv.string, validate_sql_select)
})

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

done!

Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

So now we can remove the returns_rows check ?

Copy link
Contributor Author
@dgomes dgomes Mar 6, 2018 •
edited
Loading

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

whats is wrong with checking returns_rows ?

_LOGGER.warning("%s returned no results", self._query)
self._state = None
self._attributes = {}
return

for res in result:
_LOGGER.debug(res.items())
_LOGGER.debug("result = %s", res.items())
data = res[self._column_name]
self._attributes = {k: str(v) for k, v in res.items()}

if data is None:
_LOGGER.error("%s returned no results", self._query)
return
self._attributes = {k: v for k, v in res.items()}

if self._template is not None:
self._state = self._template.async_render_with_possible_json_value(
Expand Down
26 changes: 26 additions & 0 deletions tests/components/sensor/test_sql.py
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,7 +1,11 @@
"""The test for the sql sensor platform."""
import unittest
import pytest
import voluptuous as vol

from homeassistant.components.sensor.sql import validate_sql_select
from homeassistant.setup import setup_component
from homeassistant.const import STATE_UNKNOWN

from tests.common import get_test_home_assistant

Expand Down Expand Up @@ -35,3 +39,25 @@ def test_query(self):

state = self.hass.states.get('sensor.count_tables')
self.assertEqual(state.state, '0')

def test_invalid_query(self):
"""Test the SQL sensor for invalid queries."""
with pytest.raises(vol.Invalid):
validate_sql_select("DROP TABLE *")

config = {
'sensor': {
'platform': 'sql',
'db_url': 'sqlite://',
'queries': [{
'name': 'count_tables',
'query': 'SELECT * value FROM sqlite_master;',
'column': 'value',
}]
}
}

assert setup_component(self.hass, 'sensor', config)

state = self.hass.states.get('sensor.count_tables')
self.assertEqual(state.state, STATE_UNKNOWN)
0