ORM query of python+django

In the use of python Back end development , Many people will encounter the use of native sql still django Self contained orm It is good to operate the database ？ There are many reasons for this , One of them is right orm Not familiar with , As a result, some queries may not know how to implement , Therefore, the original sql, today , Let's summarize the common orm Inquire about ：

One 、 Direct inquiry ：

1、 The return result is a list of nested objects ：

models.Classes.objects.all() # amount to select * from classes;

2、 The returned result is a list of dictionary forms ：

models.Classes.objects.values() # amount to select * from classes
models.Classes.objects.values('id','name') # amount to select id,name from classes, You can specify the fields to query

3、 The returned result is a set of tuples in the list ：

models.Classes.objects.values_list() # amount to select * from classes
models.Classes.objects.values_list('id','name') # amount to select id,name from classes, You can specify the fields to query

Two 、 Filter repeat （distinct）：

models.Classes.objects.values('name').distinct() # select distinct name from classes
models.Classes.objects.values('id','name').distinct() # take （id,name） Filter as a group

3、 ... and 、 Query criteria （where）：

models.Classes.objects.filter(id=2) # select * from classes where id = 2
models.Classes.objects.filter(id__gt=2) # select * from classes where id > 2
models.Classes.objects.filter(id__gte=2) # select * from classes where id >= 2
models.Classes.objects.filter(id__lt=2) # select * from classes where id < 2
models.Classes.objects.filter(id__lte=2) # select * from classes where id <= 2
models.Classes.objects.exclude(id=2) # select * from classes where id != 2
models.Classes.objects.values().filter(id__lte=2,name='nick') # select * from classes where id <= 2 and name ='nick'

Four 、 contain ......（in and not in）：

models.Classes.objects.filter(id__in=[2,3]) # select * from classes where id in (2,3)
models.Classes.objects.exclude(id__in=[2,3]) # select * from classes where id not in (2,3)

  5、 ... and 、 stay ...... Between （between...and...）：

models.Classes.objects.filter(id__range=[1,3]) # select * from classe where id between 1 and 3

6、 ... and 、 Fuzzy matching （like）：

models.Classes.objects.filter(name__endswith='on') # select * from classes where name like "%on"
models.Classes.objects.filter(name__iendswith='on') # select * from classes where name like "%on" Ignore case when matching
models.Classes.objects.filter(name__startswith='aaa') # select * from classes where name like "aaa%"
models.Classes.objects.filter(name__istartswith='bbb') # select * from classes where name like "bbb%" Ignore case when matching
models.Classes.objects.filter(name__contains='ccc') # select * from classes where name like "%ccc%"
models.Classes.objects.filter(name__icontains='ccc') # select * from classes where name like "%ccc%" Ignore case when matching

  7、 ... and 、 Regular expressions ：

models.Classes.objects.filter(name__regex='\w+') # Regular Expression Matching
models.Classes.objects.filter(name__iregex='\w+') # Regular Expression Matching , Ignore case

   8、 ... and 、 Statistics （count）：

models.Classes.objects.count() # select count(*) from classes
models.Classes.objects.filter(id__gt=2).count() # select count(*) from classes where id > 2

  Nine 、 Sort （order by）：

models.Classes.objects.values().order_by('age') # select * from classes order by age Ascending
models.Classes.objects.values().order_by('-age') # select * from classes order by age desc Descending
models.Classes.objects.values().order_by('age','-id') # select * from classes order by age asc,id desc According to the first age Ascending row ,age Same press id Descending order

    Ten 、 grouping （group by）：

from django.db.models import Count,Min,Max,Sum,Avg
res = models.Classes.objects.values('name').annotate(s=Sum('age')) # select name,sum(age) as s from classes group by name; Group by name , The sum of the ages of each group is calculated
res = models.Classes.objects.values('name').annotate(s=Sum('age')).filter(s__gt=30) # amount to select name,sum(age) as s from classes group by name having(s>30);

   11、 ... and 、 Pagination / How many before （limit）：

models.Classes.objects.values()[1:3] # Get the number of queries by slicing select * from classes limit 1,2

   Twelve 、 Take the last one （last）, Take the first one （first）：

models.Classes.objects.last() # The last object is returned
models.Classes.objects.first() # The first object is returned

   13、 ... and 、 Take the specified field , The list set object is returned （only）：

models.Classes.objects.only('name') # Take only name Field , But I will also check id Field

   fourteen 、 Get all fields after excluding the specified field , The list set object is returned （defer）：

models.Classes.objects.defer('name') # Don't check name Field .select id, age from classes. Designated as id When is invalid , It still shows id Field

    15、 ... and 、 perhaps （Q）：

from django.db.models import Q
models.Classes.objects.values().filter(Q(id__gte=3)|Q(name='nick')) # select * from classes where id>=3 or name='nick'
models.Classes.objects.values().filter((Q(id__gte=3)|Q(name='nick'))&Q(age=19)) # select * from classes where (id>=3 or name='nick') and age=19

   sixteen 、 Get the current value of the field （F）：

from django.db.models import F
models.Classes.objects.update(age=F('age')+1) # Add the age of each person 1

The above are some commonly used orm Inquire about , If it helps you , Please give me a compliment , thank you ！