How to disable SQLAlchemy´s lazy loading?

user_9295414 0
Viewed 513

I´m developing a python app which uses Flask-SQLAlchemy and it´s ORM Mapper.
I´ve got two tables with a Many To One relation. The main problem is that I want to load the content of both Objects with one join query and not later, when I access the object of the child table. I already tried to get these behavior by using the joinedload option like this:

Event.query.filter(Event.timestamp == day)
.join(Event.user)
.options(joinedload(Event.user))
.all()

Also tried to set the lazy="joined" attribute in the entity-class for the relationship, which caused no difference.

The SQL Query looks right and the join is correctly generated, but if I access the user attribute later, another join query is sent. With other strategies it´s also not working, like the contains_eager option.
So the expected behavior would be, to save all information on the first load and don´t execute a query later on.

All SQLAlchemy options are default, except the DATABASE_URI. Is there any global option to disable this behavior or to override the default?

The Entities are the following:

class Event(db.Model):
    __tablename__ = "event"
    __table_args__ = (
        db.ForeignKeyConstraint(
            ["username", "userfirstname"], ["users.name", "users.firstname"]
        ),
    )
    timestamp = db.Column(db.Date, primary_key=True, index=True)
    username= db.Column(db.String, primary_key=True)
    userfirstname= db.Column(db.String, primary_key=True)
    ...

    user = db.relationship("UserEntity")

class UserEntity(db.Model):
    __tablename__ = "users"
    name= db.Column(db.String, primary_key=True)
    firstname= db.Column(db.String, primary_key=True)
    ...
sqlalchemy python
2 Answers

try this

class Event(db.Model):
    __tablename__ = "event"
    __table_args__ = (
        db.ForeignKeyConstraint(
            ["username", "userfirstname"], ["users.name", "users.firstname"]
        ),
    )
    timestamp = db.Column(db.Date, primary_key=True, index=True)
    username= db.Column(db.String, primary_key=True)
    userfirstname= db.Column(db.String, primary_key=True)
    user = db.relationship("UserEntity", back_populates="events")
    user_id = Column(Integer, ForeignKey('user.id'))
    

class UserEntity(db.Model):
    __tablename__ = "users"
    name= db.Column(db.String, primary_key=True)
    firstname= db.Column(db.String, primary_key=True)
    events = relationship(MyOtherClass, lazy='joined')

from sqlalchemy docs:

joined applies a JOIN to the given SELECT statement so that related rows are loaded in the same result set. Joined eager loading is detailed at Joined Eager Loading.
user_34590640

I´ve found the mistake. The basic idea of disabling lazyloading was right and worked fine. I just messed up the object refs on the validation step and destroyed the SQLAlchemy background logic.

user_92954140
Related