day61

def query(request):
# publish_obj = models.Publish.objects.filter(name='人民邮电')[0]
# publish_obj = models.Publish.objects.filter(id=2)[0]

    # models.Book.objects.create(
    #     title='python3',
    #     price= 100,
    #     # publish= publish_obj,   #方式1
    #     publish_id = 2,
    # )
    # models.Book.objects.filter(id=4).delete()       # 删除和更新记录和单表是一样的
    # models.Book.objects.filter(id=2).update(price = 200)



# 一对一

# models.Author.objects.create(
#     name = 'yang',
#     age = 18,
#     # ad_id= models.AuthorDetail.objects.get(id = 1),   #.id  在类里添
#     ad_id_id= 2 ,   #  在数据库里添
# )

# 更新和删除
models.Author.objects.filter(id=2).update(name= 'wang')
# models.Author.objects.filter(id=2).delete()

# 多对多的增删改
#增加： 添加一本书，关联两个作者
    # 1.先把作者录入了 ， 然后选择
    # 2. 或者输入作者，去查询

# book_obj = models.Book.objects.create(
#     title='python3',
#     price= 100,
#     publish_id = 2,
# )

# author1 = models.Author.objects.get(id=1)
# author3 = models.Author.objects.get(id=2)
book_obj = models.Book.objects.get(id=2)

# book_obj.authors.add(1,2)

# book_obj.authors.add(author1,author3)  #book_obj.authors 找到了第三张表，添加
                        #除了放对象还可以放id

# book_obj.authors.add(*[1,2])    #用的比较多

#删除
# book_obj.authors.remove(1)  # authors 连接第三方，里面的是author对象或者id
                    # book_obj 已经是bookid为2   了   class Book表指向了author表(to 'author')
# book_obj.authors.remove(1,3)  # authors 连接第三方，里面的是author对象或者id
# book_obj.authors.remove(*[1,3])  # authors 连接第三方，里面的是author对象或者id

# book_obj.authors.clear()    #清空为3 的所有关系 全部清除
# book_obj.authors.set(['1',])    #清空所有的book_id 为3的第三张表里的所有记录
# authors连接第三张表，再重新写上book_id为3的对应的author_id 为1的   不是前几个作者写的，直接删前的，加后面的
# book_obj.authors.set(['1','3'])    #清空所有的book_id 为3的第三张表里的所有记录



