django第三天 小记

# -*- coding: utf-8 -*-
import os
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'day16.settings')
import django
django.setup()

from app01 import models


# 1.all() <QuerySet [<Person: 1-lily>, <Person: 2-admin>]>
ret = models.Person.objects.all()

# 2.filter(条件) <QuerySet [<Person: 1-lily>]>
ret = models.Person.objects.filter(username='lily')

# 3.get(条件) 查不到报错，app01.models.DoesNotExist: Person matching query does not exist.
# ret = models.Person.objects.get(username='lucy')

# 3.get(条件) 查到一条，返回一个对象 1-lily
ret = models.Person.objects.get(username='lily')

# 3.get(条件) 查到2条或2条以上会报错 app01.models.MultipleObjectsReturned: get() returned more than one Person -- it returned 2!
# ret = models.Person.objects.get(age=18)

# 4.exclude(条件) <QuerySet [<Person: 2-admin>]>
ret = models.Person.objects.exclude(username='lily')

# 5.values() <QuerySet [{'pid': 1, 'username': 'lily', 'age': 18, 'birth': datetime.datetime(2019, 7, 28, 14, 26, 37, 621631, tzinfo=<UTC>), 'gender': 0}, {'pid': 2, 'username': 'admin', 'age': 18, 'birth': datetime.datetime(2019, 7, 28, 15, 28, 2, 856414, tzinfo=<UTC>), 'gender': 0}]>
ret = models.Person.objects.values()
for i in ret:
    # {'pid': 1, 'username': 'lily', 'age': 18, 'birth': datetime.datetime(2019, 7, 28, 14, 26, 37, 621631, tzinfo=<UTC>), 'gender': 0}
    # {'pid': 2, 'username': 'admin', 'age': 18, 'birth': datetime.datetime(2019, 7, 28, 15, 28, 2, 856414, tzinfo=<UTC>), 'gender': 0}
    # print(i)
    pass

# 5.values() <QuerySet [{'username': 'lily', 'age': 18}, {'username': 'admin', 'age': 18}]>
ret = models.Person.objects.values('username', 'age')

# 6.values_list() <QuerySet [('lily', 18), ('admin', 18)]>
ret = models.Person.objects.values_list('username', 'age')

# 7.count() 2
ret = models.Person.objects.count()

# 8.order_by(字段名) <QuerySet [<Person: 1-lily>, <Person: 2-admin>]>
ret = models.Person.objects.order_by('pid')

# 8.order_by(字段名) <QuerySet [<Person: 2-admin>, <Person: 1-lily>]>
ret = models.Person.objects.order_by('-pid')

# 9.reverse() <QuerySet [<Person: 2-admin>, <Person: 1-lily>]>
ret = models.Person.objects.order_by('pid').reverse()

# 10.first() 对象 1-lily
ret = models.Person.objects.first()

# 11.last() 对象 2-admin
ret = models.Person.objects.last()

# <QuerySet [{'age': 18}, {'age': 18}]>
ret = models.Person.objects.values('age')

# 12.distinct() <QuerySet [{'age': 18}]>
ret = models.Person.objects.values('age').distinct()

# 13.exists() False
ret = models.Person.objects.filter(username='lucy').exists()

# 13.exists() True
ret = models.Person.objects.filter(username='lily').exists()


print(ret)

# -*- coding: utf-8 -*-
import os
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'day16.settings')
import django
django.setup()

from app01 import models


# 1.等于 <QuerySet [<Person: 2-admin>]>
ret = models.Person.objects.filter(pid=2)

# 2.gt 大于greater than
# <QuerySet [<Person: 2-admin>, <Person: 3-lucy>]>
ret = models.Person.objects.filter(pid__gt=1)

# 3.gte 大于等于 greater than or equal to
# <QuerySet [<Person: 1-lily>, <Person: 2-admin>, <Person: 3-lucy>]>
ret = models.Person.objects.filter(pid__gte=1)

# 4.lt 小于 less than
# <QuerySet [<Person: 1-lily>, <Person: 2-admin>]>
ret = models.Person.objects.filter(pid__lt=3)

