python輕量級orm框架 peewee常用功能速查

金色旭光發表於2021-09-12
PythonORM框架

peewee常用功能速查

peewee 簡介

Peewee是一種簡單而小的ORM。它有很少的（但富有表現力的）概念，使它易於學習和直觀的使用。

常見orm資料庫框架

  1. Django ORM
  2. peewee
  3. SQLAlchemy

Django ORM

優點：
易用，學習曲線短
和Django緊密集合，用Django時使用約定俗成的方法去運算元據庫
缺點：
QuerySet速度不給力，會逼我用Mysqldb來操作原生sql語句。

Peewee

優點：
Django式的API，使其易用
輕量實現，很容易和任意web框架整合
缺點：
不支援自動化 schema 遷移
不能像Django那樣，使線上的mysql表結構生成結構化的模型。

SQLAlchemy

優點：
巨牛逼的API，使得程式碼有健壯性和適應性
靈活的設計，使得能輕鬆寫複雜查詢
缺點：
工作單元概念不常見
重量級 API，導致長學習曲線

peewee 簡單demo

import datetime
from peewee import *

db = MySQLDatabase(
    "test", host="127.0.0.1", port=3306, user="root", passwd="123456"
)
db.connect()


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

class Person(BaseModel):
    name = CharField()
    age = IntegerField()
    height = IntegerField()
    sex = BooleanField(default='male')

if __name__ == "__main__":
    Person.create_table()

    # 建立
    Person.create(name='tom', age=30, height=177)
    
    # 查詢
    res = Person.select().where(Person.name=='tom')
    print(res)
    print(res[0])
    print(res[0].name)
    print(res[0].age)
    print(res[0].height)
    print(res[0].sex)
>>>>
SELECT `t1`.`id`, `t1`.`name`, `t1`.`age`, `t1`.`High`, `t1`.`sex` FROM `person` AS `t1` WHERE (`t1`.`name` = 'ljk')
1
tom
30
177
True

Model 和 Field 關係

在ORM物件關聯式資料庫中 Model是一個類，對映到資料庫表中就是一個表。Filed是欄位，對映到表中就是欄位。model例項就是資料庫中的一條記錄。在peewee中Model和Field的關係如下：

Thing 對應關係
Model 類 表
Field 例項 表中欄位
Model 例項 表中資料

資料庫連線和model類定義的典型使用

import datetime
from peewee import *

db = SqliteDatabase('my_app.db')

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

class User(BaseModel):
    username = CharField(unique=True)

class Tweet(BaseModel):
    user = ForeignKeyField(User, backref='tweets')
    message = TextField()
    created_date = DateTimeField(default=datetime.datetime.now)
    is_published = BooleanField(default=True)
  1. 建立一個資料庫例項
db = SqliteDatabase('my_app.db')
  1. 建立一個基礎model類
class BaseModel(Model):
    class Meta:
        database = db

定義一個用於建立資料庫連線的基模類是一種推薦的做法，因為將不必為後續表指定資料庫。

3.定義一個普通 model 類

class User(BaseModel):
    username = CharField(unique=True)

模型定義使用的是其他流行的orm(如SQLAlchemy或Django)中看到的宣告式風格。因為User繼承了BaseModel 類，所以User類可以繼承資料庫連線。

User已經明確定義了一個具有唯一約束的使用者名稱列。因為我們沒有指定主鍵，peewee 會自動新增一個自增整數主鍵欄位，名為 id。沒有指定主鍵的表peewee會自動建立一個名字為id的自增主鍵。

Model 模型

為了不汙染model的名稱空間，model的配置放在特殊的元屬性類中。這是從Django的框架中借鑑過來的。


contacts_db = SqliteDatabase('contacts.db')

class Person(Model):
    name = CharField()

    class Meta:
        database = contacts_db

