Djangos Model Update provides an introduction to the various Django Model update operations. This post is a companion article to djangos Model Select.

Basic operation

SQL > select * from User where User = 1
User.objects.all()

SQL: select * from User where name = '@@@'
User.objects.filter(name=Operation coffee Bar)

SQL: select * from User where name! = 'Operation coffee Bar'
User.objects.exclude(name=Operation coffee Bar)

Select * from User where id = 724; select * from User where id = 724
User.objects.get(id=123)
Copy the code

Common operations

SQL: select count(1) from User
User.objects.count()
User.objects.filter(name=Operation coffee Bar).count()

SQL: select * from User where id > 724 SQL: select * from User where id > 724
User.objects.filter(id__gt=724)
User.objects.filter(id__gt=1, id__lt=10)

SQL: select * from User where id in (11,22,33)
User.objects.filter(id__in=[11, 22, 33])
User.objects.exclude(id__in=[11, 22, 33])

SQL: select * from User where pub_date isnull SQL: select * from User where pub_date isnull
User.objects.filter(pub_date__isnull=True)

# like, contains is case sensitive, icontains is case insensitive, the same as startswith, endswith
User.objects.filter(name__contains="sre")
User.objects.exclude(name__contains="sre")

SQL: select * from User where id between 3 and 8
User.objects.filter(id__range=[3, 8])

# order by 'id', 'id', '-id', 'id
User.objects.filter(name=Operation coffee Bar).order_by('id')
User.objects.filter(name=Operation coffee Bar).order_by('-id')

# order by, order by, order by, order by, order by
User.objects.filter(name=Operation coffee Bar).order_by('name'.'-id')
Copy the code

Advanced operation

SQL: select * from User limit 3;
User.objects.all()[:3]

# limit, take the third after the data, there is no corresponding SQL, such as: select * from User limit 3100000 0, fetch the data from article 3, article 10000000, article 10000000 is greater than the data in the table)
User.objects.all()[3:]

# offset, fetch the result of the 10-20 data (excluding 10, including 20), there is no corresponding SQL, refer to the above SQL writing
User.objects.all()[10:20]

SQL: select username,count(1) from User group by username;
from django.db.models import Count
User.objects.values_list('username').annotate(Count('id'))

SQL: select distinct(username) from User
User.objects.values('username').distinct().count()

SQL: select username,fullname from accounts_user
User.objects.values_list('username'.'fullname')

Values_list normally results in a list, in which each data corresponds to a tuple. When only one column is queried, you can use the flat tag to remove the tuple and store the result of each data as a string in the list, thus avoiding the trouble of parsing the tuple
User.objects.values_list('username', flat=True)

Select Max, min, sum, average
from django.db.models import Sum,Count,Max,MinAvg User), objects, aggregate (Count (" id ")). The User objects. Aggregate (Sum (" age "))Copy the code

The time field

# match date, date
User.objects.filter(create_time__date=datetime.date(2018, 8, 1))
User.objects.filter(create_time__date__gt=datetime.date(2018, 8, 2))


# match year # match month # match day # match week_day # match hour # match minute # match second
User.objects.filter(create_time__year=2018)
User.objects.filter(create_time__year__gte=2018)

# Archive statistics by day
today = datetime.date.today()
select = {'day': connection.ops.date_trunc_sql('day'.'create_time')}
deploy_date_count = Task.objects.filter(
    create_time__range=(today - datetime.timedelta(days=7), today)
).extra(select=select).values('day').annotate(number=Count('id'))
Copy the code

The use of Q

Q object to encapsulate the key parameters, so as to better apply multiple queries, can combine & (and), | (or), ~ (not) operator.

For example, the following statement

from django.db.models import Q

User.objects.filter(
    Q(role__startswith='sre_'),
    Q(name='Public Account') | Q(name=Operation coffee Bar))Copy the code

Convert to SQL as follows:

select * from User where role like 'sre_%' and (name='Public Account' or name=Operation coffee Bar)
Copy the code

Q is more often used for search logic, such as the foreground search box to enter a character, the background to the database to check whether the title or content is included

_s = request.GET.get('search')

_t = Blog.objects.all()
if _s:
    _t = _t.filter(
        Q(title__icontains=_s) |
        Q(content__icontains=_s)
    )

