OK, coffee has been applied. This mapping can be done in 0.9 but only if you
use classical mappings, AbstractConcreteBase and declarative aren’t ready yet.
In 1.0, I made a lot of improvements (see
http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#improvements-to-declarative-mixins-declared-attr-and-related-features
<http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#improvements-to-declarative-mixins-declared-attr-and-related-features>)
that manage to get this to work using a fully declarative approach. A minimal
example is below. In particular, you *don’t* want to have a “polymorphic_on”
column added to any specific table when using concrete mappings, there’s no
base table that’s shared. The AbstractConcreteBase produces this
“polymorphic_on” for you, and it’s only part of the “polymorphic union” query
you see in the SQL output of the script:
from sqlalchemy import Column, String, Integer, create_engine, ForeignKey, Float
from sqlalchemy.orm import Session, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.collections import attribute_mapped_collection
from sqlalchemy.ext.declarative import declared_attr, AbstractConcreteBase
Base = declarative_base()
class Mammut(Base):
__tablename__ = "mammut"
id = Column(Integer, primary_key=True)
nodes = relationship(
'TreeNode',
lazy='dynamic',
back_populates='mammut',
)
class TreeNode(AbstractConcreteBase, Base):
id = Column(Integer, primary_key=True)
name = Column(String)
@declared_attr
def __tablename__(cls):
if cls.__name__ == 'TreeNode':
return None
else:
return cls.__name__.lower()
@declared_attr
def __mapper_args__(cls):
return {'polymorphic_identity': cls.__name__, 'concrete': True}
@declared_attr
def parent_id(cls):
return Column(Integer, ForeignKey(cls.id))
@declared_attr
def mammut_id(cls):
return Column(Integer, ForeignKey('mammut.id'))
@declared_attr
def mammut(cls):
return relationship("Mammut", back_populates="nodes")
@declared_attr
def children(cls):
return relationship(
cls,
back_populates="parent",
collection_class=attribute_mapped_collection('name'),
)
@declared_attr
def parent(cls):
return relationship(
cls, remote_side="%s.id" % cls.__name__,
back_populates='children')
class IntTreeNode(TreeNode):
value = Column(Integer)
class FloatTreeNode(TreeNode):
value = Column(Float)
miau = Column(String(50), default='zuff')
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
session = Session(e)
root = IntTreeNode(name='root')
IntTreeNode(name='n1', parent=root)
n2 = IntTreeNode(name='n2', parent=root)
IntTreeNode(name='n2n1', parent=n2)
m1 = Mammut()
m1.nodes.append(n2)
m1.nodes.append(root)
session.add(root)
session.commit()
session.close()
root = session.query(TreeNode).filter_by(name='root').one()
print root.children
that polymorphic query at the bottom looks like the following, note that the
“type”, which is our polymorphic_on, is a virtual column:
SELECT pjoin.id AS pjoin_id, pjoin.name AS pjoin_name, pjoin.value AS
pjoin_value, pjoin.miau AS pjoin_miau, pjoin.mammut_id AS pjoin_mammut_id,
pjoin.parent_id AS pjoin_parent_id, pjoin.type AS pjoin_type
FROM (SELECT floattreenode.id AS id, floattreenode.name AS name,
floattreenode.value AS value, floattreenode.miau AS miau,
floattreenode.mammut_id AS mammut_id, floattreenode.parent_id AS parent_id,
'FloatTreeNode' AS type
FROM floattreenode UNION ALL SELECT inttreenode.id AS id, inttreenode.name AS
name, inttreenode.value AS value, CAST(NULL AS VARCHAR(50)) AS miau,
inttreenode.mammut_id AS mammut_id, inttreenode.parent_id AS parent_id,
'IntTreeNode' AS type
FROM inttreenode) AS pjoin
WHERE pjoin.name = ?
> On Nov 4, 2014, at 10:33 AM, Michael Bayer <[email protected]> wrote:
>
> there is a lot lot lot going on here. The example isn’t working in 1.0
> for different reasons, for example.
>
> However lets start with just the error you have, and to do that, lets please
> just show the minimal amount of code to reproduce:
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.ext.declarative import declared_attr, AbstractConcreteBase
>
>
> Base = declarative_base()
>
>
> class Mammut(Base):
> __tablename__ = "mammut"
>
> id = Column(Integer, primary_key=True)
> nodes = relationship(
> 'TreeNode',
> backref='mammut',
> )
>
>
> class TreeNode(AbstractConcreteBase, Base):
> id = Column(Integer, primary_key=True)
> name = Column(String(50), nullable=False)
> depth = Column(Integer, default=0)
> data_type = Column(String(50))
>
> @declared_attr
> def mammut_id(cls):
> return Column(Integer, ForeignKey('mammut.id'))
>
> def __init__(self, name, value=None, parent=None):
> self.name = name
> self.parent = parent
> self.depth = 0
> self.value = value
> if self.parent:
> self.depth = self.parent.depth + 1
>
>
> class IntTreeNode(TreeNode):
> value = Column(Integer)
>
> __tablename__ = 'int'
> __mapper_args__ = {"concrete": True, "polymorphic_identity": 'int'}
>
>
> class FloatTreeNode(TreeNode):
> value = Column(Float)
> miau = Column(String(50), default='zuff')
>
> __tablename__ = 'float'
> __mapper_args__ = {"concrete": True, "polymorphic_identity": 'float'}
>
> node = IntTreeNode('rootnode', value=2)
>
> mut = Mammut()
> mut.nodes.append(node)
>
> The issue is that you can’t just do a single “backref” to concrete classes.
> You have to use the instructions at
> http://docs.sqlalchemy.org/en/rel_0_9/orm/inheritance.html#relationships-with-concrete-inheritance
>
> <http://docs.sqlalchemy.org/en/rel_0_9/orm/inheritance.html#relationships-with-concrete-inheritance>
> to create each reference from IntTreeNode / FloatTreeNode etc. back.
>
> This has been improved in 1.0. We can say:
>
> class Mammut(Base):
> __tablename__ = "mammut"
>
> id = Column(Integer, primary_key=True)
> nodes = relationship(
> 'TreeNode',
> back_populates='mammut',
> )
>
> class TreeNode(AbstractConcreteBase, Base):
> id = Column(Integer, primary_key=True)
> name = Column(String(50), nullable=False)
> depth = Column(Integer, default=0)
> data_type = Column(String(50))
>
> @declared_attr
> def mammut_id(cls):
> return Column(Integer, ForeignKey('mammut.id'))
>
> @declared_attr
> def mammut(cls):
> return relationship("Mammut", back_populates='nodes')
>
> def __init__(self, name, value=None, parent=None):
> self.name = name
> self.parent = parent
> self.depth = 0
> self.value = value
> if self.parent:
> self.depth = self.parent.depth + 1
>
> however in 0.9, this won’t work, and after some experimentation I don’t think
> it’s possible in 0.9 to have a backref pointing to an AbstractConcreteBase,
> sorry.
>
> Also, the attempt to make a self-referential relationship from TreeNode to
> itself is also not possible in the way you are attempting. There is no
> TreeNode table, so this would require distinct relationships and foreign keys
> on each concrete table, however I’m not getting that to work either.
>
> I think you might not intend to use AbstractConcreteBase here in any case as
> it seems like you want there to be a base table (this creates one?).
>
>
> I’ll try to look more later but overall there’s kind of too much going on
> here and concrete inheritance is not very easy to use, sorry.
>
>
>
>
>
>
>
>
>
>
>
>
>> On Nov 4, 2014, at 7:28 AM, delijati <[email protected]
>> <mailto:[email protected]>> wrote:
>>
>> Hello,
>>
>> i posted my question on stakoverflow. So to not repeat myself:
>>
>> https://stackoverflow.com/questions/26724897/adjacency-list-abstract-base-class-inheritance-used-in-relationship
>>
>> <https://stackoverflow.com/questions/26724897/adjacency-list-abstract-base-class-inheritance-used-in-relationship>
>>
>> Josip
>>
>>
>> --
>> 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 [email protected]
>> <mailto:[email protected]>.
>> To post to this group, send email to [email protected]
>> <mailto:[email protected]>.
>> Visit this group at http://groups.google.com/group/sqlalchemy
>> <http://groups.google.com/group/sqlalchemy>.
>> For more options, visit https://groups.google.com/d/optout
>> <https://groups.google.com/d/optout>.
>
>
> --
> 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 [email protected]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> Visit this group at http://groups.google.com/group/sqlalchemy
> <http://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
