group_by和having的⽤法
#encoding: utf-8
from sqlalchemy import create_engine,Column,Integer,String,Float,func,and_,or_,Enummysql group by order by
declarative import declarative_base
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')
# adata.drop_all()
#
# ate_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])
# sessionmit()
#⽤group_by根据年龄来分组
result = session.unt(User.id)).group_der)
print(result)
'''
der AS user_gender, count(user.id) AS count_1
FROM user GROUP der
'''
#⽤group_by和having⼀起⽤
ret = session.unt(User.id)).group_der).having(User.age>20)
print(ret)
'''
der AS user_gender, count(user.id) AS count_1, user.age AS user_age
FROM user GROUP der
HAVING user.age > %(age_1)s
'''

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。