group_by和having的用法

#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)
    age = Column(Integer,default=0)
    gender = Column(Enum('male','female','secret'),default='male')


# Base.metadata.drop_all()
#
# Base.metadata.create_all()
#
# user1 = User(username='小一',age=17,gender='male')
# user2 = User(username='小二',age=18,gender='male')
# user3 = User(username='小三',age=19,gender='male')
# user4 = User(username='小四',age=20,gender='female')
# user5 = User(username='小五',age=21,gender='female')
# user6 = User(username='小六',age=22,gender='male')
#
# session.add_all([user1,user2,user3,user4,user5,user6])
# session.commit()

#用group_by根据年龄来分组
result = session.query(User.gender,func.count(User.id)).group_by(User.gender)
print(result)
'''
SELECT user.gender AS user_gender, count(user.id) AS count_1 
FROM user GROUP BY user.gender

'''

#用group_by和having一起用

ret = session.query(User.gender,func.count(User.id)).group_by(User.gender).having(User.age>20)
print(ret)

'''
SELECT user.gender AS user_gender, count(user.id) AS count_1, user.age AS user_age 
FROM user GROUP BY user.gender 
HAVING user.age > %(age_1)s
'''