SQLAlchemy Bidirectional Relationship association proxy

Question

Update:

For anyone having this issue, with the very latest SQLAlchemy this behaviour has been fixed.

Original issue:

I am having a problem with getting association proxies to update correctly.

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

But changing UserKeyword with this line:

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

and adding this to Keyword:

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

So a 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 removals do strange things. Removing from the association proxy lists like so:

>>> rory.keywords.remove(chicken)

Causes an integrity error as SA tries to set one of the foreign key columns to null.

Doing this:

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

Results in this:

>>> chicken.users
[None]

I have missed something obvious haven't I?

Answer 1

UserKeyword requires that it be associated with both a Keyword and User at the same time in order to be persisted. When you associate it with a User and Keyword, but then remove it from the User.user_keywords collection, it's still associated with the 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 were to flush() this right now, you've got a UserKeyword object ready to go but it has no User on it, so you get that NULL error. At INSERT time, the object is not considered to be an "orphan" unless it is not associated with any Keyword.user_keywords or User.user_keywords collections. Only if you were to say, del chicken.user_keywords[0] or equivalent, would you see that no INSERT is generated and the UserKeyword object is forgotten.

If you were to flush the object to the database before removing it from "rory", then things change. The UserKeyword is now persistent, and when you remove "chicken" from "rory.keywords", a "delete-orphan" event fires off which does delete the UserKeyword, even though it still is associated with the Keyword object:

rory.keywords.append(chicken)

session.flush()

rory.keywords.remove(chicken)

session.flush()

you see the 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, "isn't that inconsistent?" And at the moment I'd say, "absolutely". I need to look into the test cases to see what the rationale is for this difference in behavior, I've identified in the code why it occurs in this way and I'm pretty sure this difference in how an "orphan" is considered for "pending" versus "persistent" objects is intentional, but in this particular permutation obviously produces a weird result. I might make a change in 0.8 for this if I can find one that is feasible.

edit: http://www.sqlalchemy.org/trac/ticket/2655 summarizes the issue which I'm going to have to think about. There is a test for this behavior specifically, need to trace that back to its origin.