SQLAlchemy delete

SQLAlchemy

这里首先要提到SQLAlchemy中的外键约束，分为四种，分别是RESTRICT、NO ACTION、CASCADE、SET NULL

RESTRICT：父表数据被删除时，会被阻止。（SQLAlchemy默认为RESTRICT）
NO ACTION：MySQL中同RESTRICT。
CASCADE：父表数据被删除，子表数据对应删除（级联删除）。
SET NULL：父表数据被删除，子表对应字段设置为NULL。

首先是RESTRICT

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表创建表语句

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()

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表创建表语句

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表创建表语句

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中执行删除后，结果均为下

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表创建表语句

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中执行删除后，结果均为下

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()

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

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")