99.SQLAlchemy关联表删除策略设置

from turtle import title
from util_db import Base, Session
from sqlalchemy import Column, Integer, String, Text, ForeignKey

# 创建表t_user
class User(Base):
    __tablename__ = 't_user'
    id = Column(Integer, primary_key = True, autoincrement = True)
    uname = Column(String(50), nullable = False, name = 'name')

#创建表t_news
class News(Base):
    __tablename__ = 't_news'
    id = Column(Integer, primary_key = True, autoincrement = True)
    title = Column(String(50), nullable = False)
    content = Column(Text, nullable = False)
                        # 外键（关联哪个表的哪个属性）
    '''
    # 若子表中有父表对应的关联数据，删除父表对应的数据，会阻止删除 默认
    uid = Column(Integer, ForeignKey('t_user.id'))
    SQL:  delete from t_user where id = 1

    Cannot delete or update a parent row: a foreign key constraint fails 
    (`flask_db`.`t_news`, CONSTRAINT `t_news_ibfk_1` FOREIGN KEY (`uid`) REFERE
    NCES `t_user` (`id`))
    '''

    '''
    # 若子表中有父表对应的关联数据，删除父表对应的数据，会阻止删除 默认
    uid = Column(Integer, ForeignKey('t_user.id',ondelete='RESTRICT'))
    # SQL:  delete from t_user where id = 1

    # Cannot delete or update a parent row: a foreign key constraint fails 
    # (`flask_db`.`t_news`, CONSTRAINT `t_news_ibfk_1` FOREIGN KEY (`uid`) REFERE
    # NCES `t_user` (`id`))
    '''
    
    '''
    # 级联删除
    uid = Column(Integer, ForeignKey('t_user.id',ondelete='CASCADE'))
    # 会成功删除两个表中的数据
    delete from t_user where id = 1

    AffectedRows : 1
    '''
    
    #  父表对应数据被删除，子表数据会设置为null
    uid = Column(Integer, ForeignKey('t_user.id',ondelete='SET NULL'))
    

# 添加数据
class create_data():
    user = User(uname = 'sxt')
    news1 = News(title = 'python',content = 'flask', uid = 1)
    news2 = News(title = 'MySQL', content = 'SQL', uid = 1)
    # 先添加user的，再添加news。否则无法关联，容易报错
    with Session() as ses:
        ses.add(user)
        ses.commit()
    with Session() as ses:
        ses.add(news1)
        ses.add(news2)
        ses.commit()
        

if __name__ == "__main__":
    Base.metadata.create_all()
    create_data()