Can a global filter be applied at the engine level, as opposed to session?

[ScissorHill]

Hello,

My intended use case is well-described in /extending_query/filter_public.py:

Uses for this kind of recipe include tables that have "soft deleted" rows marked as "deleted" that should be skipped, rows that have access control rules that should be applied on a per-request basis, etc.

Why the example doesn't work in my case

I often use Pandas, specifically the read_sql_query to retrieve data. As I understand it, the event listener in the example applies to the Session, and read_sql_query establishes connection via the Engine.

Example (modified /extending_query/filter_public.py)

   ...
    # works for columns too
    cols = (
        sess.query(User.id, Address.id)
        .join(User.addresses)
        .order_by(User.id, Address.id)
        .all()
    )
    assert cols == [(1, 1), (1, 2), (2, 4), (5, 9)]
    
    import pandas as pd
    from pandas.util.testing import assert_frame_equal

    retreived_df = pd.read_sql_query(
        sa.select(
            User.id.label('user_id'), 
            Address.id.label('address_id')
        )
        .join(User,)
        .order_by(User.id, Address.id),
        con=engine
                     )

    target_df = pd.DataFrame([(1, 1), (1, 2), (2, 4), (5, 9)], columns=['user_id', 'address_id'])

    assert_frame_equal(retreived_df, target_df)

Stack trace

AssertionError: DataFrame are different

DataFrame shape mismatch
[left]:  (10, 2)
[right]: (4, 2)

Versions

• OS: Ubuntu 18.04
• Python: 3.6
• SQLAlchemy: 1.4.2

Additional context

While I realise that Pandas is perhaps outside the Sqlalchemy box, I feel there's good reason to make global filter work in this case as well. In my experience, the hybrid Pandas-Sqlalchemy approach is a very quick, intuitive and elegant way of retrieving data. This makes a global filter rather risky, as it's likely to be accidentally bypassed.

I did come across listeners that apply to the Engine rather than Session in the core events documentation. But it's not clear whether or how something like with_loader_criteria can be applied.

Any suggestions in this regard would be appreciated!

[zzzeek]

hey there -

with_loader_criteria() is very specific to the ORM and there are no plans for this construct to work with plain Core. the main strengths of with_loader_criteria() is that it accommodates complicated automated SQL concepts like table inheritance and relationship loading that are not otherwise capable of being intercepted and are specific to the ORM.

When you have straight Core select() objects, these are pretty simple to manipulate directly. Unlike the ORM, there are no additional relationship loader or extra SELECT statements being generated, so you only need look at the .selected_columns or .froms collection of a select() to know what its SELECTing from, and you can then apply WHERE criteria right to it. As noted, you would use an event like before_execute() to apply criteria to the SELECT.

Looking at the example though, we can see it would be very tedious to make it work with Core events because we want access to the classes such as HasPrivate and all of that. Meaning, we really do want to be able to run with_loader_criteria with pandas and IMO this should be possible. The whole point of SQLAlchemy 2.0 making ORM and Core look consistent is that in your example, you should be able to do this:

    retreived_df = pd.read_sql_query(
        sa.select(
            User.id.label('user_id'),
            Address.id.label('address_id')
        )
        .join(User,)
        .order_by(User.id, Address.id),
        con=sess
    )

that is, pass the Session right to Pandas instead of the Engine. Pandas calls .execute() on it like it would for the Connection, and returns the Result which is again the same Result it gets from connection.execute() in any case. Your event hook would get the query in terms of the mapped classes as we need here. Pandas can and should add this support which would be very easy, I took a look at https://github.com/pandas-dev/pandas/blob/master/pandas/io/sql.py#L1912 and they would need to adjust their SQLAlchemy detection code to also receive Session objects.

so to sum up:

with_loader_criteria() is highly ORM specific

the thing you are looking to do here is in fact highly ORM specific

Pandas should allow you to pass a Session, I'd send them a feature request.

[ScissorHill]

That makes sense, thank you!