222 ORM多表增删改和查询

0.1 mysql的知识

多表查询

外键 foreignkey

一对一 foreignkey+unique

被关联的表的数据（先写，才可被关联）不可改，不可删，可以增

fk_publish 写关联的表无所谓常用的放这张表

publish		fk+unique(1对1)
id	name	publish_detail_id
1	20期	1、2不能重复

不常用的

publish_detail
	id	tel
	1

多对一

book		fk_publish
id	title	publish_id
1	python	1

多对多

互相建外键，不行，谁先建？✖

建第三张表 ✔

多对多有外键，不可删23 也可以增加4 可以删除author里的1

book2authot	fk_book	fk_author
id	book_id	author_id
1	1	2
2	1	3
3

author
id	name	技术描述
1	cls	还行

很多公司不用foreign_key 了，牵一发而动全身，强制约束，导致操作变复杂

现在通过逻辑来+，建没有关系的表，inner join查。

写一段orm语句，写一段原生sql

select author.技术描述 from (select * from (从硬盘到内存) book inner join author on book.id == author.id) as t1 inner join book2author on t1.id == book2author.id;

book表，既有多对一，又有多对多，

我关联别人的，有范围
别人关联我，我随便增加	改和删不行
他连别人，所以可以随便删除，但是不可以随便添加
别人连他，所以可以随便添加，但是不可以随便删除

外键关联施，受需要有相关项

外键关联受，必须有，不可改，不可删，可以增

1 创建模型

1.1建表

ad = models.OneToOneField(to='AuthorDetail' ) 
自动生成ad_id 名字

class Author():
	name = models.CharField(max_length=32)
	age = ..IntegerField()
	authorDetail = ..OneToOneField(to = 'AuthorDetail')
class AuthorDetail(): 不常使用的字段，另建一个表，否则数据量大
	telephone = ..BigInt
	addr = ...Char
class Publish():
	name = Char ml32
	city = Char
class Book():
	title = Char ml32
	price = Decimal md = 5 , dp = 2
	auth = Manytomany(to= 'Author')
	publish = Foreign.key(to = 'Publish')

1.2 多对多的三种创建

方式1 手动创建建三个表

联合唯一: 同时不重复

  	author = models.ForeignKey(to="Author")
    book = models.ForeignKey(to="Book")

    class Meta:
        unique_together = ("author", "book")

方式2 manytomany

books = models.ManyToManyField(to="Book", related_name="authors")

方式3 manytomany 手动指定

author = models.ForeignKey(to="Author")
    book = models.ForeignKey(to="Book")
    class Meta:
        unique_together = ("author", "book")

through 指定这个表作为第三张表指定字段

decimal为什么精度最高？存的是字符串，所以不会改变

再存浮点型的时候，小数点，存储机制，所以会变化。。。

show create table app01_author; 查询key

UNIQUE KEY ad_id_id (ad_id_id), 指定了后者，名字前者

alter table app01_author drop index ad_id_id; 删除uninkey

1.3 插入

publish = models.ForeignField(to='Publish' )
自动生成publish_id 名字

models.Book.objects.filter(id = 8)<QuerySet [<Book: 流浪地球>]>  
models.Book.objects.filter(id=8)[0]  流浪地球

方式1 
publish_obj=Publish.objects.get(id=1) #拿到id为1的出版对象
book_obj=Book.objects.create(title="金瓶眉",publishDate="2012-12-12",price=100,publish=publish_obj) #出版社对象作为值给publish，其实就是自动将publish字段变成publish_id,然后将publish_obj的id给取出来赋值给publish_id字段，注意你如果不是publish类的对象肯定会报错的 
方式2 
 book_obj=Book.objects.create(title="金瓶眉",publishDate="2012-12-12",price=100,publish_id=1)	 属性

print(models.Book.objects.filter(id=1)[0]) #三体
print(models.Book.objects.filter(publish_id=2)[1]) #小小小小的火

publish_obj = models.Publish.objects.filter(id=1)[0]      		#Publish object
publish_obj = models.Publish.objects.filter(id=1)
	 #<QuerySet [<Publish: Publish object>]>
models.Book.objects.create(title= '人生',price=13,publish= publish_obj )
models.Book.objects.create(title= '流浪地球',price=111,publish_id= 1 )
# publish_id = 1 或者 publish = publish对象 filter()[0] 或者 get()

1.4 增（多对多）

1.3 1 第三张表

book_obj = models.Book.objects.get(id=2)
book_obj.authors   (第三张表)
book_obj.authors.add(1,2)

add里面是作者对象或者是作者的id

book_obj1.auth. auth连接第三方就是第三张表了

 #增加： 添加一本书，关联两个作者
        # 1.先把作者录入了 ， 然后选择
        # 2. 或者输入作者，去查询
