当然强大的Query对象有个很有用的特性,那就是它是可以串联的,意味着Query对象的每一步操作将会返回一个Query对象,你可以将相同的方法串联到一起形成表达式结构,假如说我们要查询用户名为”ed”并且全名为”Ed Jones”的用户,你可以直接串联调用filter()两次,表示SQL语句里的AND连接:
代码如下:
>>> for user in session.query(User).\
... filter(User.name=='ed').\
... filter(User.fullname=='Ed Jones'):
... print user
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 = ? AND users.fullname = ?
('ed', 'Ed Jones')
<User('ed','Ed Jones', 'f8s7ccs')>
下面列举一些使用filter()常见的筛选过滤操作:
>>> for user in session.query(User).\
... filter(User.name=='ed').\
... filter(User.fullname=='Ed Jones'):
... print user
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 = ? AND users.fullname = ?
('ed', 'Ed Jones')
<User('ed','Ed Jones', 'f8s7ccs')>
下面列举一些使用filter()常见的筛选过滤操作:
1. 相等
代码如下:
query.filter(User.name == 'ed')
2. 不等
2. 不等
代码如下:
query.filter(User.name != 'ed')
3. LIKE
3. LIKE
代码如下:
query.filter(User.name.like('%ed%'))
4. IN
4. IN
代码如下:
query.filter(User.name.in_(['ed', 'wendy', 'jack']))
# works with query objects too:
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']))
# works with query objects too:
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
6. IS NULL
代码如下:
filter(User.name == None)
7. IS NOT NULL
7. IS NOT NULL
代码如下:
filter(User.name != None)
8. AND
8. AND
代码如下:
from sqlalchemy import and_
filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
# or call filter()/filter_by() multiple times
filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')
9. OR
from sqlalchemy import and_
filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
# or call filter()/filter_by() multiple times
filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')
9. OR
代码如下:
from sqlalchemy import or_
filter(or_(User.name == 'ed', User.name == 'wendy'))
10. 匹配
from sqlalchemy import or_
filter(or_(User.name == 'ed', User.name == 'wendy'))
10. 匹配
代码如下:
query.filter(User.name.match('wendy'))
match()参数内容由数据库后台指定。(注:原文是“The contents of the match parameter are database backend specific.”,不太明白这个操作的意思)
query.filter(User.name.match('wendy'))
match()参数内容由数据库后台指定。(注:原文是“The contents of the match parameter are database backend specific.”,不太明白这个操作的意思)
好了,今天就介绍这么多,基本上都是蹩脚的翻译,希望对大家能够帮助
更多SQL内容来自木庄网络博客
- 欢迎访问木庄网络博客
- 可复制:代码框内的文字。
- 方法:Ctrl+C。