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
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.
[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.
[sqlalchemy] Session Management Issues While Integrating With Old Code
Hello all, I am trying to migrate some custom ORM code to use SQLAlchemy instead for database interactions. I'm having some issues with proper session management. The main issue that seems to occur is the operationalerror 'mysql has gone away' with every now and then one comes up StatementError can't reconnect until invalid transaction is rolled back (original cause: InvalidRequestError). I am aware that others have had issues with the first error on long standing connections. For example, if someone creates a session when python script initially loaded and tries to reuse much later, the error occurs. There is scoped_session which should be tied to the specific request (Flask) and solve the issue if a session is created and closed within the route. The problem comes with trying to wrap the code in such a way that it aligns with previous code. With SQLAlchemy, you call session.query(ClassName).get(some_stuff) I would like to use the structure below instead: ClassName.get(some_stuff) To do so, ClassName inherits from a base class that creates the get() method, which in turn converts to the SQLAlchemy format (and similar for filter, update, and similar functions). A simple example would be @classmethod def get(cls, arg): return session.query(cls).get(arg) Does anyone have any recommendations on how to best set that session variable though to use within the base SQLAlchemy class, so that it is scoped to a request? Is it possible? I suppose I could modify all the functions and pass in a session variable as well, so from within Flask route the user would create a (scoped) session and pass it in to all the class methods (ClassName.get(session, arg)), but that changes syntax and isn't quite as clear. I thought I would create a custom Session class with a global session var that a user could set during route function (and then use that static variable within get() function), but then as multiple Flask users interact, the static variable would be overwritten by others prematurely. I know there is Flask-SQLAlchemy, and it provides a *close* syntax such as User.query.get() through the query attribute. Does anyone know how it does the session management behind the scenes to keep them sane? I am looking for pure SQLAlchemy solutions, since the API is used both through Flask (web) and standard python. -- 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.
