1 Django中关系查询

• 一对一
• 一对多
• 多对多

1.1 一对多

1.1.1 app下创建models类

class Class(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=10, null=False)

    def __str__(self):
        return "<{}-{}>".format(self.id, self.name)

    class Meta:
        db_table = "class"


class Student(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=10, null=False)
    # class_id = models.ForeignKey(to="Class", to_field="id",on_delete=models.DO_NOTHING)
    cls = models.ForeignKey(to="Class", to_field="id",on_delete=models.DO_NOTHING)
    def __str__(self):
        return "<{}-{}-{}>".format(self.id, self.name, self.cls_id)

    class Meta:
        db_table = "student"
##### 注意 ####
1. 在 Student表中 
字段 class_id 迁移后会变为 class_id_id,所以最终将class_id改为cls,迁移后的数据库变为cls_id
2. 在对def __str__(self): 进行操作的时候,不需要进行数据库的迁移

1.1.2 插入数据

班级表插入数据

python manage.py shell
# 为班级创建数据
>>> data = {"id":1,"name":"一班"}
>>> class_one = Class.objects.create(**data)
>>> class_one
<Class: <1-一班>>
# 批量创建数据
>>> class_list = []  # 定义承载数据的列表
>>> class_two = Class(id=2,name="二班")  # 定义对象
>>> class_three = Class(id=3,name="三班")  # 定义对象
>>> class_list.append(class_two)
>>> class_list.append(class_three)
>>> class_list
[<Class: <2-二班>>, <Class: <3-三班>>]
>>> class_all = Class.objects.bulk_create(class_list) 
>>> class_all  # 注意此处返回的是两个对象
[<Class: <2-二班>>, <Class: <3-三班>>]

学生表插入数据

## 特别注意当出现该种情况的时候程序会报错
>>> data = {"id":1,"name":"kevin-1","cls":1} 
## 正确的应该为
data = {"id":1,"name":"kevin-1","cls_id":1} 
stu_one = Student.objects.create(**data)  # 创建数据
>>> stu_two = Student(id=2,name="Kevin-2",cls_id=2)
>>> stu_two.save()
>>> stu_two
<Student: <2-Kevin-2-2>>
## API update
>>> update_test = Student.objects.filter(id=1).update(cls_id=1)
>>> stu_all
<QuerySet [{'id': 1, 'name': 'Kevin-1', 'cls_id': 1}, {'id': 2, 'name': 'Kevin-2', 'cls_id': 2}]>

外键操作查询

正向查询

对象查找

>>> stuOne = Student.objects.first()  
>>> stuOne
<Student: <1-Kevin-1-1>>
>>> stuOne.cls   # 注意采用的是对象的名称
<Class: <1-一班>>
>>> stuOne.cls.name
'一班'
>>> stuOne.cls.id
1
>>> 

字段查找

>>> stuOne2 = Student.objects.values("id")
>>> stuOne2
<QuerySet [{'id': 1}, {'id': 2}, {'id': 3}]>
>>> stuOne2 = Student.objects.values("id","name")
>>> stuOne2
<QuerySet [{'id': 1, 'name': 'Kevin-1'}, {'id': 2, 'name': 'Kevin-2'}, {'id': 3, 'name': 'Kevin-3'}]>
>>> stuOne2 = Student.objects.values("id","name","cls__name")
>>> stuOne2
<QuerySet [{'id': 1, 'name': 'Kevin-1', 'cls__name': '一班'}, {'id': 2, 'name': 'Kevin-2', 'cls__name': '二班'}, {'id': 3, 'name': 'Kevin-3', 'cls__name': '三班'}]>

反向查询

对象查找

>>> clsOne = Class.objects.first()
>>> clsOne
<Class: <1-一班>>
>>> dir(clsOne)
['DoesNotExist', 'MultipleObjectsReturned', '__class__', '__delattr__', '__dict__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__getstate__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__setstate__', '__sizeof__', '__str__', '__subclasshook__', '__weakref__', '_check_column_name_clashes', '_check_field_name_clashes', '_check_fields', '_check_id_field', '_check_index_together', '_check_local_fields', '_check_long_column_names', '_check_m2m_through_same_relationship', '_check_managers', '_check_model', '_check_model_name_db_lookup_clashes', '_check_ordering', '_check_swappable', '_check_unique_together', '_do_insert', '_do_update', '_get_FIELD_display', '_get_next_or_previous_by_FIELD', '_get_next_or_previous_in_order', '_get_pk_val', '_get_unique_checks', '_meta', '_perform_date_checks', '_perform_unique_checks', '_save_parents', '_save_table', '_set_pk_val', '_state', 'check', 'clean', 'clean_fields', 'date_error_message', 'delete', 'from_db', 'full_clean', 'get_deferred_fields', 'id', 'name', 'objects', 'pk', 'prepare_database_save', 'refresh_from_db', 'save', 'save_base', 'serializable_value', 'student_set', 'unique_error_message', 'validate_unique']
>>> clsOne.student_set
<django.db.models.fields.related_descriptors.create_reverse_many_to_one_manager.<locals>.RelatedManager object at 0x7fb13e491ba8>
>>> clsOne.student_set.all()
<QuerySet [<Student: <1-Kevin-1-1>>]>
>>> clsOne.student_set.all().values()
<QuerySet [{'id': 1, 'name': 'Kevin-1', 'cls_id': 1}]>

