2019年8月10日 星期六

[ 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? 
  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: 
  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: 
  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: 
  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.

沒有留言:

張貼留言

[ Py DS ] Ch5 - Machine Learning (Part1)

Source From  Here   What Is Machine Learning?   Before we take a look at the details of various machine learning methods, let’s start by ...