book_obj = models.Book.objects.create(title='人间词话',price=23,publish_id=1)

wuqishi = models.Author.objects.get(name='伍绮诗')
liucixin = models.Author.objects.get(name='刘慈欣')
book_obj.auth.add(wuqishi,liucixin)   #给第三张表add
				#增的是author对象
book_obj1.auth.add(*[1,2])		#增的是author id

1.5 删

一对一更新和删除

models.Author.objects.filter(id=2) <QuerySet [<Author: xxx>]>

models.Author.objects.filter(id=2).update(name= 'wang')
# models.Author.objects.filter(id=2).delete()

多对多删除

    # book_obj.auth.remove(1)     #author的id author对象
    book_obj.authors.remove(1,3)
    book_obj.authors.remove(*[1,3]) 
    # book_obj.auth.clear()             #全删对象
    # book_obj.auth.set([1,2])        # 删除并且赋值
    # data = {'title':'围城','price':12}
    # models.Book.objects.filter(id=6).update(**data)

2 下午

2. 查

2.0 原理

基于对象的：子查询

正向查询：

关系属性在哪张表，去关联的表去查询

往onetoone的里面去找，就是正向查询 ,否则就是反向查询

基本写法

对象= models.类.objects.filter()[0] /get()

正向查询：

用属性（.all()多 .属性少）对象.关联的属性.属性或者all()

反向查询：

	用表名(_set : 多)  （.属性 ：少）	对象.前者的表名_ _ set.all() 或属性

如果没写 __ str __ 的情况下，写__ __ dict __显示文字

多对多的增

add里面是作者对象或者是作者的id

 book_1 = models.Book.objects.filter(title='人间词话')[0]
 book_1.auth.add(1,2)

2.11 一对一

正		：找作者伍绮诗的addr
author_obj = models.Author.objects.filter(name='伍绮诗')[0]
print(author_obj.authorDetail.addr)     #美国
反		：找书本人间词话的出版社
book_obj3 = models.Book.objects.filter(title='人间词话')[0]
print(book_obj3.publish.city)       #北京

2.12 一对多

正 			:找书本人间词话的出版社
book_obj3 = models.Book.objects.filter(title='人间词话')[0]
print(book_obj3.publish.city)    #北京 前三个都是属性 后三个all
反			：找出版社 新华书店 出版的图书   多  
publish1 = models.Publish.objects.filter(name='人民邮电')[0]
print(publish1.book_set.all())  ##<QuerySet [<Book: 无声告白

2.13 多对多

正			： 查询book 正能量被几个作者 写出来	多
book_obj4 = models.Book.objects.filter(title='正能量')[0]
print(book_obj4,book_obj4.auth.all())       #正能量 <QuerySet
反			： 查询 作者伍绮诗 写的所有书籍		多
author_obj2 = models.Author.objects.filter(name='伍绮诗')[0]
print(author_obj2.book_set.all())

orm 类与对象与mysql的映射封装，但是有些复杂的写不出来，mysql可以写出来 ---超哥

orm没有级联更新

解决办法：

基于上下划线的

两种方式:

models.Book.objects.filter(id=2)) 
	# <QuerySet [<Book: python>]>
	
models.Book.objects.filter(id=2).values('publishs__city')      #用属性	 <QuerySet [{'publish__city': '沙河'}]>

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

效率问题

翻译成一句，所以效率快
不连表的，翻译成两句，效率慢

ER图 Entity Relationship Diagram

only full group by 设置了没法读取无效率的操作，前后对应

五道题有可能就是这样的错误，做不出来硬题做不出来，简单的题做出来

sql语句没问题

原生sql

select name,max(price) from app01_book INNER JOIN app01_publish on app01_book.publishs_id=app01_publish.id GROUP BY name ;

orm

print(models.Book.objects.values('publishs_id').annotate(a=Max('price')).values('publishs__name','a'))# value里显示的
按照publish-id 分组，按

anno

前面values 写分组依据，后面annotate写统计结果

select avg(*) from name group by publish_id

写 orm的话

models.publish.object.values('publish_id').annotate('m=Avg('')')

models.publish.object.values('publish_id').annotate('m=Avg('')').values（）

替换前面

链表操作

因为连表所以正反都可以  
print(models.Publish.objects.values('name').annotate(m=Max('bp1__price')))
    #<QuerySet [{'name': '20期出版社', 'm': Decimal('58.00')}, {'name': '人民邮电', 'm': Decimal('61.00')}, {'name': '译林', 'm': Decimal('146.30')}]>
    print(models.Book.objects.values('publishs__name').annotate(m= Max('price')))
    #<QuerySet [{'publishs__name': '20期出版社', 'm': Decimal('63.00')}, {'publishs__name': '人民邮电', 'm': Decimal('66.00')}, {'publishs__name': '译林', 'm': Decimal('151.30')}]>

