108.SQLAlchemy数据的分组

from random import randint
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()
    with Session() as ses:
        for i in range(100):
            user = User(name=f'name{i}', age = randint(1,100))
            ses.add(user)
        ses.commit()

# 测试1： 统计每个年龄的人数
def query_by_age():
    with Session() as ses:
        #              无需获取全部字段， 统计年龄数量
        user = ses.query(User.age, func.count(User.id)).group_by(User.age)
        print(user.all())
        print(type(user)) # 这是一个query对象，说明可以进行二次过滤
    
# 测试2： 统计大于18的人数
def query_by_gt_18():
    with Session() as ses:
        #              无需获取全部字段， 统计年龄数量
        user = ses.query(User.age, func.count(User.id)).group_by(User.age).having(User.age >18).all()
        print(user)
    

if __name__ == "__main__":
    # create_data()
    # query_by_age()
    query_by_gt_18()