Hi,
I have a custom setup where a Python script fetches historical data directly from the Home Assistant database (statistics table) and returns it as a JSON string. I use a command_line sensor to pull this data into Home Assistant every hour.
Here is the sensor configuration in my configuration.yaml:
command_line:
- sensor:
name: "Sun togrid monthly"
unique_id: sun_togrid_monthly
command: "python3 /config/show_month_togrid.py"
command_timeout: 30
scan_interval: 3600
value_template: >
{{ value_json.months | length }}
json_attributes:
- months
- first_statistics
- last_statistics
When the script runs successfully, the sensor.sun_togrid_monthly states and attributes look like this in the Developer Tools:
months:
2026-05: 150.25
2026-06: 245.80
2026-07: 310.12
2026-08: 195.45
2026-09: 12.35
first_statistics: '2026-05-03T18:00:00+02:00'
last_statistics: '2026-09-03T15:00:00+02:00'
friendly_name: Sun togrid monthly
The sensor works perfectly and the dashboard updates correctly. However, when I manually query the SQLite database (home-assistant_v2.db) using SQL, I can´t find the stored data.
My question is: Where exactly does Home Assistant store this active JSON attribute data? Does a sensor like this effectively live entirely in RAM while Home Assistant is running, relying on the script to rebuild the data upon a restart, or is it permanently cached somewhere outside of the standard 10-day recorder purge cycle?
Thanks for any insights into how Home Assistant handles this under the hood!
Thanks
( Here is my card, just for info: )
type: custom:apexcharts-card
header:
show: true
title: Solar to grid monthly
show_states: true
colorize_states: true
graph_span: 1y
apex_config:
chart:
height: 200
toolbar:
show: false
plotOptions:
bar:
columnWidth: 55%
borderRadius: 2
grid:
padding:
top: -10
bottom: -10
left: 5
right: 5
xaxis:
type: datetime
labels:
format: MMM yy
yaxis:
labels:
formatter: |
EVAL:function(value) {
return Math.round(value);
}
tooltip:
x:
format: MMMM yyyy
series:
- entity: sensor.sun_togrid_monthly
name: To grid
type: column
unit: kWh
yaxis_id: kWh
color: darkgreen
show:
in_header: false
data_generator: |
const months = entity.attributes.months || {};
return Object.entries(months)
.filter(([month, value]) => value !== null)
.sort(([a], [b]) => a.localeCompare(b))
.map(([month, value]) => {
const [year, monthNumber] = month.split('-');
return [
new Date(
Number(year),
Number(monthNumber) - 1,
1
).getTime(),
Number(value)
];
});
- entity: sensor.sun_togrid_monthly
name: Last month
unit: ' kWh'
color: darkgreen
show:
in_chart: false
in_header: true
name_in_header: false
data_generator: |
const months = entity.attributes.months || {};
const entries = Object.entries(months)
.filter(([_, value]) => value !== null)
.sort(([a], [b]) => a.localeCompare(b));
if (entries.length === 0) return [];
const [lastMonth, lastValue] = entries[entries.length - 1];
return [[Date.now(), Number(lastValue)]];
card_mod:
style: |
#header {
padding-bottom: 0px !important;
display: flex !important;
justify-content: space-between !important;
align-items: center !important;
padding-bottom: 4px !important;
}
#header__title {
padding-bottom: 0 !important;
margin: 0 !important;
}
#header__states {
margin: 0 !important;
padding: 0 !important;
margin-bottom: -6px !important;
}
#states__state {
margin: 0 !important;
}