在簡單model示例中，你會注意到，我們建立了一個定義資料庫的BaseModel，然後擴充套件了它。這是定義資料庫和建立模型的首選方法。
你可以通過ModelClass._meta來使用：

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
AttributeError: type object 'Person' has no attribute 'Meta'

>>> Person._meta
<peewee.modeloptions object="" at="" 0x7f51a2f03790="">

ModelOptions實現了幾個檢視model metadata的方法：

{'id': <peewee.autofield object="" at="" 0x7f51a2e92750="">,
 'name': <peewee.charfield object="" at="" 0x7f51a2f0a510="">}

>>> Person._meta.primary_key
<peewee.autofield object="" at="" 0x7f51a2e92750="">

>>> Person._meta.database
<peewee.sqlitedatabase object="" at="" 0x7f519bff6dd0="">

Model 在ORM資料中就是一張表，那麼表的屬性可以有如下選項。它們是被定義在Meta中後設資料。

Option Meaning 是否可繼承?
database 指定表建立依附的資料庫 yes
table_name 表名 no
table_function 生成表名的函式 yes
indexes 多行索引 yes
primary_key 主鍵 yes
constraints 表約束的列表 yes
schema 模型的資料庫架構 yes
only_save_dirty 呼叫model.save（）時，僅儲存髒欄位，指定欄位？ yes
options 建立表擴充套件的選項字典 yes
table_settings 在右括號後設定字串的列表 yes
temporary 指示臨時表 yes
legacy_table_names 使用舊錶名生成（預設情況下啟用） yes
depends_on 指示此表依賴於另一個表進行建立 no
without_rowid 指示表不應具有rowid（僅限SQLite） no
strict_tables 指示嚴格的資料型別（僅限SQLite，3.37+） yes

Filed 欄位

Field類是用來將Model屬性對映到資料庫列。每個欄位型別都有一個相應的SQL儲存類，將python資料型別轉化為基本的儲存型別。
當建立Model類時，fields被定義成類的屬性。它看起來和django的資料庫框架很類似。

class User(Model):
    username = CharField()
    join_date = DateTimeField()
    about_me = TextField()

在上面的例子中，因為沒有field有主鍵屬性primary_key=True，所以會建立一個名字是id的自增主鍵。
peewee中可用的欄位包括：

欄位型別 Sqlite Postgresql MySQL
AutoField integer serial integer
BigAutoField integer bigserial bigint
IntegerField integer integer integer
BigIntegerField integer bigint bigint
SmallIntegerField integer smallint smallint
IdentityField not supported int identity not supported
FloatField real real real
DoubleField real double precision double precision
DecimalField decimal numeric numeric
CharField varchar varchar varchar
FixedCharField char char char
TextField text text text
BlobField blob bytea blob
BitField integer bigint bigint
BigBitField blob bytea blob
UUIDField text uuid varchar(40)
BinaryUUIDField blob bytea varbinary(16)
DateTimeField datetime timestamp datetime
DateField date date date
TimeField time time time
TimestampField integer integer integer
IPField integer bigint bigint
BooleanField integer boolean bool
BareField untyped not supported not supported
ForeignKeyField integer integer integer

欄位初始化引數

所有欄位型別接受的引數及其預設值

  • null = False 允許空值
  • index = False 建立索引
  • unique = False 建立唯一索引
  • column_name = None 顯式指定資料庫中的列名
  • default = None 預設值，可以使任意值或可呼叫物件
  • primary_key = False 指明主鍵
  • constraints = None 約束條件
  • sequence = None 序列名字（如果資料庫支援）
  • collation = None 排序欄位
  • unindexed = False 虛表上的欄位不應該被索引
  • choices = None 兩種可選項：value display
  • help_text = None 幫助說明欄位。表示此欄位的任何有用文字的字串
  • verbose_name = None 表示此欄位的使用者友好名稱的字串
  • index_type = None 索引型別

欄位特有引數

在一些欄位中有些自己特有的引數，如下：

