peewee 查询

　　查询

　本章节将讲述在关系数据库的基本CRUD操作

• Model.create(),执行Insert操作

• Model.save()和Model.update(),执行Update操作

• Model.delete_instance()和Model.delete()，执行Delete操作

• Model.select()，执行Select操作

     新增记录

　　使用Model.create()新增模型实例。方法接受关键字参数，关键字名字关联到模型类的字段名称。返回实例，并且表中增加一行。

 

>>> User.create(username='Charlie')
<__main__.User object at 0x2529350>

 

这将插入新行记录。主键自动返回存储在模型实例上。

你也可以用代码构造一个模型实例，然后调用save方法：

>>> user = User(username='Charlie')
>>> user.save()  # save() returns the number of rows modified.
1
>>> user.id
1
>>> huey = User()
>>> huey.username = 'Huey'
>>> huey.save()
1
>>> huey.id
2

当模型有外键时，你可以直接将外键对应的模型实例赋值到外键字段。

 tweet = Tweet.create(user=huey, message='Hello!')

也可以将外键对应模型实例的主键赋值到字段：

>>> tweet = Tweet.create(user=2, message='Hello again!')

如果你想简单的插入行但不需要创建模型实例，可以使用Model.insert()：

>>> User.insert(username='Mickey').execute()
3

 

执行插入查询以后，新插入行的主键返回。

批量插入

有几种快速插入的方法。最直接的方法就是循环调用Model.create()

data_source = [
    {'field1': 'val1-1', 'field2': 'val1-2'},
    {'field1': 'val2-1', 'field2': 'val2-2'},
    # ...
]

for data_dict in data_source:
    Model.create(**data_dict)

以上方法较慢，有如下原因：

1. 如果你用默认的自动提交，每当调用create方法时时，都会提交事务，所以这样很慢。
2. 这种方法有少量的Python逻辑，而且每次都会有InsertQuery生成并且被解析为ＳＱＬ语句。
3. 发往数据库有许多数据而不是ＳＱＬ原生字节需要数据库解析
4. 我们得到了最后插入的id在某些情况下会引起额外的查询。

可以简单的在一个原子操作中包装而得到显著的速度提升。

# This is much faster.
with db.atomic():
    for data_dict in data_source:
        Model.create(**data_dict)

还不够快，可以使用insert_many()。接受字典list作参数。

 

根据你的数据源的行数。可以将批量操作分割成块。

# Insert rows 1000 at a time.
with db.atomic():
    for idx in range(0, len(data_source), 1000):
        Model.insert_many(data_source[idx:idx+1000]).execute()

如果你想要插入的数据存储在另一张表中，你也可以创建一个INSERT查询，它的插入数据源是一个SELECT查询。用Model.insert_from()方法

query = (TweetArchive
         .insert_from(
             fields=[Tweet.user, Tweet.message],
             query=Tweet.select(Tweet.user, Tweet.message))
         .execute())

更新已有的记录

一旦一个模型实例有一个主键，任何save方法的后续调用将要引起UPDATE而不是INSERT操作。模型的主键不会更改。

>>> user.save()  # save() returns the number of rows modified.
1
>>> user.id
1
>>> user.save()
>>> user.id
1
>>> huey.save()
1
>>> huey.id
2

如果你更新多个记录，使用UPDATE插询。下列例子将更新Tweet多个满足如果是昨天之前发表的实例，将它们标示为已发表，Model.update()接受关键字参数，这些

关键字个模型的字段一一对应。

>>> today = datetime.today()
>>> query = Tweet.update(is_published=True).where(Tweet.creation_date < today)
>>> query.execute()  # Returns the number of rows that were updated.
4

更多信息，请看关于 Model.update()和UpdateQuery部分

如果你想要了解更多关于原子性更新的知识（比如自增列），请见原子性更新部分介绍。

原子性更新

Peewee可以让你使用原子性更新。比如我们需要更新一些计数器。直接的方法是：

>>> for stat in Stat.select().where(Stat.url == request.url):
...     stat.counter += 1
...     stat.save()

不要这样做！不仅速度很慢，而且容易冲突如果多进程同时在更新这个计数器时。

应该用update方法原子性的更新计数器

>>> query = Stat.update(counter=Stat.counter + 1).where(Stat.url == request.url)
>>> query.execute()

可以尽可能复杂的查询语句。我们将我们雇员的奖金修改为旧奖金加上工资的1成：

>>> query = Employee.update(bonus=(Employee.bonus + (Employee.salary * .1)))
>>> query.execute()  # Give everyone a bonus!

我们可以使用子查询去更新某列的值。假如我们有一个存储User模型某个user tweets数量的字段，我们想要阶段性的更新它的值：

>>> subquery = Tweet.select(fn.COUNT(Tweet.id)).where(Tweet.user == User.id)
>>> update = User.update(num_tweets=subquery)
>>> update.execute()

删除记录

删除单个模型实例，可以使用Model.delete_instance()方法。delete_instance()将删除模型实例，选择性的递归删除依赖的对象（recursive=True）

>>> user = User.get(User.id == 1)
>>> user.delete_instance()  # Returns the number of rows deleted.
1

>>> User.get(User.id == 1)
UserDoesNotExist: instance matching query does not exist:
SQL: SELECT t1."id", t1."username" FROM "user" AS t1 WHERE t1."id" = ?
PARAMS: [1]

