users = session.query(func.count(User.name).label('count'), User.age).group_by(User.age)
for user in users:
print 'age:{0}, count:{1}'.format(user.age, user.count)
exists查询(不存在则为~exists())
from sqlalchemy.sql import exists
session.query(User.name).filter(~exists().where(User.role_id == Role.id))
SELECT name AS users_name FROM users WHERE NOT EXISTS (SELECT * FROM roles WHERE users.role_id = roles.id)
除了exists,any也可以表示EXISTS
session.query(Role).filter(Role.users.any())
random
from sqlalchemy.sql.functions import random
user = session.query(User).order_by(random()).all()