欄位型別 特有引數
CharField max_length
FixedCharField max_length
DateTimeField formats
DateField formats
TimeField formats
TimestampField resolution, utc
DecimalField max_digits, decimal_places, auto_round, rounding
ForeignKeyField model, field, backref, on_delete, on_update, deferrable lazy_load
BareField adapt

欄位預設引數

peewee可以為每一個欄位提供預設值，比如給intergerField 預設值0而不是NULL。你可以申明欄位時指定預設值：

class Message(Model):
    context = TextField()
    read_count = IntegerField(default=0)

在某些情況下，預設值是動態的會更有意義。一個可能的場景就是當前時間。Peewee 允許您在這些情況下指定一個函式，該函式的返回值將在建立物件時使用。注意，使用時只提供了函式，並不需要實際呼叫它。

class Message(Model):
    context = TextField()
    timestamp = DateTimeField(default=datetime.datetime.now)

如果你正在使用一個接受可變型別(list, dict等)的欄位，並想提供一個預設值。將預設值包裝在一個簡單的函式中是個好主意，這樣，多個模型例項就不會共享對同一底層物件的引用。

def house_defaults():
    return {'beds': 0, 'baths': 0}

class House(Model):
    number = TextField()
    street = TextField()
    attributes = JSONField(default=house_defaults)

索引

peewee可以通過單列索引和多列索引。可選地包括UNIQUE約束。Peewee還支援對模型和欄位的使用者定義約束。

單列索引
單列索引使用欄位初始化引數定義。下面的示例在使用者名稱欄位上新增一個惟一索引，在電子郵件欄位上新增一個普通索引

class User(Model):
    username = CharField(unique=True)
    email = CharField(index=True)

在列上新增使用者定義的約束。你可以使用constraints引數。例如，您可能希望指定一個預設值，或者新增一個CHECK約束

class Product(Model):
    name = CharField(unique=True)
    price = DecimalField(constraints=[Check('price < 10000')])
    created = DateTimeField(
        constraints=[SQL("DEFAULT (datetime('now'))")])

多列索引
可以使用巢狀元組將多列索引定義為元屬性。每個表的索引是一個2元組，第一部分是索引欄位名稱的元組，可以有多個欄位，第二部分是一個布林值，指示索引是否應該唯一。

class Transaction(Model):
    from_acct = CharField()
    to_acct = CharField()
    amount = DecimalField()
    date = DateTimeField()

    class Meta:
        indexes = (
            # create a unique on from/to/date
            (('from_acct', 'to_acct', 'date'), True),

            # create a non-unique on from/to
            (('from_acct', 'to_acct'), False),
        )

記住，如果索引元組只包含一項，則新增末尾逗號

基本操作 增刪改查

peewee中關於增刪改查的基本操作方法如下：

增：
create()：最常用建立，返回建立例項
save()：第一次執行的save是插入，第二次是修改
insert: 插入資料，不建立資料庫例項。返回id
insert_many: 批量插入
bulk_create：批量插入，類似於insert_many。可指定單次插入的數量
batch_commit: 自動新增了一個事務，然後一條條的插入
insert_from: 從另一個表中查詢的資料作為插入的資料

刪除：
delete().where().execute()
delete_instance() 直接執行刪除了，不用呼叫execute() 方法

修改：
save()： 第一次執行的save是插入，第二次是修改
update() 用於多欄位更新

查詢：
Model.get()： 檢索與給定查詢匹配的單個例項。報 Model.DoesNotExist 異常。如果有多條記錄滿足條件，則返回第一條
get_or_none() :與get使用方法相同。區別是找不到結果時不會報錯
get_by_id() :通過主鍵查詢，是一種快捷方式
Model['id_num']: 和上面的get_by_id一樣是通過主鍵查詢。
get_or_create(): 首先查詢，如果查不到將建立一個新的記錄
select() 查詢多條資料

建立

單條插入

