关联关系

一对多 : 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
    ]
}