Detail model query operation and query performance optimization of Django
- 2020-12-16 06:02:29
- OfStack
How do I see the execution of SQl when I do AN ORM query
(1) The lowest level of django.db.connection
python ES14en. py shell is used in django shell
>>> from django.db import connection
>>> Books.objects.all()
>>> connection.queries ## You can check the query time
[{'sql': 'SELECT "testsql_books"."id", "testsql_books"."name", "testsql_books"."author_id" FROM "testsql_books" LIMI
T 21', 'time': '0.002'}]
(2) django - extensions plug-in
pip install django-extensions
INSTALLED_APPS = (
...
'django_extensions',
...
)
Use of python manage in django shell.py shell_plus -- ES34en-ES35en (extensions enhanced)
So each query will have sql output
>>> from testsql.models import Books
>>> Books.objects.all()
SELECT "testsql_books"."id", "testsql_books"."name", "testsql_books"."author_id" FROM "testsql_books" LIMIT 21
Execution time: 0.002000s [Database: default]
<QuerySet [<Books: Books object>, <Books: Books object>, <Books: Books object>]>
ORM query operation and optimization
Basic operation
increase
models.Tb1.objects.create(c1='xx', c2='oo') increase 1 Can accept dictionary type data **kwargs
obj = models.Tb1(c1='xx', c2='oo')
obj.save()
check
models.Tb1.objects.get(id=123) # Get a single piece of data, report an error if it does not exist (not recommended)
models.Tb1.objects.all() # Access to all
models.Tb1.objects.filter(name='seven') # Gets the data for the specified condition
models.Tb1.objects.exclude(name='seven') # Gets the data for the specified condition
delete
models.Tb1.objects.filter(name='seven').delete() # Deletes the data for the specified condition
change
models.Tb1.objects.filter(name='seven').update(gender='0') # Will specify the condition of the data update, both supported **kwargs
obj = models.Tb1.objects.get(id=1)
obj.c1 = '111'
obj.save() # Modify a single piece of data
Query simple operation
Get the number
models.Tb1.objects.filter(name='seven').count()
Greater than, less than
models.Tb1.objects.filter(id__gt=1) # To obtain id Is greater than 1 The value of the
models.Tb1.objects.filter(id__gte=1) # To obtain id Greater than or equal to 1 The value of the
models.Tb1.objects.filter(id__lt=10) # To obtain id Less than 10 The value of the
models.Tb1.objects.filter(id__lte=10) # To obtain id Less than 10 The value of the
models.Tb1.objects.filter(id__lt=10, id__gt=1) # To obtain id Is greater than 1 and Less than 10 The value of the
in
models.Tb1.objects.filter(id__in=[11, 22, 33]) # To obtain id Is equal to the 11 , 22 , 33 The data of
models.Tb1.objects.exclude(id__in=[11, 22, 33]) # not in
isnull
Entry.objects.filter(pub_date__isnull=True)
contains
models.Tb1.objects.filter(name__contains="ven")
models.Tb1.objects.filter(name__icontains="ven") # icontains Case insensitive
models.Tb1.objects.exclude(name__icontains="ven")
range
models.Tb1.objects.filter(id__range=[1, 2]) # The scope of bettwen and
Other similar
startswith . istartswith, endswith, iendswith,
order by
models.Tb1.objects.filter(name='seven').order_by('id') # asc
models.Tb1.objects.filter(name='seven').order_by('-id') # desc
group by--annotate
from django.db.models import Count, Min, Max, Sum
models.Tb1.objects.filter(c1=1).values('id').annotate(c=Count('num'))
SELECT "app01_tb1"."id", COUNT("app01_tb1"."num") AS "c" FROM "app01_tb1" WHERE "app01_tb1"."c1" = 1 GROUP BY "app01_tb1"."id"
limit , offset
models.Tb1.objects.all()[10:20]
regex Regular matching, iregex Case insensitive
Entry.objects.get(title__regex=r'^(An?|The) +')
Entry.objects.get(title__iregex=r'^(an?|the) +')
date
Entry.objects.filter(pub_date__date=datetime.date(2005, 1, 1))
Entry.objects.filter(pub_date__date__gt=datetime.date(2005, 1, 1))
year
Entry.objects.filter(pub_date__year=2005)
Entry.objects.filter(pub_date__year__gte=2005)
month
Entry.objects.filter(pub_date__month=12)
Entry.objects.filter(pub_date__month__gte=6)
day
Entry.objects.filter(pub_date__day=3)
Entry.objects.filter(pub_date__day__gte=3)
week_day
Entry.objects.filter(pub_date__week_day=2)
Entry.objects.filter(pub_date__week_day__gte=2)
hour
Event.objects.filter(timestamp__hour=23)
Event.objects.filter(time__hour=5)
Event.objects.filter(timestamp__hour__gte=12)
minute
Event.objects.filter(timestamp__minute=29)
Event.objects.filter(time__minute=46)
Event.objects.filter(timestamp__minute__gte=29)
second
Event.objects.filter(timestamp__second=31)
Event.objects.filter(time__second=2)
Event.objects.filter(timestamp__second__gte=31)
Query complex operation
Reasons for using FK foreign key:
But multi-table queries slow things down, and large programs don't use foreign keys, they use single tables (constrained by code)
extra
extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None)
Entry.objects.extra(select={'new_id': "select col from sometable where othercol > %s"}, select_params=(1,))
Entry.objects.extra(where=['headline=%s'], params=['Lennon'])
Entry.objects.extra(where=["foo='a' OR bar = 'a'", "baz = 'a'"])
Entry.objects.extra(select={'new_id': "select id from tb where id > %s"}, select_params=(1,), order_by=['-nid'])
F
from django.db.models import F
models.Tb1.objects.update(num=F('num')+1)
Q
way 1 :
Q(nid__gt=10)
Q(nid=8) | Q(nid__gt=10)
Q(Q(nid=8) | Q(nid__gt=10)) & Q(caption='root')
way 2 :
con = Q()
q1 = Q()
q1.connector = 'OR'
q1.children.append(('id', 1))
q1.children.append(('id', 10))
q1.children.append(('id', 9))
q2 = Q()
q2.connector = 'OR'
q2.children.append(('c1', 1))
q2.children.append(('c1', 10))
q2.children.append(('c1', 9))
con.add(q1, 'AND')
con.add(q2, 'AND')
models.Tb1.objects.filter(con)
exclude(self, *args, **kwargs)
# Conditions of the query
# Conditions can be: parameters, dictionary, Q
select_related(self, *fields)
pip install django-extensions
0
prefetch_related(self, *lookups)
pip install django-extensions
1
annotate(self, *args, **kwargs)
pip install django-extensions
2
extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None)
# Construct additional query criteria or mappings, such as: subqueries
Entry.objects.extra(select={'new_id': "select col from sometable where othercol > %s"}, select_params=(1,))
Entry.objects.extra(where=['headline=%s'], params=['Lennon'])
Entry.objects.extra(where=["foo='a' OR bar = 'a'", "baz = 'a'"])
Entry.objects.extra(select={'new_id': "select id from tb where id > %s"}, select_params=(1,), order_by=['-nid'])
reverse(self):
pip install django-extensions
4
The following two fetches are objects, and note that fetches can fetch other fields (which will degrade performance by looking for that field again)
defer(self, *fields):
pip install django-extensions
5
only(self, *fields):
pip install django-extensions
6
Execute native SQL
pip install django-extensions
7