column_property with load_only and inheritance

[Mattias Lagergren]

Hi,

I'm trying to use load_only and joinedload on a relationship model.Asset.parent. The parent relation is polymorphic and can be either Task or Project with the common Base class called Context.

        import sqlalchemy.orm
        import sqlalchemy.inspection

        entity = model.session.query(
            model.Asset
        ).options(
            sqlalchemy.orm.joinedload('parent').load_only(
                'context_type', 'name', 'link'
            )
        ).first()

        state = sqlalchemy.inspection.inspect(entity.parent)
        for attribute in state.attrs:
            is_loaded = (
                attribute.loaded_value is not
                sqlalchemy.orm.base.NO_VALUE
            )
            if is_loaded:
                print attribute.key

# Output:
id
taskid
name
context_type

The id, name and context_type is from Context. And taskid is primary key on the taskid and is a foreignkey to the context.id. As you can see "name" loads fine but "link" attribute is not loaded. The "link" column is added as a column_property to Context using a __declare_last__.

These are simplified versions of the classes:

class Context(Base):
    '''Represent a context.'''
    context_type = Column(Unicode(32), nullable=False)
   
    __mapper_args__ = {
        'polymorphic_on': context_type,
        'polymorphic_identity': 'context'
    }

    name = Column(Unicode(255), default=u'', nullable=False)

    @declared_attr
    def id(cls):
        return Column(CHAR(36), primary_key=True, default=lambda: str(uuid()))

    @classmethod
    def __declare_last__(cls):
        '''Return link expression query.'''
       
        ...

        cls.link = column_property(
            sqlalchemy.type_coerce(
                query, LinkTypeDecorator
            ).label('link')
        )

class Task(Context):
    '''Represent a task.'''

    taskid = Column(
        types.CHAR(36),
        ForeignKey('context.id'),
        primary_key=True
    )

    __mapper_args__ = {
        'polymorphic_identity': 'task'
    }


class Asset(Base):
    '''Represent an Asset.'''

    context_id = sqlalchemy.Column(
        sqlalchemy.CHAR(36), sqlalchemy.ForeignKey('context.id')
    )

    parent = relationship('Context', backref=backref('assets'))

Can you see if I'm doing something wrong?

[Mike Bayer]

does "link" load if you *dont* specify load_only? does it *ever* load?
What's a LinkTypeDecorator and what's "query"? seems like you're doing
something odd there. Can't do anything without fully working code (see
http://stackoverflow.com/help/mcve).

> parent =relationship('Context',backref=backref('assets'))

>
> |
>
> Can you see if I'm doing something wrong?
>

> --
> 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
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Mattias Lagergren]

Hi Michael, thank you for your quick response!

Yeah, it seems that I got confused about the "load_only" method - it doesn't load at all. I've tried to put together an easier example to show the problem. It seems that it is related to the joinedload.

        import sqlalchemy.orm
        import sqlalchemy.inspection

        entity = model.session.query(
            model.Asset
        ).options(
            sqlalchemy.orm.joinedload(
                'parent'

            )
        ).first()

        state = sqlalchemy.inspection.inspect(entity.parent)
        for attribute in state.attrs:
            is_loaded = (
                attribute.loaded_value is not
                sqlalchemy.orm.base.NO_VALUE
            )
            if is_loaded:
                print attribute.key

And if I omit it, it load s fine. Here is a simplified version of my model:

class Context(Base):
    '''Represent a context.'''
    context_type = Column(Unicode(32), nullable=False)
   
    __mapper_args__ = {
        'polymorphic_on': context_type,
        'polymorphic_identity': 'context'
    }

    @declared_attr
    def __tablename__(cls):
        return 'context'

    name = Column(Unicode(255), default=u'', nullable=False)

    @declared_attr
    def id(cls):
        return Column(CHAR(36), primary_key=True, default=lambda: str(uuid()))

    @classmethod
    def __declare_last__(cls):
        '''Return link expression query.'''

        # Import this module.
        from . import context
        context = aliased(context.Context.__table__)

        # My real use-case is more complicated and involves a lot of joinst to other tables, but this example reproduces the
        # issue.
        cls.link = column_property(
            sqlalchemy.select(
                [context.c.name + ' ' + context.c.context_type],
                from_obj=[context]
            ).where(
                context.c.id == cls.id
            ).label('link')

        )

