「转」sqlalchemy 0.9.8 多对多级联删除

it2022-05-05 221

转自知乎 http://www.zhihu.com/question/21050551

有 A,B,C,AB,AC五张表

class A(base): __tablename__ = "a" id = Column(Integer, primary_key=True) b = relationship('B', secondary=AB, cascade="delete, delete-orphan", single_parent=True) c = relationship('C', secondary=AC, cascade="delete, delete-orphan", single_parent=True) class B(base): __tablename__ = "b" id = Column(Integer, primary_key=True) rules = relationship('A', secondary=AB, cascade="delete, delete-orphan", single_parent=True, passive_deletes=True) class C(base): __tablename__ = "c" id = Column(Integer, primary_key=True) rules = relationship('A', secondary=AC, cascade="delete, delete-orphan", single_parent=True, passive_deletes=True) AB = Table( "AB", Base.metadata, Column("A_id", Integer, ForeignKey("A.id")), Column("B_id", Integer, ForeignKey("B.id")) ) AC = Table( "AC", Base.metadata, Column("A_id", Integer, ForeignKey("A.id")), Column("C_id", Integer, ForeignKey("C.id")) )

关系是： A表对B表多对多，关系存放在AB表 A表对C表多对多，关系存放在AC表

操作：删除A表的数据时，同时删除B,C,AB,AC表与A相关联的数据。

secondary：多对多关联类 cascade：设置级联关系，删除用delete, delete-orphan single_parent：让级联支持多对多，设置为True 没有此属性会报异常：On X.x, delete-orphan cascade is not supported on a many-to-many or many-to-one relationship when single_parent is not set. Set single_parent=True on the relationship(). passive_deletes: 支持关联(被动)删除，设置为True 没有此属性会报异常：DELETE statement on table 'B' expected to delete 1 row(s); Only 0 were matched. ------------------------------------------------------------------------ 好了，这样就可以级联删除B,C,AB,AC表与A相关联的数据。

另外一个例子来自 http://stackoverflow.com/questions/5033547/sqlachemy-cascade-delete

class Parent(Base): __tablename__ = "parents" id = Column(Integer, primary_key=True) child = relationship(Child, backref="parent", passive_deletes=True) class Child(Base): __tablename__ = "children" id = Column(Integer, primary_key=True) parent_id = Column(Integer, ForeignKey("parents.id", ondelete='CASCADE'))

http://www.cnblogs.com/coder2012/p/4746941.html 外键以及relationship例子

class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(32)) addresses = relationship("Address", order_by="Address.id", backref="user") class Address(Base): __tablename__ = 'addresses' id = Column(Integer, primary_key=True) email_address = Column(String(32), nullable=False) user_id = Column(Integer, ForeignKey('users.id')) #user = relationship("User", backref=backref('addresses', order_by=id)) >>> jack = User(name='jack') >>> jack.addresses [] >>> jack.addresses = [Address(email_address='test@test.com'), Address(email_address='test1@test1.com')] >>> jack.addresses [<demo.Address object at 0x7f2536564f90>, <demo.Address object at 0x7f2535dc71d0>] >>> session.add(jack) >>> session.commit() mysql> select * from users; +----+------+ | id | name | +----+------+ | 1 | jack | +----+------+ 1 row in set (0.00 sec) mysql> select * from addresses; +----+-----------------+---------+ | id | email_address | user_id | +----+-----------------+---------+ | 1 | test@test.com | 1 | | 2 | test1@test1.com | 1 | +----+-----------------+---------+ 2 rows in set (0.00 sec) >>> session.query(User.name, Address.email_address).filter(User.id==Address.user_id).filter(Address.email_address=='test@test.com').all() 2015-08-19 14:02:02,877 INFO sqlalchemy.engine.base.Engine SELECT users.name AS users_name, addresses.email_address AS addresses_email_address FROM users, addresses WHERE users.id = addresses.user_id AND addresses.email_address = %s 2015-08-19 14:02:02,878 INFO sqlalchemy.engine.base.Engine ('test@test.com',) [('jack', 'test@test.com')]

http://www.cnblogs.com/whuyt/p/4520850.html

