- changed status to resolved
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)
-
repo owner -
reporter Hey @zzzeek thank you for your prompt response. That was a bit unclear for me, after hours reading docs and trying to debug I came up reporting the bug which was not a bug but I don't know If docs weren't clear or it was just a dumb thing of me. Thank you again :)
- Log in to comment
this is because your relationships are configured that way. add all, delete-orphan cascade: