Cascade on query([model]).delete() not working
The final assert here fails because session.query([model]).delete doesn't seem to cascade while deleting the object directly has the expected behaviour.
There is a TODO on line 3044 of query.py so I guess this is a known problem.
from sqlalchemy import Column, Integer, String, ForeignKey, Boolean
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm import sessionmaker
def create_bob(sess):
bob = User(id=1)
bob.addresses.append(Address())
sess.add(bob)
sess.commit()
return bob
Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship("User", backref=backref("addresses", cascade='all'))
Base.metadata.create_all(engine)
sess = Session()
bob = create_bob(sess)
assert sess.query(User).count() == 1
assert sess.query(Address).count() == 1
sess.delete(bob)
sess.commit()
assert sess.query(User).count() == 0
assert sess.query(Address).count() == 0
bob = create_bob(sess)
assert sess.query(User).count() == 1
assert sess.query(Address).count() == 1
sess.query(User).delete()
sess.commit()
assert sess.query(User).count() == 0
assert sess.query(Address).count() == 0
Comments (6)
-
repo owner -
repo owner the TODO message is from dcad710de2cff2ceeef18dbd06eb4b263b8c39ad and is from 2008, it is long obsolete and will remove. in-Python cascades are not feasible with bulk operations because they would require that the full table contents are loaded into memory for them to be processed, defeating the whole purpose of using query.delete().
-
repo owner - remove misleading comment, ref
#3902
Change-Id: I481628146ff31bc9ea2f8a3687f375832b17e501
→ <<cset 3bd845f3ff2a>>
- remove misleading comment, ref
-
repo owner - remove misleading comment, ref
#3902
Change-Id: I481628146ff31bc9ea2f8a3687f375832b17e501 (cherry picked from commit 3bd845f3ff2af203a79c76a5b493ccf9c58ad79e)
→ <<cset ce02bdc07f5b>>
- remove misleading comment, ref
-
repo owner - changed status to closed
expected behavior
-
reporter Thanks a lot Michael, I didn't notice the warning in bulk query docs.
- Log in to comment
this is a documented limitation of query.delete():
http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=query%20delete#sqlalchemy.orm.query.Query.delete
you'd need to configure your FOREIGN KEY appropriately for bulk to work.