Django ORM QuerySet

Model

class Goods(models.Model):
    name = models.CharField(max_length=20, unique=True,
                            validators=[RegexValidator(regex=r'^[\w]+$', message="商品名稱必須為英文或數字", code="invalid")],
                            verbose_name="商品名稱", error_messages={"invalid": "商品名稱必須為英文或數字"})
    # location = models.CharField(max_length=50, verbose_name="產地")
    band = models.ForeignKey(to="Band", related_name="goods", db_constraint=False, db_index=True, verbose_name="品牌")
    price = models.DecimalField(max_digits=9, decimal_places=2, verbose_name="定價")
    tags = models.ManyToManyField(to="Tag", blank=True, related_name="goods", db_constraint=False,
                                  related_query_name="goods", verbose_name="標籤")

    class Meta:
        verbose_name = "商品"
        verbose_name_plural = verbose_name

    def __unicode__(self):
        return self.name

    __str__ = __unicode__


class Band(models.Model):
    name = models.CharField(max_length=20, unique=True, verbose_name="品牌")

    class Meta:
        verbose_name = "品牌"
        verbose_name_plural = verbose_name

    def __unicode__(self):
        return self.name

    __str__ = __unicode__


class Tag(models.Model):
    name = models.CharField(max_length=20, unique=True, verbose_name="標籤")

    class Meta:
        verbose_name = "標籤"
        verbose_name_plural = verbose_name
        # db_table = 'tag'
        # index_together = [("name", )]
        # unique_together = [("name", )]


    def __unicode__(self):
        return self.name

    __str__ = __unicode__

QuerySet 的方法

### 返回一個新的queryset
    - all
        good = Goods.objects.all()
    - filter
        goods = Goods.objects.filter(name = name)
        goods = Goods.objects.filter(price__lt = 20)
        goods = Goods.objects.filter(price__gte = 20)
        goods = Goods.objects.filter(name__iexact = 'iphone')
        goods = Goods.objects.filter(name__contains = 'iphone')
        goods = Goods.objects.filter(name__icontains = 'iphone')
        goods = Goods.objects.filter(name__isnull = True)
        goods = Goods.objects.filter(price__range = [10, 20])
        goods = Goods.objects.filter(name__startswith = 'iphone')
        goods = Goods.objects.filter(band__name = 'quick')
        goods = Goods.objects.filter(Q(name = name) & Q(price = 22))
        goods = Goods.objects.filter(Q(name = name) & ~Q(price = 22))[10:10]
        goods = Goods.objects.filter(create_at__date = datetime.date(2015, 1, 10))
    - exclude
        goods = Goods.objects.exclude(name = name) # 取反
    - order_by
        goods = Goods.objects.order_by('-price')
    - annotate
        orders = Order.objects.filter(goods = goods).annotate(sum_amount = Sum('amount'))
    - extra
        # extra(select, where, params, tables, order_by, select_params)
        Goods.objects.extra(select = {"new_id": "select id from %s"}, select_params = (1, ), where = ["head_line=%s"])
    - distinct
        Goods.objects.distinct("id")
    - reverse
        Goods.objects.order_by("-pk").reverse() # 只有使用了order_by 才可以
    - only
        Goods.objects.filter(pk = 1).only("name") # 只會查取only欄位，返回的是QuerySet 當需要查詢別的欄位時 會再次使用SQL
    - defer # 和only 相反 ，查取的欄位是除括號中的欄位之外的欄位 返回值是一樣的
    - using # 使用哪個庫
    - union
        q1 = Goods.objects.filter(pk=1)
        q2 = Goods.objects.filter(pk=2)
        q = q1.union(q2) # 具有相同列的才可以union
    - intersection # 交集
        q1  = Goods.objects.filter(pk__lt = 3)
        q2  = Goods.objects.filter(pk__gte = 2)
        q1.intersection(q2)
    - difference # 差集
    - select_for_update
        :param nowait 無需等待即報錯 OperationalError: could not obtain lock
        :param skip_locked 無需等待即報錯 DoesNotExist: Goods matching query does not exist.
    - select_related # 連表操作  作用與外來鍵
        Goods.objects.select_related("band")
        # SELECT "my_app_goods"."id", "my_app_goods"."name", "my_app_goods"."band_id", "my_app_goods"."price", "my_app_band"."id", "my_app_band"."name" FROM "my_app_goods" INNER JOIN "my_app_band" ON ("my_app_goods"."band_id" = "my_app_band"."id")
    - prefetch_related # 在多對多模型中使用 使用子查詢來代替連表操作
        Goods.objects.prefetch_related('tags')
### 執行資料庫查詢方法
    - iterator
        # 迭代
    - aggregate #聚合
        p = Goods.objects.aggregate(Count('price'))
        p['price__count']
    - count
    - get
    - create
    - bulk_create
    - get_or_create(defaults=None, **kwargs)
    - update_or_create(defaults=None, **kwargs)
    - earliest # 獲取最先建立的記錄
        earliest(field_name = None)
        如果 field_name 為None 那麼從 model 的 get_latest_by 的屬性獲取欄位名稱
    - latest # 最新記錄
    - first 第一條記錄
    - last 最後一條記錄
    - in_bulk(id_list = None)
        返回一個以id為鍵的字典
        Goods.objects.in_bulk([1, 3, 5]) => {1: <Goods: iPhone7>, 3: <Goods: iPhone X>}
        如果id_list 為None 返回所有的值
    - delete
    - update
    - exists return BOOLEAN
### 返回queryset的子類
    - raw raw(self, raw_query, params=None, translations=None, using=None):
        Goods.objects.raw("select * from my_app_goods where name = %s", params = ["iPhone7"])
        params 能用就用可以有效防止SQL隱碼攻擊
        返回的是一個可迭代的物件
    - values
        返回特定欄位的結果，相當於SQL中的select
        Goods.objects.values('name', 'price')
        <QuerySet [{'price': Decimal('6300.00'), 'name': u'iPhone7'}]>
    - values_list
        返回的是元祖
        <QuerySet [(u'iPhone7', Decimal('6300.00')), (u'iPhone8', Decimal('6400.00'))]>
    - dates dates(self, field_name, kind, order='ASC')
    - datetimes
    - none
        return <QuerySet []>
### 屬性
    - ordered
        是否排序了
    - db
        使用的是哪個db
### OTHER
    - Q
        1. Q((Q(name = name) & Q(age = 21)) | Q(sex = 2))
        2. Q().add(Q(name = name), Q.AND).add(Q(sex = 2), Q.OR)