外键与其四种约束---一对多

外键

再MySQL中，外键可以让表之间的关系更加紧密，而SQLAlchemy同样也支持外键。通过ForeignKey类来实现，并且可以指定表的外键来约束。
基础代码同上篇一样！

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

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

    # 表中的字段 创建外键以id相关联
    uid = Column(Integer, ForeignKey('user.id'))

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

# Session = sessionmaker(bind=engine)
# session = Session() # 可以简写成一句
session = sessionmaker(bind=engine)()

# user = User(username='happy')
# session.add(user)
# session.commit()

# article = Article(title='lek', content='sssssy', uid=3)
# session.add(article)
# session.commit()

外键约束有一下几项：

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

uid = Column(Integer, ForeignKey('user.id', ondelete='RESTRICT'))
# uid = Column(Integer, ForeignKey('user.id', ondelete='CASCADE'))
# uid = Column(Integer, ForeignKey('user.id', ondelete='SET NULL'))

# ORM 删除user表中的数据 与外键的约束有关，在数据库中删还是用ORM都是一样的！
user = session.query(User).first()
session.delete(user)
session.commit()

查询外键

显示数据，要在User类中，定义str方法：

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50))
	def __str__(self):
   		 return "User(username:%s)" % self.username
    
# 查询外键
# 第一种方法 ，较麻烦
# article = session.query(Article).first()
# uid = article.uid
# user = session.query(User).get(uid)
# print(user)

# 第二种
user = session.query(User).filter(User.id == Article.uid).first()
print(user)

表的一对多

# 一对多 表关系
class Article(Base):

     __tablename__ = 'article'
     id = Column(Integer, primary_key=True, autoincrement=True)
     title = Column(String(50))
     content = Column(Text, nullable=False)
     uid = Column(Integer, ForeignKey('user.id', ondelete='SET NULL'))
     author = relationship('User')

     def __str__(self):
         return "User(Article:%s)" % self.title

 # 一个用户对应多篇文章
class User(Base):
     __tablename__ = 'user'
     id = Column(Integer, primary_key=True, autoincrement=True)
     username = Column(String(50), nullable=False)

     articles = relationship("Article")

     def __str__(self):
         return "User(username:%s)" % self.username

# Base.metadata.drop_all()
Base.metadata.create_all()
session = sessionmaker(bind=engine)()

# 查询
# 根据文章查询作者
# article = session.query(Article).first()
# print(article.author)

# 根据作者 查询文章
user = session.query(User).first()
article = user.articles

for data in article:
    print(data)

添加数据：

# 添加单条数据
# user = User(username='gd')
# article = Article(title='smeil', content='sdesd')
# article.author = user
# session.add(article)
# session.commit()

# 添加多条数据
user = User(username='dh')
article1 = Article(title='lo', content='sss')
article2 = Article(title='ve', content='yyy')
article1.author = user
article2.author = user

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

反向访问数据，

class Article(Base):

     __tablename__ = 'article'
     id = Column(Integer, primary_key=True, autoincrement=True)
     title = Column(String(50))
     content = Column(Text, nullable=False)
     uid = Column(Integer, ForeignKey('user.id', ondelete='SET NULL'))
     # author = relationship('User')

     def __str__(self):
         return "User(Article:%s)" % self.title

 # 一个用户对应多篇文章
class User(Base):
     __tablename__ = 'user'
     id = Column(Integer, primary_key=True, autoincrement=True)
     username = Column(String(50), nullable=False)

     # articles = relationship("Article")
     # 反向访问属性  注销上一个relationship，添加backref，
     articles = relationship("Article", backref='author')


     def __str__(self):
         return "User(username:%s)" % self.username