ORM - Special Usages

select_for_update

• 返回queryset，并将需要更新的行锁定，类似于SELECT ... FOR UPDATE的操作。

# 所有匹配的entries都会被锁定直到此次事务结束。
with transaction.atomic():
    entries = Entry.objects.select_for_update().filter(author=request.user)

select_related

from django.db import models
 
class Province(models.Model):
    name = models.CharField(max_length=10)
    def __unicode__(self):
        return self.name
 
class City(models.Model):
    name = models.CharField(max_length=5)
    province = models.ForeignKey(Province)
    def __unicode__(self):
        return self.name

• 传统做法

  >>> citys = City.objects.all()
  >>> for c in citys:
  ...   print c.province
  ...
  

  这样会导致线性的SQL查询，如果对象数量n太多，每个对象中有k个外键字段的话，就会导致n*k+1次SQL查询。在本例中，因为有3个city对象就导致了4次SQL查询：

  SELECT `QSOptimize_city`.`id`, `QSOptimize_city`.`name`, `QSOptimize_city`.`province_id`
  FROM `QSOptimize_city`;
  
  SELECT `QSOptimize_province`.`id`, `QSOptimize_province`.`name` 
  FROM `QSOptimize_province`
  WHERE `QSOptimize_province`.`id` = 1 ;
  
  SELECT `QSOptimize_province`.`id`, `QSOptimize_province`.`name` 
  FROM `QSOptimize_province`
  WHERE `QSOptimize_province`.`id` = 2 ;
  
  SELECT `QSOptimize_province`.`id`, `QSOptimize_province`.`name` 
  FROM `QSOptimize_province`
  WHERE `QSOptimize_province`.`id` = 1 ;
  

• select_related做法

  >>> citys = City.objects.select_related('province').all()
  >>> for c in citys:
  ...   print c.province
  ...
  

  SELECT `QSOptimize_city`.`id`, `QSOptimize_city`.`name`, 
  `QSOptimize_city`.`province_id`, `QSOptimize_province`.`id`, `QSOptimize_province`.`name` 
  FROM`QSOptimize_city` 
  INNER JOIN `QSOptimize_province` ON (`QSOptimize_city`.`province_id` = `QSOptimize_province`.`id`) ;
  

• 注意使用select_related一定要指定参数或者指定depth，否则默认depth=0，将会遍历该model的所有外键，知道遍历不到。

get_or_created

    obj, created = Person.objects.get_or_create(
        first_name='John',
        last_name='Lennon',
        defaults={'birthday': date(1940, 10, 9)},
    )

    if created:
        # means you have created a new person
    else:
        # person just refers to the existing one

Any keyword arguments passed to get_or_create() — except an optional one called defaults — will be used in a get() call. If an object is found, get_or_create() returns a tuple of that object and False. If multiple objects are found, get_or_create raises MultipleObjectsReturned. If an object is not found, get_or_create() will instantiate and save a new object, returning a tuple of the new object and True.

bulk_create

在Django中需要向数据库中插入多条数据（list）。使用如下方法，每次save()的时候都会访问一次数据库。导致性能问题:

    for i in resultlist:
        p = Account(name=i)
        p.save()

在django1.4以后加入了新的特性。使用django.db.models.query.QuerySet.bulk_create()批量创建对象，减少SQL查询次数。改进如下：

    querysetlist=[]
    for i in resultlist:
        querysetlist.append(Account(name=i))
    Account.objects.bulk_create(querysetlist)

values_list('', flat=True)

• 该查询会获得一个 django.db.models.query.ValuesListQuerySet类型的变量。如果想转化成LIST类型的，可以用下面粗暴的方法：

  picture_ids = list(picture_foreign_pictures.values_list('picture_id', flat=True))