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

Question

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

Overcurious

Answer 1

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.

Conclusion

Answer 2

You can use Query.options(raiseload('*')) or Query.enable_eagerloads(False).

Query.enable_eagerloads(False) will disable all eager loading on the query. That is, even if you put a joinedload() or something, it won't be executed.

Query.options(raiseload('*')) will install a raiseload loader on every column, making sure they're not lazily loaded: an exception is raised instead. Note that this mode is fine for development and testing environments, but may be destructive in production. Make it optional like this:

Query.options(raiseload('*') if development else defaultload([]))

also note that raiseload('*') only works for top-level relationships. It won't spread on joined entities! If you request a relationship, you have to specify it twice:

session.query(User).options(
    load_only('id'),
    joinedload(User.addresses).options(
        load_only('id'),
        raiseload('*')
    ),
    raiseload('*')
)

also, raiseload('*') only works for relationships, not columns :) For columns, use defer(..., raiseload=True)

Silverware