sqlalchemy

with app.request_context(environ):
    db.session.query(User).filter(User.id==1).update({"name":"python"})
    db.session.commit()
    print(db.session.query(User).get(1).name)

    # 事务
    user = User(mobile="13412341230", name="python111")
    db.session.add(user)
    db.session.flush()
    profile = UserProfile(id=user.id)
    db.session.add(profile)
    db.session.commit()
    print(user.name)

    from sqlalchemy.orm import contains_eager, load_only

    print(db.session.query(Relation).join(Relation.user).options(load_only(Relation.id, Relation.target_user_id),contains_eager(Relation.user).load_only(User.name)).filter(Relation.user_id == 1).all())
    select a.user_id,a.taget_user_id,b.user_name  from user_ralation as a inner join user_basic as b on a.taget_user_id=b.user_id where a.user_id=1;
    user = db.session.query(User).filter(User.id==1).first()
    print(user.profile)





    1.查询手机号码为18516952650的用户
    print(db.session.query(User).filter(User.mobile == "18516952650").first().name)

    2.查询手机号以13开始的, 并且发布文章的数量大于10篇的用户
    print(db.session.query(User).filter(and_(User.mobile.startswith("13"), User.article_count>10)).all())

    3.查询手机号以13结尾的, 或者发布文章的数量大于10篇的用户
    print(db.session.query(User).filter(or_(User.mobile.endswith("13"),User.article_count>10)).all())

    4.查询手机号不是以13结尾的用户
    print(db.session.query(User).filter(not_(User.mobile.endswith("13"))).all())

    5.查询手机号不是以13结尾的用户,获取前面的第6条开始的5条数据(第二页数据)
    print(db.session.query(User).filter(not_(User.mobile.endswith("13"))).offset(6).limit(5).all())
    users = db.session.query(User).options(load_only(User.id, User.mobile)).filter(not_(User.mobile.endswith("13"))).offset(
        5).limit(5).all()
    for i in users:
        print(i.id, i.mobile)

    6.查询手机号不是以13结尾的用户,以发用户id排序(正/降),获取前面5条数据
    print(db.session.query(User).filter(not_(User.mobile.endswith("13"))).desc().all())
    users = db.session.query(User).options(load_only(User.id, User.mobile)).filter(not_(User.mobile.endswith("13"))).order_by(User.id.desc()).offset(5).limit(5)
    for i in users:
        print(i.id, i.mobile)

    7.查询关系表,以user_id分组, 查出每个用户当前的关注数量(relation=1).
    users = db.session.query(Relation.user_id, func.count(Relation.id)).filter(Relation.relation==1).group_by(Relation.user_id).all()
    print(users)


    print(db.session.query(User).all())
    print(db.session.query(User).filter(User.mobile=="18516952650").first().name)
    print(db.session.query(User).filter(and_(User.mobile.startswith("13"), User.article_count>10)).all())

    print(User.query.get(1).mobile)
    print(db.session.query(User).get(1).name)

    users = db.session.query(User).get(1)
    print(users.name)
    users = db.session.query(Relation.user_id, func.count(Relation.target_user_id)).group_by(Relation.user_id).all()
    print(users)


    添加
    user = User(mobile="13412341234",name="python")
    db.session.add(user)
    db.session.commit()

    user1 = User(mobile="13412341235",name="python2")
    user2 = User(mobile="13412341236", name="python3")
    db.session.add_all([user1,user2])
    db.session.commit()

    print(User.query.first().mobile)

    print(User.query.filter_by(mobile="13412341234")[0].name)
    print(User.query.filter_by(mobile="13412341234",id=1)[0].name)

    user = User.query.filter(User.mobile=="13412341234")[0]
    print(user.name)
    users = User.query.filter(User.id>1)
    users = User.query.filter(or_(User.id>2, User.mobile=="13412341234"))

    users = User.query.filter(and_(User.id>0,User.mobile=="13412341234"))
    users = User.query.filter(or_(User.mobile=="13811111111", User.id==1)).all()

    users = User.query.all()
    users = User.query.filter(and_(User.name=="黑马头条号1",User.id==1))
    users = User.query.filter(User.name.startswith("13")).order_by(User.id.asc()).offset(2).limit(5).all()

    user = User.query.get(1)
    print(user.name)

    users = User.query.options(load_only(User.name,User.mobile)).filter(User.id==2).first()
    print(users.name)