SQLAlchemy delete

SQLAlchemy

这里首先要提到SQLAlchemy中的外键约束,分为四种,分别是RESTRICTNO ACTIONCASCADESET NULL

  • RESTRICT:父表数据被删除时,会被阻止。(SQLAlchemy默认为RESTRICT
  • NO ACTION:MySQL中同RESTRICT
  • CASCADE:父表数据被删除,子表数据对应删除(级联删除)。
  • SET NULL:父表数据被删除,子表对应字段设置为NULL。

首先是RESTRICT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
from sqlalchemy import create_engine, Column, String, Integer, ForeignKey, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

HOSTNAME = '127.0.0.1'
PORT = 3306
DATABASE = 'test'
USERNAME = 'root'
PASSWORD = '123456'
DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(DB_URI)
Base = declarative_base(engine)

class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(10), nullable=False)

class Article(Base):
__tablename__ = 'article'
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(20), nullable=False)
uid = Column(Integer, ForeignKey('user.id', ondelete='RESTRICT'))

Base.metadata.drop_all()
Base.metadata.create_all()

session = sessionmaker(engine)()
u1 = User(username='Hello')
session.add(u1)
session.commit()

a1 = Article(title='标题1', uid=1)
session.add(a1)
session.commit()

查看article表创建表语句

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show create table article\G
*************************** 1. row ***************************
Table: article
Create Table: CREATE TABLE `article` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(20) NOT NULL,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
CONSTRAINT `article_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

插入数据后先在SQLAlchemy中执行删除,发生如下错误

1
2
session.delete(u1)
session.commit()

1
sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`test`.`article`, CONSTRAINT `article_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`))')

然后在MySQL数据库中用sql语句删除发生如下错误

1
2
mysql> delete from user where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`article`, CONSTRAINT `article_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`))

以上表明父表数据被删除时,会被阻止

其次是NO ACTION,这里操作为

1
uid = Column(Integer, ForeignKey('user.id', ondelete='NO ACTION'))

查看article表创建表语句

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show create table article\G
*************************** 1. row ***************************
Table: article
Create Table: CREATE TABLE `article` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(20) NOT NULL,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
CONSTRAINT `article_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`) ON DELETE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

在SQLAlchemy中执行删除操作和MySQL中执行删除操作结果均同上

接着是CASCADE,操作为

1
uid = Column(Integer, ForeignKey('user.id', ondelete='CASCADE'))

查看article表创建表语句

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show create table article\G
*************************** 1. row ***************************
Table: article
Create Table: CREATE TABLE `article` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(20) NOT NULL,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
CONSTRAINT `article_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

在SQLAlchemy中执行上述删除操作和MySQL中执行删除后,结果均为下

1
2
3
4
5
mysql> select * from article;
Empty set (0.00 sec)

mysql> select * from user;
Empty set (0.00 sec)

以上表明父表数据被删除,子表数据对应删除(级联删除)

最后是SET NULL,操作为

1
uid = Column(Integer, ForeignKey('user.id', ondelete='SET NULL'))

查看article表创建表语句

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show create table article\G
*************************** 1. row ***************************
Table: article
Create Table: CREATE TABLE `article` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(20) NOT NULL,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
CONSTRAINT `article_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

在SQLAlchemy中执行上述删除操作和MySQL中执行删除后,结果均为下

1
2
3
4
5
6
7
8
9
10
mysql> select * from user;
Empty set (0.00 sec)

mysql> select * from article;
+----+---------+------+
| id | title | uid |
+----+---------+------+
| 1 | 标题1 | NULL |
+----+---------+------+
1 row in set (0.00 sec)

以上表明父表数据被删除,子表对应字段设置为NULL

为什么要说明SQLAlchemy和数据库两个层面的删除呢
接着,修改上面的代码,在Article类中加入下面一条语句,其他地方不变。

1
user = relationship('User', backref='articles')

在数据库中执行删除操作,存在外键约束,无法删除,结果如下

1
2
mysql> delete from user where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`article`, CONSTRAINT `article_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`))

在SQLAlchemy中执行删除操作

1
2
session.delete(u1)
session.commit()

执行成功未报错,查看数据库结果

1
2
3
4
5
6
7
8
9
10
mysql> select * from article;
+----+---------+------+
| id | title | uid |
+----+---------+------+
| 1 | 标题1 | NULL |
+----+---------+------+
1 row in set (0.00 sec)

mysql> select * from user;
Empty set (0.00 sec)

以上说明在SQLAlchemy中删除成功。因为在SQLAlchemy中外键未指定nullable=False,即外键可为空,那么SQLAlchemy先删除user表的一条数据u1,然后在article表中将外键对应的字段设为空即为最终结果。
要想和数据库中操作一样,即因为外键约束无法删除,那么只需要在外键中加入nullable=False

1
uid = Column(Integer, ForeignKey('user.id'), nullable=False)

此时在SQLAlchemy中执行删除操作就会报错

1
sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1048, "Column 'uid' cannot be null")