filter() of session.query()

1.equals

query.filter(User.name =='zhanglinpeng')

2. not equals

query.filter(User.name != 'zhanglinpeng')

3.LIKE

query.filter(User.name.like(%ed%))

4.in

query.filter(User.name.in_([’ed’, ’wendy’, ’jack’]))

query.filter(User.name.in_(session.query(User.name).filter(User.name.like(’%ed%’))))

5.not in

query.filter(~User.name.in_([’ed’, ’wendy’, ’jack’]))

6.is NULL

query.filter(User.name == None)

7.is not NULL

query.filter(User.name != None)

8.AND
from sqlalchemy import and_

query.filter(and_(User.name == ’ed’, User.fullname == ’Ed Jones’))

9.OR

from sqlalchemy import or_

query.filter(or_(User.name == ’ed’, User.name == ’wendy’))

10.match

query.filter(User.name.match(’wendy’))

query's method of database result

1.all()

query = session.query(User).filter(User.name.like(’%ed’)).order_by(User.id)

query.all()

返回一個列表与倡，包含所以查詢到的結(jié)果

2.first()

query.first()

返回第一個結(jié)果

3.one()

提取所以的行莉给，獲取到多行或者零行都會報錯

from sqlalchemy.orm.exc import MultipleResultsFound痪枫，NoResultFound

try:

? ? ? ? user = query.one()

except MultipleResultsFound, e:

? ? ? ? print e

try:

? ? ? ? user = query.one()

except NoResultFound, e:

? ? ? ? ?print e

4.scalar()

query.scalar()

調(diào)用one(),如果成功，返回行的第一列

5.count()

結(jié)果計(jì)數(shù)

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

SQL

for user in session.query(User).filter("id<224").order_by("id").all():

? ? ? ? ? print user.name

= //SELECT users.id AS users_id,users.name AS users_name,users.fullname AS users_fullname,users.password AS users_password?FROM user

?WHERE id<224 ORDER BY id

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<?and name=? ORDER BY users.id (224, ’fred’)

from sqlalchemy import func

ua = aliased(User)

q = q.from_self(User.id, User.name, ua.name).filter(User.name < ua.name).filter(func.length(ua.name) != func.length(User.name)).order_by("name").all()

=//SELECT anon_1.users_id AS anon_1_users_id,

anon_1.users_name AS anon_1_users_name,

users_1.name AS users_1_name FROM

(SELECT users.id AS users_id, users.name AS users_nam FROM users) AS anon_1,?

users AS users_1?

WHERE anon_1.users_name < users_1.name

AND length(users_1.name) != length(anon_1.users_name)

ORDER BY name