今日内容

基于双下划线的跨表查询（连表,正向反向链表的时候）

聚合查询

aggregate(Avg('xxx')) ---{}

分组查询

annotate(a=Max('xxx'))

F与Q查询

F针对的是单表里面某些字段的批量操作

Q Q(xxx) | Q(ssss) &Q (xss) ,

filter(Q(xxx)|Q(xxxx),title='xxxx') 组合和非Qtitle必须放后面

filter(Q(Q(xxx)|Q(xxxx))|Q(xxxx),title='xxxx') # 嵌套

忘了聚合了，同桌说的。不会。

print(models.Author.objects.values('name').annotate(c=Max('book__price')).aggregate(Avg('c')))  #{'c__avg': 206.333333}

from django.shortcuts import render,HttpResponse
from Af_Pr import models
from django.db.models import Avg,Max,Min,Count,Sum
from django.db.models import F,Q


def query(request):
    # book_obj = models.Book.objects.filter(id=1)[0]
    # book_obj.authors.add(1,2)
    book_obj1 = models.Book.objects.filter(id=3)[0]
    book_obj1.authors.add(2)

#链表操作 用属性
    # print(models.Book.objects.filter(id=2),models.Book.objects.filter(id=2).values())   #<QuerySet [<Book: 小王子>]> <QuerySet [{'id': 2, 'title': '小王子', 'price': Decimal('44.00'), 'zan': 32, 'comment': 22, 'publishs_id': 2}]>
    # print(models.Book.objects.filter(id=2).values('title'))     #<QuerySet [{'title': '小王子'}]>
    # print(models.Book.objects.filter(id=2).values_list('title'))    #<QuerySet [('小王子',)]>
    # author_obj = models.Author.objects.filter(name='李继宏')[0]
    # print(models.Author.objects.filter(name='李继宏'),models.Author.objects.filter(name='李继宏')[0])   #<QuerySet [<Author: 李继宏   李继宏
    # print(models.Book.objects.filter(id=2)[0].authors.remove(author_obj))   # 可以根据这个作者名字删除，不是作者对象
    # print(models.Book.objects.filter(id=2).values('title').filter(id=2))    #<QuerySet [{'title': '小王子'}]>
    # print(models.Book.objects.values())     #<QuerySet [{'id': 1, 'title': '飞鸟集', 'price': Decimal('23.00'), 'zan': 33, 'comment': 45, 'publishs_id': 1}, {'id': 2, 'title': '小王子', 'price': Decimal('44.00'), 'zan': 32, 'comment': 22, 'publishs_id': 2}, {'id': 3, 'title': '边城', 'price': Decimal('12.00'), 'zan': 3, 'comment': 222, 'publishs_id': 3}]>
    # print(models.Book.objects.values('title'))      #<QuerySet [{'title': '飞鸟集'}, {'title': '小王子'}, {'title': '边城'}]>

    # print(models.Book.objects.values('title').annotate(Avg('')))

    # obj = models.Book.objects.filter(authors__name='李继宏').values('title', 'authors__book__authors')
    # print(obj)    # 查看的是作者写的那本书的所有作者    #<QuerySet [{'title': '飞鸟集', 'authors__book__authors': 1}, {'title': '飞鸟集', 'authors__book__authors': 2}, {'title': '飞鸟集', 'authors__book__authors': 2}, {'title': '边城', 'authors__book__authors': 1}, {'title': '边城', 'authors__book__authors': 2}, {'title': '边城', 'authors__book__authors': 2}]>
    # print(models.Book.objects.filter(authors__name='李继宏').values('title','authors'))    #<QuerySet [{'title': '飞鸟集', 'authors': 2}, {'title': '边城', 'authors': 2}]>
    # print(models.Book.objects.filter(authors__name='李继宏'))  # <QuerySet [<Book: 飞鸟集>, <Book: 边城>]>

