subquery 子查询

#encoding: utf-8

from sqlalchemy import create_engine,Column,Integer,String,Float,func,and_,or_,Enum
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from random import randint

HOSTNAME = '127.0.0.1'

PORT = 3306

DATABASE = 'first_sqlalchemy'

USERNAME = 'root'

PASSWORD = '123456'

#dialect+driver://username:password@host:port/database
DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/" \
         "{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)

engine = create_engine(DB_URI)

Base = declarative_base(engine)

# Session = sessionmaker(engine)
# session = Session()

session = sessionmaker(engine)() #Session(**local_kw)


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),unique=True,nullable=False)
    city = Column(String(50),nullable=False)
    age = Column(Integer,default=0)
    # gender = Column(Enum('male','female','secret'),default='male')

    def __repr__(self):
        return "<User(username: %s)>"%self.username

Base.metadata.drop_all()

Base.metadata.create_all()

user1 = User(username='小李',city='长沙',age=18)
user2 = User(username='小王',city='长沙',age=18)
user3 = User(username='小b',city='北京',age=18)
user4 = User(username='小a',city='长沙',age=20)

session.add_all([user1,user2,user3,user4])
session.commit()


#寻找和李A这个人在同一个城市，并且是同年龄的人
user = session.query(User).filter(User.username=='小李').first()
users = session.query(User).filter(User.city==user.city,User.age==user.age).all()
print(users)

#用原生sql子查询怎么实现
'''
select user.id,user.username,user.city,user.age 
from user,
(select user.username,user.age,
user.city from user where user.username='小李') as li_a  
where user.city = li_a.city and user.age=li_a.age;
'''

#sqlalchemy 提供子查询 subquery，为什么要用它，对于复杂的查询，要用到它，
# 相比能提升效率
#先写原生sql里面的，然后再写外面的
#label相当于原生sql里面的as
user_sub = session.query(User.city.label('city'),User.age.label('age')).filter(
    User.username == '小李'
).subquery()#这里使用子查询
#c相当于column
user_parent = session.query(User).\
    filter(User.age==user_sub.c.age,User.city==user_sub.c.city).all()
print(user_parent)

"""
SELECT user.id AS user_id, user.username AS user_username, user.city AS user_city, user.age AS user_age 
FROM user, (SELECT user.city AS city, user.age AS age 
FROM user 
WHERE user.username = %(username_1)s) AS anon_1 
WHERE user.age = anon_1.age AND user.city = anon_1.city
"""