关于 sqlalchemy 批量删除的坑

原创于 2024-11-05 19:11:32 发布 · 1.1k 阅读

12 ·

CC 4.0 BY-SA版权

文章标签：

#flask #sqlalchemy

起因

在同步云商资源时需要将原来的记录删除，然后在写入同步到的数据。为了批量删除用到了 session.query().filter().delete() 的语法。于是有了下面这段代码：

NetworkProduct.query.filter(and_(
    NetworkProduct.provider == 'aliyun',
    NetworkProduct.region_id == region,
    NetworkProduct.product_type == NetworkProductType.bandwith_package
)).delete()

其中 NetworkProduct 被其他表关联了外键, 在删除时会出现以下错误：

pymysql.err.IntegrityError: (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`cmdb_dev`.`r_network_product_eip`, CONSTRAINT `r_network_product_eip_ibfk_1` FOREIGN KEY (`np_instance_id`) REFERENCES `network_product` (`instance_id`))')

感觉很奇怪，因为单个实例删除的时候是正常的。

np = NetworkProduct.query.get(100)
db.session.delete(np)
db.session.commit()

复现

为了发现问题所在，将上述业务代码简化了一下：

定义model

# 定义model
class Parent(db.Model):
	__tablename__ = 'parent'

	id = Column(Integer, primary_key=True)
	children = relationship('Child', backref="parent", cascade="all, delete")

class Child(db.Model):
	__tablename__ = 'child'

	id = Column(Integer, primary_key=True)
	parent_id = Column(Integer, ForeignKey('parent.id'))

写入测试数据

# 写入数据
p1 = Parent(id=1)
p2 = Parent(id=2)

c1 = Child(id=1)
c2 = Child(id=2)
c3 = Child(id=3)

p1.children = [c1, c2]
p2.children = [c3]

p1.save()
p2.save()

复现

# 删除p2
> p2 = Parent.query.get(2)
> db.session.delete(p2)
> db.session.commit()
# 删除p1
> Parent.query.filter_by(id=1).delete()
...
sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))')
[SQL: DELETE FROM parent WHERE parent.id = %(id_1)s]
[parameters: {'id_1': 1}]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

定义model时，已经设置了{js}relationship('Child', backref="parent", cascade="all, delete")。所以在删除p2 时，关联的Child 表记录也会删除。

但是使用{js}Parent.query.filter_by(id=1).delete() 时，缺会出现外键约束错误。

DEBUG

stackoverflow

从 stackoverflow上看到相关的解答，这里贴一下原文：

在这里插入图片描述

log debug

在删除p2 时，其实包含了一些隐形操作，从debug 日志中执行的可以看到执行的sql

Note: flask sqlalchemy 中增加 SQLALCHEMY_ECHO = True 配置即可

2024-08-01 16:54:06,925 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2024-08-01 16:54:06,925 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-08-01 16:54:06,935 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2024-08-01 16:54:06,935 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-08-01 16:54:06,942 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
2024-08-01 16:54:06,942 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-08-01 16:54:06,989 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-08-01 16:54:06,992 INFO sqlalchemy.engine.Engine SELECT parent.id AS parent_id 
FROM parent 
WHERE parent.id = %(pk_1)s
2024-08-01 16:54:06,992 INFO sqlalchemy.engine.Engine [generated in 0.00015s] {'pk_1': 2}
2024-08-01 16:54:07,007 INFO sqlalchemy.engine.Engine SELECT child.id AS child_id, child.parent_id AS child_parent_id 
FROM child 
WHERE %(param_1)s = child.parent_id
2024-08-01 16:54:07,007 INFO sqlalchemy.engine.Engine [generated in 0.00013s] {'param_1': 2}
2024-08-01 16:54:07,020 INFO sqlalchemy.engine.Engine DELETE FROM child WHERE child.id = %(id)s
2024-08-01 16:54:07,021 INFO sqlalchemy.engine.Engine [generated in 0.00014s] {'id': 3}
2024-08-01 16:54:07,029 INFO sqlalchemy.engine.Engine DELETE FROM parent WHERE parent.id = %(id)s
2024-08-01 16:54:07,029 INFO sqlalchemy.engine.Engine [generated in 0.00010s] {'id': 2}
2024-08-01 16:54:07,040 INFO sqlalchemy.engine.Engine COMMIT

