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>