删除满足条件的行，用DELETE查询。下列例子删除超过1年的所有Tweet对象

>>> query = Tweet.delete().where(Tweet.creation_date < one_year_ago)
>>> query.execute()  # Returns the number of rows deleted.
7

查询一个记录

可使用Model.get()方法得到满足查询条件的单个实例。

这个方法是给定查询条件的Model.select()方法并且限制返回结果是一条的快捷方法。当没有满足结果的实例时，DoesNotExist意外被抛出

>>> User.get(User.id == 1)
<__main__.User object at 0x25294d0>

>>> User.get(User.id == 1).username
u'Charlie'

>>> User.get(User.username == 'Charlie')
<__main__.User object at 0x2529410>

>>> User.get(User.username == 'nobody')
UserDoesNotExist: instance matching query does not exist:
SQL: SELECT t1."id", t1."username" FROM "user" AS t1 WHERE t1."username" = ?
PARAMS: ['nobody']

更复杂的查询操作可以使用SelectQuery.get()。下列例子得到charlie的最近的tweet：

>>> (Tweet
...  .select()
...  .join(User)
...  .where(User.username == 'charlie')
...  .order_by(Tweet.created_date.desc())
...  .get())
<__main__.Tweet object at 0x2623410>

Create or get

Peewee针对get/create操作有两种操作方法：

Model.create_or_get(),先视图创建一行，如果IntegrityeError发生意味着约束冲突，然后试着查询这个对象。

Model.get_or_create()，先视图得到对象，如果失败，新增一行。

举例说明：我们通过User 模型实现注册用户功能。User在username字段上唯一约束，所以我们根据数据库的Integrity保证

我们不会有同样多个usernames：

try:
    with db.atomic():
        return User.create(username=username)
except peewee.IntegrityError:
    # `username` is a unique column, so this username already exists,
    # making it safe to call .get().
    return User.get(User.username == username)

不用写上面这么多代码，可以用create_or_get代替：

user, created = User.create_or_get(username=username)

上例先试着创建新对象，如果失败则查询得到，根据数据库的唯一性强制约束。

如果你想先查询记录，你可用get_or_create方法，它和Diango同名函数代码相同。可以用Diango风格的关键字参数过滤器去

实现WHERE条件。这个函数返回二元元组，分别包含了实例和是否创建成功的Boolean值。

这里是用get_or_create方法创建用户账户的代码

user, created = User.get_or_create(username=username)

举例我们有Person模型，我们想要get或create实例，唯一的约束是他们的姓和名，但是创建新纪录时，我们也要填写他们的

出生年月和最喜欢颜色字段：

person, created = Person.get_or_create(
    first_name=first_name,
    last_name=last_name,
    defaults={'dob': dob, 'favorite_color': 'green'})

任何关键字参数都被当作个get的逻辑部分参数，除了defaults字典，将在新创建的实例赋值。

查询多个记录

我们使用Model.select()去从表中取得行。当建立SELECT查询时，数据库返回满足条件的所有行。Peewee允许你枚举行，也可以

用索引和切片操作。

在这个例子中，我们简单的调用select方法并且循环列出返回的值，返回类型是SelectQuery实例。例子返回User表所有行：

>>> for user in User.select():
...     print user.username
...
Charlie
Huey
Peewee

当我们按顺序枚举查询的结果时不会接触数据库以为记录已经被缓存，可以使用SelectQuery.iterator()减少内存来列举结果。

当枚举包含外键的模型结果时，注意小心接触相关联模型的字段值。当解析外键字段或枚举back-reference时会引发N+1查询行为。

当创建外键时，例如Tweet.user，你可用related_name去创建back-reference（User.tweets)。back-references返回SelectQuery实例：

>>> tweet = Tweet.get()
>>> tweet.user  # Accessing a foreign key returns the related model.
<tw.User at 0x7f3ceb017f50>

>>> user = User.get()
>>> user.tweets  # Accessing a back-reference returns a query.
<SelectQuery> SELECT t1."id", t1."user_id", t1."message", t1."created_date", t1."is_published" FROM "tweet" AS t1 WHERE (t1."user_id" = ?) [1]

可以像其他SelectQuery一样列举user.tweets back-reference：

>>> for tweet in user.tweets:
...     print tweet.message
...
hello world
this is fun
look at this picture of my food

过滤记录：

你可用python操作符去过滤记录。peewee支持丰富的查询操作符。

>>> user = User.get(User.username == 'Charlie')
>>> for tweet in Tweet.select().where(Tweet.user == user, Tweet.is_published == True):
...     print '%s: %s' % (tweet.user.username, tweet.message)
...
Charlie: hello world
Charlie: this is fun

>>> for tweet in Tweet.select().where(Tweet.created_date < datetime.datetime(2011, 1, 1)):
...     print tweet.message, tweet.created_date
...
Really old tweet 2010-01-01 00:00:00

也可以关联查询：

>>> for tweet in Tweet.select().join(User).where(User.username == 'Charlie'):
...     print tweet.message
hello world
this is fun
look at this picture of my food

用括号和python的or，and操作符实现复杂查询：

>>> Tweet.select().join(User).where(
...     (User.username == 'Charlie') |
...     (User.username == 'Peewee Herman')
... )

可以在where语句中写许多有意思的条件：