# 下午
    # 一对多查询 正向查询（用属性）    查询 出版社 译林出版过的所有书籍的名字与价格
    print(models.Book.objects.filter(publishs__name='译林').values_list('title','price'))    # <QuerySet [('飞鸟集', Decimal('23.00'))]>
    #反向查询
    print(models.Publish.objects.filter(name='译林').values_list('bp1__title','bp1__price'))
    #查询 书籍的出版社的名字
    print(models.Book.objects.filter(title='小王子').values_list('publishs__name'))    #<QuerySet [('人民邮电',)]>
    #多对多查询
    #正向  查询作者 李继宏 写作的所有书籍的名字
    print(models.Author.objects.filter(name='泰戈尔').values_list('book__title'))  #<QuerySet [('飞鸟集',), ('小王子',)]>
    # 反向  查询书籍 飞鸟集 的所有作者的电话
    print(models.Book.objects.filter(title='飞鸟集').values_list('authors__ad_id__telephone')) #<QuerySet [('123',), ('333',)]>

    # 连续跨表
    # 正向查询 出版社 译林的所有书籍的名字以及作者的名字
    # values_list 格式都是 ：  #<QuerySet [('飞鸟集', '泰戈尔'), ('飞鸟集', '李继宏')]>
    print(models.Publish.objects.filter(name='译林').values('bp1__title','bp1__authors__name'))   #<QuerySet [{'bp1__title': '飞鸟集', 'bp1__authors__name': '泰戈尔'}, {'bp1__title': '飞鸟集', 'bp1__authors__name': '李继宏'}]>
    # 反向查询
    print(models.Book.objects.filter(publishs__name='译林').values('title','authors__name')) #    <QuerySet [{'title': '飞鸟集', 'authors__name': '泰戈尔'}, {'title': '飞鸟集', 'authors__name': '李继宏'}]>

    # 练习 ：手机号以151 开头的作者 出版过的所有书籍名称 以及出版社名称
    print(models.AuthorDetail.objects.filter(telephone__startswith=123).values('author__book__title','author__book__publishs__name'))   #<QuerySet [{'author__book__title': '飞鸟集', 'author__book__publishs__name': '译林'}, {'author__book__title': '边城', 'author__book__publishs__name': '新华书店'}]>
    print(models.Author.objects.filter(ad_id__telephone__startswith=123).values('book__title','book__publishs__name'))

    # related_name      publishs = ForeignKey(to='Publish' related_name='bp1')


    # 聚合查询  #计算所有图书的平均价格
    # aggregate调用
    # models.Book.objects.aggregate(Avg('price')  也可以
    print(models.Book.objects.all().aggregate(Avg('price')))    #{'price__avg': 26.333333}
    print(models.Book.objects.aggregate(average_price=Avg('price')))    #{'average_price': 26.333333}
    print(models.Book.objects.aggregate(Avg('price'),Max('price'),Min('price')))    #{'price__avg': 26.333333, 'price__max': Decimal('44.00'), 'price__min': Decimal('12.00')}


    #  单表分组查询           value 分组条件    annotate  结果
    print(models.Book.objects.values('title').annotate(c= Count('price')))  #<QuerySet [{'title': '飞鸟集', 'c': 1}, {'title': '小王子', 'c': 1}, {'title': '边城', 'c': 1}]>
    # print(models.Book.objects.values('title').annotate(c= 'authors__name'))
    # 查询 每一个部门名称以及对应的员工数
    print(models.Author.objects.values('name').annotate(c = Count('book__title')))  #<QuerySet [{'name': '泰戈尔', 'c': 2}, {'name': '李继宏', 'c': 2}, {'name': '安东尼', 'c': 0}]>
    print(models.Author.objects.values('name').annotate(c=Count( 'book__title')).values('name'))         #<QuerySet [{'name': '泰戈尔'}, {'name': '李继宏'}, {'name': '安东尼'}]>
    print(models.Author.objects.values('name','age').annotate(c=Count('book__title')))  #<QuerySet [{'name': '泰戈尔', 'age': 78, 'c': 2}, {'name': '李继宏', 'age': 39, 'c': 2}, {'name': '安东尼', 'age': 56, 'c': 0}]>


    # 查询练习  1 统计每一个出版社最便宜的书
    print(models.Publish.objects.values('name','bp1__title').annotate(c=Min('bp1__price')))
    #<QuerySet [{'name': '译林', 'bp1__title': '飞鸟集', 'c': Decimal('23.00')}, {'name': '人民邮电', 'bp1__title': '小王子', 'c': Decimal('44.00')}, {'name': '新华书店', 'bp1__title': '边城', 'c': Decimal('12.00')}]>

    #2 练习：统计每一本书的作者个数
    print(models.Book.objects.values('title').annotate(c = Count('authors__id')))   #<QuerySet [{'title': '飞鸟集', 'c': 2}, {'title': '小王子', 'c': 1}, {'title': '边城', 'c': 1}]>
    #(3) 统计每一本以py开头的书籍的作者个数：
    print(models.Book.objects.filter(title__startswith='小').values('title').annotate(c = Count('authors__id'))) #<QuerySet [{'title': '小王子', 'c': 1}]>

    #　　(4) 统计不止一个作者的图书：
    print(models.Book.objects.values('title').annotate(c=Count('authors__id')).filter(c__gt=0).values('title','c'))
        #<QuerySet [{'title': '飞鸟集', 'c': 2}, {'title': '小王子', 'c': 1}, {'title': '边城', 'c': 1}]>
    #(5) 根据一本图书作者数量的多少对查询集 QuerySet进行排序:
    print(models.Book.objects.values('title').annotate(c= Count('authors__id')).order_by('-c'))    #<QuerySet [{'title': '边城', 'c': 3}, {'title': '飞鸟集', 'c': 2}, {'title': '小王子', 'c': 1}]>

    # 　(6) 查询各个作者出的书的总价格:
    print(models.Author.objects.values('name').annotate(Sum('book__price')))
            #<QuerySet [{'name': '泰戈尔', 'book__price__sum': Decimal('79.00')}, {'name': '李继宏', 'book__price__sum': Decimal('35.00')}, {'name': '安东尼', 'book__price__sum': Decimal('12.00')}]>

 #F和Q查询
