7.12. Django ORM-Multi-table instance

7.12.1. Create a model ¶

Next, let’s take a look at multiple tables and multiple instances.

class Book(models.Model):
    title = models.CharField(max_length=32)
    price = models.DecimalField(max_digits=5, decimal_places=2)
    pub_date = models.DateField()
    publish = models.ForeignKey("Publish", on_delete=models.CASCADE)
    authors = models.ManyToManyField("Author")
class Publish(models.Model):
    name = models.CharField(max_length=32)
    city = models.CharField(max_length=64)
    email = models.EmailField()
class Author(models.Model):
    name = models.CharField(max_length=32)
    age = models.SmallIntegerField()
    au_detail = models.OneToOneField("AuthorDetail",
on_delete=models.CASCADE)
class AuthorDetail(models.Model):
    gender_choices = (
        (0, "女"),
        (1, "男"),
        (2, "保密"),
    )
    gender = models.SmallIntegerField(choices=gender_choices)
    tel = models.CharField(max_length=32)
    addr = models.CharField(max_length=64)
    birthday = models.DateField()

7.12.2. Table structure ¶

书籍表 Book Title, price, pub_date, publish (foreign key, many-to-one), authors (many-to-many)

出版社表 Publish : name 、 city 、 email

作者表 Author : name, age, au_detail (one to one)

作者详情表 AuthorDetail : gender 、 tel 、 addr 、 birthday

The following is a description of the table association:

https://www.runoob.com/wp-content/uploads/2020/05/Django-orm2_1.png

7.12.3. Insert data ¶

We perform the following SQL insert operation in MySQL:

insert into app01_publish(name,city,email) values ("华山出版社", "华山", "hs@163.com"), ("明教出版社", "黑木崖", "mj@163.com")

# 先插入 authordetail 表中多数据
insert into app01_authordetail(gender,tel,addr,birthday) values (1,13432335433,"华山","1994-5-23"), (1,13943454554,"黑木崖","1961-8-13"), (0,13878934322,"黑木崖","1996-5-20")

# 再将数据插入 author，这样 author 才能找到 authordetail
insert into app01_author(name,age,au_detail_id) values ("令狐冲",25,1), ("任我行",58,2), ("任盈盈",23,3)

7.12.4. ORM-add data ¶

7.12.5. One to many (foreign key ForeignKey) ¶

方式一: Pass the form of the object, the data type of the return value is the object, the book object.

步骤：

• 1. Get publisher object

• 2. Pulish the publishing house attribute of the book to the publishing house object

def add_book(request):
    #  获取出版社对象
    pub_obj = models.Publish.objects.filter(pk=1).first()
    #  给书籍的出版社属性publish传出版社对象
    book = models.Book.objects.create(title="菜鸟教程", price=200,
pub_date="2010-10-10", publish=pub_obj)
    print(book, type(book))
    return HttpResponse(book)

def add_book(request):
    #  获取出版社对象
    pub_obj = models.Publish.objects.filter(pk=1).first()
    #  获取出版社对象的id
    pk = pub_obj.pk
    #  给书籍的关联出版社字段 publish_id 传出版社对象的id
    book = models.Book.objects.create(title="冲灵剑法", price=100,
pub_date="2004-04-04", publish_id=pk)
    print(book, type(book))
    return HttpResponse(book)

7.12.6. Many-to-many (ManyToManyField): add data to the third relational table ¶

方式一: Pass in the form of an object with no return value.

步骤：

• 1. Get the author object

• 2. Get book object

• 3. Use the add method to pass the author object to the authors property of the book object

def add_book(request):
    #  获取作者对象
    chong = models.Author.objects.filter(name="令狐冲").first()
    ying = models.Author.objects.filter(name="任盈盈").first()
    #  获取书籍对象
    book = models.Book.objects.filter(title="菜鸟教程").first()
    #  给书籍对象的 authors 属性用 add 方法传作者对象
    book.authors.add(chong, ying)
    return HttpResponse(book)

def add_book(request):
    #  获取作者对象
    chong = models.Author.objects.filter(name="令狐冲").first()
    #  获取作者对象的id
    pk = chong.pk
    #  获取书籍对象
    book = models.Book.objects.filter(title="冲灵剑法").first()
    #  给书籍对象的 authors 属性用 add 方法传作者对象的id
    book.authors.add(pk)

7.12.7. Association Manager (object invocation) ¶

前提：

• Many-to-many (there is an association manager in both directions)

• One-to-many (only the object of the more than one class has an association manager, that is, in reverse)

语法格式：

正向：属性名
反向：小写类名加 _set

注意： One to many can only be reversed.