你可以用Model.create()建立一個新的例項。這個方法接收關鍵字引數，引數要和表定義的欄位一致。返回值是新的例項

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

批量插入

有幾種方法可以快速載入大量資料，缺乏經驗的做法是在迴圈中呼叫Model.create來建立

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

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

上面的方法比較慢的原因有幾個：

  1. 如果沒有在事務中裝飾迴圈，那麼每個對create()的呼叫都發生在它自己的事務中。這將會非常緩慢
  2. 必須生成每個InsertQuery並將其解析為SQL
  3. 需要原生SQL語句傳入到資料庫中解析
  4. 檢索最後一個insert id，這在某些情況下會導致執行額外的查詢

可以通過一個簡單的裝飾：atomic來大幅度提高速度

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

上面的程式碼仍然沒有解決2、3、4這三點。我們可以通過 insert_many 帶來一個大的速度提升。這個方法接收多列元組或字典，然後在一次SQL語句中插入多行資料。

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

# Fastest way to INSERT multiple rows.
MyModel.insert_many(data_source).execute()

insert_many() 方法還接收多行元組，同時需要提供一個對應的欄位。

# We can INSERT tuples as well...
data = [('val1-1', 'val1-2'),
        ('val2-1', 'val2-2'),
        ('val3-1', 'val3-2')]

# But we need to indicate which fields the values correspond to.
MyModel.insert_many(data, fields=[MyModel.field1, MyModel.field2]).execute()

在裝飾中批量插入是一個好的方法。

# You can, of course, wrap this in a transaction as well:
with db.atomic():
    MyModel.insert_many(data, fields=fields).execute()

插入大量資料
在大量資料的插入場景下，根據資料來源中的行數，您可能需要將其分解為多個塊。SQLite通常有999或32766的限制

您可以編寫一個迴圈來將資料批處理成塊(在這種情況下，強烈建議您使用事務)

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

peewwee提供了一個chunked函式幫助你高效的將普通可迭代物件拆分成為可批處理物件。

from peewee import chunked

# Insert rows 100 at a time.
with db.atomic():
    for batch in chunked(data_source, 100):
        MyModel.insert_many(batch).execute()

Model.bulk_create() 的行為有點像insert_many()，但是可以用來插入沒有儲存的資料庫例項，並且可以指定每次插入的數量。如一共插入345，如果指定了一次插入100條記錄，那麼就是4次插入，3 * 100 + 1 * 45
什麼叫沒有儲存的資料庫例項呢？就是類似於User(username='kk')，建立的資料庫例項。

# Read list of usernames from a file, for example.
with open('user_list.txt') as fh:
    # Create a list of unsaved User instances.
    users = [User(username=line.strip()) for line in fh.readlines()]

# Wrap the operation in a transaction and batch INSERT the users
# 100 at a time.
with db.atomic():
    User.bulk_create(users, batch_size=100)

bulk_update() 和bulk_create類似，可以用來插入沒有儲存的資料庫例項，自動新增了一個事務，然後一條條的插入

# List of row data to insert.
row_data = [{'username': 'u1'}, {'username': 'u2'}, ...]

# Assume there are 789 items in row_data. The following code will result in
# 8 total transactions (7x100 rows + 1x89 rows).
for row in db.batch_commit(row_data, 100):
    User.create(**row)

從另一個表批量裝載

Model.insert_from() 如果要批量插入的資料儲存在另一個表中，還可以建立源為SELECT查詢的INSERT查詢。

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

刪除

要刪除單個模型例項，可以使用model.delete_instance（）快捷方式。delete_instance（）將刪除給定的模型例項，並且可以選擇遞迴地刪除任何依賴物件（通過指定recursive=True）。

刪除一個記錄：Model.delete_instance()
刪除任意記錄：Model.delete()

更新

save():單個更新
一旦模型例項有了主鍵，隨後對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:批量更新
接受關鍵字引數，其中鍵對應於模型的欄位名稱