#   F查询
    ## 查询评论数大于收藏数的书籍
    print(models.Book.objects.filter(comment__lt= F('zan')))    #<QuerySet [<Book: 小王子>]>
    #查询评论数大于收藏数2倍的书籍
    print(models.Book.objects.filter(comment__gt=F('zan')*2))   #<QuerySet [<Book: 边城>]>
    # 把所有的书籍价格 增加30
    # models.Book.objects.all().update(price=F('price')+30)

#Q 查询  &与 |或  ~非

    print(models.Book.objects.filter(Q(authors__name__startswith="李") | Q(authors__name__startswith="泰")).values('title'))
    a = models.Book.objects.filter(Q(authors__name__startswith="李") | Q(authors__name__startswith="泰")).values('title')
    # print(a.annotate(c = Max('price')))

    print(models.Book.objects.filter(Q(authors__name='泰戈尔') | Q(title='飞鸟集'), price__gt = 220 ))

    # 综合练习题
    #1 查询每个作者的姓名以及出版的书的最高价格
    print(models.Author.objects.values('name').annotate(c = Max('book__price')))    #<QuerySet [{'name': '泰戈尔', 'c': Decimal('224.00')}, {'name': '李继宏', 'c': Decimal('203.00')}, {'name': '安东尼', 'c': Decimal('192.00')}]>


    # 2 查询作者id大于2作者的姓名以及出版的书的最高价格
    print(models.Author.objects.filter(id__gt=2).values('name').annotate(c = Max('book__price')))   #<QuerySet [{'name': '安东尼', 'c': Decimal('192.00')}]>

    # 3 查询作者id大于2或者作者年龄大于等于20岁的女作者的姓名以及出版的书的最高价格
    print(models.Author.objects.filter(Q(id__gt=2) | Q(age__gte=30),name__startswith='李').values('name').annotate(c = Max('book__price')))
                                                                                              #<QuerySet [{'name': '李继宏', 'c': Decimal('203.00')}]>
    #4 查询每个作者出版的书的最高价格 的平均值
    print(models.Author.objects.values('name'))     #<QuerySet [{'name': '泰戈尔'}, {'name': '李继宏'}, {'name': '安东尼'}]>
    a = models.Author.objects.values('name').annotate(c = Max('book__price'))   #<QuerySet [{'name': '泰戈尔', 'c': Decimal('224.00')}, {'name': '李继宏', 'c': Decimal('203.00')}, {'name': '安东尼', 'c': Decimal('192.00')}]>
    print(models.Author.objects.values('name').annotate(c=Max('book__price')).aggregate(Avg('c')))  #{'c__avg': 206.333333}

    # 5 每个作者出版的所有书的价格以及最高价格的那本书的名称 （通过orm玩起来就是个死题，需要用原生sql）
    # print(models.Author.objects.values('name').annotate(c= Max('book__price')).values_list('name','book__title','c'))
    # print(models.Book.objects.values('authors__ad_id_id').annotate(m = Max('price')).values('authors__name,title'))
    # print(models.Book.objects.values_list('authors__name','price').order_by('authors__id') )
    # print(models.Book.objects.values_list('authors__name','price').order_by( '-price').filter(Q(price=224)|Q()).values('title'))
    # print(models.Book.objects.filter())
    # print(models.Book.objects.filter(Q(authors__id=1)|Q(authors__id=2)|Q(authors__id=3)).values_list('authors__name','price'))
    # print(models.Book.objects.filter(Q(authors__id=1)|Q(authors__id=2)|Q(authors__id=3)).values_list('authors__name','price'))

    print(models.Book.objects.annotate(c= Max('price')).values_list('authors__name','title'))
    return HttpResponse('ok')

