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)
