DjanGo数据库查询方法进阶

一对一查询

def test(request):

    ### 增加
    # models.UserInfo.objects.create(name='zekai', age=18)
    # models.Salary.objects.create(money="3000", us_id=1)


    ### 查询：
    ## 正向查询(子表查母表)：
    ### 1.11.22 ：res.us.name
    ### 1.11.10 : res.母表表名小写.name
    # res = models.Salary.objects.filter(money="3000").first()
    # print(res.us.name)


    ### 反向查询(母表查子表)
    ### obj.子表小写.子表列名 ： res.salary.money
    # res = models.UserInfo.objects.filter(name='zekai').first()
    # print(res.salary.money)


    return HttpResponse('ok')

多对多查询

方法一

(先得到一个表中的对象，再反向查询他们之间对应关系表中的对象，然后再正向查询另一个表对象，或者对象中的属性)

res = models.Boy.objects.filter(name='勾').first()
print(res) ### Boy object
 ### 反向查询 love中的相亲记录
love_list = res.love_set.all() ## <QuerySet [<Love: Love object>, <Love: Love object>]>
for obj in love_list:
### 正向查询 girl表中的nick
print(obj.g.nick)

方法二

先再两表关联表中查得到对象，再找另外一个表，但是查询的关键字是用神奇的双下画线

res = models.Love.objects.filter(b__name='勾').all()
    # print(res) ## <QuerySet [<Love: Love object>, <Love: Love object>]>
    # for obj in res:
    #     print(obj.g.nick)

方法三

先再中间关联表对象，其余两表用神奇的双下划线查

 ### 3.查询和 勾洋 约会的 姑娘
    # res = models.Love.objects.filter(b__name='勾').values("g__nick")
    # print(res)

Django的增删改查

增(add)

obj = models.Boy.objects.filter(name='谢').first()
print(obj)### Boy object
obj.g.add(3)
obj.g.add(*[1,2])

重置(set)

obj.g.set([4])

查询(filter)

obj = models.Boy.objects.filter(name='谢').first()
    # res = obj.g.all()
    # print(res) ## <QuerySet [<Girl: Girl object>, <Girl: Girl object>, <Girl: Girl object>]>
    # for obj  in res:
    #     print(obj.nick)

删

obj = models.Boy.objects.filter(name='谢').first()
    obj.g.clear()

    return HttpResponse('ok')

其他查询方法

查询出的数据进行去重

user_obj.distinct()

in

res = models.UserInfo.objects.filter(id__in=[1,2,3])
    # print(res)

not in

res = models.UserInfo.objects.exclude(id__in=[1,2,3])
    # print(res)

like

where name like 'ze%' ## 以ze开头的所有的数据
# startswith： 以 某单词开头
# istartswith : ignore (忽略)  以 某单词开头 忽略大小写
res = models.UserInfo.objects.filter(name__startswith="ze")
res = models.UserInfo.objects.filter(name__istartswith="ze")
 where  name  like '%ze'
 endswith : 以 某个单词结尾
 iendswith:  ignore (忽略)  以 某单词结尾 忽略大小写
res = models.UserInfo.objects.filter(name__endswith='ze')

where name like "%ze%"
 contains : 包含某一个单词
icontains: 包含某一个单词 不区分大小写
models.UserInfo.objects.filter(name__contains='ze')

betwween......and

models.UserInfo.objects.filter(id__range=[1,2])

limit 10, 20

models.UserInfo.objects.all()[开始位置:结束位置]
models.UserInfo.objects.all()[0:10]
models.UserInfo.objects.all()[10:20]

order by /asc /desc