字段表达式，e.g

User.username=='charlie'

函数表达式，e.g

fn.Lower(fn.Substr(User.username, 1, 1)) == 'a'

列之间的比较，e.g

Employee.salary < (Employee.tenure * 1000) + 40000

 

也可以嵌套查询，比如以“a“开始名字用户的tweets：

# get users whose username starts with "a"
a_users = User.select().where(fn.Lower(fn.Substr(User.username, 1, 1)) == 'a')

# the "<<" operator signifies an "IN" query
a_user_tweets = Tweet.select().where(Tweet.user << a_users)

更多查询例子

得到活跃用户：

User.select().where(User.active == True)

得到staff或者superuser的用户：

User.select().where(
    (User.is_staff == True) | (User.is_superuser == True))

得到”Charlie“的tweets：

Tweet.select().join(User).where(User.username == 'charlie')

得到staff或者superuser的tweets（FK关系映射）：

Tweet.select().join(User).where(
    (User.is_staff == True) | (User.is_superuser == True))

 

用子查询实现得到staff或者superuser的tweets

staff_super = User.select(User.id).where(
    (User.is_staff == True) | (User.is_superuser == True))
Tweet.select().where(Tweet.user << staff_super)

排序：

返回排序后的记录，用order_by方法：

>>> for t in Tweet.select().order_by(Tweet.created_date):
...     print t.pub_date
...
2010-01-01 00:00:00
2011-06-07 14:08:48
2011-06-07 14:12:57

>>> for t in Tweet.select().order_by(Tweet.created_date.desc()):
...     print t.pub_date
...
2011-06-07 14:12:57
2011-06-07 14:08:48
2010-01-01 00:00:00

用前缀+或-去标明排序

# The following queries are equivalent:
Tweet.select().order_by(Tweet.created_date.desc())

Tweet.select().order_by(-Tweet.created_date)  # Note the "-" prefix.

# Similarly you can use "+" to indicate ascending order:
User.select().order_by(+User.username)

也可以关联之间排序，假如你想要按照作者名字排序tweets，然后按照日期排序：

>> qry = Tweet.select().join(User).order_by(User.username, Tweet.created_date.desc())

SELECT t1."id", t1."user_id", t1."message", t1."is_published", t1."created_date"
FROM "tweet" AS t1
INNER JOIN "user" AS t2
  ON t1."user_id" = t2."id"
ORDER BY t2."username", t1."created_date" DESC

当在计算值上排序时，尅用必要的SQL表达式，或者用这个值的别名。看例子：

# Let's start with our base query. We want to get all usernames and the number of
# tweets they've made. We wish to sort this list from users with most tweets to
# users with fewest tweets.
query = (User
         .select(User.username, fn.COUNT(Tweet.id).alias('num_tweets'))
         .join(Tweet, JOIN.LEFT_OUTER)
         .group_by(User.username))

你可以使用select 中COUNT表达式去按tweets数量排序降序：

query = (User
         .select(User.username, fn.COUNT(Tweet.id).alias('num_tweets'))
         .join(Tweet, JOIN.LEFT_OUTER)
         .group_by(User.username)
         .order_by(fn.COUNT(Tweet.id).desc()))

也可以用select中的计算表达式的别名。注意我们没有直接使用别名，而是用SQL 帮助器包装了一下：

query = (User
         .select(User.username, fn.COUNT(Tweet.id).alias('num_tweets'))
         .join(Tweet, JOIN.LEFT_OUTER)
         .group_by(User.username)
         .order_by(SQL('num_tweets').desc()))

随机取得记录

如果你想随机得到记录。可以通过random 或rand函数去排序：

Postgresql和Sqlite通过Random函数：

# Pick 5 lucky winners:
LotteryNumber.select().order_by(fn.Random()).limit(5)

MySQL用Rand：

# Pick 5 lucky winners:
LotterNumber.select().order_by(fn.Rand()).limit(5)

分页记录

paginate方法使取得某页数据容易，两个参数：page_number,  items_per_page。

页码从1开始，所以第一页数据是Page 1

>>> for tweet in Tweet.select().order_by(Tweet.id).paginate(2, 10):
...     print tweet.message
...
tweet 10
tweet 11
tweet 12
tweet 13
tweet 14
tweet 15
tweet 16
tweet 17
tweet 18
tweet 19

你可以使用limit和offset去更灵活的分页

记录数量

可用count返回查询结果数量：

>>> Tweet.select().count()
100
>>> Tweet.select().where(Tweet.id > 50).count()
50

有些情况你要包装查询或者在inner查询的返回行中应用计数（比如DISTINCT和GROUPBY），peewee自动计数。有时

需要手动调用wrapped_count方法

聚合记录

假如你想要得到每个用户的tweets数量，annotate提供了简单的这种查询的方法：

query = User.select().annotate(Tweet)

 

以上查询相当于：

query = (User
         .select(User, fn.Count(Tweet.id).alias('count'))
         .join(Tweet)
         .group_by(User))

 查询结果返回带有自身属性和额外包含每个用户tweets数量的count属性的User实例数组。默认如果外键不为空时使用内连接，

也就是没有tweets的用户将不在返回结果中。为了避免，手动指名连接类型去返回0 tweets的用户。

query = (User
         .select()
         .join(Tweet, JOIN.LEFT_OUTER)
         .switch(User)
         .annotate(Tweet))

