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.惰性查询
- 延迟查询
- 默认查询前21条数据
- 什么时候需要数据进行查询
#实际上没有真正查询数据库
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'}]