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)
相關文章
- Django中的QuerySetDjango
- Django(19)QuerySet APIDjangoAPI
- Django常用的QuerySet操作Django
- [轉] 高效使用 django 的 querysetDjango
- Django ORMDjangoORM
- django mysqlclient ormDjangoMySqlclientORM
- 03.Django-ORMDjangoORM
- Django ORM 多表操作DjangoORM
- Django 2.0 模型層中 QuerySet 查詢操作介紹Django模型
- django中orm的使用DjangoORM
- Django ORM 單表操作DjangoORM
- Django之ORM連表操作DjangoORM
- Django-ORM-單表操作DjangoORM
- Django-基礎-2-ORMDjangoORM
- Django-ORM 之查詢排序DjangoORM排序
- Django學習筆記(13)——Django的使用者認證元件,檢視層和QuerySet APIDjango筆記元件API
- django指令碼orm中使用原生sqlDjango指令碼ORMSQL
- django的orm有什麼優點DjangoORM
- Django-ORM---查詢集介紹DjangoORM
- Django-ORM 之指定欄位別名DjangoORM
- day52:django:ORM單表/多表操作DjangoORM
- 如何在 django 中用 orm 寫子查詢DjangoORM
- Django之ORM常用欄位和引數DjangoORM
- 如何在Django ORM中進行not查詢?DjangoORM
- 請不要以python思維對待django ORMPythonDjangoORM
- Django基礎二靜態檔案和ORMDjangoORM
- Django中ORM外來鍵和表的關係(Django程式設計-4)DjangoORM程式設計
- django | 常見 SQL 及其對應的 ORM 寫法DjangoSQLORM
- ORM資料增刪改查 django請求生命週期 django路由層 反向解析ORMDjango路由
- Django中ORM找出內容不為空的資料DjangoORM
- Django基礎四之測試環境和ORM查詢DjangoORM
- 深入 Django 模型層:資料庫設計與 ORM 實踐指南Django模型資料庫ORM
- Django ORM 引發的資料庫 N+1 效能問題DjangoORM資料庫
- django ORM 中 select_related 與 prefetch_related 的使用場景DjangoORM
- Django框架:8、聚合查詢、分組查詢、F與Q查詢、ORM查詢最佳化、ORM事務操作、ORM常用欄位型別、ORM常用欄位引數Django框架ORM型別
- Django orm基本crud方法大全!!所有操作只看一篇文章就夠了!!DjangoORM
- orm使用ORM
- ORM 陷阱ORM