也可以指定自定义的聚合器，比如MIN 或者MAX：

query = (User
         .select()
         .annotate(
             Tweet,
             fn.Max(Tweet.created_date).alias('latest_tweet_date')))

假如标签应用中想找到有某数量相关联对象的标签。这个例子需要不同模型多对多配置：

class Photo(Model):
    image = CharField()

class Tag(Model):
    name = CharField()

class PhotoTag(Model):
    photo = ForeignKeyField(Photo)
    tag = ForeignKeyField(Tag)

比如我们现在想找到有至少5张照片关联的标签：

query = (Tag
         .select()
         .join(PhotoTag)
         .join(Photo)
         .group_by(Tag)
         .having(fn.Count(Photo.id) > 5))

上面查询等价于下面的SQL语句：

SELECT t1."id", t1."name"
FROM "tag" AS t1
INNER JOIN "phototag" AS t2 ON t1."id" = t2."tag_id"
INNER JOIN "photo" AS t3 ON t2."photo_id" = t3."id"
GROUP BY t1."id", t1."name"
HAVING Count(t3."id") > 5

假如我们想得到上面结果每个标签关联照片的数量：

query = (Tag
         .select(Tag, fn.Count(Photo.id).alias('count'))
         .join(PhotoTag)
         .join(Photo)
         .group_by(Tag)
         .having(fn.Count(Photo.id) > 5))

得到标量返回值

你可以通过调用Query.scalar来返回标量值：

>>> PageView.select(fn.Count(fn.Distinct(PageView.url))).scalar()
100

传入as_tuple=True来返回多个标量值：

>>> Employee.select(
...     fn.Min(Employee.salary), fn.Max(Employee.salary)
... ).scalar(as_tuple=True)
(30000, 50000)

SQL函数，子查询，和原始查询表达式

猜想你想要查询所有名字以a开始的用户，有若干方法，但是其中一个就是使用类似LOWER和SUBSTR的SQL函数。

若用SQL函数，需要fn对象去构造查询：

# Select the user's id, username and the first letter of their username, lower-cased
query = User.select(User, fn.Lower(fn.Substr(User.username, 1, 1)).alias('first_letter'))

# Alternatively we could select only users whose username begins with 'a'
a_users = User.select().where(fn.Lower(fn.Substr(User.username, 1, 1)) == 'a')

>>> for user in a_users:
...    print user.username

若经常用到任意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'))

用peewee执行修饰过的ＳＱＬ语句有两种方式：

1. Database.execute_sql()用于任意类型查询
2. RawQuery用执行返回模型实例的SELECT查询

db = SqliteDatabase(':memory:')

class Person(Model):
    name = CharField()
    class Meta:
        database = db

# let's pretend we want to do an "upsert", something that SQLite can
# do, but peewee cannot.
for name in ('charlie', 'mickey', 'huey'):
    db.execute_sql('REPLACE INTO person (name) VALUES (?)', (name,))

# now let's iterate over the people using our own query.
for person in Person.raw('select * from person'):
    print person.name  # .raw() will return model instances.

安全和SQL注入

默认peewee将参数化查询，所以所有用户传入的参数将被转义。唯一的例外就是写原始SQL查询或者传递

包含未被信任的数据的SQL对象时。为避免此事发生，确保用户定义的数据作为查询参数而不是实际的SQL查询：

# Bad!
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!
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.interpolation来查找这个参数。

窗口函数

peewee提供基础的SQL窗口函数功能，可以通过调用fn.over()来创建传递到你的分区和排序参数中。

# Get the list of employees and the average salary for their dept.
query = (Employee
         .select(
             Employee.name,
             Employee.department,
             Employee.salary,
             fn.Avg(Employee.salary).over(
                 partition_by=[Employee.department]))
         .order_by(Employee.name))

