110.SQLAlchemy中子表的查询

from random import randint,choice
from sqlalchemy import Column, Integer, String,ForeignKey,func
from sqlalchemy.orm import relationship,backref
from util_db import Base, Session


class User(Base):
    __tablename__ = 't_user'
    id = Column(Integer, primary_key = True, autoincrement=True)
    name = Column(String(32))
    age = Column(Integer)

    def __repr__(self):
        return f'<User: id={self.id} name = {self.name} age = {self.age}>'


def create_data():
    Base.metadata.drop_all()
    Base.metadata.create_all()
    names = ['百战','尚学堂','百战程序','北京尚学堂']
    with Session() as ses:
        for i in range(20):
            user = User(name=choice(names), age = randint(3,10))
            ses.add(user)
        ses.commit()

def query_data_old():
    '''
    select u.id,u.age from t_user u where (
        select name, age from t_user where name="百战") z
    z.name = u.name and u.age=z.age
    '''
    # 获取一个名为百战的人， 获取与他同名同年龄的人
    with Session() as ses:
        # 执行了两次才出结果
        u = ses.query(User).filter(User.name == "百战").first()
        users = ses.query(User).filter(u.age == User.age, u.name == User.name).all()
        print(users)

def query_data_new():
    with Session() as ses:
        # 生成一个子查询sql语句，不是结果！！！
        #                           改名字，类似于sql中的AS                                                这里不能和old一样写first(),因为生成的是sql，只可以使用limit得到第一条数据
        #          
        sub_sql = ses.query(User.name.label('uname'), User.age.label('uage')).filter(User.name == "百战").limit(1).subquery() # subquery用于告诉这是子查询语句
       #                            获取子查询中的参数得通过c来获取
        rs = ses.query(User).filter(User.name == sub_sql.c.uname, User.age==sub_sql.c.uage).all()
        print(rs)

if __name__ == "__main__":
    # create_data()
    # query_data_old()
    query_data_new()