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())
On Wed, Nov 30, 2016 at 7:20 AM, mike bayer <mike...@zzzcomputing.com>
wrote:
>
>
> 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 <mike...@zzzcomputing.com
>> <mailto:mike...@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?hi
>> ghlight=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/settabl
>> e-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
>> <mike...@zzzcomputing.com <mailto:mike...@zzzcomputing.com>
>> <mailto:mike...@zzzcomputing.com
>>
>> <mailto:mike...@zzzcomputing.com>>> wrote:
>>
>>
>>
>> On 11/28/2016 05:58 PM, Tucker Beck wrote:
>>
>> 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/2658dedb8ca8e2fd941f8075
>> c75b3c89
>> <https://gist.github.com/dusktreader/2658dedb8ca8e2fd941f807
>> 5c75b3c89>
>>
>> <https://gist.github.com/dusktreader/2658dedb8ca8e2fd941f807
>> 5c75b3c89
>> <https://gist.github.com/dusktreader/2658dedb8ca8e2fd941f807
>> 5c75b3c89>>
>>
>> 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.
>>
>>
>> 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
>> <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 sqlalchemy+unsubscr...@googlegroups.com
>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>
>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com
>> <mailto:sqlalchemy%252bunsubscr...@googlegroups.com>>
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com
>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>
>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com
>> <mailto:sqlalchemy%252bunsubscr...@googlegroups.com>>>.
>>
>> To post to this group, send email to
>> 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>>>.
>> Visit this group at
>> https://groups.google.com/group/sqlalchemy
>> <https://groups.google.com/group/sqlalchemy>
>> <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>
>> <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>
>> <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/uns
>> ubscribe
>> <https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/un
>> subscribe>
>>
>> <https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/un
>> subscribe
>> <https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/un
>> subscribe>>.
>> To unsubscribe from this group and all its topics, send an
>> email to
>> sqlalchemy+unsubscr...@googlegroups.com
>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>
>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com
>> <mailto:sqlalchemy%252bunsubscr...@googlegroups.com>>.
>> To post to this group, send email to
>> sqlalchemy@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>
>> <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>
>> <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+unsubscr...@googlegroups.com
>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com
>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>>.
>> To post to this group, send email to sqlalchemy@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+unsubscr...@googlegroups.com
>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>.
>> To post to this group, send email to 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>.
>>
>>
>>
>>
>> --
>> -=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+unsubscr...@googlegroups.com
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>> To post to this group, send email to sqlalchemy@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/to
> pic/sqlalchemy/KJXSHwbhbLA/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@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
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
