前言

我們昨天有用到 Shell如何在資料庫中建立資料的方法，接著要來查找資料庫裏面的資料。

QuerySet

我們在介紹Model時有介紹過，Django有提供資料庫存取API，讓我們能夠快速查詢資料庫，這個API就是
QuerySet讓我們可以查詢我們的資料，也能對資料進行邏輯判斷或篩選。

.all()

當使用class名稱.objects.all()時，我們可以得到該table目前存放的資料，

In [13]: Product.objects.all()
Out[13]: <QuerySet [<Product: Product object (1)>, <Product: Product object (2)>, <Product: Product object (3)>, <Product: Product object (4)>, <Product: Product object (5)>, <Product: Product object (6)>, <Product: Product object (7)>, <Product: Product object (8)>, <Product: Product object (9)>]>
In [14]: Product.objects.all()[0]
Out[14]: <Product: Product object (1)>

可以看到我們在shell 不能直接看到資料的內容，而是顯示他的object和第幾筆資料。
但是我們可以透過 定義一個 str 函式來幫助我們顯示資料的內容。

models.py

from django.db import models

class Product(models.Model):
    Product_name = models.CharField(max_length=30)
    price = models.IntegerField(default=0)
    def __str__(self) -> str:
        return f"{self.Product_name}'s price is {self.price}"

str 呈現結果

可以看到加完之後就會顯示我們要顯示的資料。

In [2]: from Django_app.models import Product

In [3]: Product.objects.all()
Out[3]: <QuerySet [<Product: ToyCar's price is 20>, <Product: ToyCar's price is 20>, <Product: Computer's price is 20000>, <Product: bike's price is 2000>, <Product: pen's price is 20>, <Product: light's price is 200>, <Product: mouse's price is 2200>, <Product: pen's price is 20>, <Product: light's price is 200>]>

.get()

我們可以利用pk的index來尋找資料

In [4]: Product.objects.get(pk=1)
Out[4]: <Product: ToyCar's price is 20>

In [5]: Product.objects.get(pk=3)
Out[5]: <Product: Computer's price is 20000>

也可以利用他的欄位來找資料。

In [6]: Product.objects.get(Product_name = "Computer")
Out[6]: <Product: Computer's price is 20000>

但是當資料庫裏面相同名稱的資料超過一筆的時候，就會報錯!
因此這時候就可以使用filter

In [7]: Product.objects.get(Product_name = "ToyCar")
---------------------------------------------------------------------------
MultipleObjectsReturned                   Traceback (most recent call last)
Cell In [7], line 1
----> 1 Product.objects.get(Product_name = "ToyCar")

File ~\Django_website\django_env\lib\site-packages\django\db\models\manager.py:85, in BaseManager._get_queryset_methods.<locals>.create_method.<locals>.manager_method(self, *args, **kwargs)
     84 def manager_method(self, *args, **kwargs):
---> 85     return getattr(self.get_queryset(), name)(*args, **kwargs)

File ~\Django_website\django_env\lib\site-packages\django\db\models\query.py:653, in QuerySet.get(self, *args, **kwargs)
    649 if not num:
    650     raise self.model.DoesNotExist(
    651         "%s matching query does not exist." % self.model._meta.object_name
    652     )
--> 653 raise self.model.MultipleObjectsReturned(
    654     "get() returned more than one %s -- it returned %s!"
    655     % (
    656         self.model._meta.object_name,
    657         num if not limit or num < limit else "more than %s" % (limit - 1),
    658     )
    659 )

MultipleObjectsReturned: get() returned more than one Product -- it returned 2!

.filter()

In [8]: Product.objects.filter(Product_name = "ToyCar")
Out[8]: <QuerySet [<Product: ToyCar's price is 20>, <Product: ToyCar's price is 20>]>

我們這時候可以再創建一個ToyCar

In [11]: Product.objects.create(Product_name = "ToyCar", price = 1000)

我們在filter後面也可以再對其他欄位進行篩選

In [14]: Product.objects.filter(Product_name = "ToyCar").filter(price = 1000)
Out[14]: <QuerySet [<Product: ToyCar's price is 1000>]>

運算子

在filter裡面如果要做更複雜的查詢
我們也可以透過Q 來做邏輯的運算，像是&、OR等

In [15]: from django.db.models import Q

AND

In [16]: Product.objects.filter(Q(Product_name = "ToyCar")& Q(price = 1000))
Out[16]: <QuerySet [<Product: ToyCar's price is 1000>]>

Q(Product_name = "ToyCar")& Q(price = 1000)轉換成SQL的話就像下面這樣

WHERE question LIKE 'ToyCar%' AND question price = 1000

OR

In [17]: Product.objects.filter(Q(Product_name = "ToyCar")or  Q(price = 1000))
Out[17]: <QuerySet [<Product: ToyCar's price is 20>, <Product: ToyCar's price is 20>, <Product: ToyCar's price is 1000>]>

如果轉換成SQL的話就像下面這個寫法

SELECT * from Product WHERE Product_name LIKE 'ToyCar%' OR price = 1000

參考資料&推薦閱讀

https://docs.djangoproject.com/en/4.1/topics/db/queries/
https://docs.djangoproject.com/en/4.1/ref/models/querysets/#prefetch-objects