On 11/08/2016 10:40 AM, [email protected] wrote:
Hello,
I am struggling with this case and so far, I haven't seen on
documentation or on the different posts, a way to solve my problem.
Firstly, I cannot have a ForeignKey linking Child to Parent because
Parent has a polymorphic identity, and PostgreSQL is not dealing with
ForeignKey in such cases. Let's say ParentA is a Parent.
So I have to define relationship between Child and Parent "manually"
Thus, I have this :
|
|
class Child(SaBaseClass):
parent_id = Column(Integer, index=True, nullable=True)
parent = relationship('Parent',
primaryjoin='foreign(Child.parent_id) == Parent.id', lazy='joined',
uselist=False, foreign_keys='Child.parent_id')
class Parent(SaBaseClass):
children = relationship('Child', primaryjoin='Child.parent_id ==
foreign(Parent.id), backref='children', cascade='all, delete-orphan',
uselist=True, lazy='joined', single_parent=True)
|
|
the first problem is that you are mixing up your foreign() directives on
your two relationships. foreign() indicates the column(s) that would
behave as though they have FOREIGN KEY CONSTRAINT placed upon them,
which note is optional, foreign() is all you need. In this case the
values for foreign() are contradictory and it seems that Child.parent_id
is your foreign() here.
Next, these relationships are bi-directional with each other. They
would be linked together using back_populates. The ORM makes great use
of this clue to know that Child.parent and Parent.children are related
to each other and it can help in a lot of situations. There seems to be
some misunderstood use of the "backref" directive here too with
"Parent.children" having a backref to..."Child.children", which makes no
sense, we will take that out.
Next is that single_parent=True makes no sense here, it should not be
needed; people use it because they saw the error/warning indicating it
should be used, which in this case you probably saw this error due to
the mis-placed foreign() directive, so we will take that out.
We can make your example look exactly like that at
http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#one-to-many,
the only change is that instead of using ForeignKey(), we use foreign()
in the primary join. Everything then works out great, see below.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, index=True, nullable=True)
parent = relationship(
'Parent',
primaryjoin='foreign(Child.parent_id) == Parent.id',
lazy='joined', back_populates="children")
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
children = relationship(
'Child', primaryjoin="foreign(Child.parent_id) == Parent.id",
cascade='all, delete-orphan', lazy='joined',
back_populates="parent")
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(e)
s = Session(e)
p1 = Parent()
c1 = Child()
p1.children.append(c1)
s.add_all([p1, c1])
s.commit()
s.delete(c1)
s.commit()
assert p1.children == []
So a child is linked to only one parent, and a parent can be linked to
several children, and when I delete a Child instance I would like to
"unlink" it from its Parent. When trying to delete a child instance I
get "Dependency rule tried to blank-out primary key column parent.id on
instance 'ParentA', which makes me think it's trying to delete Parent
instance...
If someone has a clue of what's wrong with my definition of the
relationship, thanks in advance for your help !
Isabelle
--
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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
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 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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