# 5.lte 小于等于 less than or equal to
# <QuerySet [<Person: 1-lily>, <Person: 2-admin>, <Person: 3-lucy>]>
ret = models.Person.objects.filter(pid__lte=3)

# 6.contains 包含
# <QuerySet [<Person: 2-admin>]>
ret = models.Person.objects.filter(username__contains='ad')

# 7.icontains 忽略大小写的包含
# <QuerySet [<Person: 2-admin>, <Person: 4-Administrator>]>
ret = models.Person.objects.filter(username__icontains='ad')

# 8.in 在...内
# <QuerySet [<Person: 1-lily>, <Person: 3-lucy>]>
ret = models.Person.objects.filter(pid__in=[1, 3])

# 9.range 从...到...
# <QuerySet [<Person: 1-lily>, <Person: 2-admin>, <Person: 3-lucy>]>
ret = models.Person.objects.filter(pid__range=(1, 3))
ret = models.Person.objects.filter(pid__range=[1, 3])

# 10.startswith 以...开头
# <QuerySet [<Person: 2-admin>]>
ret = models.Person.objects.filter(username__startswith='ad')

# 11.istartswith 忽略大小写 以...开头
# <QuerySet [<Person: 2-admin>, <Person: 4-Administrator>]>
ret = models.Person.objects.filter(username__istartswith='ad')

# 12.endswith 以...结尾
# <QuerySet [<Person: 1-lily>, <Person: 3-lucy>]>
ret = models.Person.objects.filter(username__endswith='y')

# 13.iendswith 忽略大小写 以...结尾
# <QuerySet [<Person: 1-lily>, <Person: 3-lucy>]>
ret = models.Person.objects.filter(username__iendswith='y')

# 14.isnull 是空的
# <QuerySet []>
ret = models.Person.objects.filter(age__isnull=True)

# 15.isnull 不是空的
# <QuerySet [<Person: 1-lily>, <Person: 2-admin>, <Person: 3-lucy>, <Person: 4-Administrator>]>
ret = models.Person.objects.filter(age__isnull=False)

# 16. birth__year 出生年份等于
# <QuerySet [<Person: 1-lily>, <Person: 2-admin>, <Person: 3-lucy>, <Person: 4-Administrator>]>
ret = models.Person.objects.filter(birth__year=2019)

# 17.birth_contains 出生日期包括
# <QuerySet [<Person: 1-lily>, <Person: 2-admin>, <Person: 3-lucy>, <Person: 4-Administrator>]>
ret = models.Person.objects.filter(birth__contains='2019-07')
print(ret)

# -*- coding: utf-8 -*-
import os
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'day16.settings')
import django
django.setup()

from app01 import models

# 插入出版社数据
# models.Publisher.objects.create(name='沙河出版社1')
# models.Publisher.objects.create(name='人民出版社')
# models.Publisher.objects.create(name='中信出版社11')
# models.Publisher.objects.create(name='工业出版社')
# models.Publisher.objects.create(name='工业出版社')
# models.Publisher.objects.create(name='沙河出版社')

# 插入书籍数据
# models.Book.objects.create(name='沙河小王子112', pub_id=1)
# models.Book.objects.create(name='沙河鸡汤哥', pub_id=1)
# models.Book.objects.create(name='人民币制作指南', pub_id=2)
# models.Book.objects.create(name='python4', pub_id=3)
# models.Book.objects.create(name='python3', pub_id=3)


# 正向查询
ret = models.Book.objects.first()
# 沙河出版社1
print(ret.pub)

# 反向查询1
pub_obj = models.Publisher.objects.first()
# <class 'django.db.models.fields.related_descriptors.create_reverse_many_to_one_manager.<locals>.RelatedManager'>
# print(type(pub_obj.book_set))
# # 不指定related_name='books'
# <QuerySet [<Book: 沙河小王子1111>, <Book: 沙河鸡汤哥>]>
# ret = pub_obj.book_set.all()

