django上课笔记3-ORM补充-CSRF (跨站请求伪造)
一、ORM补充
ORM操作三大难点: 正向操作反向操作连表 其它基本操作(包含F Q extra) 性能相关的操作 class UserInfo(models.Model): uid = models.BigAutoField(primary_key=True) username = models.CharField(max_length=32) passwd = models.CharField(max_length=64) age = models.ImageField(null=True) 插入一列,可以为空值 ug = models.ForeignKey('UserGroup',null=True) def __str__(self): return self.name 在views.py的视图函数里查询数据时可以直接看到具体的名字信息并且根据了uid排序 userinfo_list = models.UserInfo.objects.all().order_by('uid') 在views.py的视图函数里 from django.db.models import Count,Sum,Max,Min # values里面的字符串是根据什么group by , A是聚合条件的别名, Count里的是聚合条件,filter(A__gt=2)相当于having v = models.UserInfo.objects.values('ut_id').annotate(A=Count('uid')) print(v.jquery) v = models.UserInfo.objects.values('ut_id').annotate(A=Count('uid')).filter(A__gt=2) F , Q , extra from django.db.models import F,Q,extra F,更新时用于获取原来的值 models.UserInfo.objects.all().update(age=F(age+1)) #让数据库里所有的age+1 models.UserInfo.objects.all().filter(id=1,name='root') #多个条件之间的关系是and关系 Q,用于构造复杂查询条件 models.UserInfo.objects.all().filter(Q(id=1) | Q(name='root')) # Q里边是or条件 q1 = Q() #生成q1对象 q1.connector = 'OR' #q1里3个条件的关系是or q1.children.append(('uid',1)) q1.children.append(('uid',3)) q1.children.append(('uid',5)) q2 = Q() #生成q2对象 q2.connector = 'OR' #q2里3个条件的关系是or q2.children.append(('age',1)) q2.children.append(('age',3)) q2.children.append(('age',5)) con = Q() #生成con对象 con里边2个条件的关系是and con.add(q1,'AND') con.add(q2,'AND') 动态生成Q condition_dict = { 'k1':[1,2,3,4,5] 'k2':[1,2,3] 'k3':[1,2] } con = Q() for k,v in condition_dict: q = Q() q.connector = 'OR' for i in v: q.append(i) con.add(q,'AND') extra, 额外查询条件以及相关表,排序 models.UserInfo.objects.filter(id__gt=1) models.UserInfo.objects.all() # id name age ut_id models.UserInfo.objects.extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None) # a. 映射 # select # select_params=None # select 此处 from 表 # b. 条件 # where=None # params=None, # select * from 表 where 此处 # c. 表 # tables # select * from 表,此处 # c. 排序 # order_by=None # select * from 表 order by 此处 models.UserInfo.objects.extra( select={'newid':'select count(1) from app01_usertype where id>%s'}, #newid是别名 select_params=[1,], #替换%s where = ['age>%s'], params=[18,], #替换%s order_by=['-age'], tables=['app01_usertype'] ) #最终生成语句 """ select app01_userinfo.id, (select count(1) from app01_usertype where id>1) as newid from app01_userinfo,app01_usertype where app01_userinfo.age > 18 order by app01_userinfo.age desc """ result = models.UserInfo.objects.filter(id__gt=1).extra( where=['app01_userinfo.id < %s'], params=[100,], tables=['app01_usertype'], order_by=['-app01_userinfo.id'], select={'uid':1,'sw':"select count(1) from app01_userinfo"} ) print(result.query) # SELECT (1) AS "uid", (select count(1) from app01_userinfo) AS "sw", "app01_userinfo"."id", "app01_userinfo"."name", "app01_userinfo"."age", "app01_userinfo"."ut_id" FROM "app01_userinfo" , "app01_usertype" WHERE ("app01_userinfo"."id" > 1 AND (app01_userinfo.id < 100)) ORDER BY ("app01_userinfo".id) DESC 原生SQL语句 from django.db import connection, connections cursor = connection.cursor() # connection=default数据(默认数据库) cursor = connections['db2'].cursor() #第二个数据库 cursor.execute("""SELECT * from auth_user where id = %s""", [1]) row = cursor.fetchone() row = cursor.fetchall() - extra - 原生SQL语句 - raw result = models.UserInfo.objects.raw('select * from userinfo') [obj(UserInfo),obj,] result = models.UserInfo.objects.raw('select id,1 as name,2 as age,4 as ut_id from usertype') [obj(UserInfo),obj,] v1 = models.UserInfo.objects.raw('SELECT id,title FROM app01_usertype',translations=name_map) ==========================补充1========================== # q = models.UserInfo.objects.all() # select * from userinfo # select * from userinfo inner join usertype on ... # for row in q: # print(row.name,row.ut.title) 正向操作连表查询 # 性能优化 # select_related: 查询主动做连表 # q = models.UserInfo.objects.all().select_related('ut','gp') 括号里的是外键名 # select * from userinfo # select * from userinfo inner join usertype on ... # for row in q: # print(row.name,row.ut.title) # 性能优化 # prefetch_related: 不做连表,做多次查询 # q = models.UserInfo.objects.all().prefetch_related('ut') # select * from userinfo; # Django内部:ut_id = [2,4] # select * from usertype where id in [2,4] # for row in q: # print(row.id,row.ut.title) ==========================补充2多对多操作========================== class Boy(models.Model): name = models.CharField(max_length=32) class Girl(models.Model): nick = models.CharField(max_length=32) class Love(models.Model): b = models.ForeignKey('Boy') g = models.ForeignKey('Girl') # 1. 和alex有关系的姑娘 # obj = models.Boy.objects.filter(name='alex').first() # love_list = obj.love_set.all() # for row in love_list: # print(row.g.nick) # # # love_list = models.Love.objects.filter(b__name='alex') # for row in love_list: # print(row.g.nick) #性能优化 # love_list = models.Love.objects.filter(b__name='alex').values('g__nick') #字典的形式 # for item in love_list: # print(item['g__nick']) # # love_list = models.Love.objects.filter(b__name='alex').select_related('g') # for obj in love_list: # print(obj.g.nick) 1. ManyToManyField 2. 自定义关系表 3. 复合 class Boy(models.Model): name = models.CharField(max_length=32) m = models.ManyToManyField('Girl',through="Love",through_fields=('b','g',)) # 查询和清空 class Girl(models.Model): nick = models.CharField(max_length=32) # m = models.ManyToManyField('Boy') # 自动生成第三张表 class Love(models.Model): b = models.ForeignKey('Boy') g = models.ForeignKey('Girl') class Meta: # 联合唯一索引 unique_together = [ ('b','g'), ]
二、CSRF (跨站请求伪造)
a. 基本应用 form表单中添加 {% csrf_token %} b. 全站禁用 去settings里边注释掉 # 'django.middleware.csrf.CsrfViewMiddleware', c. 局部禁用 'django.middleware.csrf.CsrfViewMiddleware', from django.views.decorators.csrf import csrf_exempt @csrf_exempt def csrf1(request): if request.method == 'GET': return render(request,'csrf1.html') else: return HttpResponse('ok') d. 局部使用 # 'django.middleware.csrf.CsrfViewMiddleware', from django.views.decorators.csrf import csrf_exempt,csrf_protect @csrf_protect def csrf1(request): if request.method == 'GET': return render(request,'csrf1.html') else: return HttpResponse('ok') c. 特殊CBV(在django的CBV中csrf只能加到类上边,不能加到类里边) from django.views import View from django.utils.decorators import method_decorator @method_decorator(csrf_protect,name='dispatch') class Foo(View): def get(self,request): pass def post(self,request): pass PS:CBV中添加装饰器 def wrapper(func): def inner(*args,**kwargs): return func(*args,**kwargs) return inner # 1. 指定方法上添加装饰器 # class Foo(View): # # @method_decorator(wrapper) # def get(self,request): # pass # # def post(self,request): # pass # 2. 在类上添加 # @method_decorator(wrapper,name='dispatch') # class Foo(View): # # def get(self,request): # pass # # def post(self,request): # pass Ajax提交数据时候,携带CSRF: a. 放置在data中携带 <form method="POST" action="/csrf1.html"> {% csrf_token %} <input id="user" type="text" name="user" /> <input type="submit" value="提交"/> <a onclick="submitForm();">Ajax提交</a> </form> <script src="/static/jquery-1.12.4.js"></script> <script> function submitForm(){ var csrf = $('input[name="csrfmiddlewaretoken"]').val(); var user = $('#user').val(); $.ajax({ url: '/csrf1.html', type: 'POST', data: { "user":user,'csrfmiddlewaretoken': csrf}, success:function(arg){ console.log(arg); } }) } </script> b. 放在请求头中 <form method="POST" action="/csrf1.html"> {% csrf_token %} <input id="user" type="text" name="user" /> <input type="submit" value="提交"/> <a onclick="submitForm();">Ajax提交</a> </form> <script src="/static/jquery-1.12.4.js"></script> <script src="/static/jquery.cookie.js"></script> <script> function submitForm(){ var token = $.cookie('csrftoken'); var user = $('#user').val(); $.ajax({ url: '/csrf1.html', type: 'POST', headers:{'X-CSRFToken': token}, data: { "user":user}, success:function(arg){ console.log(arg); } }) } </script>