查询parent记录：select * from parent where id=2;
查询关联的children： select * from child where parent_id=2;
删除关联的children： delete from child where id=3
删除parent记录： delete from parent where id=2

现在来看看失败的场景

2024-08-01 19:23:30,522 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2024-08-01 19:23:30,522 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-08-01 19:23:30,538 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2024-08-01 19:23:30,538 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-08-01 19:23:30,543 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
2024-08-01 19:23:30,543 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-08-01 19:23:30,552 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-08-01 19:23:30,553 INFO sqlalchemy.engine.Engine DELETE FROM parent WHERE parent.id = %(id_1)s
2024-08-01 19:23:30,554 INFO sqlalchemy.engine.Engine [generated in 0.00009s] {'id_1': 1}

直接执行了 DELETE FROM parent WHERE parent.id = 1

问题说明

这里就有疑问了，Parent 和 Child 不是通过{js}relationship()方法声明了级联删除吗？为什么删除Parent的时候不会自动删除其children记录呢？

原因就是 通过{js}relationship() 声明的级联删除，并不是通过数据库的外键约束来执行，而是 sqlalchemy orm的行为。就像上面删除p2的时候所执行的操作那样。

Note: relationship() 并不会生成数据库外键级联删除配置。

CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

而删除p1时执行的session.query().filter().delete() 方法不会事先将Parent以及其关联的 children 拉取下来，而是直接调用DELETE来删除。意味着relationship()定义的关联在这个场景中不会生效。

解决

既然已经发现问题所在，那上面的代码该怎么改呢？

两个方案：

一个一个删除

parents = Parent.query.all()
for p in parent:
	db.session.delete(p)
db.session.commit()

增加数据库外键级联删除配置

class Parent(db.Model, BaseModel):
__tablename__ = 'parent'

id = Column(Integer, primary_key=True)
children = relationship('Child', backref="parent", passive_deletes=True)

class Child(db.Model, BaseModel):
    __tablename__ = 'child'

    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id', ondelete='CASCADE'))

在定义Child 外键时，在{js}ForeignKey() 中增加 ondelete='CASCADE'
值得一提的是参数passive_deletes , 这里直接引用官方文档的说明：

passive_deletes=False

Indicates loading behavior during delete operations.

A value of True indicates that unloaded child items should not be loaded during a delete operation on the parent. Normally, when a parent item is deleted, all child items are loaded so that they can either be marked as deleted, or have their foreign key to the parent set to NULL. Marking this flag as True usually implies an ON DELETE <CASCADE|SET NULL> rule is in place which will handle updating/deleting child rows on the database side.

Additionally, setting the flag to the string value ‘all’ will disable the “nulling out” of the child foreign keys, when the parent object is deleted and there is no delete or delete-orphan cascade enabled. This is typically used when a triggering or error raise scenario is in place on the database side. Note that the foreign key attributes on in-session child objects will not be changed after a flush occurs so this is a very special use-case setting. Additionally, the “nulling out” will still occur if the child object is de-associated with the parent.

大意就是说默认情况下 passive_deletes 取值为False，此时在删除parent的时候，关联的children也会被清理或者对应的外键设置为NULL。当设置为True时，则不会执行这些操作。意味着此时需要由数据库外键约束来处理。因此当设置了外键约束时，会选择将 passive_deletes 设置为 True

我想应该是了解决有些数据库不支持外键功能，需要在orm这一层来实现外键约束的功能。例如早期的sqllite