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.

沒有留言:

張貼留言

[Git 常見問題] error: The following untracked working tree files would be overwritten by merge

  Source From  Here 方案1: // x -----删除忽略文件已经对 git 来说不识别的文件 // d -----删除未被添加到 git 的路径中的文件 // f -----强制运行 #   git clean -d -fx 方案2: 今天在服务器上  gi...