>>> 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.get()方法查詢到給條件的資料。如果是通過主鍵查詢，也可以用一個快捷方法 Model.get_by_id()。
此方法是使用給定查詢呼叫Model.select（）的快捷方式，但將結果集限制為一行。需要注意的是使用get()方法，如果沒有找到匹配的資料會丟擲錯誤：DoesNotExist

get

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

>>> User.get_by_id(1)  # Same as above.
<__main__.User object at 0x252df10>

>>> User[1]  # Also same as above.
<__main__.User object at 0x252dd10>

>>> 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']

單條記錄查詢方法：

  • Model.get()
  • Model.get_by_id()
  • Model.get_or_none() - if no matching row is found, return None.
  • Model.select()
  • SelectBase.get()
  • SelectBase.first() - return first record of result-set or None.

查詢或建立

Model.get_or_create() 它首先嚐試檢索匹配的行。如果失敗，將建立一個新行。

通常，可以依賴唯一約束或主鍵來防止建立重複物件。例如，假設我們希望使用示例使用者模型實現註冊新使用者帳戶。使用者模型對使用者名稱欄位有唯一的約束，因此我們將依賴資料庫的完整性保證，以確保不會出現重複的使用者名稱：

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)

上面的例子首先嚐試建立，然後回退到查詢，依靠資料庫來強制執行唯一約束。
如果您希望首先嚐試檢索記錄，可以使用get_or_create()。該函式返回一個2元組，其中包含例項和一個布林值，該值指示物件是否被建立。

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

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

查詢多行記錄

可以通過Model.select()獲取多行資料。peewee允許你迭代這些資料，同時也可以索引和切片。

>>> query = User.select()
>>> [user.username for user in query]
['Charlie', 'Huey', 'Peewee']

>>> query[1]
<__main__.User at 0x7f83e80f5550>

>>> query[1].username
'Huey'

>>> query[:2]
[<__main__.User at 0x7f83e80f53a8>, <__main__.User at 0x7f83e80f5550>]

select()是很智慧的，在查詢一次的前提下可以多次迭代，切片，下標取值等。

在快取結果時，同一查詢的後續迭代不會命中資料庫。要禁用此行為（以減少記憶體使用），請在迭代時呼叫Select.iterator()。

除了返回模型例項外，Select查詢還可以返回字典、元組和命名元組。根據您的用例，您可能會發現將行作為字典使用更容易

>>> query = User.select().dicts()
>>> for row in query:
...     print(row)

{'id': 1, 'username': 'Charlie'}
{'id': 2, 'username': 'Huey'}
{'id': 3, 'username': 'Peewee'}

iterator()：不快取查詢結果
預設情況下，peewee將快取迭代Select查詢時返回的行。這是一種優化，允許多次迭代以及索引和切片，而不會導致額外的查詢。但是，當您計劃在大量行上進行迭代時，這種快取可能會有問題。

為了減少記憶體的消耗，使用iterator()方法。這個方法允許返回結果不快取資料。使用更少的記憶體。

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)

對於簡單的查詢，您可以通過將行作為字典返回來進一步提高速度。namedtuples或元組。以下方法可用於任何Select查詢，以更改結果行型別。
dicts()
namedtuples()
tuples()

objects: 將多個查詢表放在一個例項中
當對包含多個表中的列的大量行進行迭代時，peewee將為返回的每一行構建查詢模型。對於複雜查詢，此操作可能很慢。例如，如果我們選擇一個tweet列表以及tweet作者的使用者名稱和頭像，Peewee必須為每一行建立兩個物件（tweet和使用者）。除了上述行型別之外，還有第四個方法objects（），它將作為模型例項返回行，但不會分解模型查詢。

query = (Tweet
         .select(Tweet, User)  # Select tweet and user data.
         .join(User))

# Note that the user columns are stored in a separate User instance
# accessible at tweet.user:
for tweet in query:
    print(tweet.user.username, tweet.content)

