Limiting queries from single-table inheritance declarative models

71 views

Skip to first unread message

Tucker Beck

unread,

Nov 28, 2016, 5:58:47 PM11/28/16

to sqlalchemy

Hello, I'm writing today about an interesting problem we ran into with our sqlalchemy based data store api.

Our schema is based on the star-schema idea where we have a large 'fact table' with lots of rows. Within that table, each row has a foreign key to a small 'dimension table' in which each row has a unique name. Thus, the type of each row can be defined by the relationship between the fact row and the dimension row.

We wanted to be able to use SQLAlchemy to add some custom functionality for the different types of rows we have in our 'fact table'. After learning about SQLAlchemy's inheritance models, I decided to see if we could support the schema that we had already devised for our project. The single-inheritance pattern seemed to fit the best, but I couldn't find a single case where someone was using a star-schema and needed the type discriminator to be derived from the foreign key to the dimension table.

Further, I found as I was digging into the mechanics of the thing that you could not create a row in the fact table that was typed by the derived class at creation time. And, you cannot limit queries from the fact table by creating the queries against the derived classes. Suppose that (using declarative base) I have the fact table represented by a model called HybridModel. This model has two derived classes HybridAlpha and HybridBeta. I would like to be able to create a new row in the table wrapped by HybridModel by calling something like HybridAlpha(**kwargs) and have the type of the new row reflect the inheritance model I've described above. Next I wanted to be able to formulate a query against one of the derived models and have it limited by the type associated with the derived class. So, calling something like `session.query(HybridAlpha).all()` would only return rows with a type associated with the HybridAlpha model.

After a lot of tinkering and experimentation, I've come up with the following solution:

https://gist.github.com/dusktreader/2658dedb8ca8e2fd941f8075c75b3c89

I would appreciate any thoughts and feedback on the matter. I'm not sure that my approach to this solution has been sound, and I would appreciate feedback.

Thanks, and keep up the great work! SQLAlchemy is just magic!

mike bayer

unread,

Nov 29, 2016, 10:17:03 AM11/29/16

to sqlal...@googlegroups.com

I'd try to do things much more simply than this. Most simply, just
assign polymorphic_identity to be the integer foreign key value.
Otherwise, you should be able to do polymorphic_on on a correlated
subquery, which itself you set up as a column_property(). There should
be no need to modify Query or anything like that.

I'm out of time today but if you need more help I can try to work up an
example later on.

>
> Thanks, and keep up the great work! SQLAlchemy is just magic!
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> 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
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

Tucker Beck

unread,

Nov 29, 2016, 5:02:05 PM11/29/16

to sqlal...@googlegroups.com

The issue with using the foreign_key integer value as the discriminator is that you won't know what that is at class declaration time. The type_name, however, you can declare as a part of the class as you would with a normal string discriminator. I'm not sure how you would do a correlated subquery for the polymorphic_on attribute. I modified the query object so that I could filter results for the derived classes. It doesn't seem like that happens automatically. Maybe I'm just not doing it right.

an email to sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy+unsubscribe@googlegroups.com>.

To post to this group, send email to sqlal...@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

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 a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscribe@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

-=Tucker A. Beck=-

Illustrious Writer
Devious Coder
Last Hope for the Free World
Also, Modest

mike bayer

unread,

Nov 29, 2016, 5:59:15 PM11/29/16

to sqlal...@googlegroups.com

On 11/29/2016 05:01 PM, Tucker Beck wrote:
> The issue with using the foreign_key integer value as the discriminator
> is that you won't know what that is at class declaration time. The
> type_name, however, you can declare as a part of the class as you would
> with a normal string discriminator. I'm not sure how you would do a
> correlated subquery for the polymorphic_on attribute. I modified the
> query object so that I could filter results for the derived classes. It
> doesn't seem like that happens automatically. Maybe I'm just not doing
> it right.

The correlated subquery will not scale as well as a direct identifier,
but here is an adaption of your test using column_property(), and you
should be able to set polymorphic_on to the select() object directly
too. There's another example of this at
http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html?highlight=polymorphic_on#sqlalchemy.orm.mapper.params.polymorphic_on.

For the "use the id" approach, you would need to query from HybridType
up front and populate the polymorphic_identity attributes after the
fact, this is a feature that is not directly supported yet but there is
a recipe to do so at
https://bitbucket.org/zzzeek/sqlalchemy/issues/2792/settable-polymorphic-identity.

from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, column_property
from sqlalchemy import (
Column, Integer, ForeignKey, Text,
select, create_engine,
)

Base = declarative_base()

class HybridType(Base):
__tablename__ = 'hybrid_types'
id = Column(Integer, primary_key=True)
name = Column(Text)

class HybridModel(Base):
__tablename__ = 'hybrids'

id = Column(Integer, primary_key=True)
name = Column(Text)
hybrid_type_id = Column(Integer, ForeignKey('hybrid_types.id'))
hybrid_type = relationship('HybridType')

hybrid_type_prop = column_property(
select([HybridType.name]).
where(HybridType.id == hybrid_type_id).
as_scalar()
)

__mapper_args__ = {
"polymorphic_on": hybrid_type_prop
}

def __repr__(self):
return "{} ({}:{})".format(type(self).__name__, self.name, self.id)

def __init__(self, **kwargs):
self.hybrid_type_name =
type(self).__mapper_args__['polymorphic_identity']
super(HybridModel, self).__init__(**kwargs)

@hybrid_property
def hybrid_type_name(self):
return self.hybrid_type.name

@hybrid_type_name.setter
def hybrid_type_name(self, value):
self.hybrid_type_id = (
select([HybridType.id]).
where(HybridType.name == value)
)

