九、运算符
peewee 支持以下类型的比较:
比较 | 意义 |
---|---|
== | x 等于 y |
< | x 小于 y |
<= | x 小于或等于 y |
|x 大于 y
= |x 大于或等于 y
!=| x 不等于 y
<<| x IN y,其中 y 是列表或查询|x 是 y,其中 y 是 None/NULL
% |x LIKE y 其中 y 可能包含通配符
** |x ILIKE y 其中 y 可能包含通配符
^ |x 异或 y
~ |一元否定(例如,NOT x)
因为我用完了要覆盖的运算符,所以有一些额外的查询操作可用作方法:
方法 | 意义 |
---|---|
.in_(value) | IN 查找(与 相同<<)。 |
.not_in(value) | 不在查找中。 |
.is_null(is_null) | IS NULL 或 IS NOT NULL。接受布尔参数。 |
.contains(substr) | 子字符串的通配符搜索。 |
.startswith(prefix) | 搜索以 开头的值prefix。 |
.endswith(suffix) | 搜索以 结尾的值suffix。 |
.between(low, high) | 搜索 和 之间的low值high。 |
.regexp(exp) | 正则表达式匹配(区分大小写)。 |
.iregexp(exp) | 正则表达式匹配(不区分大小写)。 |
.bin_and(value) | 二进制与。 |
.bin_or(value) | 二进制或。 |
.concat(other) | 使用 连接两个字符串或对象 |
.distinct() | 标记列以进行 DISTINCT 选择。 |
.collate(collation) | 使用给定的排序规则指定列。 |
.cast(type) | 将列的值转换为给定的类型。 |
要使用逻辑运算符组合子句,请使用: |
操作员 | 意义 | 例子 |
---|---|---|
& | 和 | (User.is_active == True) & (User.is_admin == True) |
(pipe) | 或者 (User.is_admin) | (User.is_superuser) |
~ | NOT(一元否定) ~(User.username.contains(‘admin’)) | |
以下是您可以如何使用其中一些查询运算符: |
# Find the user whose username is "charlie".
User.select().where(User.username == 'charlie')
# Find the users whose username is in [charlie, huey, mickey]
User.select().where(User.username.in_(['charlie', 'huey', 'mickey']))
Employee.select().where(Employee.salary.between(50000, 60000))
Employee.select().where(Employee.name.startswith('C'))
Blog.select().where(Blog.title.contains(search_string))
以下是您可以如何组合表达式。比较可以任意复杂。
笔记
请注意,实际比较包含在括号中。Python 的运算符优先级要求将比较用括号括起来。
# Find any users who are active administrations.
User.select().where(
(User.is_admin == True) &
(User.is_active == True))
# Find any users who are either administrators or super-users.
User.select().where(
(User.is_admin == True) |
(User.is_superuser == True))
# Find any Tweets by users who are not admins (NOT IN).
admins = User.select().where(User.is_admin == True)
non_admin_tweets = Tweet.select().where(Tweet.user.not_in(admins))
# Find any users who are not my friends (strangers).
friends = User.select().where(User.username.in_(['charlie', 'huey', 'mickey']))
strangers = User.select().where(User.id.not_in(friends))
警告
尽管您可能很想在查询表达式中使用 python 的 、 和 运算符,但in这些都
and不起作用。表达式的返回值总是强制为布尔值。同样,和都将它们的参数视为布尔值并且不能重载。ornotinandornot所以请记住:
- 使用.in_()and.not_in()代替inandnot in
- 使用&代替and
- 使用|代替or
- 使用~代替not
使用.is_null()代替or 。is None== None- 使用逻辑运算符时,不要忘记将比较用括号括起来。
有关更多示例,请参阅表达式部分。
笔记
SQLite 的 LIKE 和 ILIKE
因为 SQLite 的LIKE操作默认是不区分大小写的,所以 peewee 会使用 SQLiteGLOB操作进行区分大小写的搜索。glob
操作使用星号作为通配符,而不是通常的百分号。如果您使用 SQLite 并且想要区分大小写的部分字符串匹配,请记住使用星号作为通配符。
9.1 三值逻辑
由于 SQL 处理的方式NULL,有一些特殊的操作可用于表达:
- IS NULL
- IS NOT NULL
- IN
- NOT IN
虽然可以将and运算符与否定运算符 ( ) 一起使用,但有时要获得正确的语义,您需要显式使用and 。IS NULLIN~IS NOT NULLNOT IN
使用and的最简单方法是使用运算符重载:IS NULLIN
# Get all User objects whose last login is NULL.
User.select().where(User.last_login >> None)
# Get users whose username is in the given list.
usernames = ['charlie', 'huey', 'mickey']
User.select().where(User.username << usernames)
如果您不喜欢运算符重载,则可以调用 Field 方法:
# Get all User objects whose last login is NULL.
User.select().where(User.last_login.is_null(True))
# Get users whose username is in the given list.
usernames = ['charlie', 'huey', 'mickey']
User.select().where(User.username.in_(usernames))
要否定上述查询,您可以使用一元否定,但为了获得正确的语义,您可能需要使用特殊的和运算符:IS NOTNOT IN
# Get all User objects whose last login is *NOT* NULL.
User.select().where(User.last_login.is_null(False))
# Using unary negation instead.
User.select().where(~(User.last_login >> None))
# Get users whose username is *NOT* in the given list.
usernames = ['charlie', 'huey', 'mickey']
User.select().where(User.username.not_in(usernames))
# Using unary negation instead.
usernames = ['charlie', 'huey', 'mickey']
User.select().where(~(User.username << usernames))
9.2 添加用户定义的运算符
因为我用完了要重载的 python 运算符,所以 peewee 中缺少一些运算符,例如modulo. 如果您发现需要支持上表中没有的算子,添加自己的算子非常容易。
以下是modulo在 SQLite 中添加对的支持的方法:
from peewee import *
from peewee import Expression # The building block for expressions.
def mod(lhs, rhs):
# Note: this works with Sqlite, but some drivers may use string-
# formatting before sending the query to the database, so you may
# need to use '%%' instead here.
return Expression(lhs, '%', rhs)
现在您可以使用这些自定义运算符来构建更丰富的查询:
# Users with even ids.
User.select().where(mod(User.id, 2) == 0)
有关更多示例,请查看playhouse.postgresql_ext 模块的源代码,因为它包含许多特定于 postgresql 的 hstore 的运算符。
9.3 表达式
Peewee 旨在提供一种简单、富有表现力和 Pythonic 的方式来构建 SQL 查询。本节将简要介绍一些常见的表达式类型。
可以组合两种主要类型的对象来创建表达式:
- Field实例
- SQL 聚合和函数使用fn
我们将假设一个简单的“用户”模型,其中包含用户名和其他内容的字段。它看起来像这样:
class User(Model):
username = CharField()
is_admin = BooleanField()
is_active = BooleanField()
last_login = DateTimeField()
login_count = IntegerField()
failed_logins = IntegerField()
比较使用查询运算符:
# username is equal to 'charlie'
User.username == 'charlie'
# user has logged in less than 5 times
User.login_count < 5
可以使用按位 and和or组合比较。运算符优先级由 python 控制,比较可以嵌套到任意深度:
# User is both and admin and has logged in today
(User.is_admin == True) & (User.last_login >= today)
# User's username is either charlie or charles
(User.username == 'charlie') | (User.username == 'charles')
比较也可以与函数一起使用:
# user's username starts with a 'g' or a 'G':
fn.Lower(fn.Substr(User.username, 1, 1)) == 'g'
我们可以做一些相当有趣的事情,因为表达式可以与其他表达式进行比较。表达式还支持算术运算:
# users who entered the incorrect more than half the time and have logged
# in at least 10 times
(User.failed_logins > (User.login_count * .5)) & (User.login_count > 10)
表达式允许我们进行原子更新:
# when a user logs in we want to increment their login count:
User.update(login_count=User.login_count + 1).where(User.id == user_id)
表达式可以在查询的所有部分中使用,所以请进行实验!
9.4 行值
许多数据库支持行值,类似于 Python元组对象。在 Peewee 中,可以通过Tuple. 例如,
# If for some reason your schema stores dates in separate columns ("year",
# "month" and "day"), you can use row-values to find all rows that happened
# in a given month:
Tuple(Event.year, Event.month) == (2019, 1)
行值更常见的用途是在单个表达式中与子查询中的多个列进行比较。还有其他方法可以表达这些类型的查询,但行值可能会提供一种简洁易读的方法。
例如,假设我们有一个包含事件类型、事件源和一些元数据的表“EventLog”。我们还有一个“IncidentLog”,其中包含事件类型、事件源和元数据列。我们可以使用行值将事件与某些事件相关联:
class EventLog(Model):
event_type = TextField()
source = TextField()
data = TextField()
timestamp = TimestampField()
class IncidentLog(Model):
incident_type = TextField()
source = TextField()
traceback = TextField()
timestamp = TimestampField()
# Get a list of all the incident types and sources that have occured today.
incidents = (IncidentLog
.select(IncidentLog.incident_type, IncidentLog.source)
.where(IncidentLog.timestamp >= datetime.date.today()))
# Find all events that correlate with the type and source of the
# incidents that occured today.
events = (EventLog
.select()
.where(Tuple(EventLog.event_type, EventLog.source).in_(incidents))
.order_by(EventLog.timestamp))
表达这种类型查询的其他方法是使用连接 或连接子查询。上面的示例只是为了让您了解如何Tuple使用。
当新数据来自子查询时,您还可以使用行值更新表中的多个列。例如,请参见此处。
9.5 SQL 函数
SQL 函数,如COUNT()or SUM(),可以使用 fn()帮助器表示:
# Get all users and the number of tweets they've authored. Sort the
# results from most tweets -> fewest tweets.
query = (User
.select(User, fn.COUNT(Tweet.id).alias('tweet_count'))
.join(Tweet, JOIN.LEFT_OUTER)
.group_by(User)
.order_by(fn.COUNT(Tweet.id).desc()))
for user in query:
print('%s -- %s tweets' % (user.username, user.tweet_count))
帮助程序公开任何 SQL 函数,fn就好像它是一个方法一样。参数可以是字段、值、子查询,甚至是嵌套函数。
9.6 嵌套函数调用
假设您需要获取用户名以 . 开头的所有用户的 列表。有几种方法可以做到这一点,但一种方法可能是使用一些 SQL 函数,如LOWER和SUBSTR。要使用任意 SQL 函数,请使用特殊fn()对象来构造查询:
# Select the user's id, username and the first letter of their username, lower-cased
first_letter = fn.LOWER(fn.SUBSTR(User.username, 1, 1))
query = User.select(User, first_letter.alias('first_letter'))
# Alternatively we could select only users whose username begins with 'a'
a_users = User.select().where(first_letter == 'a')
>>> for user in a_users:
... print(user.username)
9.7 SQL 助手
有时您可能只想简单地传入一些任意 sql。您可以使用特殊SQL类来执行此操作。一个用例是引用别名时:
# We'll query the user table and annotate it with a count of tweets for
# the given user
query = (User
.select(User, fn.Count(Tweet.id).alias('ct'))
.join(Tweet)
.group_by(User))
# Now we will order by the count, which was aliased to "ct"
query = query.order_by(SQL('ct'))
# You could, of course, also write this as:
query = query.order_by(fn.COUNT(Tweet.id))
使用 peewee 执行手工编写的 SQL 语句有两种方法:
- Database.execute_sql()用于执行任何类型的查询
- RawQuery用于执行SELECT查询和返回模型实例。
9.8 安全性和 SQL 注入
默认情况下,peewee 将参数化查询,因此用户传入的任何参数都将被转义。此规则的唯一例外是,如果您正在编写原始 SQL 查询或传入SQL可能包含不受信任数据的对象。为了缓解这种情况,请确保将任何用户定义的数据作为查询参数传入,而不是实际 SQL 查询的一部分:
# Bad! DO NOT DO THIS!
query = MyModel.raw('SELECT * FROM my_table WHERE data = %s' % (user_data,))
# Good. `user_data` will be treated as a parameter to the query.
query = MyModel.raw('SELECT * FROM my_table WHERE data = %s', user_data)
# Bad! DO NOT DO THIS!
query = MyModel.select().where(SQL('Some SQL expression %s' % user_data))
# Good. `user_data` will be treated as a parameter.
query = MyModel.select().where(SQL('Some SQL expression %s', user_data))
笔记
MySQL 和 Postgresql 用于’%s’表示参数。另一方面,SQLite 使用’?'.
请务必使用适合您的数据库的字符。您也可以通过检查找到该参数 Database.param。