python全栈025-----高级查询和子查询

高级查询和子查询

初始代码：

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,DECIMAL,Boolean,Enum,DateTime,TEXT,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship

HOSTNAME = '127.0.0.1'
DATABASE = 'class'
PORT = 3306
USERNAME = 'root'
PASSWORD = 'root'
DB_URL = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE)
engine = create_engine(DB_URL)
Base = declarative_base(engine)

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(50))
    gender = Column(Enum('男','女'))
    age = Column(Integer)

    def __str__(self):
        return 'Article(name:%s)'% self.title

Base.metadata.create_all()
Session = sessionmaker(bind=engine)
session = Session()

import random
for i in range(10):
    user = User(name = 'zaaaacki',gender = '男',age = random.randint(10,25))
    session.add(user)
session.commit()

生成数据：

group_by:一般通过聚合函数一起使用

• 我们想通过性别来分组查找

result = session.query(User.gender).group_by(User.gender).all()
print(result)

• 运行代码：分组成功，
• 我们想要知道男女两组分别多少人怎么办呢，我们就要用到我们的聚合函数了
• 通过id使用聚合函数

result = session.query(User.gender,func.count(User.id)).group_by(User.gender).all()
print(result)

• 运行代码：男6，女4和我们的数据一样。成功获取到我们分组个每组人数
• 我们按照年龄来分组呢

result = session.query(User.age,func.count(User.id)).group_by(User.age).all()
这样也行result = session.query(User.age,func.count(User.age)).group_by(User.age).all()#count后面一个用的是id一个是age
print(result)

• 运行代码：成功

having：having是对查找结果进一步过滤

• 如果我们想查找小于18岁的呢，在group_by后面写入having方法，添加查询条件

result = session.query(User.age,func.count(User.id)).group_by(User.age).having(User.age<18).all()
print(result)

• 运行代码：成功

join方法：

join查询分为两种，一种是inner join，另一种是outer join。默认的是inner join，如果指定left join或者right join则为outerjoin。

子查询：

sqlalchemy也支持子查询，比如现在要查找一个用户的用户名以及该用户的邮箱地址数量。要满足这个要求，可以在子查询中找到所有用户的邮箱数（通过group by合并同一用户），然后再讲结果放在父查询中进行使用：

• 我们填写新的数据：
• 如果我们想查询和张三在同一个城市同样年龄的人，我们一般会这么操作

user = session.query(User).filter(User.name == '张三').first()
data = session.query(User).filter(User.city ==user.city,User.age ==user.age).all()
for i in data:
    print(i)

• 运行代码：
• 如果我们想使用子查询该怎么写呢：我们需要给User.city起别名age也是，要不然会报错，找不到city和age
• 下面的sub.c.city也是固定写法

sub = session.query(User.city.label('city'),User.age.label('age')).filter(User.name =='张三').subquery()
data = session.query(User).filter(User.city ==sub.c.city,User.age == sub.c.age).all()
for item in data:
    print(item)

• 运行代码：子查询写起来有些麻烦，但是我们应该了解，万一以后碰到呢