# def order(request):
#     for i in range(3300):
#         print(i)
#         return render(request,'order.html',locals())

5 每个作者的书的最高价格的名称

中间的select 不能是* ，提示 not id 不懂三联表以价格进行排序以名字分组 GROUP BY b.name 把每一个第一个读出来

print(models.Author.objects.order_by('book__price').annotate(m= Max('book__price')).values_list('book__title','m','name'))

# <QuerySet [('边城', Decimal('56.00'), '钱钟书'), ('边城', Decimal('56.00'), '刘慈欣'), ('边城', Decimal('12.00'), '安东尼'), ('飞鸟集', Decimal('34.00'), '泰戈尔'), ('飞鸟集', Decimal('32.00'), '李继宏')]>

无法处理，必须进行分组处理，group里的第一个title不符合

select * from (select name,title,price from af_pr_author INNER JOIN af_pr_book_authors on af_pr_author.id=
af_pr_book_authors.author_id INNER JOIN af_pr_book on af_pr_book.id=
af_pr_book_authors.book_id ORDER BY af_pr_book.price desc) b  GROUP BY b.name  ;

刘慈欣	平凡的世界	56
安东尼	边城	12
李继宏	飞鸟集	32
泰戈尔	小王子	34
钱钟书	平凡的世界	56

row 原生函数

像下面这样执行原生SQL语句

 ret =models.Publish.objects.raw('select * from af_pr_book;') 
 <RawQuerySet: select * from af_pr_book;> <class 'django.db.models.query.RawQuerySet'>    
返回的是RawQuerySet 实例

 for i in ret:
        print(i.title)     #飞鸟集 小王子  边城 围城 三体 人

直接执行自定义SQL

from django.db import connection, connections
cursor = connection.cursor()  # cursor = connections['default'].cursor()
cursor.execute("""SELECT * from af_pr_author where id = %s""", [1])
ret = cursor.fetchone()
print(ret)      #(1, '泰戈尔', 78, 3)

ret = models.Student.objects.raw('select id, tname as hehe from app02_teacher')
    for i in ret:
        print(i.id, i.hehe)
raw()方法自动将查询字段映射到模型字段
d = {'tname': 'haha'}
    ret = models.Student.objects.raw('select * from app02_teacher', translations=d)
    for i in ret:
        print(i.id, i.sname, i.haha)
原生SQL还可以使用参数
d = {'tname': 'haha'}
    ret = models.Student.objects.raw('select * from app02_teacher where id > %s', translations=d, params=[1,])
    for i in ret:
        print(i.id, i.sname, i.haha)

Python脚本中调用Django环境

(django外部脚本使用models)

import os

if __name__ == '__main__':
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "BMS.settings")
    import django
    django.setup()

    from app01 import models  #引入也要写在上面三句之后

    books = models.Book.objects.all()
    print(books)

day61

聚合返回的是字典

models.A

分组

查询作者 出版过的书的平均价格
models.Author.objects.values('name').annotate(Avg('bp1__price'))
models.Book.objects.values('authors__name').annotate(Abg('price'))

models.Book.objects.filter(commet__lt=F('zan'))
models.Book.objects.all().filter(F('year')+10)

查看2019年的点赞数小于100的，或者评论小于100的书籍名称和价格
models.Book.objects.filter(Q(zan__lt)=100|Q(comment__lt=100),date__year=2019)

作业：

1查询每个作者的姓名以及出版的书的最高价格
models.Author.objects.values('name').annotate(m=Max('book__price'))
models.Book.objects.values('author__name').annotate(m=Max('price'))
2 查询作者id大于2作者的姓名以及出版的书的最高价格
models.Author.objects.filter(id__gt=2).values('name').annotate(m=Max('book__price'))
3 查询作者id大于2或者作者年龄大于等于20岁的女作者的姓名以及出版的书的最高价格
models.Author.objects.filter(Q(sex='女')&Q(Q(id__gt=2)|Q(age__gte=20))).values('name').annotate(m=Max('book__price'))
4查询每个作者出版的书的最高价格 的平均值
models.Author.objects.annotate(m = Max('book__price')).aggregate(Avg('a'))
5 每个作者出版的所有书的价格以及最高价格的那本书的名称 （通过orm玩起来就是个死题，需要用原生sql）
models.Author.objects.values('name').annotate(m = Max('book__price'))
models.Author.objects.values('name').annotate(m=)

day62今日内容

锁，事务

ajax 

cookie 和 session

配置环境

不是内置的py文件，

外部脚本如何操作 Django

if __name__ == '__main__':
    
    import os					# 配置Django的环境
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "ormmysqlmanytables.settings")
    import django

    django.setup()	# 启动Django

    from Af_Pr import models  # 引入也要写在上面三句之后

    books = models.Book.objects.all()	
    print(books)

