Django 08.14

正想查询

查询所有用户类型

res =models.UserInfo.objects.all()

for obj in res:

print(obj.username,obj.age,obj.ut.title)

反向查询:

查询所有用户的类型

res=models.UserType.objects.all()

for obj in res:

#表名小写_set

# print(obj.title,obj.userinfo_set.all())

#releate name的方式

#ut = models.ForeignKey("UserType",null=True,related_name="users")

print(obj.title,obj.users.all())

ORM用法大全

1.字段名过滤

sql="SELECT * FROM test_userinfo WHERE id > 3"

res = models.UserInfo.objects.filter(id__gt=3)

sql="SELECT * FROM test_userinfo WHERE id >= 3"

res = models.UserInfo.objects.filter(id__gte=3)

sql="SELECT * FROM test_userinfo WHERE id < 3"

res = models.UserInfo.objects.filter(id__lt=3)

sql="SELECT * FROM test_userinfo WHERE id <= 3"

res = models.UserInfo.objects.filter(id__lte=3)

sql = "SELECT * FROM test_userinfo WHERE NOT (id = 4)"

res = models.UserInfo.objects.exclude(id=4)

sql = "SELECT * FROM test_userinfo WHERE id = 2 and name = 'zekai'"

res = models.UserInfo.objects.filter(id=2,username="zekai")

2.in |not in

sql = "SELECT * FROM test_userinfo WHERE id IN(2, 4, 5)'"

res = models.UserInfo.objects.filter(id__in=[2,4,5])

sql = "SELECT * FROM test_userinfo WHERE NOT id IN(2, 4, 5)'"

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

3.between...and...

sql = "SELECT * FROM test_userinfo WHERE id BETWEEN 4 AND 8'"

res = models.UserInfo.objects.filter(id__range=[4,8])

4.like

where username like "ze%"

res = models.UserInfo.objects.filter(username__startswith="ze")

res = models.UserInfo.objects.filter(username__istartswith="zekai")

where username like "%kk"

res = models.UserInfo.objects.filter(username__endswith="kkk")

res = models.UserInfo.objects.filter(username__iendswith="jjj")

where username like "%hh%"

res = models.UserInfo.objects.filter(username__contains="hhh")

res = models.UserInfo.objects.filter(username__icontains="hhh")

可插入re查询

res = models.UserInfo.objects.filter(username__regex="^zekai$")

5.count

select count(*) from userinfo where id>3

select count(id) from userinfo where id>3

models.UserInfo.objects.filter(id__gt=3).count()

6.order by

order by id desc,age asc;

SELECT *FROMtest_userinfoORDER BYtest_userinfo.id` DESC

res = models.UserInfo.objects.all().order_by("-id","age")

7. group by

select id,sum(age) as s, username from userinfo group by username

from django.db.models import Count,Min,Max,Sum
# res = models.UserInfo.objects.values("id","username").annotate(s=Sum('age'))

select id,sum(age) as s, username from userinfo group by username having S>50;

res = models.UserInfo.objects.values("name").annotate(s=Sum("age")).filter(s__gt=50)

8,limit

limit 0,10分页

res = models.UserInfo.objects.all()[1:4]

9.last

res = models.UserInfo.objects.last()

print(res)

10.only

res = models.UserInfo.objects.only("username")

print(res)

11.defer

res = models.UserInfo.objects.defer("id")

print(res)

12. or

Q

from django.db.models import Q
# res = models.UserInfo.objects.filter(Q(Q(id__gt=3)|Q(username='zekai')) & Q(age=23))

13. F

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

14.原生的sql 类似mysql

from django.db import connection,connections

cursor = connection.cursor() #cursor = connections['default'].cursor()

cursor.execute("""select * from UserInfo where id=%s""")

row = cursor.fetchone()

print(row)

print(res.query) #查看上述代码的sql语句

return HttpResponse("ok")