# Using ".objects()" will not create the tweet.user object and assigns all
# user attributes to the tweet instance:
for tweet in query.objects():
    print(tweet.username, tweet.content)

為了獲得最佳效能，您可以執行查詢，然後使用底層資料庫遊標對結果進行迭代。
Database.execute()。接受查詢物件，執行查詢，並返回DB-API 2.0遊標物件。游標將返回原始行元組：

query = Tweet.select(Tweet.content, User.username).join(User)
cursor = database.execute(query)
for (content, username) in cursor:
    print(username, '->', content)

事務

資料庫事務（Transaction）是一種機制，包含了一組資料庫操作命令
事務把所有的命令作為一個整體一起向系統提交或撤銷操作請求，即這一組資料庫命令要麼都執行，要麼都不執行，因此事務是一個不可分割的工作邏輯單元。
事務具有 4 個特性，即原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）和永續性（Durability），這 4 個特性通常簡稱為 ACID。

peewee事務
Peewee實現事務的方法是Database.atomic()方法，非常簡單
當事務執行成功之後，它會自動commit()，不需要我們手動調。當事務的程式碼塊中丟擲異常時，它會自動呼叫rollback()，將資料庫狀態恢復到操作之前，保證要麼命令全部執行，要麼全部不執行。

Peewee中實現事務有兩種使用方式，一種是將atomic當做Context manager使用，另外一種將atomic當修飾器使用。
Context manager

with db.atomic():
    for data_dict in data_source:
        MyModel.create(**data_dict)

裝飾器

@db.atomic()
def insert_data()
    for data_dict in data_source:
        MyModel.create(**data_dict)

事務其他特性：

  • 除了自動commit()和rollback()之外，也可以手動呼叫commit()和rollback()方法
  • 事務支援巢狀使用
  • 在一個事務中對資料庫操作能夠有效減少事務的耗時，增加操作效率

過濾

您可以使用普通的python操作符過濾特定的記錄。