字段查找

>>> one = Class.objects.filter(id=1).values("student__id","student__name","student__cls")
>>> one
<QuerySet [{'student__id': 1, 'student__name': 'Kevin-1', 'student__cls': 1}]>

---

related_name

对student表中增加related_name和related_query_name的属性重新生成迁移文件

class Student(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=10, null=False)
    cls = models.ForeignKey(to="Class", to_field="id", on_delete=models.DO_NOTHING, related_name="stu",
                            related_query_name="stuObj")

默认值 : None
作用 : 反向操作时，使用的字段名，用于代替 【表名_set】 如： obj.表名_set.all()

>>> classOne = Class.objects.first()
>>> classOne.stu.all()
<QuerySet [<Student: <1-Kevin-1-1>>]>
>>> classOne.stu.all().values()
<QuerySet [{'id': 1, 'name': 'Kevin-1', 'cls_id': 1}]>

related_query_name

默认值 : None
作用 : 反向操作时，使用的连接前缀，用于替换【表名】 如： models.UserGroup.objects.filter(表名字段名=1).values('表名字段名')

>>> Class.objects.values("stuObj__id","stuObj__name")
<QuerySet [{'stuObj__id': 1, 'stuObj__name': 'Kevin-1'}, {'stuObj__id': 2, 'stuObj__name': 'Kevin-2'}, {'stuObj__id': 3, 'stuObj__name': 'Kevin-3'}]>
>>> Class.objects.filter(id=1).values("stuObj__id","stuObj__name")
<QuerySet [{'stuObj__id': 1, 'stuObj__name': 'Kevin-1'}]>

1.2.多对多

1.2.1 使用框架自带的方式自行创建关联表

class Student(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=10, null=False)
    cls = models.ForeignKey(to="Class", to_field="id", on_delete=models.DO_NOTHING, related_name="stu",
                            related_query_name="stuObj")
    course = models.ManyToManyField(to="Course")

    def __str__(self):
        return "<{}-{}-{}>".format(self.id, self.name, self.cls_id)

    class Meta:
        db_table = "student"


class Course(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=10, null=False)

执行迁移文件数据库中查看

| onetomany_course  
| student                    
| student_course

mysql> desc student_course;
+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| student_id | int(11) | NO   | MUL | NULL    |                |
| course_id  | int(11) | NO   | MUL | NULL    |                |
+------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

插入数据

>>> course_list = []
>>> Chinese = Course(id=1,name="语文")
>>> Math = Course(id=2,name="数学")
>>> English = Course(id=3,name="英语")
>>> course_list.append(Chinese)
>>> course_list.append(Math)
>>> course_list.append(English)
>>> Course.objects.bulk_create(course_list)
[<Course: Course object (1)>, <Course: Course object (2)>, <Course: Course object (3)>]
>>> Course.objects.values()
<QuerySet [{'id': 1, 'name': '语文'}, {'id': 2, 'name': '数学'}, {'id': 3, 'name': '英语'}]>

create

正向

# 创建对象并且关联对象
Student.objects.first().course.create(id=3,name="英语")
<Course: Course object (3)>
# 创建了新的对象
>>> Course.objects.all().values()
<QuerySet [{'id': 1, 'name': '语文'}, {'id': 2, 'name': '数学'}, {'id': 3, 'name': '英语'}]>
####
    def __str__(self):
        return "<{}-{}-{}-{}>".format(self.id, self.name, self.cls_id, self.course.values())
####
>>> stu = Student.objects.first()
>>> stu
<Student: <1-Kevin-1-1-<QuerySet [{'id': 3, 'name': '英语'}]>>>

---

反向

>>> course = Course.objects.first().student_set.create(id=4,name="Kevin-4")
>>> stu = Student.objects.get(id=4)
>>> stu
<Student: <4-Kevin-4-None-<QuerySet [{'id': 1, 'name': '语文'}]>>>

add

正向

