I’m working on an integration in which I would like to respond to transactions on the Home Assistant database. More specifically, I would like to be able to run a function every time a particular event is written to the database (i.e., once the event is committed to the database or at least flushed to the database session).
I’m not sure if this is even possible.
I have examined the source code of the recorder component which is responsible for writing events to the database. And I have read about sqlalchemy event listeners which allow one to hook database transaction events. So now I think I may have a plan for achieving my goal. However, there are a few pieces of my plan that I am unsure about - I’ll get to these in a moment.
But first, my plan so far is to use sqlalchemy.event.listen to hook INSERTs or COMMITs to the database using the sqlalchemy session from the recorder component thread. I think this approach should allow me to call code from a hook whenever an INSERT/COMMIT transaction contains the event that I am tracking.
Hook INSERT Transactions
from sqlalchemy.event import listen from sqlalchemy.orm import object_session from homeassistant.components.recorder.models import Events tracked_event = # This is the event whose insertion I want to track def hook(mapper, connection, db_event): session = object_session(event) event = Events.to_native(db_event) if event == tracked_event: # Do stuff with the event now that I know it has been written to the database # Register a listener for database INSERTs listen(Events, 'after_insert', hook)
Hook COMMIT Transactions
from sqlalchemy.event import listen from homeassistant.components import recorder def hook(session): # If possible, examine the session to see if tracked_event was inserted # Then do stuff with the event session = self.hass.data[recorder.DATA_INSTANCE].get_session() listen(session, 'after_commit', hook)
So this might be workable up to this point, but here are the things I’m unsure of…
Can I use the Recorder’s database session from a different thread?
The Recorder runs in its own thread and uses sqlalchemy.orm.scoping.scoped_session which is apparently thread-safe. However, the documentation makes it sound like a scoped_session won’t contain the same transaction data if it’s used by a different thread.
scoped_sessionobject by default uses [thread local storage], so that a single
Sessionis maintained for all who call upon the
scoped_sessionregistry, but only within the scope of a single thread. Callers who call upon the registry in a different thread get a
Sessioninstance that is local to that other thread.
If I’m reading this correctly, data that is flushed (but not committed) will not be available in the shared session of a different thread - it is only visible in the Recorder thread’s session. If this is the case, then I don’t think it’s possible to hook transactions made by the Recorder.
What is the scope and thread of execution for
I’ve read through a ton of sqlalchemy docs and examples, and it’s still no clearer:
- Which thread will run the function
hookmodify state outside of its local scope (like
foo = # say foo is some object that lives in the main thread def hook(mapper, connection, db_event): foo.bar = 123 listen(Events, 'after_insert', hook)
hook can’t modify state outside of its local scope, then I can’t use
sqlalchemy.event.listen to do anything interesting when a database transaction event occurs.
I want to be able to react to changes in the HA database, but I’m not sure how to do it or if it’s possible. There seems to be at least one potential solution to my problem, but I don’t know quite enough about sqlalchemy to be sure.
If anyone could shed some light on this topic, I would appreciate it. Thanks!
- Recorder component
- Recorder component source code
- SQLAlchemy Events
- How to add sqlalchemy DB events after a commit
I should mention that I am not interested in listening for events on the HA event bus. I can do that, but it is not suited to my needs because the event listeners are called before the events are written to the database.