class Task(Context):
    '''Represent a task.'''

    @declared_attr
    def __tablename__(cls):
        return 'task'

    @declared_attr
    def __table_args__(cls):
        return {
            'mysql_engine': 'InnoDB',
            'mysql_charset': 'utf8'

        }

    taskid = Column(
        types.CHAR(36),
        ForeignKey('context.id'),
        primary_key=True
    )

    __mapper_args__ = {
        'polymorphic_identity': 'task'
    }


class Asset(Base):
    '''Represent an Asset.'''

    @declared_attr
    def __tablename__(cls):
        return 'asset'

    @declared_attr
    def __table_args__(cls):
        return {
            'mysql_engine': 'InnoDB',
            'mysql_charset': 'utf8'

        }

    context_id = sqlalchemy.Column(
        sqlalchemy.CHAR(36), sqlalchemy.ForeignKey('context.id')
    )

    parent = relationship('Context', backref=backref('assets'))

I hope this example makes more sense. Can you see any obvious problems with my approach and why it wouldn't work?

If I add the link as a declared_attr instead it does work:

    @declared_attr
    def link(cls):
        return column_property(cls.name + ' ' + cls.context_type)

However, my real-life use-case link is more complicated and I need to do imports that would cause circular import errors if I used declared_attr.


Best regards,
Mattias L

[Mattias Lagergren]

Hi Michael,

I've been putting together a self-contained example. If you save it to a file test_model you should be able to run it:

import sqlalchemy
import sqlalchemy.orm
import sqlalchemy.inspection
import sqlalchemy.ext.declarative

DATABASE_URL = <database-url>

Base = sqlalchemy.ext.declarative.declarative_base()

class Context(Base):
    '''Represent a context.'''

    context_type = sqlalchemy.Column(
        sqlalchemy.types.Unicode(32), nullable=False

    )

    __mapper_args__ = {
        'polymorphic_on': context_type,
        'polymorphic_identity': 'context'
    }

    __tablename__ = 'context'

    name = sqlalchemy.Column(
        sqlalchemy.types.Unicode(255), default=u'', nullable=False
    )

    id = sqlalchemy.Column(
        sqlalchemy.types.CHAR(36), primary_key=True

    )

    @classmethod
    def __declare_last__(cls):
        '''Return link expression query.'''

        # Import this module.
        import test_model as context
        context = sqlalchemy.orm.aliased(context.Context.__table__)

        # My real use-case is more complicated and involves a lot of joinst to

        # other tables, but this example reproduces the issue.
        cls.link = sqlalchemy.orm.column_property(

            sqlalchemy.select(
                [context.c.name + ' ' + context.c.context_type],
                from_obj=[context]
            ).where(
                context.c.id == cls.

id
            ).label('link')

        )


class Task(Context):
    '''Represent a task.'''

    __tablename__ = 'task'

    __mapper_args__ = {
        'polymorphic_identity': 'task'
    }

    taskid = sqlalchemy.Column(
        sqlalchemy.types.CHAR(36),
        sqlalchemy.ForeignKey('context.id'),
        primary_key=True
    )

class Asset(Base):
    '''Represent an Asset.'''

    __tablename__ = 'asset'

    assetid = sqlalchemy.Column(
        sqlalchemy.types.CHAR(36), primary_key=True
    )

    context_id = sqlalchemy.Column(
        sqlalchemy.CHAR(36), sqlalchemy.ForeignKey('context.id')
    )

    parent = sqlalchemy.orm.relationship(
        'Context'
    )