>>> user = User.get(User.username == 'Charlie')
>>> for tweet in Tweet.select().where(Tweet.user == user, Tweet.is_published == True):
...     print(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

...     
print(tweet.message)
hello world
this is fun
look at this picture of my food

記錄分類

給返回的資料排序，可以使用order_by

1.普通使用

>>> for t in Tweet.select().order_by(Tweet.created_date):
...     print(t.pub_date)

2.倒序排列
可以使用desc或者-號

Tweet.select().order_by(Tweet.created_date.desc())

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

3.正序排列

User.select().order_by(+User.username)

4.高階使用
對計算值進行排序時，可以包括必要的SQL表示式，也可以引用指定給該值的別名。

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

您可以使用select子句中使用的相同計數表示式進行訂購。在下面的示例中，我們按tweet ID的COUNT（）降序排序：

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

同樣，也可以使用如上

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

計數

可以使用count來計算返回數量

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

分頁

paginate() 方法可以很簡單的獲取一個分頁的資料。paginate有兩個引數：page_number 和 items_per_page。第一個引數是取回資料的頁數；第二個引數是每一頁多少元素。這兩個引數加起來才能完成分頁

>>> 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()來實現

Tweet.select().order_by(Tweet.id).offset(10).limit(10)

offset(10) 跳過10個記錄
limit(10) 取10個記錄

聚合查詢

聚合查詢：對查詢出來的結果進一步處理，包括統計，分組，求最大值，求平均值等。

聚合常用的函式：
COUNT：計算表中的記錄數（行數）

SUM：計算表中數值列中資料的合計值

AVG：計算表中數值列中資料的平均值

MAX：求出表中任意列中資料的最大值

MIN：求出表中任意列中資料的最小值

用於彙總的函式稱為聚合函式或者聚集函式。所謂聚合，就是將多行彙總為一行。實際上，所有的聚合函式都是這樣，輸入多行輸出一行。

聚合函式的使用：

mysql> select * from person;
+----+------+-----+------+-----+
| id | name | age | High | sex |
+----+------+-----+------+-----+
|  1 | ljk  |  30 |  177 |   1 |
|  2 | aghj |  23 |  168 |   1 |
+----+------+-----+------+-----+
2 rows in set (0.00 sec)
************************************
*              聚合函式              *
************************************
mysql> select count(*) from person;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
----------------------------------------
mysql> select sum(age) from person;
+----------+
| sum(age) |
+----------+
|       53 |
+----------+
1 row in set (0.00 sec)
----------------------------------------
mysql> select avg(high) from person;
+-----------+
| avg(high) |
+-----------+
|  172.5000 |
+-----------+
1 row in set (0.00 sec)
----------------------------------------
mysql> select max(high) from person;
+-----------+
| max(high) |
+-----------+
|       177 |
+-----------+
1 row in set (0.00 sec)


mysql> select * from person;
+----+------+-----+------+-----+
| id | name | age | High | sex |
+----+------+-----+------+-----+
|  1 | ljk  |  30 |  177 |   1 |
|  2 | aghj |  23 |  168 |   1 |
|  3 | 0    |  22 |  165 |   0 |
+----+------+-----+------+-----+
3 rows in set (0.00 sec)


mysql> select avg(High) from person group by sex;
+-----------+
| avg(High) |
+-----------+
|  172.5000 |
|  165.0000 |
+-----------+
2 rows in set (0.00 sec)

# 使用having對分組的資料篩選
mysql> select avg(High) as high from person group by sex having high > 170;
+----------+
| high     |
+----------+
| 172.5000 |
+----------+
1 row in set (0.00 sec)

where：分組之前篩選資料
where 子句的作用是在對查詢結果進行分組前，將不符合where條件的行去掉，即在分組之前過濾資料，where條件中不能包含聚組函式，使用where條件過濾出特定的行。

having： 對分組之後篩選分組的資料
having 子句的作用是篩選滿足條件的組，即在分組之後過濾資料，條件中經常包含聚組函式，使用having 條件過濾出特定的組，也可以使用多個分組標準進行分組。

總結一下過濾的順序

on->join->where->group by->having

分組
查詢使用者以及每個人擁有的tweet賬號數量。這裡使用了group_by，將結果根據User表分類。

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

假設有如下資料庫，一個多對多的關係。

class Photo(Model):
    image = CharField()

class Tag(Model):
    name = CharField()

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

查詢Tag記錄，按照Tag分組，篩選出每組Tag裡Photo數量超過5個的記錄。

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

HAVING 子句可以讓我們篩選分組後的各組資料。
HAVING，它與 GROUP BY 配合使用，為聚合操作指定條件。
WHERE 子句只能指定行的條件，而不能指定組的條件。所以當資料分組之後就需要 HAVING 對分組的資料篩選。

具體區別:

  1. where 用在group_by前，having用在group_by之後。
  2. 聚合函式（avg、sum、max、min、count），不能作為條件放在where之後，但可以放在having之後

Scalar

對查詢出來的資料做處理
可以通過呼叫Query.scalar()來檢索標量值。例如

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

您可以通過傳遞來檢索多個標量值

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

視窗

視窗函式是指對作為SELECT查詢一部分處理的資料滑動視窗進行操作的聚合函式。視窗功能可以執行以下操作：
對結果集的子集執行聚合。
計算一個執行總數。
排名結果。
將行值與前面（或後面！）行中的值進行比較。
peewee支援SQL視窗函式，可以通過呼叫Function.over（）並傳入分割槽或排序引數來建立這些函式。

複雜篩選

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 contain wildcards
** x ILIKE y where y may contain wildcards
^ x XOR y
~ Unary negation (e.g., NOT x)

篩選方法

因為用完了要重寫的操作符，所以有一些額外的查詢操作可以作為方法使用

Method Meaning
.in_(value) 查詢在範圍內
.not_in(value) 查詢不在範圍內
.is_null(is_null) 為空或不為空。接受布林引數
.contains(substr) 萬用字元搜尋子字串
.startswith(prefix) 查詢以prefix開頭的資料
.endswith(suffix) 查詢以prefix結尾的資料
.between(low, high) 查詢在low和high中間的值
.regexp(exp) 正規表示式匹配匹配的資料，貪婪模式
.iregexp(exp) 正規表示式匹配匹配的資料，非貪婪模式
.bin_and(value) 二進位制加
.bin_or(value) 二進位制或
.concat(other) Concatenate two strings or objects using ||.
.distinct() 標記重複的資料
.collate(collation) 指定具有給定排序規則的列
.cast(type) 將列的值強制轉換為給定型別

聯合查詢邏輯操作

使用邏輯操作的聯合查詢

Operator Meaning Example
& AND (User.is_active == True) & (User.is_admin == True)
| OR (User.is_admin) | (User.is_superuser)
~ NOT (unary negation) ~(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))

