from .models import Goods

from app.extensions import db

goods1 = Goods(name='魅族18s', price=3400, cate_id=4)

goods2 = Goods(name='华为P40', price=5200, cate_id=4)

goods3 = Goods(name='OPPO Note3', price=2759, cate_id=4)

db.session.add(goods1) # 添加一条数据

db.session.add_all([goods2, goods3])  # 添加多条数据

db.commit() # 提交

# 查询全部数据

goods = Goods.query.all() 

# 按照条件过滤， 结果为查询集，需要循环解析

goods = Goods.query.filter_by(name='华为P40')

# 按照 id主键查询，得到商品对象, 不存在，结果为None

goods = Goods.query.get(1)

# 按照id查询，不存在，抛出404异常

goods = Goods.query.get_or_404(100)

# 先查询对象，后修改属性，最后提交即可

goods = Goods.query.get(1)

goods.sales = 200

db.session.commit()   

# 使用filter_by先过滤，后传入字典更新，最后提交,此种方式，同时可以更新多条记录

Goods.query.filter_by(id=2).update({'stock':3000})

db.session.commit()

# 先查询对象，后删除对象, 最后提交

goods6 = Goods.query.get(6)

db.session.delete(goods6)

db.session.commit()  

# 使用filter_by先过滤，后删除，最后提交，此种方式，同时可以删除多条记录

Goods.query.filter_by(id=5).delete()

db.session.commit()

# 查询id大于3的所有商品

goods = Goods.query.filter(Goods.id > 3).all()

# 查询 id 不等于 3的所有商品

goods = Goods.query.filter(Goods.id != 3).all()

# 查询 id不等于4的所有商品

goods = Goods.query.filter(not_(Goods.id == 4)).all()

from sqlalchemy import or_, and_, not_

# 满足任何一个条件即可

goods = Goods.query.filter(or_(Goods.id < 2, Goods.id > 3)).all()

goods = Goods.query.filter(Goods.id.in_([1, 3, 5]) ).all()

# 必须多个条件同时成立

goods = Goods.query.filter(and_(Goods.id < 5, Goods.id > 2)).all()

goods = Goods.query.filter(Goods.id.between(2, 6)).all()

# 多个条件之间，默认就是 且的关系

goods = Goods.query.filter(Goods.id < 4, Goods.id > 2).all()   

# 查询相反结果

goods = Goods.query.filter(~(Goods.id < 4)).all()

goods = Goods.query.filter(not_(Goods.id < 4)).all()

goods = Goods.query.filter(not_(Goods.id.in_([1, 3, 5]) )).all()

# 直接判断 值是否为None

cates = Cate.query.filter(Cate.parent_id==None).all()

# 使用 is_ 方法进行判断

cates = Cate.query.filter(Cate.parent_id.is_(None)).all()

# 查询商品名包含 魅族的 商品信息

goods = Goods.query.filter(Goods.name.like('%魅族%') ).all()

from sqlalchemy import desc, asc

# 按照价格升序排序

goods = Goods.query.order_by('price').all()

goods = Goods.query.order_by(asc('price')).all()

# 按照价格降序排序

goods = Goods.query.order_by(desc('price')).all()

# 数据分页，第1页数据, 每页2条

pagination = Goods.query.order_by(desc('price')).paginate(1, 2)

# 当前页的数据

goods = paginaton.items

# 总的页码

pages = pagination.pages

# 总的记录数

total = pagination.total

# 当前页码

page = pagination.page

from sqlalchemy import func 

res = db.session.query(func.max(Goods.price), func.min(Goods.price),  func.count(Goods.id) ).all()

# [(Decimal('5200.00'), Decimal('2759.00'), 4)]

res = Goods.query.with_entities(func.max(Goods.price), func.min(Goods.price),  func.count(Goods.id) ).all()

# [(Decimal('5200.00'), Decimal('2759.00'), 4)]

res = db.session.query(Goods.cate_id,Cate.name, func.max(Goods.price), func.min(Goods.price),  func.count(Goods.id) ).filter(Goods.cate_id == Cate.id).group_by(Goods.cate_id).all()

res = Goods.query.join(Cate, Goods.cate_id==Cate.id).group_by(Goods.cate_id).with_entities(Cate.id, Cate.name, func.max(Goods.price), func.min(Goods.price),  func.count(Goods.id)).all()

res = Goods.query.filter(Goods.cate_id==Cate.id).group_by(Goods.cate_id).with_entities(Cate.id, Cate.name, func.max(Goods.price), func.min(Goods.price),  func.count(Goods.id)).all()

# [(4, '手机', Decimal('5200.00'), Decimal('3400.00'), 3), (5, '游戏手机', Decimal('2759.00'), Decimal('2759.00'), 1)]

# 以下两种方式都可

res = Goods.query.filter(Goods.stock < 5000).update({'stock': Goods.stock + 2000})

res2 = Goods.query.filter(Goods.stock < 3000).update({Goods.stock: Goods.stock + 3000})

# 注意：更新之后，需要保存

db.session.commit()

goods = Goods.query.get(3)

goods.stock -= 400

db.session.commit()