Master Django ORM: From Bookstore Schema Design to Advanced Queries

Preface

In everyday development we often need to perform a large number of CRUD operations on databases. While raw SQL offers the highest performance and flexibility, most scenarios do not require such strict performance or complex requirements, so using a simple and fast ORM is preferred.

Table Structure Design

We design a simple book management system (not a library). The system includes a Book table to store book information, a Publisher table for publishing houses, an Author table for authors, and a many‑to‑many table linking books and authors.

Each book belongs to a publisher, so the final schema looks like this:

Django models code

from django.db import models

# Author table
class Author(models.Model):
    name = models.CharField(verbose_name="作者姓名", max_length=8)
    age = models.IntegerField(verbose_name="作者年龄")
    phone = models.CharField(verbose_name="作者联系方式", max_length=11)

# Publisher table
class Publish(models.Model):
    title = models.CharField(verbose_name="出版社名称", max_length=16)
    phone = models.CharField(verbose_name="出版联系方式", max_length=11)

# Book table
class Book(models.Model):
    title = models.CharField(verbose_name="书名", max_length=32)
    price = models.DecimalField(verbose_name="价格", max_digits=5, decimal_places=2)
    PublishDate = models.DateField(verbose_name="初版日期")
    publish = models.ForeignKey(to=Publish, verbose_name="所属出版社", on_delete=models.CASCADE)

# Book‑many‑author (many‑to‑many) table
class BookManyAuthor(models.Model):
    book = models.ForeignKey(to=Book, verbose_name="所属图书", on_delete=models.CASCADE)
    author = models.ForeignKey(to=Author, verbose_name="所属作者", on_delete=models.CASCADE)

MySQL SQL files

The data in the accompanying web_author.sql, web_book.sql, web_bookmanyauthor.sql and web_publish.sql files matches the diagrams above.

Query Operations

Retrieve all (all)

Syntax:

models.<ModelClass>.objects.all()

author_list = models.Author.objects.all()
print(author_list)

Filter with conditions (filter)

Syntax:

models.<ModelClass>.objects.filter(<condition>)

author = models.Author.objects.filter(name="张三")
print(author)

Because filter() may return multiple rows, the result is a QuerySet, not a single object. Use .first() to get the first matching record.

author = models.Author.objects.filter(name="张三").first()
print(author, type(author))
print(author.name, author.phone)

Multiple conditions in filter

author = models.Author.objects.filter(name="张三", age=22).first()
print(author, type(author))
print(author.name, author.phone)

Common filter condition shortcuts

# contains
field__contains="三"   # WHERE field LIKE "%三%"
# startswith
field__startswith="三"  # WHERE field LIKE "三%"
# endswith
field__endswith="三"    # WHERE field LIKE "%三"
# is null / not null
field__isnull=True   # WHERE field IS NULL
field__isnull=False  # WHERE field IS NOT NULL
# in list
field__in=[1,2,3]    # WHERE field IN (1,2,3)
# greater / greater or equal
field__gt=1          # WHERE field > 1
field__gte=1         # WHERE field >= 1
# less / less or equal
field__lt=1          # WHERE field < 1
field__lte=1         # WHERE field <= 1
# date parts
date_field__year=2020
date_field__month=3
date_field__day=4
# range
date_field__range=("2020-01-01", "2020-06-01")

Single‑object retrieval (get)

get()

returns a single model instance but raises an error if the query matches zero or multiple rows.

author = models.Author.objects.get(name="李四")
print(author, type(author))
print(author.name, author.phone)

Because of the strict behavior, using filter().first() is usually safer.

Inspecting raw SQL (query)

After a filter() call, the query attribute reveals the generated SQL.

sql = models.Author.objects.filter(name="李四").query
print(sql)

Conclusion

This article demonstrated how to design a simple book‑store database, create corresponding Django models (Book, Publisher, Author, and the many‑to‑many relationship), and use the ORM to query all records, apply filters, retrieve a single object safely, and view the underlying SQL.