>>> 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_password
FROM users
WHERE 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_password
FROM users
WHERE users.name LIKE ? ORDER BY users.id
LIMIT ? OFFSET ?
('%ed',1,0)<User('ed','Ed Jones','f8s7ccs')>
>>>from sqlalchemy.orm.excimport MultipleResultsFound
>>>try:
... user= query.one()
... except MultipleResultsFound, e:
... print e
SELECT users.id AS users_id,
users.name AS users_name,
users.fullname AS users_fullname,
users.password AS users_password
FROM users
WHERE users.name LIKE ? ORDER BY users.id('%ed',)
Multiple rows were found for one()
>>>from sqlalchemy.orm.excimport NoResultFound
>>>try:
... user= query.filter(User.id==99).one()
... except NoResultFound, e:
... print e
SELECT users.id AS users_id,
users.name AS users_name,
users.fullname AS users_fullname,
users.password AS users_password
FROM users
WHERE users.name LIKE ? AND users.id= ? ORDER BY users.id('%ed',99)
No row was found for one()
>>>foruserin session.query(User).\
... filter("id<224").\
... order_by("id").all():
... printuser.name
SELECT users.id AS users_id,
users.name AS users_name,
users.fullname AS users_fullname,
users.password AS users_password
FROM users
WHERE id<224 ORDER BY id()
ed
wendy
mary
fred
>>> session.query(User).filter("id<:value and name=:name").\
... params(value=224, name='fred').order_by(User.id).one()
SELECT users.id AS users_id,
users.name AS users_name,
users.fullname AS users_fullname,
users.password AS users_password
FROM users
WHERE id<User('fred','Fred Flinstone','blah')>
>>> session.query(User).from_statement(
... "SELECT * FROM users where name=:name").\
... params(name='ed').all()
SELECT * FROM users where name=?
('ed',)[<User('ed','Ed Jones','f8s7ccs')>]
我们还可以在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,12as 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_1
FROM (SELECT users.id AS users_id,
users.name AS users_name,
users.fullname AS users_fullname,
users.password AS users_password
FROM users
WHERE users.name LIKE ?) AS anon_1
('%ed',)2
count()方法被用于确定返回的结果集中有多少行,让我们观察一下产生的SQL语句,SQLAlchemy先是取出符合条件的所有行集合,然后再通过SELECT count(*)来统计有多少行。当然有点SQL知识的同学可能知道这条语句可以以更精简的方式写出来,比如SELECT count(*) FROM table,当然现代版本的SQLAlchemy不会去揣摩这样的想法。
>>>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_name
FROM 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_1
FROM users
('*',)4