可能你嘗試使用python語法中的in and or 和not操作，但是在查詢中是不生效的。所有的操作返回都是一個布林值。
建議如下：

  1. 用.in_()和.not_in()替換 in和 not in
  2. 用&替換and
  3. 用|替換or
  4. 用～替換not
  5. 用.is_null()替換 is None 或 == None

SQL 方法

SQL方法，如like，sum等，可以通過fn來表達
從peewee中匯入fn：from peewee import fn

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

fn可以表達任何SQL方法，它的引數可以是欄位，值，子查詢甚至巢狀函式

基礎使用

  1. fn.AVG() 返回指定列的平均值，NULL值不包括在計算中。
  2. fn.SUM() 返回指定列的數目，NULL值不包括在計算中。
  3. fn.MIN() 返回指定列的最小值，NULL值不包括在計算中。
  4. fn.MAX() 返回指定列的最大值，NULL值不包括在計算中。
  5. fn.DATE() 返回指定日期時間格式列的日期格式
  6. fn.DECIMAL(10, 2) ===> decimal(10,2)中的“2”表示小數部分的位數

進階使用

  1. fn.to_char() 返回指定列格式化後的字串 e.g.: fn.to_char(18.88, '99.999') ===> 18.888; fn.to_char(model.field, '')。
  2. fn.char_length(str) 返回字串字元數
  3. fn.array_agg() 接受一組值並返回一個陣列。
  4. fn.array_agg(model.name).order_by(model.id.asc()) # array_agg(name order by id asc)
  5. fn.rank().over(partition_by=[field1, field2, or aggregation_field1], order_by=[fn.SUM(Booking.slots).desc()]) 實現rank() over(partition by filed order by filed)分割槽功能。
  6. fn.length() 返回指定列的長度。也可應用於order_by。e.g.: .order_by(fn.length(model.field).asc())。
  7. fn.CONCAT() 返回合併的字串（CONCAT一定要大寫，小寫的concat用法不一樣）。fn.CONCAT(model.id, '-', model.name) ===> '188-張三'

SQL helper

有時，您可能想在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語句有兩種方法

  1. Database.execute_sql() 用於執行任何型別的查詢
  2. RawQuery 執行SELECT查詢並返回模型例項
query = MyModel.raw('SELECT * FROM my_table WHERE data = %s', user_data)
query.execute_sql()

安全和SQL隱碼攻擊

預設情況下，peewee將引數化查詢，因此使用者傳入的任何引數都將被轉義。
請確保將任何使用者定義的資料作為查詢引數傳入，而不是作為實際SQL查詢的一部分傳入：

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來查詢此引數。

小結

個人水平問題翻譯並不是很準確，由於方法太多使用也未給出適當例子。後面有時間挑增刪改查等功能寫詳細操作。
</peewee.sqlitedatabase></peewee.autofield></peewee.charfield></peewee.autofield></peewee.modeloptions>

相關文章