if __name__ == '__main__':
    print 'Loaded attributes: '

    # Create engine.
    engine = sqlalchemy.create_engine(
        DATABASE_URL
    )
    Session = sqlalchemy.orm.sessionmaker(bind=engine)
    session = Session()

    entity = session.query(
        Asset
    ).options(
        sqlalchemy.orm.joinedload('parent')

    ).first()

    state = sqlalchemy.inspection.inspect(entity.parent)
    for attribute in state.attrs:
        is_loaded = (
            attribute.loaded_value is not
            sqlalchemy.orm.base.NO_VALUE
        )
        if is_loaded:
            print attribute.key

On Monday, November 2, 2015 at 3:16:07 PM UTC+1, Mattias Lagergren wrote:

[Mattias Lagergren]

Hi again Mike, or anyone else who has some good advice. 

I've tried a similar setup but with a relation to a sub-class to context and it seems that it doesn't have this problem when using joinedload. Any hints on how to further debug this issue would be helpful

Thanks!

Mattias L

...

[Mike Bayer]

On 11/03/2015 07:32 AM, Mattias Lagergren wrote:
> Hi Michael,
>
> I've been putting together a self-contained example. If you save it to a
> file test_model you should be able to run it:

what's the SQL output with echo=True on the engine? is the subquery for
context rendered in the SQL and is it correct?

What happens if you load just a Context object by itself?


the example here isn't exactly "self contained" because it loads data
that's not there. Also there's no need to "import test_model", "cls",
right there is the Context object.

What happens if you run the example using no inheritance at all?

It might be possible that __declare_last__() isn't appropriate here, can
you try just saying "Context.link = column_property()"... right after
you declare Context?



>
> |
>
> importsqlalchemy
> importsqlalchemy.orm
> importsqlalchemy.inspection
> importsqlalchemy.ext.declarative
>
> DATABASE_URL =<database-url>
>
> Base=sqlalchemy.ext.declarative.declarative_base()
>
>
> classContext(Base):
> '''Represent a context.'''
>
> context_type =sqlalchemy.Column(
> sqlalchemy.types.Unicode(32),nullable=False

> )
>
> __mapper_args__ ={
> 'polymorphic_on':context_type,
> 'polymorphic_identity':'context'
> }
>
> __tablename__ ='context'
>
> name =sqlalchemy.Column(
> sqlalchemy.types.Unicode(255),default=u'',nullable=False
> )
>
> id =sqlalchemy.Column(
> sqlalchemy.types.CHAR(36),primary_key=True
> )
>
> @classmethod
> def__declare_last__(cls):
> '''Return link expression query.'''
>
> # Import this module.
> importtest_model ascontext
> context =sqlalchemy.orm.aliased(context.Context.__table__)
>
> # My real use-case is more complicated and involves a lot of joinst to
> # other tables, but this example reproduces the issue.
> cls.link =sqlalchemy.orm.column_property(
> sqlalchemy.select(

> [context.c.name +' '+context.c.context_type],

> from_obj=[context]
> ).where(
> context.c.id ==cls.id

> ).label('link')
> )
>
>
> classTask(Context):

> '''Represent a task.'''
>
> __tablename__ ='task'
>
> __mapper_args__ ={
> 'polymorphic_identity':'task'
> }
>
> taskid =sqlalchemy.Column(
> sqlalchemy.types.CHAR(36),
> sqlalchemy.ForeignKey('context.id'),
> primary_key=True
> )
>
>
> classAsset(Base):
> '''Represent an Asset.'''
>
> __tablename__ ='asset'
>
> assetid =sqlalchemy.Column(
> sqlalchemy.types.CHAR(36),primary_key=True
> )
>

> context_id =sqlalchemy.Column(
> sqlalchemy.CHAR(36),sqlalchemy.ForeignKey('context.id')
> )
>