class HybridAlpha(HybridModel):
__mapper_args__ = {'polymorphic_identity': 'alpha'}

class HybridBeta(HybridModel):
__mapper_args__ = {'polymorphic_identity': 'beta'}

engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add(HybridType(name='alpha'))
session.add(HybridType(name='beta'))
session.add(HybridAlpha(name='alpha_instance'))
session.add(HybridBeta(name='beta_instance'))
print(session.query(HybridAlpha).all())

> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>
> <mailto:sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>.

> Visit this group at https://groups.google.com/group/sqlalchemy

> <https://groups.google.com/group/sqlalchemy>.

> For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
>
> --
> SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve

> <http://stackoverflow.com/help/mcve> for a full description.
> --- You received this message because you are subscribed to a topic
> in the Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/unsubscribe

> <https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/unsubscribe>.

> To unsubscribe from this group and all its topics, send an email to

> sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.

> Visit this group at https://groups.google.com/group/sqlalchemy

> <https://groups.google.com/group/sqlalchemy>.

> For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
>
>
>
> --
> -=Tucker A. Beck=-
>
> Illustrious Writer
> Devious Coder
> Last Hope for the Free World
> Also, Modest
>

> 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>.

Tucker Beck

unread,

Nov 29, 2016, 7:32:20 PM11/29/16

to sqlal...@googlegroups.com

Thanks for this! What allows the query to filter in the derived classes when using a column property instead of a hybrid property? Is it declaring the __mapper_args__ in the base class declaration instead of after it?

Also, it seems like having the hybrid_type_prop column property obviates the need for the hybrid property hybrid_type_name...that is, except for the setter. Is there a way to give a column_property a setter?

an email to sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>
<mailto:sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>>.

To post to this group, send email to sqlal...@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>
<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>>.

Visit this group at https://groups.google.com/group/sqlalchemy

<https://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.

--
SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve
<http://stackoverflow.com/help/mcve> for a full description.
--- You received this message because you are subscribed to a topic
in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/unsubscribe
<https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/unsubscribe>.

To unsubscribe from this group and all its topics, send an email to

sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>.

To post to this group, send email to sqlal...@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>.

Visit this group at https://groups.google.com/group/sqlalchemy

<https://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.

--
-=Tucker A. Beck=-

Illustrious Writer
Devious Coder
Last Hope for the Free World
Also, Modest

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

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+unsubscribe@googlegroups.com
<mailto:sqlalchemy+unsubscribe@googlegroups.com>.

To post to this group, send email to sqlal...@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

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 a topic in the Google Groups "sqlalchemy" group.

To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscribe@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

mike bayer

unread,

Nov 30, 2016, 10:20:38 AM11/30/16

to sqlal...@googlegroups.com

On 11/29/2016 07:31 PM, Tucker Beck wrote:
> Thanks for this! What allows the query to filter in the derived classes
> when using a column property instead of a hybrid property? Is it
> declaring the __mapper_args__ in the base class declaration instead of
> after it?

Yea I don't think setting __mapper_args__ after the fact on the class
does anything. Those __mapper_args__ are only for the mapper()
constructor directly, which gets called when the class is declared.

you can set the actual polymorphic_on property right now only using the
special class.__mapper__._set_polymorphic_on(<something>), that's one of
a very few setters that's available on the mapper after the fact and
even then it's not fully public API right now.

>
> Also, it seems like having the hybrid_type_prop column property obviates
> the need for the hybrid property hybrid_type_name...that is, except for
> the setter. Is there a way to give a column_property a setter?

a column_property against a SQL expression is strictly a read-only
thing, since "writing" to it makes no sense in terms of persistence.

the column_property aspect here isn't strictly necessary, the mapper
will create the property internally as a special case for the
polymorphic_on setting if not provided. the example can be re-organized
w/ your hybrid in at least two ways:

class HybridModel(Base):
# ...

@declared_attr
def __mapper_args__(cls):
return {
"polymorphic_on": cls.hybrid_type_name
}

or

class HybridModel(Base):
# ...

_hybrid_name_expr = (

select([HybridType.name]).
where(HybridType.id == hybrid_type_id).
as_scalar()
)

__mapper_args__ = {

"polymorphic_on": _hybrid_name_expr
}

# ...

@hybrid_type_name.expression
def hybrid_type_name(cls):
return cls._hybrid_name_expr

>
> On Tue, Nov 29, 2016 at 2:59 PM, mike bayer <mik...@zzzcomputing.com
> <mailto:mik...@zzzcomputing.com>> wrote:
>
>
>
> On 11/29/2016 05:01 PM, Tucker Beck wrote:
>
> The issue with using the foreign_key integer value as the
> discriminator
> is that you won't know what that is at class declaration time. The
> type_name, however, you can declare as a part of the class as
> you would
> with a normal string discriminator. I'm not sure how you would do a
> correlated subquery for the polymorphic_on attribute. I modified the
> query object so that I could filter results for the derived
> classes. It
> doesn't seem like that happens automatically. Maybe I'm just not
> doing
> it right.
>
>
> The correlated subquery will not scale as well as a direct
> identifier, but here is an adaption of your test using
> column_property(), and you should be able to set polymorphic_on to
> the select() object directly too. There's another example of this
> at
> http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html?highlight=polymorphic_on#sqlalchemy.orm.mapper.params.polymorphic_on

> <http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html?highlight=polymorphic_on#sqlalchemy.orm.mapper.params.polymorphic_on>.

>
> For the "use the id" approach, you would need to query from
> HybridType up front and populate the polymorphic_identity attributes
> after the fact, this is a feature that is not directly supported yet
> but there is a recipe to do so at
> https://bitbucket.org/zzzeek/sqlalchemy/issues/2792/settable-polymorphic-identity