template(内部过滤器) 已经配置了找文件里基本都是用的反射

今日的内容

查询： 多个维度的查询 模糊匹配啊 各个字段  多条件搜索 也可以做

任何的框架都必须有事务

锁（mysql）

myisam情况下

表级锁：

MySQL 两种：

表共享读锁，阻塞写，不阻塞读

表独占写锁，相反

如何加表锁

myisam

在查的时候，会自动加读锁，

在执行更新操作（update,delete,insert） ,自动加表写锁

　显示加锁：
　　　　　　共享读锁：lock table tableName read;
　　　　　　独占写锁：lock table tableName write;

同时加多锁：lock table t1 write,t2 read;
　　　　　　批量解锁：unlock tables;

3.MyISAM表锁优化建议

1查看表级锁的使用情况

mysql> show status like 'table%';

　Table_locks_immediate：产生表级锁定的次数；
Table_locks_waited：出现表级锁定争用而发生等待的次数；此值越高则说明存在着越严重的表级锁争用情况。此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的存储引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永久阻塞。

2缩短锁定时间

　　如何让锁定时间尽可能的短呢？唯一的办法就是让我们的Query执行时间尽可能的短。
a)尽两减少大的复杂Query，将复杂Query分拆成几个小的Query分布进行；
b)尽可能的建立足够高效的索引，让数据检索更迅速；
c)尽量让MyISAM存储引擎的表只存放必要的信息，控制字段类型；
d)利用合适的机会优化MyISAM表数据文件、

3分离能并行的操作

读写相互阻塞的表锁

MyISAM的存储引擎还有一个非常有用的特性，那就是ConcurrentInsert（并发插入）的特性。
　　MyISAM存储引擎有一个控制是否打开Concurrent Insert功能的参数选项：concurrent_insert，可以设置为0，1或者2。三个值的具体说明如下：
　　　　concurrent_insert=2，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录；
　　　　concurrent_insert=1，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置；
　　　　concurrent_insert=0，不允许并发插入。
　　可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表查询和插入的锁争用。例如，将concurrent_insert系统变量设为2，总是允许并发插入；同时，通过定期在系统空闲时段执行OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞。

4 合理利用读写优先级

默认写优先，

但是可以设置优先级读SET LOW_PRIORITY_UPDATES=1

或者读操作多了，阻塞多了，自动降低写操作的优先级

Innodb

InnoDB默认采用行锁，在未使用索引字段查询时升级为表锁。

MySQL认为全表扫描效率更高，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。关于执行计划

全表更新。事务需要更新大部分或全部数据，且表又比较大。若使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突。

多表级联。事务涉及多个表，比较复杂的关联查询，很可能引起死锁，造成大量事务回滚。这种情况若能一次性锁定事务涉及的表，从而可以避免死锁、减少数据库因事务回滚带来的开销。

三行级锁定

1.InnoDB锁定模式及实现机制

可以说InnoDB的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX），

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

2.InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁

（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

3.间隙锁（Next-Key锁）

4.死锁

5.什么时候使用表锁

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由】

但在个别特殊事务中，也可以考虑使用表级锁：

事务需要更新大部分或全部数据，表又比较大

事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。

6.InnoDB行锁优化建议

　　（1）要想合理利用InnoDB的行级锁定，做到扬长避短，我们必须做好以下工作：

　　　　　　a)尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定；
　　　　　　b)合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；
　　　　　　c)尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；
　　　　　　d)尽量控制事务的大小，减少锁定的资源量和锁定时间长度；
　　　　　　e)在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本。

　　（2）由于InnoDB的行级锁定和事务性，所以肯定会产生死锁，下面是一些比较常用的减少死锁产生概率的小建议：

　　　　　　a)类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；
　　　　　　b)在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
　　　　　　c)对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

　　　　（3）可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

四查看死锁，解除锁

　结合上面对表锁和行锁的分析情况，解除正在死锁的状态有两种方法：

　　　　第一种：

　　　1.查询是否锁表

　　　　　　　　show OPEN TABLES where In_use > 0;

　　　　　　2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）

　　　　　　　　show processlist

　　　　　　3.杀死进程id（就是上面命令的id列）

　　　　　　　　kill id

　　第二种：

　1.查看下在锁的事务 

　　　　　　　　SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

　　　　　　2.杀死进程id（就是上面命令的trx_mysql_thread_id列）

　　　　　　　　kill 线程ID

　　例子：

　　　　查出死锁进程：SHOW PROCESSLIST
　　　　杀掉进程          KILL 420821;

　　其它关于查看死锁的命令：

