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

 

基礎(chǔ)信息

 

Django中，model通過(guò)Manager獲取QuerySet，每個(gè)model至少有objects這個(gè) Manager 。

 

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

 

用SQL來(lái)對(duì)比， QuerySet 相當(dāng)于 SELECT ， filter 相當(dāng)于 WHERE 或者是 LIMIT

 

每次添加一條filter，會(huì)獲得一個(gè)新的 QuerySet 。

 

 

思路

 

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

 

設(shè)計(jì)表結(jié)構(gòu)

 

 

schema

 

創(chuàng)建model

 

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

 

python manage.py startapp learning

 

INSTALLED_APPS = [

     ...

    'learning',

    ...

]

創(chuàng)建對(duì)應(yīng)的model

 

class Student(models.Model):

    name = models.CharField(max_length=255, help_text="學(xué)生姓名")

    create_time = models.DateTimeField(auto_now_add=True, help_text="創(chuàng)建時(shí)間")

    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)建時(shí)間")

    def __str__(self):

        return self.name

 

 

class Classe(models.Model):

    name = models.CharField(max_length=255, help_text="班級(jí)名稱")

    teacher = models.ForeignKey(Teacher, help_text="老師")

    create_time = models.DateTimeField(auto_now_add=True, help_text="創(chuàng)建時(shí)間")

    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í)間")

測(cè)試數(shù)據(jù)

 

使用factory-boy來(lái)輔助構(gòu)造測(cè)試數(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)在來(lái)偽造測(cè)試數(shù)據(jù)

 

在tests.py添加測(cè)試用例

 

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)

可以看到，添加了一個(gè)老師，兩個(gè)班，每個(gè)班加入了40個(gè)學(xué)生

 

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

可以看到結(jié)果

 

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>]>

和我們預(yù)期的一致，值得注意的是，Django執(zhí)行的是lazy query，也就是說(shuō)前面的filter和exclude并沒(méi)有去查詢數(shù)據(jù)庫(kù)，后面的分片才導(dǎo)致了那次的查詢

 

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>]>

如果有多個(gè)column參與排序，可以使用 Coalesce

 

select_related

 

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

 

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

 

有時(shí)候查詢只需要部分字段的結(jié)果，可以用defer和only來(lái)限制查詢的結(jié)果

 

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`

如果訪問(wèn)到?jīng)]有獲取的字段時(shí)，會(huì)再?gòu)臄?shù)據(jù)庫(kù)中讀一次

 

文章來(lái)源：簡(jiǎn)書(shū)