Day070 Django(六)

24_案例9_添加学生班级信息

重写save()

需求:

        from student.models import *
stu = Stu(sname='zhangsan',score=88,Clazz=Clazz(cname='HTML5'))
stu.save()
stu = Stu(sname='lisi',score=90,Clazz=Clazz(cname='MySQL'))
stu.save()
stu = Stu(sname='wangwu',score=99,Clazz=Clazz(cname='Python'))
stu.save()
stu = Stu(sname='zhangjie',score=66,Clazz=Clazz(cname='HTML5'))
stu.save()
stu = Stu(sname='xiena',score=89,Clazz=Clazz(cname='Python'))
stu.save()


#通过以上语句能够实现插入两张表的数据

stu.save() : student 对象调用的方法。所以只需要重写 student 类的 save() 方法,不需要重新定义 Manager
      

创建模型类

        # -*- coding: utf-8 -*-

from django.db import models

# Create your models here.

class Clazz(models.Model):
    cno = models.AutoField(primary_key=True)
    cname = models.CharField(max_length=30)

    def __str__(self):
        return '%s,%s'%(self.cno,self.cname)


class Stu(models.Model):
    sno = models.AutoField(primary_key=True)
    sname = models.CharField(max_length=30)
    score = models.PositiveIntegerField()
    created = models.DateField(auto_now_add=True)
    clazz = models.ForeignKey(Clazz,on_delete=models.CASCADE)

    # 重写 save() 方法
    def save(self, force_insert=False, force_update=False, using=None, update_fields=None):
        # 获取班级对象
        try:
            self.clazz = Clazz.objects.get(cname=self.clazz.cname)
        except Clazz.DoesNotExist:
            self.clazz = Clazz.objects.create(cname=self.clazz.cname)
        
        # 插入学生表信息(调用父类的方法)
        # super(Stu, self).save(force_insert, force_update, using,update_fields)
        # 或着
        return models.Model.save(self, force_insert, force_update, using,update_fields)


    def __str__(self):
        return 'Stu:%s,%s'%(self.sname,self.score)
 
        
# 快速查找到 save() 方法所在的位置的方法      
# Stu.save()
      


25_聚合函数查询

聚合函数

  • MAX() MIN() COUNT() SUM() AVG()
  • 公式:模型类名.objects.aggregate(Avg('score'))
        from student.models import *

def showsql():
    from django.db import connection
    print connection.queries[-1]['sql']
    
#求最高成绩

from django.db.models import Max
Stu.objects.aggregate(Max('score'))
{'score__max': 99}

showsql()
SELECT MAX(`t_stu`.`score`) AS `score__max` FROM `t_stu`


#求最低成绩

from django.db.models import Min
Stu.objects.aggregate(m=Min('score'))
{'m': 66}

showsql()
SELECT MIN(`t_stu`.`score`) AS `m` FROM `t_stu`


#学生总人数

from django.db.models import Count
Stu.objects.aggregate(c=Count('sno'))
{'c': 5}
showsql()
SELECT COUNT(`t_stu`.`sno`) AS `c` FROM `t_stu`


#求总成绩

from django.db.models import Sum
Stu.objects.aggregate(s=Sum('score'))
{'s': 432}
showsql()
SELECT SUM(`t_stu`.`score`) AS `s` FROM `t_stu`


#求平均成绩

from django.db.models import Avg
Stu.objects.aggregate(a=Avg('score'))
{'a': 86.4}
showsql()
SELECT AVG(`t_stu`.`score`) AS `a` FROM `t_stu`
      


26_分组聚合函数

Group By 分组

  • 聚合函数[ max() min() count() sum() avg() ]
  • 公式:模型类.objects.values('分组字段名').annotate(Avg('字段名'))

准备工作

        from student.models import *
from django.db.models import *  

def showsql():
    from django.db import connection
    print connection.queries[-1]['sql']
Stu.objects.annotate(c=Count('sno'))
<QuerySet [<Stu: Stu:zhangsan,88>, <Stu: Stu:lisi,90>, <Stu: Stu:wangwu,99>, <Stu: Stu:zhangjie,66>, <Stu: Stu:xiena,89>]>

showsql()
SELECT `t_stu`.`sno`, `t_stu`.`sname`, `t_stu`.`score`, `t_stu`.`created`, `t_stu`.`clazz_id`, COUNT(`t_stu`.`sno`) AS `c` FROM `t_stu` GROUP BY `t_stu`.`sno` ORDER BY NULL LIMIT 21

