1. 返回列表和标量(scalar)
前面我们注意到query对象可以返回可迭代的值(iterator value)，然后我们可以通过for in来查询。不过query对象的all()、one()以及first()方法将返回非迭代值(non-iterator value)，比如说all()返回的是一个列表：
>>> query = session.query(user).\>>> filter(user.name.like('%ed')).order_by(user.id)>>> query.all() select users.id as users_id, users.name as users_name, users.fullname as users_fullname, users.password as users_passwordfrom userswhere users.name like ? order by users.id('%ed',) [user('ed','ed jones', 'f8s7ccs'), user('fred','fred flinstone', 'blah')]
first()方法限制并仅作为标量返回结果集的第一条记录：
>>> query.first() select users.id as users_id, users.name as users_name, users.fullname as users_fullname, users.password as users_passwordfrom userswhere users.name like ? order by users.id limit ? offset ?('%ed', 1, 0)
one()方法，完整的提取所有的记录行，并且如果没有明确的一条记录行(没有找到这条记录)或者结果中存在多条记录行，将会引发错误异常noresultfound或者multipleresultsfound：
>>> from sqlalchemy.orm.exc import multipleresultsfound>>> try: ... user = query.one()... except multipleresultsfound, e:... print eselect users.id as users_id, users.name as users_name, users.fullname as users_fullname, users.password as users_passwordfrom userswhere users.name like ? order by users.id('%ed',) multiple rows were found for one()>>> from sqlalchemy.orm.exc import noresultfound>>> try: ... user = query.filter(user.id == 99).one()... except noresultfound, e:... print eselect users.id as users_id, users.name as users_name, users.fullname as users_fullname, users.password as users_passwordfrom userswhere users.name like ? and users.id = ? order by users.id('%ed', 99) no row was found for one()
2. 使用原义sql (literal sql)
query对象能够灵活的使用原义sql查询字符串作为查询参数，比如我们之前用过的filter()和order_by()方法：
>>> for user in session.query(user).\... filter(id> session.query(user).filter(id>> session.query(user).from_statement(... select * from users where name=:name).\... params(name='ed').all()select * from users where name=?('ed',) []
我们还可以在query()中直接使用列名来指派我们想要的列而摆脱映射类的束缚：
>>> session.query(id, name, thenumber12).\... from_statement(select id, name, 12 as ... thenumber12 from users where name=:name).\... params(name='ed').all()select id, name, 12 as thenumber12 from users where name=?('ed',) [(1, u'ed', 12)]
3. 计数 (counting)
对于query来说，计数功能也有个单独的方法称为count()：
>>> session.query(user).filter(user.name.like('%ed')).count() select count(*) as count_1from (select users.id as users_id, users.name as users_name, users.fullname as users_fullname, users.password as users_passwordfrom userswhere users.name like ?) as anon_1('%ed',) 2
count()方法被用于确定返回的结果集中有多少行，让我们观察一下产生的sql语句，sqlalchemy先是取出符合条件的所有行集合，然后再通过select count(*)来统计有多少行。当然有点sql知识的同学可能知道这条语句可以以更精简的方式写出来，比如select count(*) from table，当然现代版本的sqlalchemy不会去揣摩这样的想法。
假使我们要让查询语句更加精炼或者要明确要统计的列，我们可以通过表达式func.count()直接使用count函数，比如下面的例子介绍统计并返回每个唯一的用户名字：
>>> from sqlalchemy import func>>> session.query(func.count(user.name), user.name).group_by(user.name).all() select count(users.name) as count_1, users.name as users_namefrom users group by users.name() [(1, u'ed'), (1, u'fred'), (1, u'mary'), (1, u'wendy')]
对于刚才提到的简单select count(*) from table语句，我们可以通过下面的例子来实现：
>>> session.query(func.count('*')).select_from(user).scalar()select count(?) as count_1from users('*',) 4
当然如果我们直接统计user的主键，上面的语句可以更加简练，我们可以省去select_from()方法：
>>> session.query(func.count(user.id)).scalar() select count(users.id) as count_1from users() 4