>>> courses
<QuerySet [<Course: <1-语文>>, <Course: <2-数学>>]>
>>> stu
<Student: <1-Kevin-1-1-<QuerySet [{'id': 3, 'name': '英语'}]>>>
>>> stu.course.add(*courses)
>>> Student.objects.get(id=1)
<Student: <1-Kevin-1-1-<QuerySet [{'id': 1, 'name': '语文'}, {'id': 2, 'name': '数学'}, {'id': 3, 'name': '英语'}]>>>

---

反向

>>> math = Course.objects.get(id=2)
>>> math
<Course: <2-数学>>
>>> math.student_set.add(*[1,2,3])
>>> Student.objects.filter(id=1).values()
<QuerySet [{'id': 1, 'name': 'Kevin-1', 'cls_id': 1}]>
>>> Student.objects.get(id=1)
<Student: <1-Kevin-1-1-<QuerySet [{'id': 2, 'name': '数学'}]>>>
>>> Student.objects.get(id=2)
<Student: <2-Kevin-2-2-<QuerySet [{'id': 2, 'name': '数学'}]>>>
>>> Student.objects.get(id=3)
<Student: <3-Kevin-3-3-<QuerySet [{'id': 2, 'name': '数学'}]>>>

remove

正向

>>> Student.objects.get(id=1)
<Student: <1-Kevin-1-1-<QuerySet [{'id': 2, 'name': '数学'}]>>>
>>> Student.objects.get(id=1).course.remove(2)
>>> Student.objects.get(id=1)
<Student: <1-Kevin-1-1-<QuerySet []>>>

---

反向

>>> Student.objects.get(id=2)
<Student: <2-Kevin-2-2-<QuerySet [{'id': 2, 'name': '数学'}]>>>
>>> Course.objects.get(id=2).student_set.remove(2)
>>> Student.objects.get(id=2)
<Student: <2-Kevin-2-2-<QuerySet []>>>

clear

>>> Student.objects.get(id=1)
<Student: <1-Kevin-1-1-<QuerySet [{'id': 1, 'name': '语文'}, {'id': 2, 'name': '数学'}, {'id': 3, 'name': '英语'}]>>>
>>> stuOne = Student.objects.get(id=1)
>>> stuOne.course.clear()
>>> stuOne
<Student: <1-Kevin-1-1-<QuerySet []>>>

set

>>> Student.objects.get(id=3)
<Student: <3-Kevin-3-3-<QuerySet [{'id': 2, 'name': '数学'}]>>>
>>> Student.objects.get(id=3).course.set([1])
>>> Student.objects.get(id=3)
<Student: <3-Kevin-3-3-<QuerySet [{'id': 1, 'name': '语文'}]>>>

---

>>> Student.objects.get(id=3)
<Student: <3-Kevin-3-3-<QuerySet [{'id': 1, 'name': '语文'}]>>>
>>> Course.objects.get(id=3).student_set.set([3])
>>> Student.objects.get(id=3)
<Student: <3-Kevin-3-3-<QuerySet [{'id': 1, 'name': '语文'}, {'id': 3, 'name': '英语'}]>>>

---

注意 :
对于ForeignKey对象，clear()和remove()方法仅在null=True时存在。

class Book(models.Model):
    title = models.CharField(max_length=32)
    publisher = models.ForeignKey(to=Publisher)

没有clear()和remove()方法：

>>> models.Publisher.objects.first().book_set.clear()
Traceback (most recent call last):
  File "<input>", line 1, in <module>
AttributeError: 'RelatedManager' object has no attribute 'clear'

当ForeignKey字段设置null=True时，

class Book(models.Model):
    name = models.CharField(max_length=32)
    publisher = models.ForeignKey(to=Class, null=True)

此时就有clear()和remove()方法：

models.Publisher.objects.first().book_set.clear()

1.2.2 自行创建第三张表

class Score(models.Model):
    id = models.AutoField(primary_key=True)
    student = models.ForeignKey(to="Student", to_field="id", on_delete=models.DO_NOTHING)
    course = models.ForeignKey(to="Course", to_field="id", on_delete=models.DO_NOTHING)
    grade = models.IntegerField(max_length=3)

    def __str__(self):
        return "<{}-{}-{}-{}>".format(self.id, self.student_id, self.course_id, self.grade)

    class Meta:
        db_table = "score"
        unique_together = ("student", "course")

1.2.3 设置ManyTomanyField并指定自行创建的第三张表

class Score(models.Model):
    id = models.AutoField(primary_key=True)
    student = models.ForeignKey(to="Student", to_field="id", on_delete=models.DO_NOTHING)
    course = models.ForeignKey(to="Course", to_field="id", on_delete=models.DO_NOTHING)
    grade = models.IntegerField()

    def __str__(self):
        return "<{}-{}-{}-{}>".format(self.id, self.student_id, self.course_id, self.grade)

    class Meta:
        db_table = "score"
        unique_together = ("student", "course")