I have been trying to convert an SQL Sensor returned value to a rounded one (2 decimals) and nothing seems to be working. It gives me the correct value, but with too many decimals.
The current config is:
platform: sql
db_url: db_link
scan_interval: 600
queries:
- name: Energy Today
query: “select max(state)-min(state) as value from states where entity_id=‘sensor.energy_total’ and date(last_changed) = date(date(now()));”
column: ‘value’
value_template: “{{ value | round(2) }}”
unit_of_measurement: ‘kWh’
I tried changing value from the value_template parameter to (value), value_json.value, even to specific name of the sensor, but no matter what I do, the error is the same: TypeError: the JSON object must be str, bytes or bytearray, not ‘float’
sql: Error on device update!
Traceback (most recent call last):
File “/usr/local/lib/python3.6/dist-packages/homeassistant/helpers/entity_platform.py”, line 244, in _async_add_entity
await entity.async_device_update(warning=False)
File “/usr/local/lib/python3.6/dist-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/concurrent/futures/thread.py”, line 56, in run
result = self.fn(*self.args, **self.kwargs)
File “/usr/local/lib/python3.6/dist-packages/homeassistant/components/sensor/sql.py”, line 157, in update
data, None)
File “/usr/local/lib/python3.6/dist-packages/homeassistant/helpers/template.py”, line 161, in async_render_with_possible_json_value
variables[‘value_json’] = json.loads(value)
File “/usr/lib/python3.6/json/init.py”, line 348, in loads
‘not {!r}’.format(s.class.name))
TypeError: the JSON object must be str, bytes or bytearray, not ‘float’
Ok. So for now I was able to “trick” the result by altering the SQL command and introducing ROUND(x,2) in the SQL query. Hopefully the value_template method will work soon.
I had the same problem.
The value_template is validated using homeassistant.helpers.template.Template.async_render_with_possible_json_value() passing the value in the selected column for parsing.
The thing is, the parsing process includes creating a json object with the passed value, as you can see in your error, it expects the passed value to be either str, bytes or bytearray and your value is float.
I had the same problem when I tried to pass a Integer value.
If you change your query to to cast the value as string it should work.
Maybe this should do the trick:
select CAST(max(state)-min(state) AS CHAR CHARACTER SET utf8) as value from states where entity_id=‘sensor.energy_total’ and date(last_changed) = date(date(now()))