Query before_compile issue

[Eric Lemoine]

Hi

I want to use the FilteredQuery pattern [1], but I am facing a problem related to SQLAlchemy not calling "before_compile" as often as I'd expect it.

Here's a simple example:

@event.listens_for(Query, "before_compile", retval=True)
def before_compile(query):
    print("XXXXX before_compiled called XXXXX")
    return query

class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    addresses = relationship("Address", back_populates="user")

class Address(Base):
    __tablename__ = "address"
    id = Column(Integer, primary_key=True)
    email = Column(String)
    user_id = Column(Integer, ForeignKey("user.id"))
    user = relationship("User", back_populates="addresses")

and then I do the following twice:

for u in Session().query(User):
    for a in u.addresses:
        print(u.name, a.email)

The first time before_compile is called twice, one call for the SELECT FROM "user" query, and another call for the SELECT FROM "address" query. But the second time before_compile is called only once. It's called for the SELECT FROM "user" query, but not for the SELECT FROM "addresses" query. Is it expected?

I am using SQLAlchemy 1.3.10.

The full test case is here: https://gist.github.com/elemoine/3fa86da54fc1195e314fa18999d05a68

[1] https://github.com/sqlalchemy/sqlalchemy/wiki/FilteredQuery

[Mike Bayer]

sure, it sounds like u.addresses is already loaded.   if you want u.addresses to emit a query every time it is called you need to use the "dynamic" loader strategy, that is, lazy="dynamic".

The full test case is here: https://gist.github.com/elemoine/3fa86da54fc1195e314fa18999d05a68

[1] https://github.com/sqlalchemy/sqlalchemy/wiki/FilteredQuery

--

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/ef750756-4a8e-4043-bd5e-873a188612ec%40googlegroups.com.

[Eric Lemoine]

The first time before_compile is called twice, one call for the SELECT FROM "user" query, and another call for the SELECT FROM "address" query. But the second time before_compile is called only once. It's called for the SELECT FROM "user" query, but not for the SELECT FROM "addresses" query. Is it expected?

I am using SQLAlchemy 1.3.10.

sure, it sounds like u.addresses is already loaded.   if you want u.addresses to emit a query every time it is called you need to use the "dynamic" loader strategy, that is, lazy="dynamic".

I think the problem is related to "baked queries". I don't have the problem with enable_baked_queries set to False in sessionmaker.

Thanks for your response.

[Mike Bayer]

oh right that too, those queries are cached.   but whatever changes you made to the query should also be cached, how come you need it to be different every time?

Thanks for your response.

--

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/6ff6bf6c-0fa6-4795-a6f7-0b35d9417a08%40googlegroups.com.

[eric.l...@gmail.com]

On Tue, Oct 22, 2019 at 5:59 PM Mike Bayer <mik...@zzzcomputing.com> wrote:

On Tue, Oct 22, 2019, at 11:58 AM, Eric Lemoine wrote:

The first time before_compile is called twice, one call for the SELECT FROM "user" query, and another call for the SELECT FROM "address" query. But the second time before_compile is called only once. It's called for the SELECT FROM "user" query, but not for the SELECT FROM "addresses" query. Is it expected?

I am using SQLAlchemy 1.3.10.

sure, it sounds like u.addresses is already loaded.   if you want u.addresses to emit a query every time it is called you need to use the "dynamic" loader strategy, that is, lazy="dynamic".

I think the problem is related to "baked queries". I don't have the problem with enable_baked_queries set to False in sessionmaker.

oh right that too, those queries are cached.   but whatever changes you made to the query should also be cached, how come you need it to be different every time?

I have HTTP queries with authenticated users associated to tenants. And I wanted to use the Filtered Query pattern to only select objects bound to the current tenant, without having to write complex queries (with explicit filters for the current tenant) in the request handling code. But that doesn't work, because of that "baked queries" issue. The Filtered Query example in the wiki page works because the filter is fixes (obj.public == True). In my case I want something like obj.tenant_id == get_tenant_id_from_request().

Thanks Mike.

--

Eric

[Mike Bayer]

it sounds like you should use a bound parameter with a lambda inside of it, there's not an explicit "on lazyload" hook at the moment.   but yeah turn off the baked queries does it too

Thanks Mike.

--

Eric

--

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/CAEE1YngiAsqqUwFJAaHZC0pj%3DU0pUtfGtEA2UPxv0toaCNcEwg%40mail.gmail.com.

[eric.l...@gmail.com]

it sounds like you should use a bound parameter with a lambda inside of it, there's not an explicit "on lazyload" hook at the moment.

Yep,  that seems to be doing the job!

[Mike Bayer]

On Tue, Oct 22, 2019, at 12:30 PM, eric.l...@gmail.com wrote:

it sounds like you should use a bound parameter with a lambda inside of it, there's not an explicit "on lazyload" hook at the moment.

Yep,  that seems to be doing the job!

that worked?  wow

:)

guess you are using a threadlocal to get the context?

--

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/CAEE1Yngbpq8s4EoJOoLQ07t852VLK0-5RGMQuzM7rREBSaGBjA%40mail.gmail.com.

[eric.l...@gmail.com]

On Wed, Oct 23, 2019 at 3:16 PM Mike Bayer <mik...@zzzcomputing.com> wrote:

On Tue, Oct 22, 2019, at 12:30 PM, eric.l...@gmail.com wrote:

it sounds like you should use a bound parameter with a lambda inside of it, there's not an explicit "on lazyload" hook at the moment.

Yep,  that seems to be doing the job!

that worked?  wow

:)

SQLAlchemy always has a solution for me. But often uneasy to find :-)

guess you are using a threadlocal to get the context?

Yes indeed.

--

Eric

[Mike Bayer]

There's a long history, going back over a decade, that issues which have existed for a long time are suddenly reported in twos, that is, within a day of each other.

A recent example is just yesterday Alembic reporting both that type comparison *does* check the length of the types, and that it is mis-documented, and literally three hours earlier a report stating that it *does not* check the length:   https://github.com/sqlalchemy/alembic/issues/613  https://github.com/sqlalchemy/alembic/issues/612

today, we have this issue, which I can confirm has existed since version 1.2 over two years ago, reported for the first time ever here, and then a day later in https://github.com/sqlalchemy/sqlalchemy/issues/4947 .

I realized I can likely have the baked loader invalidate itself if before_compile occurs.   this will allow it to work for other cached loaders too like select in loading.

will try that now.

--

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/CAEE1YnjRsi9kh-V3NtVUQL--TqbCaGPCj4V%3Drj8v2sc8bq%3DU%3Dg%40mail.gmail.com.

[eric.l...@gmail.com]

today, we have this issue, which I can confirm has existed since version 1.2 over two years ago, reported for the first time ever here, and then a day later in https://github.com/sqlalchemy/sqlalchemy/issues/4947 .

Even more funny is that this Issue uses a tenant in the provided test-case, which was exactly my use-case too!

Very good to see a proper fix for this!