# 指定related_name='books'
# <QuerySet [<Book: 沙河小王子1111>, <Book: 沙河鸡汤哥>]>
# ret = pub_obj.books.all()


# 反向查询2
# <QuerySet [<Book: 沙河小王子1111>, <Book: 沙河鸡汤哥>]>
# ret = models.Book.objects.filter(pub__name='沙河出版社1')

# 不指定related_name='books'
# <QuerySet [<Publisher: 沙河出版社1>]>
# ret = models.Publisher.objects.filter(book__name='沙河小王子1111')

# 指定related_name='books'
# <QuerySet [<Publisher: 沙河出版社1>]>
# ret = models.Publisher.objects.filter(books__name='沙河小王子1111')

# 指定related_query_name='bookxxx'
# <QuerySet [<Publisher: 沙河出版社1>]>
ret = models.Publisher.objects.filter(bookxxx__name='沙河小王子1111')

print(ret)

# set创建一对多的关系，外键不能使用[id] 只能写Queryset
pub_obj.books.set(models.Book.objects.all())

# create创建数据并添加一对多的关系
pub_obj.books.create(name='西游记')

# clear 必须是外键可以为空的时候，才能使用
pub_obj.books.clear()

import os
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "day16.settings")
import django
django.setup()

from app01 import models

author_obj = models.Author.objects.first()
print(author_obj)
print(author_obj.name)
print(author_obj.books.all())

book_obj = models.Book.objects.first()
print(book_obj.author_set.all())


ret = models.Author.objects.filter(books__name='沙河鸡汤哥')

# all()  所关联的所有对象
author_obj.books.all()
# set()  设置多对多的关系  [id]   [对象]
# author_obj.books.set([1])
# author_obj.books.set(models.Book.objects.filter(id__in=[1,3,5]))


# add()  添加多对多的关系  id   对象
# author_obj.books.add(2)
# author_obj.books.add(*models.Book.objects.filter(id=4))


# remove()  删除多对多的关系  id   对象
# author_obj.books.add(2)
# author_obj.books.remove(*models.Book.objects.filter(id__in=[1,2]))

# clear()  删除所有多对多的关系  id   对象
# author_obj.books.clear()

# create  创建一个对象 并添加多对多的关系
author_obj.books.create(name='西游记',pub_id=1)

import os
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "day16.settings")
import django
django.setup()

from app01 import models

from django.db.models import Max,Min,Sum,Avg,Count

ret = models.Book.objects.filter(id__range=[1,4]).aggregate(sum=Sum('price'))

# 统计每一本书的作者个数
ret = models.Book.objects.annotate(count=Count('author')).values()

# 统计出每个出版社买的最便宜的书的价格
# 方式一
ret = models.Publisher.objects.annotate(Min('book__price')).values()

# 方式二 annotate 前的values中的字段就是分组的条件   annotate后不再用values
ret = models.Book.objects.values('pub').annotate(min= Min('price'))

# 统计不止一个作者的图书
ret = models.Book.objects.annotate(count=Count('author')).filter(count__gt=1)

# 查询各个作者出的书的总价格
ret = models.Author.objects.annotate(Sum('books__price')).values()
for i in ret:
    print(i)

import os

os.environ.setdefault("DJANGO_SETTINGS_MODULE", "day16.settings")
import django

django.setup()

from app01 import models

from django.db.models import F,Q

# ret = models.Book.objects.filter(sale__gt=F('store')).values()

# models.Book.objects.all().update(pub_id=1)


# obj = models.Book.objects.update(sale=F('sale')*2+13)


#  | 或  & 与  ~
ret = models.Book.objects.filter(Q(~Q(id__gt=4)|Q(id__lt=2))&Q(pub_id=1))


print(ret)

import os

os.environ.setdefault("DJANGO_SETTINGS_MODULE", "day16.settings")
import django

django.setup()

from app01 import models

from django.db import transaction
import time

try:
    with transaction.atomic():
        # 一系列操作
        models.Publisher.objects.create(name='xxxxxx')
        time.sleep(5)
        int('aaa')

except Exception as e:
    print(e)