# 一对一   一对多   多对多
# 一对多
# 查询主键为2 的书籍的出版社所在的城市
# book_obj = models.Book.objects.get(id=1)
# ret = book_obj.publish.city                   #正向查询用属性   ，反向查询用
# print(book_obj.publish)              #Publish object
# print(book_obj.publish.__dict__)   #{'_state': <django.db.models.base.ModelState object at 0x0000000004647C88>, 'id': 2, 'name': '人民邮电', 'city': '北京'}
# print(ret)                      #北京
'''
# 反向查询的：查看译林出版过哪些书籍
publish_obj = models.Publish.objects.get(name='译林')    # 基于model，所以get
publish_obj = models.Publish.objects.filter(name='译林').first() # 与get一样

print('first',publish_obj)    #  first Publish object
print('book_set',publish_obj.book_set)    #book_set app01.Book.None

book_list = publish_obj.book_set.all()      #类名小写_set
print(book_list)        #<QuerySet [<Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>]>
                        #写了str之后的  格式   <QuerySet [<Book: unix>, <Book: java>, <Book: c#>]>
for i in book_list:
    print('书',i.title)

# print(models.Book.objects.filter(id=2))
print('1',publish_obj.book_set.all().filter(id =2))       # set多条  多对多
print('2',publish_obj.book_set.filter(id =2))
        #1 <QuerySet []>        2 <QuerySet []>

# 一对一
# yang是哪个城市的
# author_obj = models.Author.objects.get(name='yang')
# addr_info = author_obj.ad_id.addr
# print(addr_info)    #沙河

# 反向查询 ： 看一下谁在  于辛庄
# addr_obj = models.AuthorDetail.objects.get(addr='于辛庄')
# author_info = addr_obj.author.name      # 一对一的肯定是一条

# print(author_info)      #wang

#多对多
# 正向查询的：python的作者是谁
book_obj1 = models.Book.objects.filter(title='python3').first()
print('多对多.first()',book_obj1)          # 多对多.first() python3
authors_list = book_obj1.authors.all()      #  跨到了第三张表里
print(authors_list)         #<QuerySet [<Author: yang>, <Author: wang>, <Author: zhang>]>

# # 反向查询 ： wang写了哪些书
author_obj = models.Author.objects.get(name='wang')
print(author_obj.book_set.all())        #<QuerySet [<Book: python>, <Book: python3>, <Book: C++>]>

'''
'''
## 链表操作 用属性
print('链表操作',models.Book.objects.filter(id=2)) # <QuerySet [<Book: python>]>
ret = models.Book.objects.filter(id=2).values('publishs__city') #用属性
print(ret) #<QuerySet [{'publish__city': '沙河'}]>
rett = models.Book.objects.filter(id=2).values('publishs__city','title')
print(rett) #<QuerySet [{'publishs__city': '沙河', 'title': 'python'}]>

ret1 = models.Publish.objects.filter(book__id = 2 ).values('city')
print(ret1)          #<QuerySet [{'city': '沙河'}]>
ret1 = models.Publish.objects.filter(book__id = 2 ).values('city','book__price')
print(ret1)         #<QuerySet [{'city': '沙河', 'book__price': Decimal('12.00')}]>

print(models.Author.objects.filter(name='yang').values('ad_id__addr'))   #<QuerySet [{'ad_id__addr': '沙河'}]>
print(models.AuthorDetail.objects.filter(author__name='yang').values('addr'))    #<QuerySet [{'addr': '沙河'}]>
# print(models.AuthorDetail.objects.filter(Author__name='yang').values('addr'))   #error
print(models.AuthorDetail.objects.filter(author__name='yang').all())     #.all()  和 不写一样    <QuerySet [<AuthorDetail: AuthorDetail object>]>
print(models.AuthorDetail.objects.filter(author__name='yang').values())  #都是authordetail的 <QuerySet [{'id': 1, 'birthday': datetime.date(2019, 5, 24), 'telephone': 110, 'addr': '沙河'}]>   #<QuerySet [{'addr': '沙河'}]>

print(models.Author.objects.filter(name='yang').values().filter(name='yang'))  #<QuerySet [{'id': 1, 'name': 'yang', 'age': 18, 'ad_id_id': 1}]>

#多对多
# python的作者是谁       表达，不是字段，用户听不懂
            # 先连到第三张表，又连到第一张表author
print(models.Book.objects.filter(title='python').values('authors__name'))   #<QuerySet [{'authors__name': 'yang'}, {'authors__name': 'wang'}, {'authors__name': 'zhang'}]>
print(models.Author.objects.filter(book__title='python').values('name'))    #<QuerySet [{'name': 'yang'}, {'name': 'wang'}, {'name': 'zhang'}]>
# 可以操作第三张表，不是自动生成的没法用add什么的方法

# 作者写了 多少书
print(models.Author.objects.filter(name='yang').values('book__title'))      #<QuerySet [{'book__title': 'python'}, {'book__title': 'python3'}, {'book__title': 'unix'}]>
# 查询人民邮电出版的所有书籍的名字以及作者的名字  跨三个表

# print(models.Publish.objects.filter(name='人民邮电').values('book__title','book__authors__name'))
#<QuerySet [{'book__title': 'unix', 'book__authors__name': 'yang'}, {'book__title': 'go', 'book__authors__name': None}, {'book__title': 'c', 'book__authors__name': None}]>
    # filter  可以写startwith   或者container


# 手机号大于115 的作者出版过的所有书籍名称以及出版社名称
# print(models.AuthorDetail.objects.filter(telephone__gt=115).values('author__book__title','author__book__publishs__name'))

# print(models.AuthorDetail.objects.filter(telephone__gt=115).values('author__book__title','author__book__publishs__name'))
#<QuerySet [{'author__book__title': 'python', 'author__book__publishs__name': '20期出版社'}, {'author__book__title': 'python3', 'author__book__publishs__name': '20期出版社'}, {'author__book__title': 'C++', 'author__book__publishs__name': '20期出版社'}, {'author__book__title': 'C++', 'author__book__publishs__name': '20期出版社'}]>

# 可以把数字转换成字符串吗？
# print(models.AuthorDetail.objects.filter(telephone=115).values('author__book__title','author__book__publishs__name'))


# related_name  代替表名下划线
# 反向查询

'''
# a = models.Author.objects.filter(name='yang')[0]
# print(a.book_set.all())
# 双线划线时 代替的是类名 booklist
# publish_obj =
# publish_obj.book_set.all().filter(id=2)

#######################聚合查询#################

aggregate调用

print(models.Book.objects.all().aggregate(Avg('price')))
print(models.Book.objects.all().aggregate(Max('price')))
    #{'price__avg': 27.833333}
    #{'price__max': Decimal('100.30')}

###########     F和Q查询
# 把所有的书籍价格加1 ：自己做，把所有的拿出来加1
print(models.Book.objects.filter(price=F('price')+1))       # filter里面些什么都行
ret = models.Book.objects.filter(price=F('price')).update(price=F('price')+1)  # 可以拼接字符串

print(models.Book.objects.filter(comment__gt= F('zan')))        #<QuerySet [<Book: python>, <Book: go>, <Book: c#>]>

# models.Book.objects.filter()
# models.Book.

#Q 查询  &与 |或  ~非
print(models.Book.objects.filter(price__gt=50,comment__gt=2))       #<QuerySet [<Book: linux>, <Book: c#>]>

print(models.Book.objects.filter(Q(price__gt=50)|Q(comment__gt=2)))     #<QuerySet [<Book: C++>, <Book: python>, <Book: python3>, <Book: go>, <Book: c>, <Book: linux>, <Book: c#>]>
print(models.Book.objects.filter(Q(price__gt=50)&Q(comment__gt=2)))     #<QuerySet [<Book: linux>, <Book: c#>]>
print(models.Book.objects.filter(Q(price__gt=50)&~Q(comment__gt=2)))