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) <User('ed','Ed Jones', 'f8s7ccs')>

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<224")./...       order_by("id").all(): ...   print user.nameSELECT users.id AS users_id,    users.name AS users_name,    users.fullname AS users_fullname,    users.password AS users_passwordFROM usersWHERE id<224 ORDER BY id() edwendymaryfred

當然很多人可能會和我感覺一樣，會有些不適應，因為使用ORM就是為了擺脫SQL語句的，沒想到現在又看到SQL的影子了。呵呵，SQLAlchemy也要照顧到使用上的靈活性嘛，畢竟有些查詢語句直接編入要容易得多。