Association proxies with relationships pointing to same table breaks on clear

Using documentation examples of association proxy (http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html) I tried creating some of them to split data, in my case I want to get keywords of type1, or type2 inside user, creating and updating with append works fine but when I try to clear the list and commit to database, sqlalchemy executes an update trying to set UserKeyword.user_id as null instead of deleting that row. Here is the full code transcribed to the example.

from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import relationship, backref, sessionmaker
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.collections import attribute_mapped_collection

sqlite_engine = create_engine('sqlite:///:memory:')
Base = declarative_base(bind=sqlite_engine)
Session = sessionmaker(bind=sqlite_engine)

session = Session()


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))

    type1_relationship = relationship(
        'UserKeyword',
        primaryjoin='and_(UserKeyword.user_id == User.id, Keyword.type == \'type1\')')
    type1 = association_proxy('type1_relationship', 'keyword',
                    creator=lambda v:
                                UserKeyword(data=v, type='type1')
                )

    type2_relationship = relationship(
        'UserKeyword',
        primaryjoin='and_(UserKeyword.user_id == User.id, Keyword.type == \'type2\')')
    type2 = association_proxy('type2_relationship', 'keyword',
                    creator=lambda v:
                                UserKeyword(data=v, type='type2')
                )

    def __init__(self, name):
        self.name = name


class UserKeyword(Base):
    __tablename__ = 'user_keyword'
    user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
    keyword_id = Column(Integer, ForeignKey('keyword.id'), primary_key=True)

    keyword = relationship("Keyword")

    def __init__(self, data=None, type=None):
        if data and type:
            self.keyword = Keyword(type=type, **data)


class Keyword(Base):
    __tablename__ = 'keyword'
    id = Column(Integer, primary_key=True)
    name = Column('name', String(64))
    type = Column(String)

    def __init__(self, name, type):
        self.name = name
        self.type = type

    def __repr__(self):
        return 'Keyword name=%r, type=%r' % (self.name, self.type)


Base.metadata.create_all()
user = User('log')
user.type1.append({'name': 'testing1'})
session.add(user)
session.commit()
print(user.type1)
print(user.type2)

user.type1.clear()
session.commit()

The commit statement will break because of that update instead of a delete.

Comments (2)