1. ORM
1.1 对表的基本查
# 单表的查
# 1、查询所有
res = Class.objects.all() # QuerySet对象,(列表套对象)
print(res) # <QuerySet [<Class: Class object>, <Class: Class object>, <Class: Class object>, <Class: Class object>]>
for row in res:
print(row.id, row.cname)
# 2. 指定字段查询 values, value_list
res = Class.objects.values('cname').all() # QuerySet对象,(列表套字典)
print(res) # <QuerySet [{'cname': 'pyton8期'}, {'cname': 'pyton9期'}, {'cname': 'pyton10期'}, {'cname': 'pyton11期'}]>
res = Class.objects.values_list('cname').all() # QuerySet对象,(列表套元组)
# print(res) # <QuerySet [('pyton8期',), ('pyton9期',), ('pyton10期',), ('pyton11期',)]>
# 3、filter 筛选
res = Class.objects.filter(id=1).all()
print(res) # <QuerySet [<Class: Class object>]>
for row in res:
print(row.id, row.cname) # 1 pyton8期
# 4、大于,大于等于,小于,小于等于 __gt, __gte, __lt, __lte
res = Class.objects.filter(id__lt=3)
print(res) # <QuerySet [<Class: Class object>, <Class: Class object>]>
for row in res:
print(row.id, row.cname) # 1 pyton8期 2 pyton9期
# 一对多
# 1、查询所有
res = Students.objects.all() # 列表套对象
print(res) # <QuerySet [<Students: Students object>, <Students: Students object>, <Students: Students object>, <Students: Students object>, <Students: Students object>, <Students: Students object>, <Students: Students object>, <Students: Students object>]>
# 2、查询一个
res = Students.objects.first()
print(res) # Students object
print(res.id, res.sname, res.stu_cla_id) # 1 富强 1
#
# 3、正向查询
res = Students.objects.all()
for row in res:
print(row.id, row.sname, row.stu_cla.cname)
# 1 富强 pyton8期
# 2 民主 pyton10期
# 3 文明 pyton9期
# ... ... ...
# 4、 反向查询 外键关联的表名小写_set.filter()
# 需求:查询所有课程下的所有学生姓名
res = Class.objects.all()
for row in res:
print(row.cname, row.students_set.all())
for k in row.students_set.all():
print(row.cname, k.sname)
# pyton8期 富强
# pyton8期 公正
# pyton8期 法治
# pyton9期 文明
# pyton9期 平等
# pyton10期 民主
# pyton11期 和谐
# pyton11期 自由
# 5、神奇的双下划线(跨表查询) (列表套字典)
res = Students.objects.values('sname', 'stu_cla__cname')
print(res) # <QuerySet [{'sname': '富强', 'stu_cla__cname': 'pyton8期'}, {'sname': '民主', 'stu_cla__cname': 'pyton10期'}, {'sname': '文明', 'stu_cla__cname': 'pyton9期'}, {'sname': '和谐', 'stu_cla__cname': 'pyton11期'}, {'sname': '自由', 'stu_cla__cname': 'pyton11期'}, {'sname': '平等', 'stu_cla__cname': 'pyton9期'}, {'sname': '公正', 'stu_cla__cname': 'pyton8期'}, {'sname': '法治', 'stu_cla__cname': 'pyton8期'}]>
# 多对多
# 查询 老师 zeikai 所教的班级
# 方式一
res = Teachers.objects.filter(sname='zekai').first()
print(res.id, res.sname) # 1 zekai
tea_cla_list = res.teacherclass_set.all()
print('tea_cla_list',tea_cla_list) # <QuerySet [<TeacherClass: TeacherClass object>, <TeacherClass: TeacherClass object>]>
for obj in tea_cla_list:
print(obj.cid.cname)
# pyton8期
# pyton9期
# 方式二
res = TeacherClass.objects.filter(tid__sname='zekai').all()
for obj in res:
print(obj.cid.cname)
# pyton8期
# pyton9期
# 方式三
res = TeacherClass.objects.filter(tid__sname='zekai').values('cid__cname')
print(res) # <QuerySet [{'cid__cname': 'pyton8期'}, {'cid__cname': 'pyton9期'}]>
1.2 高级查询
# 高级查询
# 1、in
res = Class.objects.filter(id__in = [1,2,3])
print(res) # <QuerySet [<Class: Class object>, <Class: Class object>, <Class: Class object>]>
for row in res:
print(row.id, row.cname)
# 1 pyton8期
# 2 pyton9期
# 3 pyton10期
# 2、 not in
res = Class.objects.exclude(id__in=[1,2,3])
print(res) # <QuerySet [<Class: Class object>]>
for row in res:
print(row.id, row.cname) # 4 pyton11期
# 3、like
# startswith, istartwith,(不区分大小写) where name like 'py%' ## 以py开头的所有的数据
# endswith, iendswith where name like '%py'
# contains, icontains 包含某一个单词 where name like "%py%"
res = Class.objects.filter(cname__istartswith='py')
print(res) # <QuerySet [<Class: Class object>, <Class: Class object>, <Class: Class object>, <Class: Class object>]>
for row in res:
print(row.id, row.cname)
# 1 pyton8期
# 2 pyton9期
# 3 pyton10期
# 4 pyton11期
# 4、between ... and ..
res = Class.objects.filter(id__range=[1,3])
print(res) # <QuerySet [<Class: Class object>, <Class: Class object>, <Class: Class object>]>
# 5、limit 10,20
res = Class.objects.all()[0:3] #[ 开始位置: 结束位置]
print(res) # <QuerySet [<Class: Class object>, <Class: Class object>, <Class: Class object>]>
# 6、order by age asc, name desc
res = Class.objects.all().order_by('id', 'cname') # 默认升序前面加一个 ‘-’ 代表 降序
print(res.query) # SELECT "app1_class"."id", "app1_class"."cname" FROM "app1_class" ORDER BY "app1_class"."id" ASC, "app1_class"."cname" ASC
# 7、group by
from django.db.models import Count, Max, Min, Sum
res = Class.objects.values('cname').annotate(xxx=Count('id'))
print(res.query) # SELECT "app1_class"."cname", COUNT("app1_class"."id") AS "xxx" FROM "app1_class" GROUP BY "app1_class"."cname"
# 8、only: 只取某一列值
res = Class.objects.only('id', 'cname').all()
print(res.query) # SELECT "app1_class"."id", "app1_class"."cname" FROM "app1_class"
# 9、defer: 除了这几列之外的所有值 (但是不管怎样, 都会查 id)
res = Students.objects.defer('id','sname').all()
print(res.query) # SELECT "app1_students"."id", "app1_students"."stu_cla_id" FROM "app1_students"
# 10、 using: 想要使用哪个数据库, 就将这个数据库的配置名称写到using中
Class.objects.all().using('xxxx')
# 11、count 表中总共有多少条数据
res = Class.objects.count() # count不需要参数
print(res)
# 12、第一条数据,最后一条数据
res = Class.objects.first()
ret = Class.objects.last()
print(res, ret) # Class object Class object
#
# 13、 gt 大于 gte: 大于等于 lt 小于 lte 小于等于
res = Class.objects.filter(id__gt=2)
print(res) # <QuerySet [<Class: Class object>, <Class: Class object>]>
# 14、and操作
res = Class.objects.filter(id=1,cname='pyton8期')
print(res) # <QuerySet [<Class: Class object>]>
print(res.query) # SELECT "app1_class"."id", "app1_class"."cname" FROM "app1_class" WHERE ("app1_class"."id" = 1 AND "app1_class"."cname" = pyton8期)
# 15、or操作
from django.db.models import Q
res = Class.objects.filter(Q(id=2) | Q(cname='pyton8期'))
print(res.query) # SELECT "app1_class"."id", "app1_class"."cname" FROM "app1_class" WHERE ("app1_class"."id" = 2 OR "app1_class"."cname" = pyton8期)
# 16、在原来的基础上更新值
from django.db.models import F
UserInfo.objects.update(age=F('age') + 1) # 表中存在可以需要更新的值,使用方式如此
# 17、原生sql
# 方式一
from django.db import connection
cursor = connection.cursor()
cursor.execute("select * from app1_class where id=%s", [1,])
res = cursor.fetchall()
print(res) # [(1, 'pyton8期')]
res = cursor.fetchone()
print(res)
# 方式二
res= Class.objects.raw('select * from main.app1_class')
print(res) # <RawQuerySet: select * from main.app1_class>
for row in res:
print(row.id, row.cname)
# 1 pyton8期
# 2 pyton9期
# 3 pyton10期
# 4 pyton11期
1.3 增
# # 增加一条数据
Class.objects.create(cname='pyton8期')
# 增加多条数据
obj = [
Class(cname='pyton9期'),
Class(cname='pyton10期'),
Class(cname='pyton11期'),
]
Class.objects.bulk_create(obj)
obj = [
Students(sname='富强', stu_cla_id=1),
Students(sname='民主', stu_cla_id=3),
Students(sname='文明', stu_cla_id=2),
Students(sname='和谐', stu_cla_id=4),
Students(sname='自由', stu_cla_id=4),
Students(sname='平等', stu_cla_id=2),
Students(sname='公正', stu_cla_id=1),
Students(sname='法治', stu_cla_id=1),
]
Students.objects.bulk_create(obj)
多对多创建外键
1. 自己定制
2. 通过 ManyToManyField() 来生成第三张表
models.py:
class Boy(models.Model):
name = models.CharField(max_length=32, null=True)
g = models.ManyToManyField('Girl', null=True)
class Girl(models.Model):
nick = models.CharField(max_length=32, null=True)
1.4 删
# 删除
# Students.objects.all().delete()
# Students.objects.filter(sname='法治').delete()
【注意】
stu_cla = models.ForeignKey('Class', null=True, on_delete=models.CASCADE)
CASCADE: 设置级联删除
SET_NULL : 取消级联删除
1.5 改
Students.objects.filter(id=7).update(sname='发财')
2. XSS攻击
2. Xss攻击
全程:跨站脚本(js)攻击
原因:
用户输入的内容不可控
<script>1.获取用户的cookie 2. 发送这个cookie到黑客的数据库</script>
views.py:
msg = []
def comment(request):
if request.method == 'GET':
return render(request, "comment.html")
else:
comment = request.POST.get('comment')
msg.append(comment)
return render(request, "comment.html")
def show(request):
return render(request, 'show.html', {"msg":msg})
comment.html:
<form action="/comment/" method="post">
<input type="text" name="comment">
<input type="submit" value="提交">
</form>
show.html:
<ul>
{% for item in msg %}
<li>{{ item | safe }}</li>
{% endfor %}
</ul>