关联关系
2023年8月3日大约 2 分钟
关联关系
一对多 : ForeignKey
模型类
from app.extensions import db
class Cate(db.Model):
__tablename__ = 'tb_cate'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
name = db.Column(db.String(10))
# 外键,关联自身的主键
parent_id = db.Column(db.Integer, db.ForeignKey('tb_cate.id'), nullable=True)
# 指明关联字段,和实质的表无关系,只是在查询时,实现关联查询
parent = db.relationship('Cate', remote_side=[id], backref="sub")
def __repr__(self):
return 'Cate: %s' % self.name
class Goods(db.Model):
__tablename__ = 'tb_goods'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
name = db.Column(db.String(50))
price = db.Column(db.DECIMAL(10, 2))
stock = db.Column(db.Integer, default=0)
sales = db.Column(db.Integer, default=0)
# 外键,关联分类表的主键
cate_id = db.Column(db.Integer, db.ForeignKey('tb_cate.id'))
# 指明关联字段,和实质的表无关系,只是在查询时,实现关联查询
cate = db.relationship('Cate', backref='goods')
def __repr__(self):
return 'Goods: %s' % self.name
增加数据
# 基于relation正向添加数据, 添加商品时,同时添加分类
goods = Goods(name='纳米防爆膜', price=20, cate=Cate(name='手机膜', parent_id=3))
db.session.add(goods)
db.session.commit()
# 基于relation反向添加数据, 添加分类时,同时添加商品
cate = Cate(name='手机壳', parent_id=3)
cate.goods = [Goods(name='磨砂手机壳', price=19.2), Goods(name='亲肤材质手机壳', price=9.9)]
db.session.add(cate)
db.session.commit()
查询数据
# 基于relation正向查询数据, 查询商品时,同时查询商品对应的分类信息
goods= Goods.query.get(3)
goods_data = {
'id': goods.id,
'name': goods.name,
'price': str(goods.price),
'cate': {
'id':goods.cate.id,
'name': goods.cate.name
}
}
# 基于relation反向查询数据, 查询分类时,同时查询分类对应的商品信息
cate= Cate.query.get(4)
cate_data = {
'id': cate.id,
'name': cate.name,
'goods':[
{
'id':goods.id,
'name': goods.name,
'price': str(goods.price)
} for goods in cate.goods ]
}
多对多 : ManyToMany
模型类
class Goods(db.Model):
__tablename__ = 'tb_goods'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
name = db.Column(db.String(50))
price = db.Column(db.DECIMAL(10, 2))
stock = db.Column(db.Integer, default=0)
sales = db.Column(db.Integer, default=0)
# 创建关系
tags = db.relationship("Tag", secondary="tb_tag_goods", backref="goods_list")
class Tag(db.Model):
__tablename__ = 'tb_tag'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
title = db.Column(db.String(20))
class TagToGoods(db.Model):
__tablename__ = 'tb_tag_goods'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
tag_id = db.Column(db.Integer, db.ForeignKey('tb_tag.id'))
goods_id = db.Column(db.Integer, db.ForeignKey('tb_goods.id'))
增加数据
# 正向添加
goods = Goods(name='VIVO Note3', price=12.5)
goods.tags = [Tag(title='正品'), Tag(title='便宜')]
db.session.add(goods)
db.session.commit()
# 反向添加
tag = Tag(title='假一赔十')
tag.goods_list = [Goods(name='Nova7plus', price=3299), Goods(name='Nova8Pro', price=3500)]
db.session.add(tag)
db.session.commit()
查询数据
# 正向查询
goods = Goods.query.get(8)
goods_data = {
'id': goods.id,
'name': goods.name,
'price': goods.price,
'tags': [
{
'id': tag.id,
'title': tag.title
}
for tag in goods.tags
]
}
# 反向查询
tag = Tag.query.first()
tag_data = {
'id': tag.id,
'title': tag.title,
'goods_list': [
{
'id': goods.id,
'name': goods.name,
'price': str(goods.price)
}
for goods in tag.goods_list
]
}