> <https://bitbucket.org/zzzeek/sqlalchemy/issues/2792/settable-polymorphic-identity>.

>
>
>
> from sqlalchemy.ext.hybrid import hybrid_property
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import sessionmaker, relationship, column_property
> from sqlalchemy import (
> Column, Integer, ForeignKey, Text,
> select, create_engine,
> )
>
> Base = declarative_base()
>
>
> class HybridType(Base):
> __tablename__ = 'hybrid_types'
> id = Column(Integer, primary_key=True)
> name = Column(Text)
>
>
> class HybridModel(Base):
> __tablename__ = 'hybrids'
>
> id = Column(Integer, primary_key=True)
> name = Column(Text)
> hybrid_type_id = Column(Integer, ForeignKey('hybrid_types.id

> <http://hybrid_types.id>'))

> hybrid_type = relationship('HybridType')
>
> hybrid_type_prop = column_property(
> select([HybridType.name]).
> where(HybridType.id == hybrid_type_id).
> as_scalar()
> )
>
> __mapper_args__ = {
> "polymorphic_on": hybrid_type_prop
> }
>
> def __repr__(self):
> return "{} ({}:{})".format(type(self).__name__, self.name

> <http://self.name>, self.id <http://self.id>)

>
> def __init__(self, **kwargs):
> self.hybrid_type_name =
> type(self).__mapper_args__['polymorphic_identity']
> super(HybridModel, self).__init__(**kwargs)
>
> @hybrid_property
> def hybrid_type_name(self):

> return self.hybrid_type.name <http://self.hybrid_type.name>

>
> @hybrid_type_name.setter
> def hybrid_type_name(self, value):
> self.hybrid_type_id = (
> select([HybridType.id]).
> where(HybridType.name == value)
> )
>
>
> class HybridAlpha(HybridModel):
> __mapper_args__ = {'polymorphic_identity': 'alpha'}
>
>
> class HybridBeta(HybridModel):
> __mapper_args__ = {'polymorphic_identity': 'beta'}
>
>
> engine = create_engine('sqlite:///:memory:', echo=True)
> Base.metadata.create_all(engine)
> Session = sessionmaker(bind=engine)
> session = Session()
> session.add(HybridType(name='alpha'))
> session.add(HybridType(name='beta'))
> session.add(HybridAlpha(name='alpha_instance'))
> session.add(HybridBeta(name='beta_instance'))
> print(session.query(HybridAlpha).all())
>
>
>
> On Tue, Nov 29, 2016 at 7:16 AM, mike bayer

> <mik...@zzzcomputing.com <mailto:mik...@zzzcomputing.com>
> <mailto:mik...@zzzcomputing.com

> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com
> <mailto:sqlalchemy%252Buns...@googlegroups.com>>
> <mailto:sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com
> <mailto:sqlalchemy%252Buns...@googlegroups.com>>>.

> To post to this group, send email to

> sqlal...@googlegroups.com <mailto:sqlal...@googlegroups.com>

> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>
> <mailto:sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>>>.

> sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com
> <mailto:sqlalchemy%252Buns...@googlegroups.com>>.

> To post to this group, send email to

> sqlal...@googlegroups.com <mailto:sqlal...@googlegroups.com>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>.

> <https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>>.
>
>
>
>
>
> --
> -=Tucker A. Beck=-
>
> Illustrious Writer
> Devious Coder
> Last Hope for the Free World
> Also, Modest
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve

> <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
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>
> <mailto:sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>.

> Visit this group at https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
>
> --
> SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve
> <http://stackoverflow.com/help/mcve> for a full description.
> --- You received this message because you are subscribed to a topic
> in the Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/unsubscribe
> <https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/unsubscribe>.
> To unsubscribe from this group and all its topics, send an email to

> sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.

> Visit this group at https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
>
>
>
> --
> -=Tucker A. Beck=-
>
> Illustrious Writer
> Devious Coder
> Last Hope for the Free World
> Also, Modest
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> 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
> <mailto:sqlalchemy+...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.

Tucker Beck

unread,

Dec 3, 2016, 9:38:38 AM12/3/16

to sqlal...@googlegroups.com

This is what I ended up with, and it *seems* to work pretty well. Does my approach here pass the sniff test?

from sqlalchemy.ext.hybrid import hybrid_property

from sqlalchemy.ext.declarative import declarative_base, declared_attr

from sqlalchemy.orm import sessionmaker, relationship, column_property

from sqlalchemy import (

Column, Integer, ForeignKey, Text,

select, create_engine,

)

Base = declarative_base()

class HybridType(Base):

__tablename__ = 'hybrid_types'

id = Column(Integer, primary_key=True)

name = Column(Text)

class HybridModel(Base):

__tablename__ = 'hybrids'

hybrid_type_identity = 'base'

id = Column(Integer, primary_key=True)

name = Column(Text)

hybrid_type_id = Column(Integer, ForeignKey('hybrid_types.id'))

hybrid_type = relationship('HybridType')

@declared_attr

def __mapper_args__(cls):

return dict(

polymorphic_on=cls.hybrid_type_name,

polymorphic_identity=cls.hybrid_type_identity,

)

def __repr__(self):

return "{} ({}:{})".format(type(self).__name__, self.name, self.id)

def __init__(self, **kwargs):

self.hybrid_type_name = self.hybrid_type_identity

super(HybridModel, self).__init__(**kwargs)

@hybrid_property

def hybrid_type_name(self):

return self.hybrid_type.name

@hybrid_type_name.expression

def hybrid_type_name(cls):

return (

select([HybridType.name]).

where(HybridType.id == cls.hybrid_type_id).

as_scalar()

)

@hybrid_type_name.setter

def hybrid_type_name(self, value):

self.hybrid_type_id = (

select([HybridType.id]).

where(HybridType.name == value)

)

class HybridAlpha(HybridModel):

hybrid_type_identity = 'alpha'

class HybridBeta(HybridModel):

hybrid_type_identity = 'beta'

engine = create_engine('sqlite:///:memory:', echo=False)

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)

session = Session()

session.add(HybridType(name='base'))

session.add(HybridType(name='alpha'))

session.add(HybridType(name='beta'))

session.add(HybridModel(name='base_instance'))

session.add(HybridAlpha(name='alpha_instance'))

session.add(HybridBeta(name='beta_instance'))

print(session.query(HybridModel).all())

print(session.query(HybridAlpha).all())

print(session.query(HybridAlpha).one().hybrid_type_name)

session.query(HybridAlpha).one().hybrid_type_name = 'beta'

print(session.query(HybridBeta).all())

<mik...@zzzcomputing.com <mailto:mike_mp@zzzcomputing.com>
<mailto:mike_mp@zzzcomputing.com

an email to sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>>
<mailto:sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>>>.

To post to this group, send email to

sqlal...@googlegroups.com <mailto:sqlalchemy@googlegroups.com>

<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>>
<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>
<mailto:sqlalchemy@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>>>.

sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>>.

To post to this group, send email to

sqlal...@googlegroups.com <mailto:sqlalchemy@googlegroups.com>
<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>>.

an email to sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>
<mailto:sqlalchemy+unsubscribe@googlegroups.com

<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>>.

To post to this group, send email to sqlal...@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>
<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>>.

Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.

--
SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve
<http://stackoverflow.com/help/mcve> for a full description.
--- You received this message because you are subscribed to a topic
in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/unsubscribe
<https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/unsubscribe>.
To unsubscribe from this group and all its topics, send an email to

sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>.

To post to this group, send email to sqlal...@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>.

Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.

--
-=Tucker A. Beck=-

Illustrious Writer
Devious Coder
Last Hope for the Free World
Also, Modest

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

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+unsubscribe@googlegroups.com
<mailto:sqlalchemy+unsubscribe@googlegroups.com>.

To post to this group, send email to sqlal...@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

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 a topic in the Google Groups "sqlalchemy" group.

To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscribe@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

mike bayer

unread,

Dec 3, 2016, 7:26:06 PM12/3/16

to sqlal...@googlegroups.com

On 12/01/2016 05:29 PM, Tucker Beck wrote:
> This is what I ended up with, and it *seems* to work pretty well. Does
> my approach here pass the sniff test?

everything looks idiomatic, so if it does what you need, then it's greatg!

>
> from sqlalchemy.ext.hybrid import hybrid_property
> from sqlalchemy.ext.declarative import declarative_base, declared_attr
> from sqlalchemy.orm import sessionmaker, relationship, column_property
> from sqlalchemy import (
> Column, Integer, ForeignKey, Text,
> select, create_engine,
> )
>
> Base = declarative_base()
>
>
> class HybridType(Base):
> __tablename__ = 'hybrid_types'
> id = Column(Integer, primary_key=True)
> name = Column(Text)
>
>
> class HybridModel(Base):
> __tablename__ = 'hybrids'
> hybrid_type_identity = 'base'
>
> id = Column(Integer, primary_key=True)
> name = Column(Text)
> hybrid_type_id = Column(Integer, ForeignKey('hybrid_types.id

> <http://hybrid_types.id>'))
> hybrid_type = relationship('HybridType')
>

> @declared_attr
> def __mapper_args__(cls):
> return dict(
> polymorphic_on=cls.hybrid_type_name,
> polymorphic_identity=cls.hybrid_type_identity,
> )
>
> def __repr__(self):

> return "{} ({}:{})".format(type(self).__name__, self.name
> <http://self.name>, self.id <http://self.id>)
>
> def __init__(self, **kwargs):

> self.hybrid_type_name = self.hybrid_type_identity
> super(HybridModel, self).__init__(**kwargs)
>
> @hybrid_property
> def hybrid_type_name(self):

> return self.hybrid_type.name <http://self.hybrid_type.name>

> <mik...@zzzcomputing.com <mailto:mik...@zzzcomputing.com>
> <mailto:mik...@zzzcomputing.com

> <http://self.name>, self.id <http://self.id> <http://self.id>)

> <http://self.hybrid_type.name> <http://self.hybrid_type.name>

> <mailto:mik...@zzzcomputing.com>

> <mailto:mik...@zzzcomputing.com <mailto:mik...@zzzcomputing.com>>
> <mailto:mik...@zzzcomputing.com
> <mailto:mik...@zzzcomputing.com>
>
> <mailto:mik...@zzzcomputing.com

> sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com
> <mailto:sqlalchemy%252Buns...@googlegroups.com>>
>

> <mailto:sqlalchemy%2Bunsu...@googlegroups.com
> <mailto:sqlalchemy%252Buns...@googlegroups.com>
> <mailto:sqlalchemy%252Buns...@googlegroups.com
> <mailto:sqlalchemy%25252Bun...@googlegroups.com>>>

> <mailto:sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com
> <mailto:sqlalchemy%252Buns...@googlegroups.com>>
>
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com
> <mailto:sqlalchemy%252Buns...@googlegroups.com>

> <mailto:sqlalchemy%252Buns...@googlegroups.com
> <mailto:sqlalchemy%25252Bun...@googlegroups.com>>>>.

>
> To post to this group, send email to
> sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>

> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>
> <mailto:sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>>>>.

> sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com
> <mailto:sqlalchemy%252Buns...@googlegroups.com>>

> <mailto:sqlalchemy%2Bunsu...@googlegroups.com
> <mailto:sqlalchemy%252Buns...@googlegroups.com>
> <mailto:sqlalchemy%252Buns...@googlegroups.com
> <mailto:sqlalchemy%25252Bun...@googlegroups.com>>>.

> To post to this group, send email to
> sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>

> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>
> <mailto:sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>>>.

> <https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>
> <https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>>>.
>
>
>
>
>
> --
> -=Tucker A. Beck=-
>
> Illustrious Writer
> Devious Coder
> Last Hope for the Free World
> Also, Modest
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal,
> Complete, and
> Verifiable Example. See
> http://stackoverflow.com/help/mcve
> <http://stackoverflow.com/help/mcve>

> <http://stackoverflow.com/help/mcve
> <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

> <mailto:sqlalchemy%252Buns...@googlegroups.com>>>.

> To post to this group, send email to

> sqlal...@googlegroups.com <mailto:sqlal...@googlegroups.com>

> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>
> <mailto:sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>>>.

> sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com

> <mailto:sqlalchemy%252Buns...@googlegroups.com>>.

> To post to this group, send email to

> sqlal...@googlegroups.com <mailto:sqlal...@googlegroups.com>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>.

> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>
> <mailto:sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>.

> sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.

> Visit this group at https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
>
>
>
> --
> -=Tucker A. Beck=-
>
> Illustrious Writer
> Devious Coder
> Last Hope for the Free World
> Also, Modest
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> 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
> <mailto:sqlalchemy+...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.

Tucker Beck

unread,

Dec 27, 2016, 11:44:48 PM12/27/16

to sqlal...@googlegroups.com

Mike (or whoever else might have insight):

I used the pattern above, and it worked great until I tried use it for a really large table that I was querying and filtering by the hybrid property. Something like this:

query(HybridModel).filter_by(hybrid_type_name='alpha')

The resulting query does a seq-scan applying a sub-select to get the hybrid_type_name and then comparing it to the filter value ('alpha' in the example). Obviously, this is sub-optimal, and I want the query to do an index-scan comparing the hybrid_type_id matching the filter_value to the hybrid_type_id of each row. The table has an index on hybrid_type_id, so that query is orders of magnitude faster. Before using this revised pattern we discussed, I got the query I wanted by adding a comparator to my class that basically inverts the select used by the hybrid property expression. That worked great before using this pattern.

However, with the new pattern, the delcared_attr __mapper_args__ attempts to use the expression from the comaprator for polymorphic_on, and that query results in an integer (primary key) which won't work because we defined our polymorphic identities using the name

Do you have any ideas on how I might resolve this issue?

<mik...@zzzcomputing.com <mailto:mike_mp@zzzcomputing.com>
<mailto:mike_mp@zzzcomputing.com

<mailto:mike_mp@zzzcomputing.com>
<mailto:mike_mp@zzzcomputing.com <mailto:mike_mp@zzzcomputing.com>>
<mailto:mike_mp@zzzcomputing.com
<mailto:mike_mp@zzzcomputing.com>

<mailto:mike_mp@zzzcomputing.com

sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>>

<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com
<mailto:sqlalchemy%25252Bunsubscr...@googlegroups.com>>>

<mailto:sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>>

<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>

<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com
<mailto:sqlalchemy%25252Bunsubscr...@googlegroups.com>>>>.

To post to this group, send email to
sqlal...@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>
<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>>
<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>
<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>>>
<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>
<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>>
<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>
<mailto:sqlalchemy@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>>>>.

sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>>

<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com
<mailto:sqlalchemy%25252Bunsubscr...@googlegroups.com>>>.

To post to this group, send email to
sqlal...@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>
<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>>
<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>
<mailto:sqlalchemy@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>>>.

an email to sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>>
<mailto:sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com

<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>>>.

To post to this group, send email to

sqlal...@googlegroups.com <mailto:sqlalchemy@googlegroups.com>
<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>>
<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>
<mailto:sqlalchemy@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>>>.

sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com

<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>>.

To post to this group, send email to

sqlal...@googlegroups.com <mailto:sqlalchemy@googlegroups.com>
<mailto:sqlalchemy@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>>.

an email to sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>
<mailto:sqlalchemy+unsubscribe@googlegroups.com

<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>>.

To post to this group, send email to sqlal...@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>
<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>>.

Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.

--
SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve
<http://stackoverflow.com/help/mcve> for a full description.
--- You received this message because you are subscribed to a topic
in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/unsubscribe
<https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/unsubscribe>.
To unsubscribe from this group and all its topics, send an email to

sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>.

To post to this group, send email to sqlal...@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>.

Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.

--
-=Tucker A. Beck=-

Illustrious Writer
Devious Coder
Last Hope for the Free World
Also, Modest

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

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+unsubscribe@googlegroups.com
<mailto:sqlalchemy+unsubscribe@googlegroups.com>.

To post to this group, send email to sqlal...@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>.

Tucker Beck

unread,

Dec 28, 2016, 1:16:41 PM12/28/16

to sqlal...@googlegroups.com

Another update on this issue. It seems that the issue stems from having a declared_attr for the __tablename__. If I remove that and make it a static class attribute, things work as expected.

mike bayer

unread,

Dec 28, 2016, 5:19:58 PM12/28/16

to sqlal...@googlegroups.com

On 12/27/2016 04:36 PM, Tucker Beck wrote:
> Mike (or whoever else might have insight):
>
> I used the pattern above, and it worked great until I tried use it for a
> really large table that I was querying and filtering by the hybrid
> property. Something like this:
> query(HybridModel).filter_by(hybrid_type_name='alpha')

Well I mentioned a correlated subquery won't scale here.

>
> The resulting query does a seq-scan applying a sub-select to get the
> hybrid_type_name and then comparing it to the filter value ('alpha' in
> the example). Obviously, this is sub-optimal, and I want the query to do
> an index-scan comparing the hybrid_type_id matching the filter_value to
> the hybrid_type_id of each row. The table has an index on
> hybrid_type_id, so that query is orders of magnitude faster. Before
> using this revised pattern we discussed, I got the query I wanted by
> adding a comparator to my class that basically inverts the select used
> by the hybrid property expression. That worked great before using this
> pattern.

I'm not sure what "invert the select" looks like.

>
> However, with the new pattern, the delcared_attr __mapper_args__
> attempts to use the expression from the comaprator for polymorphic_on,
> and that query results in an integer (primary key) which won't work
> because we defined our polymorphic identities using the name

I'm not understanding this part either, can this be demonstrated with
the simple test script I gave you ?

> <http://self.name>, self.id <http://self.id> <http://self.id>)
>
> def __init__(self, **kwargs):

> self.hybrid_type_name = self.hybrid_type_identity
> super(HybridModel, self).__init__(**kwargs)
>
> @hybrid_property
> def hybrid_type_name(self):
> return self.hybrid_type.name

> <http://self.hybrid_type.name> <http://self.hybrid_type.name>

> <mik...@zzzcomputing.com <mailto:mik...@zzzcomputing.com>
> <mailto:mik...@zzzcomputing.com

> <mailto:mik...@zzzcomputing.com>

> <mailto:mik...@zzzcomputing.com <mailto:mik...@zzzcomputing.com>>
> <mailto:mik...@zzzcomputing.com
> <mailto:mik...@zzzcomputing.com>
> <mailto:mik...@zzzcomputing.com

> <mailto:mik...@zzzcomputing.com>

> <mailto:mik...@zzzcomputing.com
> <mailto:mik...@zzzcomputing.com>>
> <mailto:mik...@zzzcomputing.com
> <mailto:mik...@zzzcomputing.com>
> <mailto:mik...@zzzcomputing.com <mailto:mik...@zzzcomputing.com>>>
> <mailto:mik...@zzzcomputing.com
> <mailto:mik...@zzzcomputing.com>
> <mailto:mik...@zzzcomputing.com
> <mailto:mik...@zzzcomputing.com>>
>
> <mailto:mik...@zzzcomputing.com
> <mailto:mik...@zzzcomputing.com>
> <mailto:mik...@zzzcomputing.com

> <mailto:sqlalchemy%252Buns...@googlegroups.com
> <mailto:sqlalchemy%25252Bun...@googlegroups.com>
> <mailto:sqlalchemy%25252Bun...@googlegroups.com
> <mailto:sqlalchemy%2525252Bu...@googlegroups.com>>>>

>
> To post to this group, send email to
> sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>

> <mailto:sqlal...@googlegroups.com>>>>>.

> To post to this group, send email to
> sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>

> <mailto:sqlal...@googlegroups.com>>>>.

> <http://stackoverflow.com/help/mcve
> <http://stackoverflow.com/help/mcve>
> <http://stackoverflow.com/help/mcve
> <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

> <mailto:sqlalchemy%25252Bun...@googlegroups.com>>>>.

> To post to this group, send email to
> sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>

> <mailto:sqlal...@googlegroups.com>>>>.

> <mailto:sqlalchemy%25252Bun...@googlegroups.com>>>.

> To post to this group, send email to
> sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>

> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>
> <mailto:sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>>>.

> <mailto:sqlalchemy%252Buns...@googlegroups.com>>>.

> To post to this group, send email to

> sqlal...@googlegroups.com <mailto:sqlal...@googlegroups.com>

> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>
> <mailto:sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>>>.

> sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com

> <mailto:sqlalchemy%252Buns...@googlegroups.com>>.

> To post to this group, send email to

> sqlal...@googlegroups.com <mailto:sqlal...@googlegroups.com>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>.

> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>
> <mailto:sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>.

>
>
>
>
> --
> -=Tucker A. Beck=-
>
> Illustrious Writer
> Devious Coder
> Last Hope for the Free World
> Also, Modest
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> 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
> <mailto:sqlalchemy+...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

Tucker Beck

unread,

Dec 29, 2016, 11:09:45 AM12/29/16

to sqlal...@googlegroups.com

Mike,

Here's the solution I came up with. It shows the 'inverted select' used in the comparator vs the hybrid expression

class ModelBase(db.Model):

__abstract__ = True

def __repr__(self):

return "{} ({}:{})".format(type(self).__name__, self.name, self.id)

class HybridType(ModelBase):

__tablename__ = 'hybrid_types'

id = db.Column(db.Integer, primary_key=True)

name = db.Column(db.Text)

class HybridModel(ModelBase):

__tablename__ = 'hybrids'

id = db.Column(db.BigInteger, primary_key=True)

name = db.Column(db.Text)

hybrid_type_id = db.Column(db.Integer, db.ForeignKey('hybrid_types.id'), nullable=False)

hybrid_type = db.relationship('HybridType')

def __init__(self, *args, **kwargs):

self.hybrid_type_name = self.hybrid_type_identity

return super().__init__(*args, **kwargs)

@classproperty

def hybrid_type_identity(cls):

return inflection.underscore(cls.__name__)

@declared_attr

def __mapper_args__(cls):

return dict(

polymorphic_on=cls.hybrid_type_name_subquery(),

polymorphic_identity=cls.hybrid_type_identity,

)

@hybrid_property

def hybrid_type_name(self):

return self.hybrid_type.name

@hybrid_type_name.setter

def hybrid_type_name(self, value):

self.hybrid_type_id = (

select([HybridType.id]).

where(HybridType.name == value)

)

@hybrid_type_name.expression

def hybrid_type_name(cls):

return cls.hybrid_type_name_subquery()

@classmethod

def hybrid_type_name_subquery(cls):

return select([HybridType.name]).where(HybridType.id == cls.hybrid_type_id).as_scalar()

class HybridComparator(Comparator):

def operate(self, op, other):

return op(HybridType.hybrid_type_id, select([HybridType.id]).where(HybridType.name == other).as_scalar())

@hybrid_type_name.comparator

def hybrid_type_name(cls):

return cls.HybridComparator(cls)

class HybridAlpha(HybridModel):

pass

class HybridBeta(HybridModel):

pass

class TestHybrids:

def test_all_query(self):

db.session.add(HybridType(name=HybridAlpha.hybrid_type_identity))

db.session.add(HybridType(name=HybridBeta.hybrid_type_identity))

db.session.add(HybridAlpha(name='alpha_instance'))

db.session.add(HybridBeta(name='beta_instance'))

all_alphas = HybridAlpha.query.all()

assert HybridModel.query.count() == 2

assert HybridAlpha.query.count() == 1

assert HybridBeta.query.count() == 1

<mik...@zzzcomputing.com <mailto:mike_mp@zzzcomputing.com>
<mailto:mike_mp@zzzcomputing.com

<mailto:mike_mp@zzzcomputing.com>
<mailto:mike_mp@zzzcomputing.com <mailto:mike_mp@zzzcomputing.com>>
<mailto:mike_mp@zzzcomputing.com
<mailto:mike_mp@zzzcomputing.com>
<mailto:mike_mp@zzzcomputing.com

<mailto:mike_mp@zzzcomputing.com>
<mailto:mike_mp@zzzcomputing.com
<mailto:mike_mp@zzzcomputing.com>>
<mailto:mike_mp@zzzcomputing.com
<mailto:mike_mp@zzzcomputing.com>
<mailto:mike_mp@zzzcomputing.com <mailto:mike_mp@zzzcomputing.com>>>
<mailto:mike_mp@zzzcomputing.com
<mailto:mike_mp@zzzcomputing.com>
<mailto:mike_mp@zzzcomputing.com
<mailto:mike_mp@zzzcomputing.com>>

<mailto:mike_mp@zzzcomputing.com
<mailto:mike_mp@zzzcomputing.com>
<mailto:mike_mp@zzzcomputing.com

sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>>

<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com
<mailto:sqlalchemy%25252Bunsubscr...@googlegroups.com>>>

<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com
<mailto:sqlalchemy%25252Bunsubscr...@googlegroups.com>>

<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com
<mailto:sqlalchemy%25252Bunsubscr...@googlegroups.com>
<mailto:sqlalchemy%25252Bunsubscr...@googlegroups.com
<mailto:sqlalchemy%2525252Bunsubsc...@googlegroups.com>>>>

<mailto:sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>
<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>>

<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com
<mailto:sqlalchemy%25252Bunsubscr...@googlegroups.com>>>

<mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com>
<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com
<mailto:sqlalchemy%25252Bunsubscr...@googlegroups.com>>

<mailto:sqlalchemy%252Bunsubscri...@googlegroups.com
<mailto:sqlalchemy%25252Bunsubscr...@googlegroups.com>
<mailto:sqlalchemy%25252Bunsubscr...@googlegroups.com
<mailto:sqlalchemy%2525252Bunsubsc...@googlegroups.com>>>>>.

To post to this group, send email to
sqlal...@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>>>>>.

mike bayer

unread,

Jan 2, 2017, 12:22:15 PM1/2/17

to sqlal...@googlegroups.com

the usage of "classproperty" here suggests you're using SQLAlchemy's
internal function, which has special meaning in that it gets treated
like declared_attr, and you can see in INSERT it is using HybridModel
for everyone. Using a different classproperty approach below the test
case passes.

from sqlalchemy import *
from sqlalchemy.orm import *

from sqlalchemy.ext.declarative import declarative_base, declared_attr

from sqlalchemy.ext.hybrid import hybrid_property, Comparator

Base = declarative_base()

class classproperty(property):
"""A decorator that behaves like @property except that operates
on classes rather than instances.

The decorator is currently special when using the declarative
module, but note that the
:class:`~.sqlalchemy.ext.declarative.declared_attr`
decorator should be used for this purpose with declarative.

"""

def __init__(self, fget, *arg, **kw):
super(classproperty, self).__init__(fget, *arg, **kw)
self.__doc__ = fget.__doc__

def __get__(desc, self, cls):
return desc.fget(cls)

class ModelBase(Base):

__abstract__ = True

def __repr__(self):
return "{} ({}:{})".format(type(self).__name__, self.name, self.id)

class HybridType(ModelBase):
__tablename__ = 'hybrid_types'

id = Column(Integer, primary_key=True)
name = Column(Text)

class HybridModel(ModelBase):
__tablename__ = 'hybrids'

id = Column(Integer, primary_key=True)
name = Column(Text)

hybrid_type_id = Column(Integer, ForeignKey('hybrid_types.id'),
nullable=False)

hybrid_type = relationship('HybridType')

def __init__(self, *args, **kwargs):
self.hybrid_type_name = self.hybrid_type_identity
return super().__init__(*args, **kwargs)

@classproperty
def hybrid_type_identity(cls):

return cls.__name__

class Hybrita(HybridModel):
pass

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

session.add(HybridType(name=HybridAlpha.hybrid_type_identity))
session.add(HybridType(name=Hybrita.hybrid_type_identity))
session.add(HybridAlpha(name='alpha_instance'))
session.add(Hybrita(name='beta_instance'))

all_alphas = session.query(HybridAlpha).all()
assert session.query(HybridModel).count() == 2
print("------")
assert session.query(HybridAlpha).count() == 1
assert session.query(Hybrita).count() == 1

On 12/29/2016 11:09 AM, Tucker Beck wrote:
> Mike,
>
> Here's the solution I came up with. It shows the 'inverted select' used
> in the comparator vs the hybrid expression
>
> class ModelBase(db.Model):
> __abstract__ = True
>
> def __repr__(self):
> return "{} ({}:{})".format(type(self).__name__, self.name

> <http://self.name>, self.id <http://self.id>)

>
>
> class HybridType(ModelBase):
> __tablename__ = 'hybrid_types'
> id = db.Column(db.Integer, primary_key=True)
> name = db.Column(db.Text)
>
>
> class HybridModel(ModelBase):
> __tablename__ = 'hybrids'
>
> id = db.Column(db.BigInteger, primary_key=True)
> name = db.Column(db.Text)
> hybrid_type_id = db.Column(db.Integer,

> db.ForeignKey('hybrid_types.id <http://hybrid_types.id>'), nullable=False)

> hybrid_type = db.relationship('HybridType')
>
> def __init__(self, *args, **kwargs):
> self.hybrid_type_name = self.hybrid_type_identity
> return super().__init__(*args, **kwargs)
>
> @classproperty
> def hybrid_type_identity(cls):
> return inflection.underscore(cls.__name__)
>
> @declared_attr
> def __mapper_args__(cls):
> return dict(
> polymorphic_on=cls.hybrid_type_name_subquery(),
> polymorphic_identity=cls.hybrid_type_identity,
> )
>
> @hybrid_property
> def hybrid_type_name(self):

> return self.hybrid_type.name <http://self.hybrid_type.name>

>
> @hybrid_type_name.setter
> def hybrid_type_name(self, value):
> self.hybrid_type_id = (
> select([HybridType.id]).
> where(HybridType.name == value)
> )
>

> <mik...@zzzcomputing.com <mailto:mik...@zzzcomputing.com>
> <mailto:mik...@zzzcomputing.com

> <http://self.name>, self.id <http://self.id>
> <http://self.id> <http://self.id>)
>
> def __init__(self, **kwargs):

> <mailto:mik...@zzzcomputing.com>

> <mailto:mik...@zzzcomputing.com <mailto:mik...@zzzcomputing.com>>
> <mailto:mik...@zzzcomputing.com
> <mailto:mik...@zzzcomputing.com>
> <mailto:mik...@zzzcomputing.com

> <mailto:mik...@zzzcomputing.com>

> <mailto:mik...@zzzcomputing.com
> <mailto:mik...@zzzcomputing.com>>
> <mailto:mik...@zzzcomputing.com
> <mailto:mik...@zzzcomputing.com>
> <mailto:mik...@zzzcomputing.com <mailto:mik...@zzzcomputing.com>>>
> <mailto:mik...@zzzcomputing.com
> <mailto:mik...@zzzcomputing.com>
> <mailto:mik...@zzzcomputing.com
> <mailto:mik...@zzzcomputing.com>>
> <mailto:mik...@zzzcomputing.com
> <mailto:mik...@zzzcomputing.com>
> <mailto:mik...@zzzcomputing.com

> <mailto:mik...@zzzcomputing.com>

> <mailto:sqlalchemy%25252Bun...@googlegroups.com
> <mailto:sqlalchemy%2525252Bu...@googlegroups.com>
> <mailto:sqlalchemy%2525252Bu...@googlegroups.com
> <mailto:sqlalchemy%252525252B...@googlegroups.com>>>>>

>
> To post to this group, send email to
> sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>

> <mailto:sqlal...@googlegroups.com>>>>>>.

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> 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
> <mailto:sqlalchemy+...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

Tucker Beck

unread,

Jan 4, 2017, 6:21:11 PM1/4/17

to sqlal...@googlegroups.com

Mike:

Actually, the classproperty I was using was a custom decorator that I made. It doesn't work quite the same as the example you provided, so I will have to compare the two approaches.

Here is the one I had:

class classproperty(property):

"""

This defines a decorator that can be used to describe a read-only property

that is attached to the class itself instead of an instance.

"""

def __get__(self, cls, owner):

return classmethod(self.fget).__get__(None, owner)()

I will try your method and see how it works for my project.

Thanks for the response!

<mik...@zzzcomputing.com <mailto:mike_mp@zzzcomputing.com>
<mailto:mike_mp@zzzcomputing.com

<mailto:mike_mp@zzzcomputing.com>
<mailto:mike_mp@zzzcomputing.com <mailto:mike_mp@zzzcomputing.com>>
<mailto:mike_mp@zzzcomputing.com
<mailto:mike_mp@zzzcomputing.com>
<mailto:mike_mp@zzzcomputing.com

<mailto:mike_mp@zzzcomputing.com>
<mailto:mike_mp@zzzcomputing.com
<mailto:mike_mp@zzzcomputing.com>>
<mailto:mike_mp@zzzcomputing.com
<mailto:mike_mp@zzzcomputing.com>
<mailto:mike_mp@zzzcomputing.com <mailto:mike_mp@zzzcomputing.com>>>
<mailto:mike_mp@zzzcomputing.com
<mailto:mike_mp@zzzcomputing.com>
<mailto:mike_mp@zzzcomputing.com
<mailto:mike_mp@zzzcomputing.com>>
<mailto:mike_mp@zzzcomputing.com
<mailto:mike_mp@zzzcomputing.com>
<mailto:mike_mp@zzzcomputing.com

Reply all

Reply to author

Forward

0 new messages