Django ORM 一对一,一对多,多对多, 添加,批量插入和查询 F与Q添加
模型类
class Book(models.Model):
nid = models.AutoField(primary_key=True)
title = models.CharField(max_length=32, default=None)
create_time = models.DateTimeField()
price = models.DecimalField(decimal_places=2, max_digits=8, default=None)
publish_id = models.ForeignKey(to="Publish", on_delete=models.CASCADE)
authors = models.ManyToManyField(to='Author')
def __str__(self):
return str(self.nid) + ':' + self.title
class Publish(models.Model):
nid = models.AutoField(primary_key=True)
name = models.CharField(max_length=32)
email = models.CharField(max_length=32)
class Author(models.Model):
name = models.CharField(max_length=32)
age = models.IntegerField()
email = models.CharField(max_length=32)
ad = models.OneToOneField(to="AuthorDetail", on_delete=models.CASCADE)
class AuthorDetail(models.Model):
address = models.CharField(max_length=32)
telephone = models.IntegerField()
批量插入
book_list = [] for i in range(100): book = Book(title='book_%s' % i, price=i * i) book_list.append(book) Book.objects.bulk_create(book_list)
一对多和多对多 添加操作
# 一对多添加用户
publish = Publish.objects.get(nid=1)
Book.objects.create(
title='ubuntu',
price=122,
create_time='2012-2-2',
publish_id=publish,
# publish_id_id=publish.nid )
# 多对多添加用户
author01 = Author.objects.get(id="1")
author02 = Author.objects.get(id="2")
book = Book.objects.get(nid='2')
# book.authors.add(author01, author02)
# 多对多第二种
book.authors.add(1, 2)
# 多对多第三种
book.authors.add(*[1, 2])
# 删除关联的作者
book.authors.remove(author01)
# 删除所有关联的作者
book.authors.clear()
# 先删除所有在绑定
book.authors.set(1)
查询操作
基于对象的跨表查询(基于子查询)
# 一对多查询 # 正查 book = Book.objects.get(nid='1') # 取到关联的对象 publish = book.publish_id print(publish.name) # 反查 publish = Publish.objects.get(nid='1') books = publish.book_set.all() # 多对多查询 book = Book.objects.get(nid='1') authors = book.authors.all() author = Author.objects.get(id='1') book = author.book_set.all() # 一对一 author = Author.objects.get(id='1') ad = author.ad author_detail = AuthorDetail.objects.get(id='1') author = author_detail.author
# 基于双下划线的跨表查询(基于join实现的)
# KEY:正向查询按字段,反向查询按表明小写
# linux这本书的出版社名字 # 正向 name = Book.objects.filter(title='linux').values('publish_id__name') # 反向 name = Publish.objects.filter(book__title='linux').values('name') # 查询第一个出版社对用的所有书 # 正向 book = Publish.objects.filter(nid='1').values('book__title') # 反向 book = Book.objects.filter(publish_id_id='1').values('title') # 查询Linux这本书所有作者的手机号 telephone = Book.objects.filter(title='linux').values('authors__ad__telephone') telephone = Author.objects.filter(book__title='linux').values('ad__telephone') # 查询id=1的出版社的作者的书籍和作者的名字 title_name = Publish.objects.filter(nid='1').values('book__authors__name', 'book__title') title_name = Book.objects.filter(publish_id_id=1).values('title', 'authors__name') # 查询作者手机号的开头为110的书籍和出版社名称 title_name = Author.objects.filter(ad__address__startswith='北京').values('book__title', 'book__publish_id__name')
# 聚合和分组
from django.db.models import Avg, Max, Sum, Min, Count
avg = Book.objects.all().aggregate(price=Avg('price'))
# 单表分组查询
# 查询每个出版社id以及对应大的书籍个数
count = Book.objects.values('publish_id').annotate(count=Count(1))
# values中的值为分类的参数
'''
select Count(1) from Book GROUP by publish_id
'''
# 跨表分组查询
# 查看每一个出版社名称以及对应的书籍个数
count = Book.objects.values('publish_id__name').annotate(count=Count(1))
count = Publish.objects.values('name').annotate(c=Count('book__nid'))
# 查找作者大于一个的书籍对应的作者数
query = Book.objects.annotate(c=Count('authors')).filter(c__gt=1).values('c', 'title')
F与Q
F 比较两列大小
Q 两个关系用 与或非 表示
# F与Q查询 from django.db.models import F, Q # 查询评论数大于点赞数的书籍名称 title = Book.objects.filter(commit_count__gt=F('poll_count') * 1).values('title') # 让所有的书籍价格加100 price = Book.objects.update(price=F('price') + 100) # 取出评论大于20或者点赞数小于10 ret = Book.objects.filter(Q(commit_count__gt=20) | ~Q(poll_count__gt=10)) # 取价格在等于20或者评论数大于20且点赞数小于10的 ret = Book.objects.filter(Q(Q(price=20) | Q(commit_count__gt=20)) & ~Q(poll_count__gt=10)) ret = Book.objects.filter(Q(Q(price=20) | Q(commit_count__gt=20)), ~Q(poll_count__gt=10))
Q的添加操作
q = Q()
#添加的元素关系为或
q.connertor ="or"
#添加查询条件
q.children.append(("name","123"))
q.children.append("age","12")