Bi-Directional SQLAlchemy Proxy

Question

Update:

For everyone who has this problem, with the latest version of SQLAlchemy this behavior is fixed.

Original issue:

I have a problem with the correct proxy settings.

Using the model examples given here: http://docs.sqlalchemy.org/en/rel_0_7/orm/extensions/associationproxy.html#simplifying-association-objects

But changing UserKeyword on this line:

keyword = relationship("Keyword", backref=backref("user_keywords", cascade="all, delete-orphan")) 

and adding this to the keyword:

 users = association_proxy('user_keywords', 'user') 

So, the keyword instance has a list of users.

The following works as expected:

 >>> rory = User("rory") >>> session.add(rory) >>> chicken = Keyword('chicken') >>> session.add(chicken) >>> rory.keywords.append(chicken) >>> chicken.users [<__main__.User object at 0x1f1c0d0>] >>> chicken.user_keywords [<__main__.UserKeyword object at 0x1f1c450>] 

But moving does strange things. Removing an association from the proxy server is as follows:

 >>> rory.keywords.remove(chicken) 

Raises an integrity error because SA is trying to set one of the foreign key columns to null.

Performing this action:

 >>> rory.user_keywords.remove(rory.user_keywords[0]) 

Results in this:

 >>> chicken.users [None] 

I missed something obvious, right?

Answer 1

UserKeyword requires that it be simultaneously associated with both Keyword and User in order to be saved. When you associate it with User and Keyword , but then remove it from the User.user_keywords collection, it is still associated with Keyword .

 >>> rory.keywords.remove(chicken) # empty as we expect >>> rory.user_keywords [] # but the other side, still populated. UserKeyword # has no User, but still has Keyword >>> chicken.user_keywords [<__main__.UserKeyword object at 0x101748d10>] # but the User on that UserKeyword is None >>> chicken.user_keywords[0].user is None True # hence accessing the "association" gives us None # as well >>> chicken.users [None] 

So, if we just reset () now, you have a UserKeyword object ready to work, but no User on it, so you will get this NULL error. During INSERT, an object is not considered an “orphan” if it is not associated with any collections of Keyword.user_keywords or User.user_keywords . Only if you said del chicken.user_keywords[0] or the equivalent, you will see that no INSERT is generated and the UserKeyword object UserKeyword forgotten.

If you had to clear the object to the database before deleting it from "rory", everything will change. UserKeyword now permanent, and when you remove the “chick” from “rory.keywords”, a delete-orphan event occurs that deletes the UserKeyword , even if it is still associated with the Keyword object:

 rory.keywords.append(chicken) session.flush() rory.keywords.remove(chicken) session.flush() 

you see SQL:

 INSERT INTO "user" (name) VALUES (%(name)s) RETURNING "user".id {'name': 'rory'} INSERT INTO keyword (keyword) VALUES (%(keyword)s) RETURNING keyword.id {'keyword': 'chicken'} INSERT INTO user_keyword (user_id, keyword_id, special_key) VALUES (%(user_id)s, %(keyword_id)s, %(special_key)s) {'keyword_id': 1, 'special_key': None, 'user_id': 1} DELETE FROM user_keyword WHERE user_keyword.user_id = %(user_id)s AND user_keyword.keyword_id = %(keyword_id)s {'keyword_id': 1, 'user_id': 1} 

Now, a reasonable person would ask: "Doesn't this contradict?" And at the moment I would say "absolutely." I need to look at the test cases to understand the reason for this difference in behavior, I determined in the code why this happens in this way, and I’m sure that this difference is in the way “orphans” are considered “waiting” and “constant” "objects intentionally, but in this particular rearrangement a strange result is clearly created. I can make a change of 0.8 for this if I can find one that is possible.

edit: http://www.sqlalchemy.org/trac/ticket/2655 summarizes the question I will have to think about. There is a certain test for this behavior, it is necessary to trace it back to its beginning.