[ Python 常見問題 ] SQLAlchemy delete doesn't cascade

Source from here 
Question 
My User model has a relationship to the Address model. I've specified that the relationship should cascade the delete operation. However, when I query and delete a user, I get an error that the address row is still referenced. How do I delete the user and the addresses? 

view plaincopy to clipboardprint?

1. class User(db.Model):  
2.     id = db.Column(db.Integer, primary_key=True)  
3.     addresses = db.relationship('Address', cascade='all,delete', backref='user')  
4.   
5. class Address(db.Model):  
6.     id = db.Column(db.Integer, primary_key=True)  
7.     user_id = db.Column(db.Integer, db.ForeignKey(User.id))  

When I execute: 

view plaincopy to clipboardprint?

1. db.session.query(User).filter(User.my_id==1).delete()  

Got below error message: 

IntegrityError: (IntegrityError) update or delete on table "user" violates foreign key constraint "addresses_user_id_fkey" on table "address"
DETAIL: Key (my_id)=(1) is still referenced from table "address".
'DELETE FROM "user" WHERE "user".id = %(id_1)s' {'id_1': 1}

How-To 
You have the following: 

view plaincopy to clipboardprint?

1. db.session.query(User).filter(User.my_id==1).delete()  

Note that after "filter", you are still returned a Query object. Therefore, when you call delete(), you are calling delete() on the Query object (not the User object). This means you are doing a bulk delete (albeit probably with just a single row being deleted). The documentation for the Query.delete() method that you are using says: 

The method does not offer in-Python cascading of relationships - it is assumed that ON DELETE CASCADE/SET NULL/etc. is configured for any foreign key references which require it, otherwise the database may emit an integrity violation if foreign key references are being enforced.

As it says, running delete in this manner will ignore the Python cascade rules that you've set up. You probably wanted to do something like: 

view plaincopy to clipboardprint?

1. user = db.session.query(User).filter(User.my_id==1).first()  
2. db.session.delete(user)  

Otherwise, you may wish to look at setting up the cascade for your database as well.