Stu.objects.annotate(a=Avg('clazz'))
<QuerySet [<Stu: Stu:zhangsan,88>, <Stu: Stu:lisi,90>, <Stu: Stu:wangwu,99>, <Stu: Stu:zhangjie,66>, <Stu: Stu:xiena,89>]>

showsql()
SELECT `t_stu`.`sno`, `t_stu`.`sname`, `t_stu`.`score`, `t_stu`.`created`, `t_stu`.`clazz_id`, AVG(`t_stu`.`clazz_id`) AS `a` FROM `t_stu` GROUP BY `t_stu`.`sno` ORDER BY NULL LIMIT 21

#求每个班学生的总成绩

Stu.objects.values('clazz').annotate(s=Sum('score'))
<QuerySet [{'s': 154, 'clazz': 1L}, {'s': 90, 'clazz': 2L}, {'s': 188, 'clazz': 3L}]>
showsql()
SELECT `t_stu`.`clazz_id`, SUM(`t_stu`.`score`) AS `s` FROM `t_stu` GROUP BY `t_stu`.`clazz_id` ORDER BY NULL LIMIT 21




# 求每个班级的学生人数

Stu.objects.values('clazz').annotate(c=Count('sno'))
<QuerySet [{'c': 2, 'clazz': 1L}, {'c': 1, 'clazz': 2L}, {'c': 2, 'clazz': 3L}]>

showsql()
SELECT `t_stu`.`clazz_id`, COUNT(`t_stu`.`sno`) AS `c` FROM `t_stu` GROUP BY `t_stu`.`clazz_id` ORDER BY NULL LIMIT 21





# 求每个班级的学生平均成绩

Stu.objects.values('clazz').annotate(a=Avg('score'))
<QuerySet [{'a': 77.0, 'clazz': 1L}, {'a': 90.0, 'clazz': 2L}, {'a': 94.0, 'clazz': 3L}]>

showsql()
SELECT `t_stu`.`clazz_id`, AVG(`t_stu`.`score`) AS `a` FROM `t_stu` GROUP BY `t_stu`.`clazz_id` ORDER BY NULL LIMIT 21
      

子查询

        Stu.objects.values('clazz').annotate(s=Sum('score')).aggregate(m=Max('s'))
{'m': 188}

showsql()
SELECT MAX(`s`) FROM (SELECT `t_stu`.`clazz_id` AS Col1, SUM(`t_stu`.`score`) AS `s` FROM `t_stu` GROUP BY `t_stu`.`clazz_id` ORDER BY NULL) subquery
      

关联查询

        Stu.objects.values('clazz__cname')
<QuerySet [{'clazz__cname': u'HTML5'}, {'clazz__cname': u'HTML5'}, {'clazz__cname': u'MySQL'}, {'clazz__cname': u'Python'}, {'clazz__cname': u'Python'}]>

showsql()
SELECT `t_cls`.`cname` FROM `t_stu` INNER JOIN `t_cls` ON (`t_stu`.`clazz_id` = `t_cls`.`cno`) LIMIT 21
      


27_原生查询

方式1

包含主键

        for d in Stu.objects.raw('select * from t_stu'):
    print d
    
Stu:zhangsan,88
Stu:lisi,90
Stu:wangwu,99
Stu:zhangjie,66
Stu:xiena,89
      

不包含主键

        for d in Stu.objects.raw('select sname,score from t_stu'):
    print d
    
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "C:\Python27\lib\site-packages\django\db\models\query.py", line 1245, in __iter__
    raise InvalidQuery('Raw query must include the primary key')
InvalidQuery: Raw query must include the primary key
      

方式2

包含主键

        from student.models import *
from django.db import connection

# 获取游标对象
cursor = connection .cursor()
cursor.execute('select * from t_stu')
5L
datas = cursor.fetchall()

print datas
((1L, u'zhangsan', 88L, datetime.date(2018, 4, 7), 1L), (2L, u'lisi', 90L, datetime.date(2018, 4, 7), 2L), (3L, u'wangwu', 99L, datetime.date(2018, 4, 7), 3L), (4L, u'zhangjie', 66L, datetime.date(2018, 4, 7), 1L), (5L, u'xiena', 89L, datetime.date(2018, 4, 7), 3L))
for d in datas:
    print d
