Flask数据库_关联表的删除策略设置(外键约束)

最新推荐文章于 2024-06-04 00:00:08 发布

NewBurlaSKing

最新推荐文章于 2024-06-04 00:00:08 发布

阅读量551

点赞数

分类专栏： Flask 文章标签： flask 数据库 python

本文链接：https://blog.csdn.net/qq_55961861/article/details/126857982

版权

Flask 专栏收录该内容

47 篇文章 1 订阅

订阅专栏

Flask数据库_关联表的删除策略设置(外键约束)

一.外键约束删除策略设置介绍

RESTRICT：若子表中有父表对应的关联数据，删除父表对应数据，会阻止删除。默认项
NO ACTION：在MySQL中，同RESTRICT。
CASCADE：级联删除。删除主表与从表互相对应的数据。没有涉及的主从表数据不会被删除！
SET NULL：父表对应数据被删除，子表对应数据项会设置为 NULL。

二.实例

db_utils.py:

#coding=utf-8


from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

USER = 'root'
PWD = 'root'
HOST = '127.0.0.1'
PORT = 3306
DATA_BASE = 'flask_db'

DB_URL = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'

# 创建一个引擎
engine = create_engine(DB_URL)
Base = declarative_base(engine)
Session = sessionmaker(engine)

main.py:

#coding=utf-8


from db_utils import Base,Session
from sqlalchemy import Column,Integer,String,Text,ForeignKey

class User(Base):
    __tablename__ = 't_user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    uname = Column(String(50),name='name')

class News(Base):
    __tablename__ = 't_news'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    content = Column(Text,nullable=True)
    # uid = Column(Integer,ForeignKey('t_user.id'))   #默认不能删除
    # uid = Column(Integer,ForeignKey('t_user.id',ondelete='RESTRICT')) #不能删除
    # uid = Column(Integer,ForeignKey('t_user.id',ondelete='NO ACTION'))  #同上
    # uid = Column(Integer,ForeignKey('t_user.id',ondelete='CASCADE'))    #主表与从表相同的数据都会删除，级联删除：DELETE from t_user WHERE id=1;
    uid = Column(Integer,ForeignKey('t_user.id',ondelete='SET NULL'))

def add_data():
    user = User(uname = 'hh')
    news = News(title='Python',content='flask',uid=1)
    news1 = News(title='MySQL',content='sqlaichemy',uid=1)

    with Session() as s:
        s.add(user)
        s.commit()

    with Session() as s:
        s.add_all([news,news1])
        s.commit()

if __name__ == '__main__':
    Base.metadata.create_all()
    add_data()