return _t
Copy the code

Foreign key: ForeignKey

Table structure:

class Role(models.Model):
    name = models.CharField(max_length=16, unique=True)


class User(models.Model):
    username = models.EmailField(max_length=255, unique=True)
    role = models.ForeignKey(Role, on_delete=models.CASCADE)
Copy the code

Forward query:

# query user role name
_t = User.objects.get(username=Operation coffee Bar)
_t.role.name
Copy the code

Reverse query:

# query all users under the role
_t = Role.objects.get(name='Role03')
_t.user_set.all()
Copy the code

Another way to reverse a query:

_t = Role.objects.get(name='Role03')

This method is faster than the previous _set method
User.objects.filter(role=_t)
Copy the code

The third method of reverse query:

If the foreign key field has related_NAME, for example, models:

class User(models.Model):
    username = models.EmailField(max_length=255, unique=True)
    role = models.ForeignKey(Role, on_delete=models.CASCADE,related_name='roleUsers')
Copy the code

All users of a role can be fetched directly with the related_name attribute

_t = Role.objects.get(name = 'Role03')
_t.roleUsers.all()
Copy the code

M2M: ManyToManyField

Table structure:

class Group(models.Model):
    name = models.CharField(max_length=16, unique=True)

class User(models.Model):
    username = models.CharField(max_length=255, unique=True)
    groups = models.ManyToManyField(Group, related_name='groupUsers')
Copy the code

Forward query:

# Query user group information
_t = User.objects.get(username = Operation coffee Bar)
_t.groups.all()
Copy the code

Reverse query:

The query group contains users
_t = Group.objects.get(name = 'groupC')
_t.user_set.all()
Copy the code

If the M2M field has related_NAME, you can use the following method to reverse lookup

_t = Group.objects.get(name = 'groupC')
_t.groupUsers.all()
Copy the code

get_object_or_404

Normally, if we want to search for a piece of data in the database, we usually use the following method:

_t = User.objects.get(id=734)
Copy the code

But when the data id=724 does not exist, the program will throw an error

abcer.models.DoesNotExist: User matching query does not exist.
Copy the code

For program compatibility and exception determination, we can use the following two methods:

A:getInstead offilter

_t = User.objects.filter(id=724)
Check if _t exists after fetching _t
Copy the code

Method 2: Useget_object_or_404

from django.shortcuts import get_object_or_404

_t = get_object_or_404(User, id=724)
The # get_object_or_404 method, which first calls Djangos get method and throws an Http404 exception if the query object does not exist
Copy the code

The implementation is similar to the following:

from django.http import Http404

try:
    _t = User.objects.get(id=724)
except User.DoesNotExist:
    raise Http404
Copy the code

get_or_create

As the name implies, find an object and create it if it does not exist, as follows:

object, created = User.objects.get_or_create(username=Operation coffee Bar)

Copy the code

Returns a tuple of Object and Created, where Object is a queried or created object and created is a Boolean value indicating whether a new object has been created

The implementation looks like this:

try:
    object = User.objects.get(username=Operation coffee Bar)
    
    created = False
exception User.DoesNoExist:
    object = User(username=Operation coffee Bar)
    object.save()
    
    created = True

returen object, created
Copy the code

Execute native SQL

If Django uses ORM, use it instead. It is not recommended to execute native SQL. There may be some security issues, but if your SQL is too complex to implement ORM, check out the following methods to execute native SQL

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute('select * from accounts_User')
    row = cursor.fetchall()

return row
Copy the code

Note that the table name is app name + underscore +model name

Related articles are recommended reading

Django Model Select
Introduction to the various uses of Django Model Update
Django configures tasks with asynchronous tasks and timed tasks
Django Model to a dictionary
Django uses Signals to send notifications that detect changes in the Model field
Django+Echarts drawing example
Djangos password management table
Django+JWT implements Token authentication
Djangos integrated Markdown editor
Djangos default permissions mechanism introduction and practice
Djangos built-in permission extension example
Django integrates OpenLDAP authentication
Django uses Channels to implement WebSocket
Django implements WebSocket using Channels — part 2
Write a freehand static page generator using Django
Django+zTree builds the organizational structure tree

Django Model Select