(1L, u'zhangsan', 88L, datetime.date(2018, 4, 7), 1L)
(2L, u'lisi', 90L, datetime.date(2018, 4, 7), 2L)
(3L, u'wangwu', 99L, datetime.date(2018, 4, 7), 3L)
(4L, u'zhangjie', 66L, datetime.date(2018, 4, 7), 1L)
(5L, u'xiena', 89L, datetime.date(2018, 4, 7), 3L)

cursor.close()
      

不包含主键

        from student.models import *
from django.db import connection
cursor= connection.cursor()
cursor.execute('select sname,score from t_stu')
5L
ds = cursor.fetchall()
print ds
((u'zhangsan', 88L), (u'lisi', 90L), (u'wangwu', 99L), (u'zhangjie', 66L), (u'xiena', 89L))
cursor.close()
      

获取一条记录

        from student.models import *
from django.db import connection
cursor= connection.cursor()
cursor.execute('select * from t_stu where sno=1')
1L
cursor.fetchone()
(1L, u'zhangsan', 88L, datetime.date(2018, 4, 7), 1L)

cursor.close()
      

封装函数实现原生查询

        def test(sql):
    with connection.cursor() as c:
        c.execute(sql)
        print [d for d in c.fetchall()]
      

MySQL外连接

  • SQL92:select * from ltable,rtable where ltable.column=rtable.column(+)
  • SQL99: select * from ltable left join rtable on (ltable.column=rtable.column)


28_Q查询_F查询

Q查询

应用场景:与、或、非。用于查询数据库中数据的删选条件

查看底层SQL

        def showsql():
    from django.db import connection
    print connection.queries[-1]['sql']
#条件与操作

from student.models import *
from django.db.models import Q,F
Stu.objects.filter(Q(sno=1)&Q(sname='zhangsan'))
<QuerySet [<Stu: Stu:zhangsan,88>]>

    
showsql()
SELECT `t_stu`.`sno`, `t_stu`.`sname`, `t_stu`.`score`, `t_stu`.`created`, `t_stu`.`clazz_id` FROM `t_stu` WHERE (`t_stu`.`sno` = 1 AND `t_stu`.`sname` = 'zhangsan') LIMIT 21


Stu.objects.get(Q(sno=1)&Q(sname='zhangsan'))
<Stu: Stu:zhangsan,88>
showsql()
SELECT `t_stu`.`sno`, `t_stu`.`sname`, `t_stu`.`score`, `t_stu`.`created`, `t_stu`.`clazz_id` FROM `t_stu` WHERE (`t_stu`.`sno` = 1 AND `t_stu`.`sname` = 'zhangsan')
#条件或操作
Stu.objects.filter(Q(created__gte='2017-12-12')|Q(sno=1))
<QuerySet [<Stu: Stu:zhangsan,88>, <Stu: Stu:lisi,90>, <Stu: Stu:wangwu,99>, <Stu: Stu:zhangjie,66>, <Stu: Stu:xiena,89>]>
showsql()
SELECT `t_stu`.`sno`, `t_stu`.`sname`, `t_stu`.`score`, `t_stu`.`created`, `t_stu`.`clazz_id` FROM `t_stu` WHERE (`t_stu`.`created` >= '2017-12-12' OR `t_stu`.`sno` = 1) LIMIT 21
#条件非操作
Stu.objects.filter(~Q(sname='zhangsan'))
<QuerySet [<Stu: Stu:lisi,90>, <Stu: Stu:wangwu,99>, <Stu: Stu:zhangjie,66>, <Stu: Stu:xiena,89>]>
showsql()
SELECT `t_stu`.`sno`, `t_stu`.`sname`, `t_stu`.`score`, `t_stu`.`created`, `t_stu`.`clazz_id` FROM `t_stu` WHERE NOT (`t_stu`.`sname` = 'zhangsan') LIMIT 21



Stu.objects.filter(~Q(sname='zhangsan',sno=1))
<QuerySet [<Stu: Stu:lisi,90>, <Stu: Stu:wangwu,99>, <Stu: Stu:zhangjie,66>, <Stu: Stu:xiena,89>]>
showsql()
SELECT `t_stu`.`sno`, `t_stu`.`sname`, `t_stu`.`score`, `t_stu`.`created`, `t_stu`.`clazz_id` FROM `t_stu` WHERE NOT (`t_stu`.`sno` = 1 AND `t_stu`.`sname` = 'zhangsan') LIMIT 21
      

F查询

应用场景:获取某个字段原有值时使用。用于修改数据库中数据

        #学号大于3的学生成绩加10分