res = models.UserInfo.objects.all().order_by('id') ## 默认升序
 前面加一个 ‘-’ 代表 降序
 res = models.UserInfo.objects.all().order_by('-id','name')
 print(res.query

group by

from django.db.models import Count, Max, Min, Sum
    # res = models.UserInfo.objects.values('name').annotate(xxx=Count('id'))
    # print(res.query)
相当于
SELECT "app01_userinfo"."id", COUNT("app01_userinfo"."name") AS "xxx"
    # FROM "app01_userinfo"
    # GROUP BY "app01_userinfo"."id";

only: 只取某一个列的值

SELECT "app01_userinfo"."id", "app01_userinfo"."name", "app01_userinfo"."age" FROM "app01_userinfo"
res = models.UserInfo.objects.only('name',  'age').all()
print(res) ## <QuerySet [<UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>]>

defer: 除了这几列之外的所有列的值

SELECT "app01_userinfo"."id", "app01_userinfo"."ut_id" FROM "app01_userinfo"
    # res = models.UserInfo.objects.defer('id','name', 'age').all()
    # print(res.query)(查看创建过程sql查询语句)

using:

想要使用哪个数据库， 就将这个数据库的配置名称写到using中

models.UserInfo.objects.all().using("xxxx")

11. 表中总共多少条数据

res = models.UserInfo.objects.count()

12. 第一条数据

res = models.UserInfo.objects.first()

13.最后一条数据

res = models.UserInfo.objects.last()

14.gt lt范围操作

res = models.UserInfo.objects.filter(id__gt=3)
res = models.UserInfo.objects.filter(id__gte=3)
res = models.UserInfo.objects.filter(id__lt=3)
res = models.UserInfo.objects.filter(id__lte=3)

15. and操作

res =  models.UserInfo.objects.filter(id=1, name='zekai')

16.or操作(导入Q)

from django.db.models import Q
    # res = models.UserInfo.objects.filter( Q(Q(id=1) | Q(name='zekai')) & Q(name='xxxx')  )

17. 在原来的基础上更新值(导入F)

from django.db.models import F
models.UserInfo.objects.update(age = F('age') + 1)

18.原生sql

from django.db import  connection
cursor = connection.cursor()
cursor.execute("select * from app01_userinfo where id=%s", [1,])
res = cursor.fetchall()
res = cursor.fetchone()

19. 原生sql

models.UserInfo.objects.raw('select * from app01_userinfo')
 models.UserInfo.objects.filter(id=12).delete()
models.UserType.objects.filter(id=2).delete()

多条操作

e.增
### 插入一条数据
models.xxx.objects.create(name='xxx')
			
### 插入多条数据 
obj = [
	models.UserInfo(name='zekai', age=12,  ut_id=2),
	models.UserInfo(name='xxxxx', age=13,  ut_id=2),
	models.UserInfo(name='dsadsa', age=14,  ut_id=1),
	models.UserInfo(name='gfdgfdg', age=24,  ut_id=2),
	models.UserInfo(name='tretre', age=45,  ut_id=3),
	models.UserInfo(name='gfdgfd', age=42,  ut_id=2),
	]
	models.UserInfo.objects.bulk_create(obj)

f.删
	models.xxxx.objects.all().delete()
	models.xxxx.objects.filter(name='kkk').delete()
			
g.改
models.userinfo.objects.filter(xxxx).update()

级联删除

ut = models.ForeignKey("UserType", null=True, on_delete=models.CASCADE)
CASCADE: 设置级联删除
SET_NULL : 取消级联删除
db_constraint=False,on_delete=models.CASCADE ：取消物理外键，django外键不取消并设置级联删除

自己建表联合唯一索引

models.py:
class Boy(models.Model):
name = models.CharField(max_length=32, null=True)

class Girl(models.Model):
nick = models.CharField(max_length=32, null=True)

class Love(models.Model):
    b = models.ForeignKey("Boy", null=True)
    g = models.ForeignKey("Girl", null=True)
							
	class Meta:
	unique_together = [
	('b',  'g')

Django建表生成第三张表·

通过 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)
views.py:
	### django
	### 添加
	obj = models.Boy.objects.filter(name='谢').first()
	# print(obj)### Boy object
	# obj.g.add(3)
	# obj.g.add(*[1,2])

	### 重置
	# obj.g.set([4])

	### 查询
	# obj = models.Boy.objects.filter(name='谢').first()
	# res = obj.g.all()
	# print(res) ## <QuerySet [<Girl: Girl object>, <Girl: Girl object>, <Girl: Girl object>]>
	# for obj  in res:
	#     print(obj.nick)

	## 删除
	obj = models.Boy.objects.filter(name='谢').first()
	obj.g.clear()

注意： ManyToManyField 只能生成两个字段（boy_id 和 girl_id）根据自己的业务逻辑去写

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>