#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 = ''

#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

'''