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>