SQLAlchemy 1.3.23 turn off before_compile with **kwargs?

[Chris Simpson]

Hello

I'd like to understand how to turn off a before_compile listener (e.g. soft delete, to include deleted items).

For example, 

I've adapted the example from the docs: https://docs.sqlalchemy.org/en/13/orm/events.html?highlight=before_compile#sqlalchemy.orm.events.QueryEvents.before_compile 

To use the field 'archived' , which works as expected.

@event.listens_for(Query, "before_compile", retval=True, bake_ok=True)

def filter_archived(query):

    for desc in query.column_descriptions:

        if desc["type"] is Person:

            entity = desc["entity"]

            query = query.filter(entity.archived == 0)

    return query


I've tried things such as:

Person.query.filter_by(archived=True).all()

But I don't understand yet where I should put such kwargs to override the before_compile events listener

Is the following the right path?

@event.listens_for(Query, "before_compile", retval=True, bake_ok=True)

def filter_archived(query, **kwargs):

    for desc in query.column_descriptions:

        if kwargs["include_archived"] is not True and desc["type"] is Person:

            entity = desc["entity"]

            query = query.filter(entity.archived == 0)

    return query

Kind regards
Chris

[Mike Bayer]

the before_compile event receives the Query object at the point it starts to turn it into a SQL construct.  When this happens, it's in response to having said something like "query.all()".   So there's no **kwarg that's specific to an operation like that.

instead, when you want to send "messages" to event handlers like before_compile(), you have to do it by altering the state of the Query itself.    Your suggestion to say "query.filter_by(archived=True)" is actually such a state change, which would be altering the "_criterion" attribute to include the "archived=True" criteria, but to consume this attribute to search for this specific criteria would be tedious and possibly error prone.

instead, you can stick with a simpler way to change the state of the Query in a way that you can detect inside your event handler easily.   Probably the simplest is to use the query.execution_options() method, where you can add arbitrary keys and values that can be read later on:

query = query.execution_options(include_archived=True)

in your handler you can check it just like thisL

def filter_archived(query):

    if not query.get_execution_options().get("include_archived", False):

        # modify the query

in SQLAlchemy 1.4 which is to be released any day now, there's a much more comprehensive event  / API for this kind of alteration, see the example at https://docs.sqlalchemy.org/en/14/_modules/examples/extending_query/filter_public.html which is the latest version of this pattern, which includes the use of execution_options to affect the state of the event handler.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d9ca992a-9075-4c1b-b4a0-32179f48b5bdn%40googlegroups.com.