On Thu, Mar 3, 2016 at 8:27 AM, Lele Gaifax <l...@metapensiero.it> wrote:
> Hi all,
>
> I have a simple m2m relationship between two entities, with a secondary
> table
> in the middle.
>
> From the left item I need to read both the right items and the middle
> ones, so
> I have two relationships on the left entity.
>
> When I delete one left item, I get the following exception:
>
> AssertionError: Dependency rule tried to blank-out primary key column
> 'persons2activities.idperson' on instance
> '<PersonActivity...>
>
> I tried to investigate, but without luck. Am I missing something?
>
> The following script exhibits the problem:
>
>
> from sqlalchemy import create_engine
> from sqlalchemy import Column, Integer, ForeignKey, String
> from sqlalchemy.orm import relationship, sessionmaker
> from sqlalchemy.ext.declarative import declarative_base
>
>
> Base = declarative_base()
>
>
> class Person(Base):
> __tablename__ = 'persons'
> id = Column(Integer, primary_key=True, nullable=False)
> name = Column(String, nullable=False)
> activities = relationship('Activity', secondary='persons2activities')
>
>
> class Activity(Base):
> __tablename__ = 'activities'
> id = Column(Integer, primary_key=True, nullable=False)
> name = Column(String, nullable=False)
>
>
> class PersonActivity(Base):
> __tablename__ = 'persons2activities'
> idperson = Column(Integer, ForeignKey('persons.id'), nullable=False,
> primary_key=True)
> idactivity = Column(Integer, ForeignKey('activities.id'),
> nullable=False, primary_key=True)
> # Comment out the following, and everything works without errors
> person = relationship(Person, backref='preferred_activities')
>
>
> def main():
> engine = create_engine('sqlite:///:memory:', echo=True)
> Session = sessionmaker(bind=engine)
> sess = Session()
> Base.metadata.create_all(engine)
>
> person = Person(id=1, name='lele')
> activity = Activity(id=1, name='carrom')
> person.activities.append(activity)
>
> sess.add(person)
> sess.flush()
> sess.commit()
>
> sess.expunge_all()
>
> person = sess.query(Person).get(1)
> sess.delete(person)
> sess.flush()
> sess.commit()
>
>
> if __name__ == '__main__':
> main()
>
>
In general I think it is not recommended to use "secondary" with a table
that you have also mapped a class to. (eg. see the warning at the bottom of
http://docs.sqlalchemy.org/en/rel_1_0/orm/basic_relationships.html#association-object
).
If SQLAlchemy is handling the many-to-many relationship itself (via the
"secondary" argument), it will delete rows from the association table when
one of the related objects is deleted. However, your
"Person.preferred_activities" relationship is one-to-many, and so the
default behaviour when deleting the Person will be to set the foreign keys
pointing to it to NULL. In this case that means setting
PersonActivity.idperson to NULL, but this is impossible since that column
is part of the primary key of the table.
You *might* be able to fix this by setting the cascade behaviour on
Person.preferred_activities to "all, delete-orphan":
http://docs.sqlalchemy.org/en/rel_1_0/orm/cascades.html#unitofwork-cascades
Hope that helps,
Simon
--
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.
