Python的ORM框架中SQLAlchemy库的查询操作的教程

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也要照顾到使用上的灵活性嘛，毕竟有些查询语句直接编入要容易得多。