# Rank employees by salary.
query = (Employee
         .select(
             Employee.name,
             Employee.salary,
             fn.rank().over(
                 order_by=[Employee.salary]))

得到原始的元组/字典

有时你想要简单的列举行数据元组，而不需要创建结果对象。可以用SelectQuery().tuples()或RawQuery.tuples():

stats = Stat.select(Stat.url, fn.Count(Stat.url)).group_by(Stat.url).tuples()

# iterate over a list of 2-tuples containing the url and count
for stat_url, stat_count in stats:
    print stat_url, stat_count

简单的，用SelectQuery.dicts()或RawQuery.dicts()可以返回行数据作为字典

stats = Stat.select(Stat.url, fn.Count(Stat.url).alias('ct')).group_by(Stat.url).dicts()

# iterate over a list of 2-tuples containing the url and count
for stat in stats:
    print stat['url'], stat['ct']

返回子句

PostgresqlDatabase 支持在Update，INSERT，DELETE查询里用RETURNING子句。

用RETURNING子句可以将查询语句影响的行数对应的模型对象返回。

例如，你用UpdateQuery去将注册过期的用户冻结。冻结后，你需要向每个用户发送邮件然他们知道情况。而不是用两次

查询，SELECT和UPDATE,通过带有RETURNING 的UPDATE的查询来一次完成。

query = (User
         .update(is_active=False)
         .where(User.registration_expired == True)
         .returning(User))

# Send an email to every user that was deactivated.
for deactivate_user in query.execute():
    send_deactivation_email(deactivated_user)

RETURNING也可用在InsertQUery和DeleteQuery上。当用在INSERT时，新增的行返回。用在DELETE时，删除的行

返回。

RETURNING子句唯一限制是，它仅包含查询FROM子句中列的表列。用模型类可以返回所有的列字段。

查询操作符

peewee支持一下类型的比较符：

Comparison	Meaning
==	x equals y
<	x is less than y
<=	x is less than or equal to y
>	x is greater than y
>=	x is greater than or equal to y
!=	x is not equal to y
<<	x IN y, where y is a list or query
>>	x IS y, where y is None/NULL
%	x LIKE y where y may conta

 

 

 

 

 

 

 

 

 

 

 

 

 

 

因为懒得重载所有操作符，所以还有当作方法的些查询操作符：

 

Method	Meaning
.contains(substr)	Wild-card search for substring.
.startswith(prefix)	Search for values beginning with prefix.
.endswith(suffix)	Search for values ending with suffix.
.between(low, high)	Search for values between low and high.
.regexp(exp)	Regular expression match.
.bin_and(value)	Binary AND.
.bin_or(value)	Binary OR.
.in_(value)	IN lookup (identical to <<).
.not_in(value)	NOT IN lookup.
.is_null(is_null)	IS NULL or IS NOT NULL. Accepts boolean param.
.concat(other)	Concatenate two strings using ||.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

用逻辑运算符去连接子句：

 

Operator	Meaning	Example
&	AND	(User.is_active == True) & (User.is_admin == True)
| (pipe)	OR	(User.is_admin) | (User.is_superuser)
~	NOT (unary negation)	~(User.username << ['foo', 'bar', 'baz'])

 

 

 

 

 

 

 

演示如何使用这些操作符：

 

# 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 << ['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))

演示如何连接表达式。比较可以很复杂：

# 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 << admins))

# Find any users who are not my friends (strangers).
friends = User.select().where(
  User.username << ['charlie', 'huey', 'mickey'])
strangers = User.select().where(~(User.id << friends))

虽然你在试图用python 的in,and,or 和not操作符，但是它们不起作用。in 表达式总是返回boolean 值。

同样的，and，or和not将把参数当作boolean值，不能被重载。

所以注意：

• 用<<代替in
• 用&代替and
• 用!代替or
• 用~代替not
• 不要忘记你的比较表达式用逻辑运算符连接时使用括号包装。

SQLite中的LIKE 和ILIKE

因为SSQLite的LIKE操作符默认是大小写不敏感，peewee用SQLite GLOB操作来实现大小写敏感的查询。

glob操作符用*代替%作为通配符。如果你使用SQLite想要大小写敏感的字符匹配，记住用*来作为通配符。

三种值化的逻辑

SQL处理NULL的方法不同，可有下列表达式中的操作：

• IS NULL
• IS NOT NULL
• IN
• NOT IN 

虽然你可以使用IS NULL 和IN前加上否定运算符~来实现，但是最好显式的用IS NOT NULL和NOT IN

来使得语义更明确。

最简单用IS NULL 和IN就是使用操作符重载：

# 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)

如果不喜欢操作符重载，可以调用字段方法：

# 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))

取反上面的查询，你可以使用一元运算符~，最好使用ＩＳ　ＮＯＴ和ＮＯＴ　ＩＮ来消除歧义。

# 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))

自定义的操作符

因为懒得操作符重载，作者减去了一些操作比如，module。如果你你在上表中没发现你想要的操作符，ｎｉ

可以很容易的加上你自己定义的

在SQLite中增加module支持

from peewee import *
from peewee import Expression # the building block for expressions

OP['MOD'] = 'mod'

def mod(lhs, rhs):
    return Expression(lhs, OP.MOD, rhs)

SqliteDatabase.register_ops({OP.MOD: '%'})

现在使用自定义的操作构建丰富的查询：

# Users with even ids.
User.select().where(mod(User.id, 2) == 0)

 

表达式

Peewee 设计成提供简单的，印象深刻的，pythonic 来构造SQL查询。这部分给出一些表达式的用法。

主要两种对象类型来创建表达式：

• Field 实例
• SQL聚合和fn函数

我们假如简单User模型，拥有username和其他事情：

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)

表达式可以用在查询的任意部分，所以自己去尝试一下！

外键

外键通过特殊的字段类ForeignKeyField来创建。么个外键也在相关联的模型间通过特殊的related_name

创建了向后引用。

外键穿越

再一次来看User和Tweet模型，注意Tweet有个ForeignKeyField指向User。这个外键可以允许你通过它接触到相关联

的user实例。我们称为外键穿越：

>>> tweet.user.username
'charlie'

除非User模型显式的选择，当得到Tweet记录时，得到User数据需要额外的查询。关于如何避免额外查询，

可以看N+1查询问题部分讲解。

反过来，我们可以查询给定User实例的相关联的tweets：

>>> for tweet in user.tweets:
...     print tweet.message
...
http://www.youtube.com/watch?v=xdhLQCYQ-nQ

这种情况下，tweets属性只是拥有预生成指向给定User实例的WHERE子句的SelectQuery而已：

>>> user.tweets
<class 'twx.Tweet'> SELECT t1."id", t1."user_id", t1."message", ...

关联表

