[Python] How can I modify the sqlalchemy query object's onclause directly?

I am using SQLAlchemy==2.0.37. I am developing an enhanced mixin and one of my needs is to modify the query object before compile. I have set up an event listener like so:

@event.listens_for(Query, "before_compile", retval=True)
def before_compile(query):

# NOTE: There is more code here that is irrelevant to my question
# ...

all_froms = query.statement.get_final_froms()

for from_clause in all_froms
# NOTE: Here is where I'm stuck


I am using get_final_froms due to third-hand recommendation from the sqlalchemy maintainers. I want to modify the query statement, before execution, to include an extra onclause in a way that will be executed.

For example query_obj in:

from sqlalchemy import and_

db = ...
query_obj = db.query(Table1, Table2).join(Table2, Table2.fk == Table1.id)


would become

query_obj = db.query(Table1, Table2).join(Table2, _and(Table2.fk == Table1.id, EXTRA_CONDITION))


Any help would be appreciated. I have scoured the sqlalchemy 2.0 documentation without success.

I have tried modifying the query.statement.onclause unsuccessfully.

Continue reading...