#!/usr/bin/env python # -*- coding: utf-8 -*- from flask import Flask from flask_sqlalchemy import SQLAlchemy import uuid app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://demo:demo@127.0.0.1/demo' app.config['SQLALCHEMY_ECHO'] = True app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True app.config['Debug'] = True db = SQLAlchemy(app) # 多对多关系表 teachers_classes = db.Table('teachers_classes', db.Column('teacher_uuid', db.String(40), db.ForeignKey('teacher.uuid', ondelete='CASCADE', onupdate='CASCADE')), db.Column('class_uuid', db.String(40), db.ForeignKey('class.uuid', ondelete='CASCADE', onupdate='CASCADE')), ) # 老师对班级多对多，班级对学生一对多 class Teacher(db.Model): __tablename__ = 'teacher' uuid = db.Column(db.String(40), primary_key=True) name = db.Column(db.String(20), unique=True) classes = db.relationship('Class', secondary=teachers_classes, backref=db.backref('teachers', lazy='dynamic'), lazy='dynamic', \ cascade='all, delete-orphan', single_parent=True) class Class(db.Model): __tablename__ = 'class' uuid = db.Column(db.String(40), primary_key=True) name = db.Column(db.String(20), unique=True) students = db.relationship('Student', backref='class', lazy='dynamic', cascade='all, delete-orphan') class Student(db.Model): __tablename__ = 'student' uuid = db.Column(db.String(40), primary_key=True) name = db.Column(db.String(20), unique=True) class_uuid = db.Column(db.String(40), db.ForeignKey('class.uuid', ondelete='CASCADE', onupdate='CASCADE')) db.drop_all() db.create_all() # 建立学生 student_a = [ Student(uuid=str(uuid.uuid1()), name=u'学生'+str(i)) for i in xrange(1, 41)] student_b = [ Student(uuid=str(uuid.uuid1()), name=u'学生'+str(i)) for i in xrange(41, 81)] student_c = [ Student(uuid=str(uuid.uuid1()), name=u'学生'+str(i)) for i in xrange(81, 121)] db.session.add_all(student_a) db.session.add_all(student_b) db.session.add_all(student_c) # 建立班级 class_a = Class(uuid=str(uuid.uuid1()), name=u'班级A', students=student_a) class_b = Class(uuid=str(uuid.uuid1()), name=u'班级B', students=student_b) class_c = Class(uuid=str(uuid.uuid1()), name=u'班级C', students=student_c) db.session.add_all([class_a, class_b, class_c]) db.session.commit() # 建立老师 teacher_a = Teacher(uuid=str(uuid.uuid1()), name=u'老师A', classes=[class_a, class_b]) db.session.add(teacher_a) db.session.commit() teacher_b = Teacher(uuid=str(uuid.uuid1()), name=u'老师B', classes=[class_b, class_c]) db.session.add(teacher_b) db.session.commit() teacher_c = Teacher(uuid=str(uuid.uuid1()), name=u'老师C', classes=[class_a, class_b, class_c]) db.session.add(teacher_c) db.session.commit() ''' from test import db, Teacher, Class, Student, teachers_classes 1 存在约束关系(sqlalchemy默认有约束的可新增，可更新，但不可删除) 1.1 多对多删除老师 db.session.query(Teacher).filter(Teacher.name==u'老师A').delete() 提示teachers_classes表删除约束方法 teacher_a = Teacher.query.filter(Teacher.name==u'老师A').first() classes = Class.query.join(teachers_classes).join(Teacher).filter(Teacher.name==u'老师A').all() 先删除班级关系 for c in classes: teacher_a.classes.remove(c) 再删除老师 db.session.delete(teacher_a) 查看 teachers_classes、teacher相关记录已删除 1.2 一对多删除班级 db.session.query(Class).filter(Class.name==u'班级C').delete() 提示teachers_classes表删除约束方法 class_c = Class.query.filter(Class.name==u'班级C').first() teachers = Teacher.query.join(teachers_classes).join(Class).filter(Class.name==u'班级C').all() 先删除老师关系 for t in teachers: class_c.teachers.remove(t) 再删除班级 db.session.delete(class_c) 查看 teachers_classes、class相关记录已删除，学生81-120记录的class_uuid更新为null 2 级联删除 cascade默认包括save-update、merge, all关键字包括save-update、merge、refresh-expire、expunge、delete 1.1 多对多删除老师 db.session.query(Teacher).filter(Teacher.name==u'老师A').delete() 查看 teachers_classes、teacher相关记录已删除 1.2 一对多删除班级 db.session.query(Class).filter(Class.name==u'班级C').delete() 查看 teachers_classes、class相关记录已删除，关联的学生81-120记录已删除 1.3 删除学生 db.session.query(Student).filter(Student.class_uuid=='dd5763a8-fff8-11e7-98e2-f0def165d278').delete(synchronize_session='fetch') 查看学生1-40记录已删除，关联的class数据未删除 ''' posted on 2015-10-08 16:29 北京涛子阅读( ...) 评论( ...) 编辑收藏

转载于:https://www.cnblogs.com/liujitao79/p/4861425.html

专利

最新回复(0)