Stu.objects.filter(Q(sno__gt=3)).update(score=F('score')+10)
2L
showsql()
UPDATE `t_stu` SET `score` = (`t_stu`.`score` + 10) WHERE `t_stu`.`sno` > 3



#学号大于3的学生入学日期提前2天

import datetime
Stu.objects.filter(Q(sno__gt=3)).update(created=F('created')+datetime.timedelta(days=-2))
2L
showsql()
UPDATE `t_stu` SET `created` = (`t_stu`.`created` + INTERVAL '172800.000000' SECOND_MICROSECOND) WHERE `t_stu`.`sno` > 3
      


29_装饰器模式

装饰器的功能

  • 将被装饰的函数当作参数传递给与装饰器对应的函数(名称相同的函数),并返回包装后的被装饰的函数
  • 被修饰器修饰的函数具有相同特定的功能

二阶装饰器

        def a(func):
    def _wrapper(*args,**kwargs):
        content = func(*args,**kwargs)
        return '<b>%s</b>'%content
    return _wrapper
@a
def b():
    return 'hello world'
    
b()
'<b>hello world</b>'
      

三阶装饰器

        def a(bold=True):
    def _wrapper(func):
        def __wrapper(*args,**kwargs):
            content = func(*args,**kwargs)
            if bold:
                return '<b>%s</b>'%content
            else:
                return '<i>%s</i>'%content
        return __wrapper
    return _wrapper
    
                
@a(bold=False)
def b():
    return 'hello world'
    
    
b()
'<i>hello world</i>'
      


30_Django事务

Django中的事务处理

  • Django中的事务是自动提交模式
        # -*- coding: utf-8 -*-

from django.db import models

# Create your models here.

class Clazz(models.Model):
    cno = models.AutoField(primary_key=True)
    cname = models.CharField(max_length=30)

    class Meta:
        db_table='t_cls'

    def __str__(self):
        return 'Clazz:%s,%s'%(self.cno,self.cname)

class Stu(models.Model):
    sno = models.AutoField(primary_key=True)
    sname = models.CharField(max_length=30)
    score = models.PositiveIntegerField(max_length=3)
    created = models.DateField(auto_now_add=True)
    clazz = models.ForeignKey(Clazz,on_delete=models.CASCADE)
    
    # 导入django.db.transaction 模块中的 atomic
    from django.db.transaction import atomic

    @atomic     # 保证事务的原子性
    def save(self, force_insert=False, force_update=False, using=None,
             update_fields=None):
        try:
            self.clazz = Clazz.objects.get(cname=self.clazz.cname)
        except Clazz.DoesNotExist:
            self.clazz = Clazz.objects.create(cname=self.clazz.cname)
            
        #制造异常
        1/0

        models.Model.save(self, force_insert, force_update, using,update_fields)


    class Meta:
        db_table='t_stu'

    def __str__(self):
        return 'Stu:%s,%s'%(self.sname,self.score)
      

测试结果

        #两张表中都没有插入新数据

from student.models import *
stu = Stu(sname='xiaowang',score=88,clazz=Clazz(cname='Oracle'))
stu.save()

Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "C:\Python27\lib\site-packages\django\utils\decorators.py", line 185, in inner
    return func(*args, **kwargs)
  File "D:\pythoncodes\django_20180402\demo\demo3\student\models.py", line 34, in save
    1/0
ZeroDivisionError: integer division or modulo by zero
      

31_QuerySet 两大特性

调用哪些方法能够返回 QuerySet ?

  • all():返回所有数据。
  • filter():返回满足条件的数据。
  • exclude():返回满足条件之外的数据。
  • order_by():对结果进行排序。

1.惰性查询

  1. 延迟查询
  2. 默认查询前21条数据
  3. 什么时候需要数据进行查询
        #实际上没有真正查询数据库
stus = StudentInfo.objects.all()


for s in stus:
    #此时才执行数据库的查询操作
    print(s)
      

2.缓存数据

for 循环的时候缓存

        #已经将数据进行了缓存
cids = [c.id for c in Clazz.objects.all()]

#此时获取的是缓存中的数据
print(cids)


from stuapp4.models import *
for c in Clazz.objects.all():
    print(c)
B201Python
B202Java
B203前端班

#数据库中查询到三条记录,但是只查看到执行的一条SQL语句
from django.db import connection
print(connection.queries)
[{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'}, {'sql': 'SELECT `stuapp4_clazz`.`id`, `stuapp4_clazz`.`cname` FROM `stuapp4_clazz`', 'time': '0.000'}]
      

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值