用join方法去关联其他的表。当在源模型和关联的模型之间有外键时，你可以不用指名别的参数：

my_tweets = Tweet.select().join(User).where(User.username == 'charlie')

默认peewee使用INNER连接，也可以用LEFT OUTER或FULL 连接：

users = (User
         .select(User, fn.Count(Tweet.id).alias('num_tweets'))
         .join(Tweet, JOIN.LEFT_OUTER)
         .group_by(User)
         .order_by(fn.Count(Tweet.id).desc()))
for user in users:
    print user.username, 'has created', user.num_tweets, 'tweet(s).'

模型多个外键

模型有多个外键时，最好的实践是显示指名你要关联的字段。

再看例子中的模型，Relationship模型用于表示一个用户关注另一个用户。模型定义如下：

class Relationship(BaseModel):
    from_user = ForeignKeyField(User, related_name='relationships')
    to_user = ForeignKeyField(User, related_name='related_to')

    class Meta:
        indexes = (
            # Specify a unique multi-column index on from/to-user.
            (('from_user', 'to_user'), True),
        )

因为有两个主键指向User，我们应该在关联时指名我们的字段名。

例如，我们查询我关注的用户，这样写：

(User
.select()
.join(Relationship, on=Relationship.to_user)
.where(Relationship.from_user == charlie))

另外，我们谁正在关注我，on用from_user列，过滤条件用to_user：

(User
.select()
.join(Relationship, on=Relationship.from_user)
.where(Relationship.to_user == charlie))

任意表字段间关联

如果你想要关联的表之间没有外键，你也可以手动指名关联谓词。

下面例子中，在User和ActiveLog之间没有显式的外键，但是ActiveLog.object_id和Usr.id之间存在隐式的关联。

不是用在特定的字段上关联，我们用表达式去关联：

user_log = (User
            .select(User, ActivityLog)
            .join(
                ActivityLog,
                on=(User.id == ActivityLog.object_id).alias('log'))
            .where(
                (ActivityLog.activity_type == 'user_activity') &
                (User.username == 'charlie')))

for user in user_log:
    print user.username, user.log.description

#### Print something like ####
charlie logged in
charlie posted a tweet
charlie retweeted
charlie posted a tweet
charlie logged out

通过在关联条件上指名别名，你可以控制peewee将关联的实例赋值到此属性。上面例子中，我们用下面作为关联：

(User.id == ActivityLog.object_id).alias('log')

然后循环列出查询结果时，我们可以直接接触ActivityLog而不用再次查询：

for user in user_log:
    print user.username, user.log.description

在多表之间关联

当调用join方法时，peewee使用上一个关联的表当作原表：

User.select().join(Tweet).join(Comment)

 

这个查询返回一个从User到Tweet的关联，然后从Tweet到Comment的关联。

如果你喜欢将同一个表关联两次，用switch方法：

# Join the Artist table on both `Ablum` and `Genre`.
Artist.select().join(Album).switch(Artist).join(Genre)

实现多对多映射

Peewee没提供一个字段去解决像django 那样的多对多关系映射-这是因为事实上这个字段是隐藏在一张中间表中。

用peewee去实现多对多映射，你必须亲自实现中间表，并且用它作媒介来查询：

class Student(Model):
    name = CharField()

class Course(Model):
    name = CharField()

class StudentCourse(Model):
    student = ForeignKeyField(Student)
    course = ForeignKeyField(Course)

去查询选修数学的所有学生：

query = (Student
         .select()
         .join(StudentCourse)
         .join(Course)
         .where(Course.name == 'math'))
for student in query:
    print student.name

去查询某学生选修的所有课程：

courses = (Course
    .select()
    .join(StudentCourse)
    .join(Student)
    .where(Student.name == 'da vinci'))

for course in courses:
    print course.name

有效的去列举多对多关系，比如：列举所有学生和他们的选修课程，我们通过StudentCourse来查询，并且预计算Student和

Course：

query = (StudentCourse
         .select(StudentCourse, Student, Course)
         .join(Course)
         .switch(StudentCourse)
         .join(Student)
         .order_by(Student.name))

 

下面代码打印学生们和他们的选修课：

last = None
for student_course in query:
    student = student_course.student
    if student != last:
        last = student
        print 'Student: %s' % student.name
    print '    - %s' % student_course.course.name

 

 因为我们在select子句中包含了Student和Course的所有字段，所以这些外键穿越是免费的，我们在一次查询中完成了整个列举。

ManyToManyField

ManyToManyField提供了在字段中表示多对多映射。你最好使用标准的peewee API去处理大多数情况，除非，如果你的模型是非常简单

并且你的查询不是太复杂，你可以使用ManyToManyField去得到快速的查询提升体验。详细使用说明请看FIelds扩展部分。

用ManyToManyField的studen和course模型：

from peewee import *
from playhouse.fields import ManyToManyField

db = SqliteDatabase('school.db')

class BaseModel(Model):
    class Meta:
        database = db

class Student(BaseModel):
    name = CharField()

class Course(BaseModel):
    name = CharField()
    students = ManyToManyField(Student, related_name='courses')

StudentCourse = Course.students.get_through_model()

db.create_tables([
    Student,
    Course,
    StudentCourse])

# Get all classes that "huey" is enrolled in:
huey = Student.get(Student.name == 'Huey')
for course in huey.courses.order_by(Course.name):
    print course.name

