# coding:utf-8

 # Author: liangjun.chen

 from datetime import datetime

 from sqlalchemy import create_engine, Column, Integer, String, Enum, func

 from sqlalchemy.ext.declarative import declarative_base

 from sqlalchemy.orm import sessionmaker, relationship, backref

 HOSTNAME = '127.0.0.1'

 PORT = 3306

 DATABASE = 'sqlalchemy_first'

 USERNAME = 'root'

 PASSWORD = ''

 DB_URI = 'mysql+pymysql://{username}:{password}@{host}:{port}/{dbname}?charset=utf8'.format(

         username=USERNAME, password=PASSWORD, host=HOSTNAME, port=PORT, dbname=DATABASE

 )

 engine = create_engine(DB_URI)

 Base = declarative_base(engine)

 Session = sessionmaker(engine)

 session = Session()

 class User(Base):

     __tablename__ = 'user'

     id = Column(Integer, primary_key=True, autoincrement=True)

     username = Column(String(50), nullable=False)

     age = Column(Integer, default=0)

     gender = Column(Enum('male', 'female', "secret"), default='male')

     def __repr__(self):

         return "User<{}>".format(self.username)

 # Base.metadata.drop_all()

 # Base.metadata.create_all()

 #

 # user = User(username='saber', age=17, gender='male')

 # user2 = User(username='saber2', age=18, gender='male')

 # user3 = User(username='saber3', age=18, gender='female')

 # user4 = User(username='saber4', age=19, gender='female')

 # user5 = User(username='saber5', age=19, gender='female')

 #

 # session.add_all([user, user2, user3, user4, user5])

 # session.commit()

 # group_by

 users = session.query(User.age, func.count(User.id)).group_by(User.age)

 print users.all()

 # having

 users = session.query(User.age, func.count(User.id)).group_by(User.age).having(User.age>18).all()

 print users