常用方法：

add() : for many-to-many, adding the specified model object to the associated object set (relational table).

注意： add() In one-to-many (that is, foreign keys), only objects can be passed *QuerySet Data type), cannot be passed id（*[id表]） .

*[ ] Use of:

Method 1: pass the object

book_obj = models.Book.objects.get(id=10)
author_list = models.Author.objects.filter(id__gt=2)
book_obj.authors.add(\*author_list)  # 将 id
大于2的作者对象添加到这本书的作者集合中
# 方式二：传对象 id
book_obj.authors.add(\*[1,3]) # 将 id=1 和 id=3
的作者对象添加到这本书的作者集合中
return HttpResponse("ok")

反向：小写表名_set

ying = models.Author.objects.filter(name="任盈盈").first()
book = models.Book.objects.filter(title="冲灵剑法").first()
ying.book_set.add(book)
return HttpResponse("ok")

create() Create a new object and add it to the associated object set at the same time

Returns the newly created object.

pub = models.Publish.objects.filter(name="明教出版社").first()
wo = models.Author.objects.filter(name="任我行").first()
book = wo.book_set.create(title="吸星大法", price=300,
pub_date="1999-9-19", publish=pub)
print(book, type(book))
return HttpResponse("ok")

remove() Removes the executed model object from the associated object set

For ForeignKey objects, this method exists only when null=True (which can be null) and no return value is returned.

author_obj =models.Author.objects.get(id=1)
book_obj = models.Book.objects.get(id=11)
author_obj.book_set.remove(book_obj)
return HttpResponse("ok")

#  清空独孤九剑关联的所有作者
book = models.Book.objects.filter(title="菜鸟教程").first()
book.authors.clear()

7.12.8. ORM query ¶

Object-based cross-table query.

正向：属性名称
反向：小写类名_set

7.12.9. One to many ¶

Query the city where the publisher of the book with the primary key of 1 is located (forward).

book = models.Book.objects.filter(pk=10).first()
res = book.publish.city
print(res, type(res))
return HttpResponse("ok")

pub = models.Publish.objects.filter(name="明教出版社").first()
res = pub.book_set.all()
for i in res:
    print(i.title)
return HttpResponse("ok")

7.12.10. one-for-one ¶

Inquire Linghu Chong’s phone number (positive)

Forward: object. The attribute (author.au_detail) can jump to the associated table (author details table)

author = models.Author.objects.filter(name="令狐冲").first()
res = author.au_detail.tel
print(res, type(res))
return HttpResponse("ok")

addr = models.AuthorDetail.objects.filter(addr="黑木崖").first()
res = addr.author.name
print(res, type(res))
return HttpResponse("ok")

7.12.11. Many to many ¶

The names and mobile phone numbers of all the authors of the rookie tutorial (positive).

Positive: 对象.属性(book.authors) You can jump to the associated table (author table).

The author’s phone number is not in the author’s table, so it is passed again. 对象.属性(i.au_detail) Jump to the associated table (author details table).

book = models.Book.objects.filter(title="菜鸟教程").first()
res = book.authors.all()
for i in res:
    print(i.name, i.au_detail.tel)
return HttpResponse("ok")

author = models.Author.objects.filter(name="任我行").first()
res = author.book_set.all()
for i in res:
    print(i.title)
return HttpResponse("ok")

7.12.12. Cross-table query based on double underscore ¶

Forward: attribute name _ _ across table attribute name reverse: lowercase class name _ _ across table attribute name

7.12.13. One to many ¶

Find out the names and prices of all books published by Cainiao Publishing House.

res =
models.Book.objects.filter(publish__name="菜鸟出版社").values_list("title",
"price")

res =
models.Publish.objects.filter(name="菜鸟出版社").values_list("book__title","book__price")
return HttpResponse("ok")

7.12.14. Many to many ¶

Inquire about the names of all the books that I have published.

Forward: obtain data across tables through attribute name _ _ cross-table attribute name (authors__name):

res = models.Book.objects.filter(authors__name="任我行").values_list("title")

Reverse: obtain data across tables by using lowercase class name _ _ cross-table attribute name (book__title):

res = models.Author.objects.filter(name="任我行").values_list("book__title")

7.12.15. one-for-one ¶

Check the mobile phone number of any bank.

Forward: through 属性名称__跨表的属性名称(au_detail__tel) Get data across tables.

res = models.Author.objects.filter(name="任我行").values_list("au_detail__tel")

Reversing: through 小写类名__跨表的属性名称（author__name） Get data across tables.

res = models.AuthorDetail.objects.filter(author__name="任我行").values_list("tel")