　　　　1：查看当前的事务
　　　　　　SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

　　　　2：查看当前锁定的事务

　　　　　　SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

　　3：查看当前等锁的事务
　　　　　　SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

超哥博客：https://www.cnblogs.com/clschao/articles/10463743.html

事务

原子性：要么全成功，要么全失败

一致性事务开始到结束的时间段内，数据都必须保持一致状态。

持久性：事务完成后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

隔离性数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的"独立"环境执行

1 全局开启setting里的

databases里

任何一个请求来了，都会被捆绑成一个事务

‘ ATOMIC_REQUESTS ’ ：True, #全局开启事务，绑定的是http请求响应整个过程

一个sql语句就是一个事务

'AUTOCOMMIT' : False , # 全局取消自动提交，慎用默认自动提交

　        "ATOMIC_REQUESTS": True, #全局开启事务，绑定的是http请求响应整个过程
        "AUTOCOMMIT":False, #全局取消自动提交，慎用
    }，
　'other':{
　　　　'ENGINE': 'django.db.backends.mysql', 
            ......
　　} #还可以

@transaction.non_atomic_requests(using='other') # 针对另外的数据库的事务限制

other 指定另外的数据库

事务里面有锁，sql语句+一个互斥锁= 排他锁，

共享锁 = 可以改，不能看（写锁）排他是读锁吗

事务 commit 别的可以用

全局的用上下文的

2 局部开启

1 装饰器

from django.db import transaction

@transaction.atomic

def viewfunc(request):

2 上下文

with transaction.atomic():

do_more_stuff()

3 嵌套

from django.db import transaction

@transaction.atomic

def viewfunc(request):

with transaction.atomic():

do_more_stuff()

锁

Django

select_for_update() # 查询 .filter(a='ss') # 给这条查询语句加锁了互斥锁

Ajax（为了用户体验）

不换页面，保留数据

异步的javascript 和 XML （提交数据的，效率高）请求（偷偷摸摸的，各种其他操作不影响这个请求异步局部刷新）

All 看所有的请求

XHR 看Ajax请求 x http request

以前的json

现在流行的是json

从圈变成x是sh刷新页面

页面

a

src 

输入网页			 --手动控制的网页

form

ajax

今日内容回顾

事务
全局	settings配置
局部
	视图函数装饰器的形式
		@transaction.atomic
		def view()...
		
	上下文的：
		with transaction.atomic():
			sql...
			
mysql 手动加锁
select_for_update()
select * from t1 for update
models.T1.objects.select_for_update().filter(id=1)
 				# 手动加了，别人访问都不了	叫排查锁（互斥锁）
共享锁

ajax

异步发请求
局部刷新
jquery版的：
$.ajax({
	url:'目标地址',			模板渲染
	type:'get',
	success: function(response)	# （）里的内容：后端传过来的内容
	｛console.log(response)｝各种dom操作
})

{% csrf_token %}
用户名:<input type="text" name="username" id="username">
<input type="button" id="sub" value="提交">
{#<span class="error"></span>#}
{#<input type="text" id="d1">#}
<div class="a1" > </div>
<div class="a2" > </div>
<div class="a3" > </div>
<div class="a4" > </div>

<script>
        var username = $('#username').val();
        var csrc_data = $('[name=csrfmiddlewaretoken]').val();

                {#print(response)#}
   $('#sub').click(function () {
        $.ajax({
            url: '{% url "index" %}',
            type: 'post',
            data : {
                uname:username,
                csrfmiddlewaretoken:csrc_data,
            },
            success: function (response)
            {
            {#  alert(response);#}
            {# $('#d1').val( response );#}
            {#console.log(response);#}
            {#$('.tex').text(response);#}
                {% for i in a  %}
                 $( '.{{ i }}').text(response);
                {% endfor %}
            if(response==='a'){
                alert('ok') }
            else{
                $('.a4').text(data['uname'] ) }
            }
         }
            )
   })
</script>

view.py

def login(request):
    a = ['a1','a2','a3']
    return render(request, 'login.html',locals())
def index(request):
    username = request.POST.get('uname')
    print(username) #123
    status ='a'
    return HttpResponse(status)

urls.py

    url(r'^login/',views.login,name='login'),
    url(r'^index/', views.index,name='index') ,

DATABASES = {
    'default':{
        'ENGINE':'django.db.backends.mysql',
        'NAME':'day62',
        'USER':'root',
        'PASSWORD':'123',
        'HOST':'127.0.0.1' ,
        'PORT':3306
    }
}

posted @ 2019-05-29 23:07 learnacode 阅读(191) 评论(0) 编辑收藏举报

刷新页面返回顶部

learnacode

222

ORM多表增删改和查询