Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can SQLAlchemy eager/joined loads be suppressed once set up?

I've got a case where most of the time the relationships between objects was such that pre-configuring an eager (joined) load on the relationship made sense. However now I've got a situation where I really don't want the eager load to be done.

Should I be removing the joined load from the relationship and changing all relevant queries to join at the query location (ick), or is there some way to suppress an eager load in a query once it is set up?

Below is an example where eager loading has been set up on the User->Address relationship. Can the query at the end of the program be configured to NOT eager load?

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
import sqlalchemy.orm as orm

##Set up SQLAlchemy for declarative use with Sqlite...
engine = sa.create_engine("sqlite://", echo = True)
DeclarativeBase = declarative_base()
Session = orm.sessionmaker(bind = engine)

class User(DeclarativeBase):
    __tablename__ = "users"
    id = sa.Column(sa.Integer, primary_key = True, autoincrement = True)
    name = sa.Column(sa.String, unique = True)
    addresses = orm.relationship("Address",
                                 lazy = "joined", #EAGER LOAD CONFIG IS HERE
                                 )
    def __init__(self, Name):
        self.name = Name

class Address(DeclarativeBase):
    __tablename__ = "addresses"
    id = sa.Column(sa.Integer, primary_key = True, autoincrement = True)
    address = sa.Column(sa.String, unique = True)
    FK_user = sa.Column(sa.Integer, sa.ForeignKey("users.id"))
    def __init__(self, Email):
        self.address = Email

##Generate data tables...
DeclarativeBase.metadata.create_all(engine)

##Add some data...
joe = User("Joe")
joe.addresses = [Address("[email protected]"),
                 Address("[email protected]")]
s1 = Session()
s1.add(joe)
s1.commit()

## Access the data for the demo...
s2 = Session()

#How to suppress the eager load (auto-join) in the query below?
joe = s2.query(User).filter_by(name = "Joe").one() # <-- HERE?
for addr in joe.addresses:
    print addr.address
like image 921
Russ Avatar asked Jan 05 '11 03:01

Russ


People also ask

What is joined eager loading?

Joined eager loading is the most fundamental style of eager loading in the ORM. It works by connecting a JOIN (by default a LEFT OUTER join) to the SELECT statement emitted by a Query and populates the target scalar/collection from the same result set as that of the parent.

What is eager load?

Eager loading is the process whereby a query for one type of entity also loads related entities as part of the query. Eager loading is achieved by the use of the Include method. It means that requesting related data be returned along with query results from the database.

What is _sa_instance_state in SQLAlchemy?

_sa_instance_state is a non-database-persisted value used by SQLAlchemy internally (it refers to the InstanceState for the instance.

What is lazy dynamic SQLAlchemy?

lazy = 'dynamic': When querying with lazy = 'dynamic', however, a separate query gets generated for the related object. If you use the same query as 'select', it will return: You can see that it returns a sqlalchemy object instead of the city objects.


1 Answers

You may override eagerness of properties on query-by-query basis, as far as I remember. Will this work?

from sqlalchemy.orm import lazyload
joe = (s2.query(User)
    .options(lazyload('addresses'))
    .filter_by(name = "Joe").one())
for addr in joe.addresses:
    print addr.address

See the docs.

like image 167
Pavel Repin Avatar answered Oct 13 '22 16:10

Pavel Repin