> parent =sqlalchemy.orm.relationship(
> 'Context'
> )
>
>
> if__name__ =='__main__':

> print'Loaded attributes: '
>
> # Create engine.
> engine =sqlalchemy.create_engine(
> DATABASE_URL
> )
> Session=sqlalchemy.orm.sessionmaker(bind=engine)
> session =Session()
>
> entity =session.query(
> Asset
> ).options(
> sqlalchemy.orm.joinedload('parent')
> ).first()
>

> state =sqlalchemy.inspection.inspect(entity.parent)
> forattribute instate.attrs:
> is_loaded =(
> attribute.loaded_value isnot
> sqlalchemy.orm.base.NO_VALUE
> )
> ifis_loaded:
> printattribute.key
>
> |
>
>
>
>

> On Monday, November 2, 2015 at 3:16:07 PM UTC+1, Mattias Lagergren wrote:
>
> Hi,
>
> I'm trying to use load_only and joinedload on a relationship
> model.Asset.parent. The parent relation is polymorphic and can be
> either Task or Project with the common Base class called Context.
>
> |
> importsqlalchemy.orm
> importsqlalchemy.inspection
>
> entity =model.session.query(
> model.Asset
> ).options(
> sqlalchemy.orm.joinedload('parent').load_only(
> 'context_type','name','link'
> )
> ).first()
>

> state =sqlalchemy.inspection.inspect(entity.parent)
> forattribute instate.attrs:
> is_loaded =(
> attribute.loaded_value isnot
> sqlalchemy.orm.base.NO_VALUE
> )
> ifis_loaded:
> printattribute.key
>

> # Output:
> id
> taskid
> name
> context_type
>
> |
>
> The id, name and context_type is from Context. And taskid is primary
> key on the taskid and is a foreignkey to the context.id

> <http://context.id>. As you can see "name" loads fine but "link"

> attribute is not loaded. The "link" column is added as a
> column_property to Context using a __declare_last__.
>
> These are simplified versions of the classes:
>
> |
>

> classContext(Base):
> '''Represent a context.'''
> context_type =Column(Unicode(32),nullable=False)
>

> __mapper_args__ ={
> 'polymorphic_on':context_type,
> 'polymorphic_identity':'context'
> }
>

> name =Column(Unicode(255),default=u'',nullable=False)
>
> @declared_attr
> defid(cls):
> returnColumn(CHAR(36),primary_key=True,default=lambda:str(uuid()))
>
> @classmethod
> def__declare_last__(cls):

> '''Return link expression query.'''
>
> ...
>

> cls.link =column_property(
> sqlalchemy.type_coerce(
> query,LinkTypeDecorator
> ).label('link')
> )
>
> classTask(Context):
> '''Represent a task.'''
>
> taskid =Column(
> types.CHAR(36),

> ForeignKey('context.id <http://context.id>'),

> primary_key=True
> )
>
> __mapper_args__ ={
> 'polymorphic_identity':'task'
> }
>
>

> classAsset(Base):
> '''Represent an Asset.'''
>
> context_id =sqlalchemy.Column(
> sqlalchemy.CHAR(36),sqlalchemy.ForeignKey('context.id

> <http://context.id>')
> )
>
> parent =relationship('Context',backref=backref('assets'))

>
> |
>
> Can you see if I'm doing something wrong?
>

[Mattias Lagergren]

Hi again,

I thought I should reply and update on what the issue was.

It might be possible that __declare_last__() isn't appropriate here, can
you try just saying "Context.link = column_property()"... right after
you declare Context?

This was a good advice and ultimately led me to the solution. Since __declare_last__ runs for all the subclasses as well I ended up with the link attribute on both Context and the subclasses. Changing this to limit it only on Context solved the issue for me.

Thanks!
Mattias L

On Monday, November 2, 2015 at 3:16:07 PM UTC+1, Mattias Lagergren wrote: