Django's object relational mapper (ORM) provides rich data query interfaces, so that you can add, delete, modify and query the data in the database through simple operation of the model without using native SQL statements. The result of query is called queryset, so this interface is called QuerySet API. Today, we will take the blog as an example to demonstrate how Django can add, delete, modify and query the database through the model.
The Article model we use is as follows:
from django.db import models
class Article(models.Model):
title = models.CharField('title', max_length=200, unique=True)
body = models.TextField('text')
created = models.DateTimeField(auto_now_add=True)
def __str__(self):
return self.title
increase
For new data, Django provides two methods, save() and create().
Method 1: save method
from .models import Article article = Article(title="My first article", body="My first article body") article.save()
Note: if this method does not actively select save(), the created object instance will only be saved in memory and will not be saved to the database. Because of this, Django also provides a more convenient create method.
Method 2: create method
article = Article.objects.create(title="My first article", body="My first article body")
Django also provides get in order to avoid duplicate creation of existing entries in the data table_ or_ Create method. It will return the queried or newly created model object instance, as well as whether the object instance has just been created.
obj, created = Article.objects.get_or_create(title="My first article", body="My first article body")
Note: for the user model operation in the auth module of Django, for example, when creating a new user, please use create_user method. This method will automatically add Hash to the password and store it in the database, as shown below:
from django.contrib.auth.models import User user = User.objects.create_user(username='john, email='john@gmail.com',password='somepwd')
Method 3: bulk_create method
When multiple pieces of data are inserted into the database in Django, SQL will be executed every time one is saved using the save or create method. The bulk provided by Django_ The Create method can add multiple pieces of data to SQL at a time, which is much more efficient, as shown below:
# Generate multiple object instances in memory articles = [Article(title="title1", body="body1"), Article(title="title2", body="body2"), Article(title="title3", body="body3")] # Execute SQL insert data once Article.objects.bulk_create(articles)
Delete
Delete deletes an existing entry from the data table. Django also allows you to delete one or more pieces of data at the same time.
Delete a single piece of data
# Delete Article 5 Article.objects.get(pk=5).delete()
Delete some data
# Delete articles with python titles Article.objects.filter(title__icontains="python").delete()
Delete all data
# Use with caution Article.objects.all().delete()
change
You can use either the save method or the update method. The difference is that the save method can not only update the existing object data in the data, but also create new objects. The update method can only be used to update existing object data. Generally speaking, if you want to update multiple object data at the same time, use the update method or bulk_ The update method is more appropriate.
Method 1: save method
article = Article.objects.get(id=1) article.title = "New article title" article.save()
Method 2: update method to update a single article
article = Article.objects.get(id=1).update(title='new title')
Method 3: update method updates multiple articles at the same time
# Update all article titles article = Article.objects.filter(title__icontains='python').update(title='Django')
Method 4: bulk_update method
And bulk_ Similar to the Create method, Django also provides bulk_ The update method can batch update the data in the database.
check
Query mainly uses get, filter and exclude methods, and these methods can be used together.
Query all data
# QuerySet type, instance object list
Article.objects.all()
# Dictionary list
Article.objects.all().values()
# Only get title dictionary form
Article.objects.all().values('title')
# Only get the title list - tuple form, only value, no key
Article.objects.all().values_list('title')
# Only get the title list, only value
Article.objects.all().values_list('title', flat=True)
Query a single piece of data
article = Article.objects.get(id=11)
When there is a problem with the above query, an error will be thrown if the id does not exist. Another way is to use the filter method so that no error will be reported even if the id does not exist.
article = Article.objects.filter(id=1).first()
A better way is to use the get provided by Django_ object_ or_ 404 method, as follows:
from django.shortcuts import get_object_or_404 article = get_object_or_404(Article, pk=1)
Query multiple data
Query by greater than, less than and not equal to
# gte: greater than or equal to, lte: less than or equal to articles = Article.objects.filter(id__gte=2).filter(id__lte=11) # Not equal to articles = Article.objects.exclude(id=10)
Query by range
# Query by range, in or range articles = Article.objects.filter(id__range=[2, 11]) articles = Article.objects.filter(id__in=[3, 6,9])
String fuzzy query
#The title contains python. If case is ignored, use icontains articles = Article.objects.filter(title__contains='python') #The title starts with python. If case is ignored, use istartswitch articles = Article.objects.filter(title__startswith='python') #The title ends in python. If case is ignored, use__ iendswith articles = Article.objects.filter(title__endswith='python')
Query by date and time
# Query articles published in 2021 Article.objects.filter(created__year=2021) # Query articles published on March 19, 2021 import datetime Article.objects.filter(created__date=datetime.date(2021,3,19)) # Query articles published after January 1, 2021 Article.objects.filter(created__gt=datetime.date(2021, 1, 1)) # Query upcoming articles compared to the current time from django.utils import timezone Article.objects.filter(created__gt=timezone.now()) # Query according to the absolute time range, and query the articles published from January 1 to June 30, 2021 article = Aritlce.objects.filter(created__gte=datetime.date(2021, 1, 1), pub_date__lte=datetime.date(2021, 6, 30)) # Query by relative time range, and use range to query articles published within 30 days after March 1 startdate = datetime.date(2021, 3, 1) enddate = startdate + datetime.timedelta(days=30) Article.objects.filter(pub_date__range=[startdate, enddate])
Slicing, sorting, de duplication
# section
articles = Article.objects.filter(created__year=2021)[:5]
# Sorting: created positive order, - indicates reverse order
articles = Article.objects.all().order_by('-created')
# duplicate removal
Article.objects.filter(title__icontains='python').distinct()
Advanced Q and F methods
Q method
Sometimes we need to execute conditional query of or logic. At this time, we can use Q method, which can connect multiple query conditions. Adding ~ before the Q object can indicate negation.
from django.models import Q # Query for articles with python or Django titles article = Article.objects.filter(Q(title__icontains='python')|Q(title__icontains='django')) # The query title contains python, not Django articles article = Article.objects.filter(Q(title__icontains='python')|~Q(title__icontains='django'))
F method
Using the F() method, you can filter a group of objects based on their own field values. It also supports arithmetic operations such as addition, subtraction, multiplication, division, modulus and power operation.
from django.db.models import F
Article.objects.filter(n_commnets__gt=F('n_pingbacks'))
Article.objects.filter(n_comments__gt=F('n_pingbacks') * 2)