# Get all students in "English 101":
engl_101 = Course.get(Course.name == 'English 101')
for student in engl_101.students:
    print student.name

# When adding objects to a many-to-many relationship, we can pass
# in either a single model instance, a list of models, or even a
# query of models:
huey.courses.add(Course.select().where(Course.name.contains('English')))

engl_101.students.add(Student.get(Student.name == 'Mickey'))
engl_101.students.add([
    Student.get(Student.name == 'Charlie'),
    Student.get(Student.name == 'Zaizee')])

# The same rules apply for removing items from a many-to-many:
huey.courses.remove(Course.select().where(Course.name.startswith('CS')))

engl_101.students.remove(huey)

# Calling .clear() will remove all associated objects:
cs_150.students.clear()

详细使用：

• ManyToManyField.add()

• ManyToManyField.remove()

• ManyToManyField.clear()

• ManyToManyField.get_through_model()

自关联

peewee为构造包含自己关联自己的查询提供了几种方法。

用模型别名

关联一个模型两次，需要在查询时创建一个代表第二个表实例的别名。以下面这个模型说明：

class Category(Model):
    name = CharField()
    parent = ForeignKeyField('self', related_name='children')

如果我们查询父种类为电子类的所有类别。一种用自关联来实现：

Parent = Category.alias()
query = (Category
         .select()
         .join(Parent, on=(Category.parent == Parent.id))
         .where(Parent.name == 'Electronics'))

当用ModelAlias来关联时，必须用on关键字来标明关联条件。上例我们使用它的父类别来关联类别。

用子查询

另一种较少见的情况是使用子查询。下面是另一种用子查询实现得到父类别是电子的所有类别的查询：

oin_query = Category.select().where(Category.name == 'Electronics')

# Subqueries used as JOINs need to have an alias.
join_query = join_query.alias('jq')

query = (Category
         .select()
         .join(join_query, on=(Category.parent == join_query.c.id)))

 将会生成下面SQL语句：

