Django数据查询

发表于 | 分类于 | | 阅读
Django数据查询

具体获取单个对象的时候， 使用 get_object_or_404() 而不是 get()

注意只在views中使用

➤ Only use it in views.
➤ Don’t use it in helper functions, forms, model methods or anything that is not a view
or directly view related.

注意处理 查询过程中会抛出的异常

  • ObjectDoesNotExist vs DoesNotExist

    ObjectDoesNotExist 可以是 任意的model， 不需要指定; DoesNotExist 需要指定具体的model

    ObjectDoesNotExist can be applied to any model object, whereas DoesNotExist is for a specific model.


from django.core.exceptions import ObjectDoesNotExist
from flavors.models import Flavor
from store.exceptions import OutOfStock

def list_flavor_line_item(sku):
    try:
        return Flavor.objects.get(sku=sku, quantity__gt=0)
    except Flavor.DoesNotExist:
        msg = 'We are out of {0}'.format(sku)
        raise OutOfStock(msg)


def list_any_line_item(model, sku):
    try:
        return model.objects.get(sku=sku, quantity__gt=0)
    except ObjectDoesNotExist:
        msg = 'We are out of {0}'.format(sku)
        raise OutOfStock(msg)
  • 有可能返回的结果不只是一个的情况 MultipleObjectsReturned
from flavors.models import Flavor
from store.exceptions import OutOfStock, CorruptedDatabase

def list_flavor_line_item(sku):
    try:
        return Flavor.objects.get(sku=sku, quantity__gt=0)
    except Flavor.DoesNotExist:
        msg = 'We are out of {}'.format(sku)
        raise OutOfStock(msg)
    except Flavor.MultipleObjectsReturned:
        msg = 'Multiple items have SKU {}. Please fix!'.format(sku)
        raise CorruptedDatabase(msg)

使用lazy Evaluation 懒加载

Django 的 ORM 查询是懒加载的, 只有loop-results的时候， 才会请求数据库, 所以可以chain ORM的方法


from django.models import Q
from promos.models import Promo

def fun_function(name=None):
    """Find working ice cream promo"""
    results = Promo.objects.active()
    results = results.filter(
        Q(name__startswith=name) |
        Q(description__icontains=name)
       ）
    results = results.exclude(status='melted')
    results = results.select_related('flavors')
       
    return results
    

Promo
    .objects
    .active()
    .filter(
    Q(name__startswith=name) |
    Q(description__icontains=name)
    )
    .exclude(status='melted')
    .select_related('flavors')

# 缺点是不好打断点， 查看具体的sql语句
# 可以使用 pdb
import pdb; pdb.set_trace()

使用有效的查询方法 Query Expressions

from django.db.models import F
from models.customers import Customer
customers = Customer.objects.filter(scoops_ordered__gt=F('store_visits'))

SELECT * from customers_customer where scoops_ordered > store_visits

尽量避免使用 Raw SQL

If it’s easier to write a query using SQL than Django, then do it. extra() is
nasty and should be avoided; raw() is great and should be used where appropriate

                                         --- Django project co-leader Jacob Kaplan-Moss

给字段增加索引 db_index=True

一开始建表不加，有需要的时候再加上

➤ The index would be used frequently, as in 10–25% of all queries.
➤ There is real data, or something that approximates real data, so we can analyze the results of
indexing.
➤ We can run tests to determine if indexing generates an improvement in results

事务 transactions

ACID

atomic

consistent
 
isolated 

durable

Django ORM 有很多事务的机制, 装饰器 上下文管理工具

  • 1 使用事务 包裹 每一次的 HTTP request 尽可能使用ATOMIC_REQUESTS

配置文件中设置


# settings/base.py
DATABASES = {
'default': {
    # ...
    'ATOMIC_REQUESTS': True,
},
}


优点

    安全
        all requests are wrapped in transactions, including only read data

缺点
     
    影响性能
        performance can suffer

使用 transaction.non_atomic_requests() 装饰器来解放某些 无关database的操作


from django.db import transaction
from django.http import HttpResponse
from django.shortcuts import get_object_or_404
from django.utils import timezone
from .models import Flavor

@transaction.non_atomic_requests
def posting_flavor_status(request, pk, status):
    flavor = get_object_or_404(Flavor, pk=pk)
    
    # This will execute in autocommit mode (Django's default).
    flavor.latest_status_change_attempt = timezone.now()
    flavor.save()
    
    with transaction.atomic():
        # This code executes inside a transaction.
        flavor.status = status
        flavor.latest_status_change_success = timezone.now()
        flavor.save()
        return HttpResponse('Hooray')
        
    # If the transaction fails, return the appropriate status
    return HttpResponse('Sadness', status_code=400)

  • 明确事务应该包含的代码块， 事务应该出现的位置 Explicit Transaction Declaration

      大部分时间使用 ATOMIC_REQUESTS
    
  Use ATOMIC_REQUESTS as long as the performance overhead
  is bearable. That means “forever” on most sites.’
    
                  -- Aymeric Augustin Django 核心开发者

建议：

➤ Database operations that do not modify the database should not be wrapped in transactions.
   数据库的非修改操作， 不应该包含在事务内
   
➤ Database operations that modify the database should be wrapped in a transaction.
   数据库的修改操作， 应该包含在事务内
   
➤ Special cases including database modifications that require database reads and performance
   considerations can affect the previous two guidelines.
   
   性能的考虑也会影响具体的操作
动作 ORM方法 通常情况使用事务
Create Data 创建 .create(), .bulk_create(), .get_or_create() ✓
Retrieve Data 获取 .get(), .filter(), .count(), .iterate(), .exists(),.exclude(), .in_bulk, etc.  
Modify Data 修改 .update() ✓
Delete Data 删除 .delete() ✓
  • Mysql中的 Transactions

Mysql中的表类型 InnoDB， MyISAM 事务可能不支持， 这时Django会默认使用autocommit 模式

Buy me a 肥仔水！