Re: [sqlalchemy] AssertionError When Removing Children For Association Object
you need to put a cascade rule on Page.user_relationships, such that when you remove a Page_to_User from the collection, it’s marked as deleted, instead of SQLAlchemy setting the page_id foreign key to NULL, which is invalid here b.c. that column is part of the primary key (and hence the error). Page_to_User can’t exist in the database without being referred to by a Page object since the primary key would be NULL. the delete-orphan cascade is introduced at: http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#configuring-delete-delete-orphan-cascade and some more information at: http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#deleting-from-collections Can you help me understand why a cascade rule is needed? I have read the documentation you linked to but still have trouble grasping why it is needed. I understand it would make sense if I deleted a page, that it should cascade on delete to page_to_user because now the FK page_id is no longer valid - the page doesn't exist, so it cannot possibly be mapped anywhere. What I'm having trouble understanding is what is cascading from what to what when deleting from this page.user_relationships collection. It doesn't cascade from page, because I am not deleting any pages, and it doesn't cascade from users, because no users are deleted. The only thing being deleted is the mapping itself. In the database table definition, there doesn't have to be any cascade settings at all for the constraints and it will still work fine. Maybe it's purely a SQLAlchemy thing and how it's designed for some reason? I would just like to understand better for future development. Thanks in advance. -Russ -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] AssertionError When Removing Children For Association Object
On Jan 9, 2014, at 10:02 AM, Russell Holloway russ.d.hollo...@gmail.com wrote: you need to put a cascade rule on Page.user_relationships, such that when you remove a Page_to_User from the collection, it’s marked as deleted, instead of SQLAlchemy setting the page_id foreign key to NULL, which is invalid here b.c. that column is part of the primary key (and hence the error). Page_to_User can’t exist in the database without being referred to by a Page object since the primary key would be NULL. the delete-orphan cascade is introduced at: http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#configuring-delete-delete-orphan-cascade and some more information at: http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#deleting-from-collections Can you help me understand why a cascade rule is needed? I have read the documentation you linked to but still have trouble grasping why it is needed. I understand it would make sense if I deleted a page, that it should cascade on delete to page_to_user because now the FK page_id is no longer valid - the page doesn't exist, so it cannot possibly be mapped anywhere. OK so, you have: Page.user_relationships - collection of PageToUser PageToUser - single User then, you are saying : some_page.user_relationships = [] session.flush() What SQL would you expect this to produce? After a flush, what would the rows in your page_to_user table look like? What I'm having trouble understanding is what is cascading from what to what when deleting from this page.user_relationships collection. It doesn't cascade from page, because I am not deleting any pages, and it doesn't cascade from users, because no users are deleted. “delete-orphan” means when an item is removed from a collection, in this case a PageToUser object, it is marked as deleted. In the database table definition, there doesn't have to be any cascade settings at all for the constraints and it will still work fine. OK you need to show what “works fine” is - how the Page.user_relationships collection can be empty on a particular Page object, yet there are PageToUser objects in the database which refer to that Page (or if you think the PageToUser row still exists, but doesn’t point to any Page, show me how that looks). What’s in the database? signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] AssertionError When Removing Children For Association Object
OK so, you have: Page.user_relationships - collection of PageToUser PageToUser - single User then, you are saying : some_page.user_relationships = [] session.flush() What SQL would you expect this to produce? After a flush, what would the rows in your page_to_user table look like? I would expect it to delete entries, resulting in no rows for that page_id DELETE FROM Page_To_User WHERE page_id = ? Perhaps it tries this instead? UPDATE Page_To_User SET page_id = NULL WHERE page_id = ? What I'm having trouble understanding is what is cascading from what to what when deleting from this page.user_relationships collection. It doesn't cascade from page, because I am not deleting any pages, and it doesn't cascade from users, because no users are deleted. “delete-orphan” means when an item is removed from a collection, in this case a PageToUser object, it is marked as deleted. So by default does page.user_relationships call the above UPDATE call and set to null or something? In the database table definition, there doesn't have to be any cascade settings at all for the constraints and it will still work fine. OK you need to show what “works fine” is - how the Page.user_relationships collection can be empty on a particular Page object, yet there are PageToUser objects in the database which refer to that Page (or if you think the PageToUser row still exists, but doesn’t point to any Page, show me how that looks). What’s in the database? In my mind, Page.user_relationship is a collection of PageToUser objects, so by emptying that collection, all those objects are deleted from Page_To_User. It doesn't have anything to do with the Page object itself though - only the associations in PageToUser. The following table definitions work without the CASCADE being used on FK at all (it may prevent deletions of Page or User rows due to FK constraints, but not in this example since we don't ever delete those rows). CREATE TABLE Page ( int page_id not null auto increment, varchar(255) title, primary key (page_id) ) Engine=InnoDB; CREATE TABLE User ( int user_id not null auto increment, varchar(255) name, primary key (user_id) ) Engine=InnoDB; CREATE TABLE Page_To_User ( int page_id not null, int user_id not null, int relationship_id not null, primary key (page_id, user_id, relationship_id), foreign key (page_id) references Page(page_id), foreign key (user_id) references User(user_id) ) Engine=InnoDB; Above, there isn't any ON DELETE CASCADE specified for any of the foreign keys. My understanding is if there were, foreign key (page_id) references Page(page_id) ON DELETE CASCADE then, if I delete row with that page_id from Page, then it will cascade from Page to Page_To_User and delete the appropriate row. If ON DELETE CASCADE is missing, it will complain if I try to delete that Page row, because there are FK constraints. But issuing a simple ' DELETE FROM Page_To_User WHERE page_id = ? ' shouldn't require any cascading, at least in SQL. Maybe the cascade keyword in SQLAlchemy does not exactly mean cascade on a table definition? -Russ -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] AssertionError When Removing Children For Association Object
On Jan 9, 2014, at 10:31 AM, Russell Holloway russ.d.hollo...@gmail.com wrote: OK so, you have: Page.user_relationships - collection of PageToUser PageToUser - single User then, you are saying : some_page.user_relationships = [] session.flush() What SQL would you expect this to produce? After a flush, what would the rows in your page_to_user table look like? I would expect it to delete entries, resulting in no rows for that page_id DELETE FROM Page_To_User WHERE page_id = ? Perhaps it tries this instead? UPDATE Page_To_User SET page_id = NULL WHERE page_id = ? You are correct in both cases.So if you follow the instructions for delete-orphan cascade as previously mentioned here: http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#configuring-delete-delete-orphan-cascade the rows will be deleted instead. Perhaps you’re being thrown off by the fact that an ORM level cascade is set on the *opposite* side as a FOREIGN KEY cascade in SQL, but that is how it works. The Page.user_relationships collection is what indicates the handling of PageToUser objects. If you want to see the origin of the “CASCADE” settings, they come from Hibernate: http://docs.jboss.org/hibernate/orm/3.3/reference/en-US/html/example-parentchild.html . signature.asc Description: Message signed with OpenPGP using GPGMail
[sqlalchemy] AssertionError When Removing Children For Association Object
Hello all, I keep hitting an assertion error, Dependency Rule Tried To Blank Out Primary Key... when trying to remove all children using an association object. My situation seems very similar to https://groups.google.com/forum/#!topic/sqlalchemy/3g4__pFHZTs However, based on Michaels response, it sounds like we must delete both objects, which I don't want to do since it is a many-many relationship. Below is a simple equivalent to my code: Page(Object): page_id = Column(Integer, primary_key = True) title = Column(String) user_relationships = relationship(Page_to_User) User(Object): user_id = Column(Integer, primary_key = True) name = Column(String) Page_to_User(Object): page_id = Column(Integer, ForeignKey(Page.page_id), primary_key = True) user_id = Column(Integer, ForeignKey(User.user_id), primary_key = True) relationship_type = (Integer, ForeignKey(Relationship.type_id), primary_key = True) page = relationship(Page) user = relationship(User) Assuming page1 object has many users tied to it, and I want to unassociate them all... print page1.user_relationships # populated with stuff, works as expected page1.user_relationships = [] session.flush() # error here My understanding is it page1.user_relationships is populated correctly due to the FK set on Page_to_User Association object. Somehow, it's getting the 'tried to blank out' error on the Page_to_User table... In the link above, Michael's write up sounds like the cause is if I try to delete the Page object, which references Page_to_User, which has foreign key to Page. It then tries to set page_id to null on Page_to_Actor due to FK constraints and ultimately fails. However, I'm not trying to delete the Page object here - just the associations to User. The Page stays. The User objects also stay. They just are not linked anymore... Can someone help explain why I still trigger this issue? I can make it go away setting viewonly=True on the user_relationships relationship() call, but I don't want it view only - I want to be able to update and work with those objects as usual. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] AssertionError When Removing Children For Association Object
On Jan 8, 2014, at 5:41 PM, Russell Holloway russ.d.hollo...@gmail.com wrote: Hello all, I keep hitting an assertion error, Dependency Rule Tried To Blank Out Primary Key... when trying to remove all children using an association object. My situation seems very similar to https://groups.google.com/forum/#!topic/sqlalchemy/3g4__pFHZTs However, based on Michaels response, it sounds like we must delete both objects, which I don't want to do since it is a many-many relationship. Below is a simple equivalent to my code: Page(Object): page_id = Column(Integer, primary_key = True) title = Column(String) user_relationships = relationship(Page_to_User) User(Object): user_id = Column(Integer, primary_key = True) name = Column(String) Page_to_User(Object): page_id = Column(Integer, ForeignKey(Page.page_id), primary_key = True) user_id = Column(Integer, ForeignKey(User.user_id), primary_key = True) relationship_type = (Integer, ForeignKey(Relationship.type_id), primary_key = True) page = relationship(Page) user = relationship(User) you need to put a cascade rule on Page.user_relationships, such that when you remove a Page_to_User from the collection, it’s marked as deleted, instead of SQLAlchemy setting the page_id foreign key to NULL, which is invalid here b.c. that column is part of the primary key (and hence the error). Page_to_User can’t exist in the database without being referred to by a Page object since the primary key would be NULL. the delete-orphan cascade is introduced at: http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#configuring-delete-delete-orphan-cascade and some more information at: http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#deleting-from-collections -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. signature.asc Description: Message signed with OpenPGP using GPGMail
