sql server 外键关系 强制用于复制 sql外键关联

SQLAlchemy03 /外键、连表关系

目录

• SQLAlchemy03 /外键、连表关系

• 1、外键
• 2、ORM关系以及一对多
• 3、一对一的关系
• 4、多对多的关系
• 5、ORM层面的删除数据
• 6、ORM层面的CASCADE

1、外键

• 外键简述
  使用SQLAlchemy创建外键非常简单。在从表中增加一个字段，指定这个字段外键的是哪个表的哪个字段就可以了。从表中外键的字段，必须和父表的主键字段类型保持一致。
• 示例代码如下：

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

class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    content = Column(Text,nullable=False)

    uid = Column(Integer,ForeignKey("user.id"))

• 外键约束有以下几项：

1. RESTRICT：父表数据被删除，会阻止删除。默认就是这一项。 
2. NO ACTION：在MySQL中，同RESTRICT。 
3. CASCADE：级联删除。 
4. SET NULL：父表数据被删除，子表数据会设置为NULL。

2、ORM关系以及一对多

• 简述
  mysql级别的外键，还不够ORM，必须拿到一个表的外键，然后通过这个外键再去另外一张表中查找，这样太麻烦了。SQLAlchemy提供了一个relationship，这个类可以定义属性，以后在访问相关联的表的时候就直接可以通过属性访问的方式就可以访问得到了。
• 示例代码：

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

    # articles = relationship("Article")

    def __repr__(self):
        return "<User(username:%s)>" % self.username

class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    content = Column(Text,nullable=False)
    uid = Column(Integer,ForeignKey("user.id"))

    author = relationship("User",backref="articles")

# 创建一对多数据
user = User(username='zhangsan')
session.add(user)
session.commit()

article = Article(title='abc',content='123',uid=1)
session.add(article)
session.commit()

# 查询一对多数据
article = session.query(Article).first()
uid = article.uid
print(article)
user = session.query(User).get(uid)
print(user)

• 另外，可以通过backref来指定反向访问的属性名称。articles是有多个。他们之间的关系是一个一对多的关系

3、一对一的关系

• 简述
  在sqlalchemy中，如果想要将两个模型映射成一对一的关系，那么应该在父模型中，指定引用的时候，要传递一个uselist=False这个参数进去。就是告诉父模型，以后引用这个从模型的时候，不再是一个列表了，而是一个对象了。
• 示例代码如下：

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

    extend = relationship("UserExtend",uselist=False)

    def __repr__(self):
        return "<User(username:%s)>" % self.username

class UserExtend(Base):
    __tablename__ = 'user_extend'
    id = Column(Integer, primary_key=True, autoincrement=True)
    school = Column(String(50))
    uid = Column(Integer,ForeignKey("user.id"))

    user = relationship("User",backref="extend")

简化代码：可以借助sqlalchemy.orm.backref来简化代码

from sqlalchemy.orm import sessionmaker,relationship,backref

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)
	# 方式一：
    # extend = relationship("UserExtend",uselist=False)

    def __repr__(self):
        return "<User(username:%s)>" % self.username

class UserExtend(Base):
    __tablename__ = 'user_extend'
    id = Column(Integer, primary_key=True, autoincrement=True)
    school = Column(String(50))
    uid = Column(Integer,ForeignKey("user.id"))
	# 方式二：
    user = relationship("User",backref=backref("extend",uselist=False))

# 创建一对一数据
user = User(username='zhangsan')
extend1 = UserExtend(school='qinghua daxue')
user.extend = extend1

session.add(user)
session.commit()

# 查看一对一数据
# 可以直接根据属性去查询对应的数据

4、多对多的关系

• 简述

1. 多对多的关系需要通过一张中间表来绑定他们之间的关系。
2. 先把两个需要做多对多的模型定义出来
3. 使用Table定义一个中间表，中间表一般就是包含两个模型的外键字段就可以了，并且让他们两个来作为一个“复合主键”。
4. 在两个需要做多对多的模型中随便选择一个模型，定义一个relationship属性，来绑定三者之间的关系，在使用relationship的时候，需要传入一个secondary=中间表。

• 代码示例：

article_tag = Table(
    "article_tag",
    Base.metadata,
    Column("article_id",Integer,ForeignKey("article.id"),primary_key=True),
    Column("tag_id",Integer,ForeignKey("tag.id"),primary_key=True)
)

