Is "uselist=False" imcompatible with lazy='joined' in relationship?

192 views
Skip to first unread message

niuji...@gmail.com

unread,
Oct 22, 2021, 6:45:09 AM10/22/21
to sqlalchemy
Although the official documentation is very robust and detailed,  one thing I noticed is not very clear.

When specifying arguments for a relationship like this:

class Bonus(Base):
    ....
    basis = relationship("Accomplishment", uselist=False, lazy='joined')

whenever there is "uselist=False", the eager-loading doesn't happen, regardless of setting the lazy='joined' or not.

If I load the Bonus object and expunge it from the session, and then try to access the 'basis' attribute, I will get a 'sqlalchemy.orm.exc.DetachedInstanceError'.


Is uselist=False naturally incompatible with eager-loading? If so, what is the underlying reasong? Thanks.

Mike Bayer

unread,
Oct 22, 2021, 9:52:23 AM10/22/21
to noreply-spamdigest via sqlalchemy


On Fri, Oct 22, 2021, at 6:45 AM, niuji...@gmail.com wrote:
Although the official documentation is very robust and detailed,  one thing I noticed is not very clear.

When specifying arguments for a relationship like this:

class Bonus(Base):
    ....
    basis = relationship("Accomplishment", uselist=False, lazy='joined')

whenever there is "uselist=False", the eager-loading doesn't happen, regardless of setting the lazy='joined' or not.

you would need to illustrate how you are getting that result.  Refer to the MCVE I wrote below which illustrates this usage.


If I load the Bonus object and expunge it from the session, and then try to access the 'basis' attribute, I will get a 'sqlalchemy.orm.exc.DetachedInstanceError'.

it seems likely that the object was expired for some reason.



Is uselist=False naturally incompatible with eager-loading? If so, what is the underlying reasong? Thanks.

all eager loading strategies are fully supported for all relationship configurations except for those which use the "dynamic" loader strategy.

see the comments in the example below for tips on diagnosing your issue.

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import inspect
from sqlalchemy import Integer
from sqlalchemy import select
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
Base = declarative_base()


class Bonus(Base):
    __tablename__ = 'bonus'

    id = Column(Integer, primary_key=True)

    basis = relationship("Accomplishment", uselist=False, lazy='joined')


class Accomplishment(Base):
    __tablename__ = 'accomplishment'

    id = Column(Integer, primary_key=True)
    bonus_id = Column(ForeignKey('bonus.id'))


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)
s.add(Bonus(basis=Accomplishment()))
s.commit()
s.close()

# echo=True shows SQL emitted, which includes the eager load:
# SELECT bonus.id, accomplishment_1.id AS id_1, accomplishment_1.bonus_id
# FROM bonus LEFT OUTER JOIN accomplishment AS accomplishment_1 ON bonus.id = accomplishment_1.bonus_id
bonus_w_accom = s.execute(select(Bonus)).scalar()

# we can see object was eagerly loaded like this, among other ways
assert 'basis' in bonus_w_accom.__dict__

# close the session, will detach all objects.  note we have not
# called session.rollback() or session.commit().  These will expire
# the attributes.  expire_on_commit=False will prevent commit() from doing so.
s.close()

# object is detached
assert inspect(bonus_w_accom).detached

# attribute is still there
assert bonus_w_accom.basis




--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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.

Reply all
Reply to author
Forward
0 new messages