SELECT t1."id", t1."name", t1."parent_id"
FROM "category" AS t1
INNER JOIN (
  SELECT t3."id"
  FROM "category" AS t3
  WHERE (t3."name" = ?)
) AS jq ON (t1."parent_id" = "jq"."id"

用.c魔法方法去根据上下文生成合适的SQL表达式从而通过子查询接触到id值：

Category.parent == join_query.c.id
# Becomes: (t1."parent_id" = "jq"."id")

  性能技巧

下面讨论一下用peewee的些许提高性能的方法。

避免N+1查询

N+1查询指的是当应用提交一次查询获取结果，然后在取得结果数据集的每一行时，应用至少再次查询一次（也可以看做是嵌套循环）。

大多数情况下，n 查询可以通过使用SQL join或子查询来避免。数据库本身可能做了嵌套循环，但是它比在你的应用代码本身里做这些n查询更高效，后者通常会导致与数据库再次潜在通讯，没有利用数据库本身关联和执行子查询时会进行切片等优化工作。

Peewee提供了几种API去减轻N+1查询的行为。再看看贯串我们这篇文档的模型，User和Tweet，这部分我们重点讲一下一些N+1场景，说明peewee怎么帮助我们避免N+1查询。

在一些场景里，N+1查询不会明显表现为显著地或可测量的性能瓶颈点。它也由你要查询的数据，使用的数据库本身，以及执行查询获取结果的潜在因素。优化前后可以测试性能，确保和你预测的变化相同。

列出最近的tweets

tweets时间轴显示最近用户的tweets。除了tweet的内容，还要显示tweet作者的用户名。N+1场景描述为：

1. 获取最近的10条tweets
2. 每个tweet，查询作者信息（10次查询）

通过用join选择两个表，peewee使得在一次查询里完成任务：

query = (Tweet
         .select(Tweet, User)  # Note that we are selecting both models.
         .join(User)  # Use an INNER join because every tweet has an author.
         .order_by(Tweet.id.desc())  # Get the most recent tweets.
         .limit(10))

for tweet in query:
    print tweet.user.username, '-', tweet.message

没有用join时，得到tweet.user.username会触发一次查询去解析外键tweet.user从而得到相关联的user。

由于我们在User上关联并选择，peewee自动为我们解析外键。

列出所有用户和他们的tweets

你想要显示若干用户和他们所有的tweets的页面。N+1场景为：

1. 取得些许用户。
2. 每个用户取到他们的tweets。

虽然和上个例子相似，但是重要区别是：我们选择tweets时，每个tweet只有一个关联的用户，所以可以直接赋值到外键，

反过来不对，因为一个用户可有任意数量tweets或者没有。

Peewee提供两两种途径去避免O(n)查询：

1.首先取到用户，然后取到关联这些用户的所有tweets。一旦peewee取到tweets，将它们与合适的用户匹配。

这种方法通常很快，但是会在所选择的每个表上执行一次查询。

2.在一个查询里得到用户和tweets。用户数据将复制，所以peewee将在列举结果集时减少重复和聚合tweets。

这种方法导致有许多数据需要传输，并且要有许多python逻辑去减少行重复。

每种方案根据查询数据的大小和结构都会可能比另一种更好。

使用prefetch

peewee使用子查询可以预获取数据。这种方法需要prefetch特殊API使用。Pre-fetch，像其名字本身，

用子查询去急切加载给定用户的相应的tweets。意味着我们用O(k)查询K张表而不是O(n)查询n行纪录。

下面演示我们如何得到若干用户和他们最近一周的tweets：

week_ago = datetime.date.today() - datetime.timedelta(days=7)
users = User.select()
tweets = (Tweet
          .select()
          .where(
              (Tweet.is_published == True) &
              (Tweet.created_date >= week_ago)))

# This will perform two queries.
users_with_tweets = prefetch(users, tweets)

for user in users_with_tweets:
    print user.username
    for tweet in user.tweets_prefetch:
        print '  ', tweet.message

注意User 查询和Tweet查询都没有JOIN子句，当我们使用prefetch时不必指名join

 

prefetch可以用于任意数量的表。可以查看API文档看其他例子。

用prefetch时应考虑的事情：

• 预查询的模型必须存在外键
• 通常它比aggregate_rows方法更高效
• 因为数据没有重复的所以传输的数据更少
• 因为不用减重复所以python逻辑更少
• 当你想要在最外的查询里使用LIMIT没问题，但是可能正确的实现限制子查询的返回结果大小有些困难。

使用aggregate_rows

aggregeate_rows一次在内存中减少重复，选择所有的数据。它和prefetch都可以完成任意复杂的查询。

使用这个特性需要当创建查询时用到特殊的标志aggregate_rows。它告诉peewee减少那些根据JOIN的结构可能会重复的行。

因为在减少重复聚合数据时有许多计算，所以可能使用aggregate_rows可能在一些查询中会比用prefetch性能低，即使面对的是

O(n)简单的 查询时，所以你不确定使用哪种方法时测试检查你的代码。

query = (User
         .select(User, Tweet)  # As in the previous example, we select both tables.
         .join(Tweet, JOIN.LEFT_OUTER)
         .order_by(User.username)  # We need to specify an ordering here.
         .aggregate_rows())  # Tell peewee to de-dupe and aggregate results.

for user in query:
    print user.username
    for tweet in user.tweets:
        print '  ', tweet.message

query = (User
         .select(User, Tweet)  # As in the previous example, we select both tables.
         .join(Tweet, JOIN.LEFT_OUTER)
         .order_by(User.username)  # We need to specify an ordering here.
         .aggregate_rows())  # Tell peewee to de-dupe and aggregate results.

for user in query:
    print user.username
    for tweet in user.tweets:
        print '  ', tweet.message

一般情况下，user.tweets返回SelectQuery，迭代它将触发额外的查询。使用aggregate_rows，user.tweets将是一个Python list，没有

额外的查询发生。

我们用LEFT OUTER关联确保没有tweets的用户也在结果数据中。

下面我们获取若干用户以及他们过去一周发表的tweets。因为我们过滤tweets时用户可以没有任何tweets，所以我们需要WHERE

子句允许有NULL ID的tweet。

week_ago = datetime.date.today() - datetime.timedelta(days=7)
query = (User
         .select(User, Tweet)
         .join(Tweet, JOIN.LEFT_OUTER)
         .where(
             (Tweet.id >> None) | (
                 (Tweet.is_published == True) &
                 (Tweet.created_date >= week_ago)))
         .order_by(User.username, Tweet.created_date.desc())
         .aggregate_rows())

for user in query:
    print user.username
    for tweet in user.tweets:
        print '  ', tweet.message

使用aggregate_rows需考虑的事情：

• 必须指定每个关联表的排序，以便行数据可以正确的聚合，有点像itertools.groupby
• 不要和LIMIT和OFFSET谓词混用，或者get（应用LIMIT 1 SQL 子句）。因为聚合结果集可能由于行的重复问题导致

      多个记录返回，限制返回数量可能导致错误。假设你有三个用户，每个有10个tweets，如果你在查询后加上

      LIMIT 5，你只返回了第一个用户和他的前5个tweets

• 通常python去减少数据重复是的这种方法性能比prefetch低，有时甚至比简单的O(n)查询性能还低！有疑问时。
• 因为每张表的每一列都包含在游标返回的行元组内，所以这种方法比prefetch需要更多带宽。

迭代大量行

默认在迭代SelectQuery时peewee缓存结果。这样使得多次迭代和索引切片容易，但是当你计划迭代大数量的行时可能有问题。

当迭代查询结果时为了减少peewee使用的内存，可用iterator方法。这个方法让你用更少的内存去迭代大量的结果集，不用缓存返回的每个实例。

# Let's assume we've got 10 million stat objects to dump to a csv file.
stats = Stat.select()

# Our imaginary serializer class
serializer = CSVSerializer()

# Loop over all the stats and serialize.
for stat in stats.iterator():
    serializer.serialize_object(stat)

可以使用native方法来为简单的查询进一步提升速度。这个方法加速peewee从原始的游标数据构造为peewee模型实例。

可看native文档了解其详细优化信息。

你也可以通过使用dicts和tuples来得到性能提升。

当迭代大量包含多表的列的行时，peewee为每个返回行重建模型图，这种操作在复杂的图上时很费时。为提速模型创建，你可以：

• 调用native方法，它不会构建图，直接将行的属性分配到模型实例上
• 使用dicts或tuples

加速批量插入操作

请看批量插入部分去了解为批量插入操作提速的细节