class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    # 方式一：
    # tags = relationship("Tag",backref="articles",secondary=article_tag)

    def __repr__(self):
        return "<Article(title:%s)>" % self.title

class Tag(Base):
    __tablename__ = 'tag'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False)
    # 方式二：
    articles = relationship("Article",backref="tags",secondary=article_tag)

    def __repr__(self):
        return "<Tag(name:%s)>" % self.name
    
# 添加多对多数据
article1 = Article(title="article1")
article2 = Article(title="article2")

tag1 = Tag(name='tag1')
tag2 = Tag(name='tag2')

article1.tags.append(tag1)
article1.tags.append(tag2)

article2.tags.append(tag1)
article2.tags.append(tag2)

session.add(article1)
session.add(article2)

session.commit()

# 查询多对多数据
article = session.query(Article).first()
print(article.tags)

tag = session.query(Tag).first()
print(tag.articles)

5、ORM层面的删除数据

• 简述：
  ORM层面删除数据，会无视mysql级别的外键约束。直接会将对应的数据删除，然后将从表中的那个外键设置为NULL。如果想要避免这种行为，应该将从表中的外键的nullable=False。
• 代码示例：

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

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

    author = relationship("User",backref='articles')

# Base.metadata.drop_all()
# Base.metadata.create_all()
#
# user = User(username='zhiliao')
# article = Article(title='hello world')
# article.author = user
# session.add(article)
# session.commit()

# orm层面的删除，删除User时Article表关联的字段会置为NULL
# 将uid设置为nullable=False，就不会造成上面的orm层面的删除
user = session.query(User).first()
session.delete(user)
session.commit()

6、ORM层面的CASCADE

• 在SQLAlchemy，只要将一个数据添加到session中，和他相关联的数据都可以一起存入到数据库中了。这些是怎么设置的呢？其实是通过relationship的时候，有一个关键字参数cascade可以设置这些属性：

1. save-update：默认选项。在添加一条数据的时候，会把其他和他相关联的数据都添加到数据库中。这种行为就是save-update属性影响的。
2. delete：表示当删除某一个模型中的数据的时候，是否也删掉使用relationship和他关联的数据。
3. delete-orphan：表示当对一个ORM对象解除了父表中的关联对象的时候，自己便会被删除掉。当然如果父表中的数据被删除，自己也会被删除。这个选项只能用在一对多上，不能用在多对多以及多对一上。并且还需要在子模型中的relationship中，增加一个single_parent=True的参数。
4. merge：默认选项。当在使用session.merge，合并一个对象的时候，会将使用了relationship相关联的对象也进行merge操作。
5. expunge：移除操作的时候，会将相关联的对象也进行移除。这个操作只是从session中移除，并不会真正的从数据库中删除。
6. all：是对save-update, merge, refresh-expire, expunge, delete几种的缩写。

• 代码示例：
  示例一：正常添加数据，默认cascade="save-update"

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

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

    author = relationship("User",backref="articles")



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

user = User(username='zhangsan')
article = Article(title='title')
article.author = user

session.add(user)
session.commit()

示例二：cascade=""

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

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

    author = relationship("User",backref="articles"，cascade="")



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

user = User(username='zhangsan')
article = Article(title='title')
article.author = user

session.add(user)   # 这样只会添加user不会添加article
session.commit()

示例三：cascade="delete"

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

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

    author = relationship("User",backref="articles"，cascade="save-update,delete")
    # 删除该文章，会将该文章对应的User删除


def my_init_db():
    """ 用来初始化数据 """
    Base.metadata.drop_all()
    Base.metadata.create_all()

    user = User(username='zhangsan')
    article = Article(title='title')
    article.author = user

    comment = Comment(content='xxx')
    comment.author = user
    session.add(comment)
    session.add(article)

    session.commit()


def operation():
    article = session.query(Article).first()
    session.delete(article)
    session.commit()

if __name__ == '__main__':
    # my_init_db()
    operation()

示例四：cascade="delete-orphan"删除孤儿数据，结合single_parent=True使用

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

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

    author = relationship("User",backref=backref("articles",cascade="save-update,delete,delete-orphan"),cascade="save-update",single_parent=True)

def my_init_db():
    Base.metadata.drop_all()
    Base.metadata.create_all()

    user = User(username='zhangsan')
    article = Article(title='title')
    article.author = user

    session.add(article)
    session.commit()


def operation():
    user = session.query(User).first()
    user.articles = []
    session.commit()


    session.commit()


if __name__ == '__main__':
    # my_init_db()
    operation()