python 第六十章 orm 查询

批量插入(bulk_create)

    # bulk_create
    obj_list = []
    for i in range(20):
        obj = models.Book(
            title=f'金梅{i}',
            price=20+i,
            publish_date=f'2019-09-{i+1}',
            publish='24期出版社'
        )
        obj_list.append(obj)

    models.Book.objects.bulk_create(obj_list)  #批量创建

request.POST --  querydict类型 {'title': ['asdf '], 'price': ['212'], 'publish_date': ['2019-09-12'], 'publish': ['asdf ']}
data = request.POST.dict() -- 能够将querydict转换为普通的python字典格式

创建数据
	models.Book.objects.create(
            # title=title,
            # price=price,
            # publish_date=publish_date,
            # publish=publish
            **data
        )

查询api

all()  结果为queryset类型

filter 条件查询
	ret = models.Book.objects.filter(title='金梅7',publish='24期出版社') #and多条件查询
	查询条件不能匹配到数据时,不会报错,返回一个空的queryset,<QuerySet []>,如果没有写查询条件会获取所有数据,queryset类型的数据还能够继续调用fitler方法
	
get()
	ret = models.Book.objects.get() #得到的是一个model对象,有且只能有一个
	1. 查不到数据会报错 :Book matching query does not exist.
	2. 超过一个就报错 :returned more than one Book -- it returned 13!

exclude()  #排除
	1.object能够调用,models.Book.objects.exclude(title__startswith='金梅')	
	2.queryset类型数据能够调用, 	models.Book.objects.all().exclude(title__startswith='金梅')

order_by()排序
	models.Book.objects.all().order_by('-price','id')  #orderby price desc,id asc;

reverse() 反转
	models.Book.objects.all().order_by('id').reverse()  #数据排序之后才能反转
	
count() 计数,统计返回结果的数量
	models.Book.objects.all().count() 
first() 返回第一条数据,结果是model对象类型
last()  返回最后一条数据,结果是model对象类型
	# ret = models.Book.objects.all().first()
	ret = models.Book.objects.all().last()
	
exists() 判断返回结果集是不是有数据	
	models.Book.objects.filter(id=9999).exists() #有结果就是True,没有结果就是False

values(返回的queryset,里面是字典类型数据)
values_list(返回的queryset,里面是数组类型数据)
    ret = models.Book.objects.filter(id=9).values('title','price') 
    # ret = models.Book.objects.all().values_list('title','price')
    # ret = models.Book.objects.all().values()
    # ret = models.Book.objects.values()  #调用values或者values_list的是objects控制器,那么返回所有数据 

distinct() 去重,配置values和values_list来使用
	models.Book.objects.all().values('publish').distinct()

filter双下划线查询

    # ret = models.Book.objects.all().values('publish').distinct()
    # ret = models.Book.objects.filter(price__gt=35)  #大于
    # ret = models.Book.objects.filter(price__gte=35) # 大于等于
    # ret = models.Book.objects.filter(price__lt=35) # 小于等于
    # ret = models.Book.objects.filter(price__lte=35) # 小于等于
    # ret = models.Book.objects.filter(price__range=[35,38]) # 大于等35,小于等于38   # where price between 35 and 38
    # ret = models.Book.objects.filter(title__contains='金梅') # 字段数据中包含这个字符串的数据都要
    # ret = models.Book.objects.filter(title__contains='金梅')
    # ret = models.Book.objects.filter(title__icontains="python")  # 不区分大小写
    # from app01.models import Book
    # ret = models.Book.objects.filter(title__icontains="python")  # 不区分大小写
    # ret = models.Book.objects.filter(title__startswith="py")  # 以什么开头，istartswith  不区分大小写
    # ret = models.Book.objects.filter(publish_date='2019-09-15')

某年某月某日:
	ret = models.Book.objects.filter(publish_date__year='2018') 
	ret = models.Book.objects.filter(publish_date__year__gt='2018')#2018写数字也可以
    ret = models.Book.objects.filter(publish_date__year='2019',publish_date__month='8',publish_date__day='1')
    
找字段数据为空的双下滑线
	models.Book.objects.filter(publish_date__isnull=True) #这个字段值为空的那些数据

表结构

from django.db import models

# Create your models here.

class Author(models.Model):
    """
    作者表
    """
    name=models.CharField( max_length=32)
    age=models.IntegerField()
    # authorDetail=models.OneToOneField(to="AuthorDetail",to_field="nid",on_delete=models.CASCADE)  #
    au=models.OneToOneField("AuthorDetail",on_delete=models.CASCADE)

class AuthorDetail(models.Model):
    """
    作者详细信息表
    """
    birthday=models.DateField()
    telephone=models.CharField(max_length=11)
    addr=models.CharField(max_length=64)
    # class Meta:
        # db_table='authordetail' #指定表名
        # ordering = ['-id',]
class Publish(models.Model):
    """
    出版社表
    """
    name=models.CharField( max_length=32)
    city=models.CharField( max_length=32)

class Book(models.Model):
    """
    书籍表
    """
    title = models.CharField( max_length=32)
    publishDate=models.DateField()
    price=models.DecimalField(max_digits=5,decimal_places=2)
    publishs=models.ForeignKey(to="Publish",on_delete=models.CASCADE,)
    authors=models.ManyToManyField('Author',)