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) #

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)))