web應用需要經(jīng)常向數(shù)據(jù)庫查詢相關的數(shù)據(jù)， Django語言 也不例外。了解Django中的數(shù)據(jù)查詢操作，對于優(yōu)化應用的性能，有著重要的意義。 

基礎信息 

Django中，model通過Manager獲取QuerySet，每個model至少有objects這個 Manager 。 

QuerySet 可以有一個或多個 filter , filter 根據(jù)傳入的參數(shù)返回 QuerySet 結果。 

用SQL來對比， QuerySet 相當于 SELECT ， filter 相當于 WHERE 或者是 LIMIT 

每次添加一條filter，會獲得一個新的 QuerySet 。 


思路 

利用Django的測試框架，構造測試數(shù)據(jù)，查看Django構建的SQL，以及查詢到的內容。 

設計表結構 


schema 

創(chuàng)建model 

創(chuàng)建測試app，并加入settings.py APPS中 

python manage.py startapp learning 

INSTALLED_APPS = [ 
     ... 
    'learning', 
    ... 
] 
創(chuàng)建對應的model 

class Student(models.Model): 
    name = models.CharField(max_length=255, help_text="學生姓名") 
    create_time = models.DateTimeField(auto_now_add=True, help_text="創(chuàng)建時間") 
    def __str__(self): 
        return self.name 


class Teacher(models.Model): 
    name = models.CharField(max_length=255, help_text="老師姓名") 
    create_time = models.DateTimeField(auto_now_add=True, help_text="創(chuàng)建時間") 
    def __str__(self): 
        return self.name 


class Classe(models.Model): 
    name = models.CharField(max_length=255, help_text="班級名稱") 
    teacher = models.ForeignKey(Teacher, help_text="老師") 
    create_time = models.DateTimeField(auto_now_add=True, help_text="創(chuàng)建時間") 
    def __str__(self): 
        return self.name 


class ClasseStudent(models.Model): 
    student = models.ForeignKey(Student) 
    classe = models.ForeignKey(Classe) 
    create_time = models.DateTimeField(auto_now_add=True, help_text="創(chuàng)建時間") 
測試數(shù)據(jù) 

使用factory-boy來輔助構造測試數(shù)據(jù)，代碼如下 

class TeacherFactory(factory.DjangoModelFactory): 
    class Meta: 
        model = Teacher 

    name = factory.Sequence(lambda n: "Teacher #%s" % n) 

class StudentFactory(factory.DjangoModelFactory): 
    class Meta: 
        model = Student 

    name = factory.Sequence(lambda n: "Student #%s" % n) 

class ClasseFactory(factory.DjangoModelFactory): 
    class Meta: 
        model = Classe 

    name = factory.Sequence(lambda n: "Class #%s" % n) 

class ClasseStudentFactory(factory.DjangoModelFactory): 
    class Meta: 
        model = ClasseStudent 

    student = factory.SubFactory(StudentFactory) 
    classe = factory.SubFactory(ClasseFactory) 
現(xiàn)在來偽造測試數(shù)據(jù) 

在tests.py添加測試用例 

class ModelTestCase(TestCase): 
    def setUp(self): 
        self.teacher_one = TeacherFactory() 
        self.class_one = ClasseFactory(teacher=self.teacher_one) 
        self.class_two = ClasseFactory(teacher=self.teacher_one) 
        for i in range(40): 
            ClasseStudentFactory(student=StudentFactory(), classe=self.class_one) 
        for i in range(40): 
            ClasseStudentFactory(student=StudentFactory(), classe=self.class_two) 
可以看到，添加了一個老師，兩個班，每個班加入了40個學生 

filter和exclude 

添加testcase 

def test_filter_chain(self): 
    query_set = Student.objects.filter(name__startswith='Student').exclude(pk=1).filter(create_time__year__gte=2015)[3:10] 
    print query_set.query 
    print query_set 
執(zhí)行 

python manage.py test python manage.py test learning.test.test.ModelTestCase.test_filter_chain 
可以看到結果 

SELECT `learning_student`.`id`, `learning_student`.`name`, `learning_student`.`create_time` FROM `learning_student` WHERE (`learning_student`.`name` LIKE BINARY Student% AND NOT (`learning_student`.`id` = 1) AND `learning_student`.`create_time` >= 2014-12-31 16:00:00) LIMIT 7 OFFSET 3
<QuerySet [<Student: Student #4>, <Student: Student #5>, <Student: Student #6>, <Student: Student #7>, <Student: Student #8>, <Student: Student #9>, <Student: Student #10>]> 
和我們預期的一致，值得注意的是，Django執(zhí)行的是lazy query，也就是說前面的filter和exclude并沒有去查詢數(shù)據(jù)庫，后面的分片才導致了那次的查詢 

order_by 

def test_order_by(self): 
        query_set = Classe.objects.order_by("-create_time") 
        print query_set.query 
        print query_set 
SELECT `learning_classe`.`id`, `learning_classe`.`name`, `learning_classe`.`teacher_id`, `learning_classe`.`create_time` FROM `learning_classe` ORDER BY `learning_classe`.`create_time` DESC 
<QuerySet [<Classe: Class #3>, <Classe: Class #2>]> 
如果有多個column參與排序，可以使用 Coalesce 

select_related 

可以用select_related查詢外鍵的信息，并將結果保存，這樣查找外鍵信息時，將不會向數(shù)據(jù)庫發(fā)送請求，如下所示 

def test_select_related(self): 
        query_set = Classe.objects.select_related("teacher") 
        print query_set.query 
        print query_set[0].teacher 
SELECT `learning_classe`.`id`, `learning_classe`.`name`, `learning_classe`.`teacher_id`, `learning_classe`.`create_time`, `learning_teacher`.`id`, `learning_teacher`.`name`, `learning_teacher`.`create_time` FROM `learning_classe` INNER JOIN `learning_teacher` ON (`learning_classe`.`teacher_id` = `learning_teacher`.`id`) 
<QuerySet [<Classe: Class #0>, <Classe: Class #1>]> 
defer和only 

有時候查詢只需要部分字段的結果，可以用defer和only來限制查詢的結果 

def test_defer(self): 
        print Classe.objects.defer("create_time").query 
        print Classe.objects.only("create_time").query 
SELECT `learning_classe`.`id`, `learning_classe`.`name`, `learning_classe`.`teacher_id` FROM `learning_classe` 
SELECT `learning_classe`.`id`, `learning_classe`.`create_time` FROM `learning_classe` 
如果訪問到?jīng)]有獲取的字段時，會再從數(shù)據(jù)庫中讀一次 

文章來源：簡書