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()
返回一個列表，包含所以查詢到的結果
2.first()
query.first()
返回第一個結果
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()
調用one(),如果成功，返回行的第一列
5.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_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
