Django ORM Single Table Operations
Django test environment build
Note that pycharm link database need to download the corresponding driver in advance, comes with sqlite3 is not sensitive to date format data, if the subsequent business needs to use the date to assist in filtering data, then it is not recommended to use sqlite3
Build method
- Create an arbitrary py file and write a fixed configuration in the file
import os
if __name__ == "__main__":
os.environ.setdefault ("DJANGO_SETTINGS_MODULE", "day06.settings" )
import django
django.setup()
- Use the
python console
provided by pycharm directly
ORM Create a table
# Mysql configuration
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
' NAME': 'orm_simple',
'HOST ': '127.0.0.1',
'PORT ': 3306,
'USER': 'root',
' PASSWORD ': 'root',
'CHARSET':'utf8'
}
}
'''__init__.py'''
import pymysql
pymysql.install_as_MySQLdb()
Create the table
from django.db import models
class Book ( models. Model ):
title = models. CharField ( max_length=32 )
price = models. decimalField ( max_digits=5, decimal_places=2 )
publish = models. CharField ( max_length=32 )
publish_data = models. DateField () # publishing time
# Routing
from django.contrib import admin
from django.urls import path
from app01 import views
urlpatterns = [
path ('admin/', admin.site.urls),
path ('book/', views.book),
]
ORM Adding Data
There are two ways to add data:
- Way 1: Instantiate the object by model class
- Way 2: Create through the method provided by the objects provided by ORM (recommended)
from django.shortcuts import render,HttpResponse
from app01 import models
def book(request):
'''Two ways to add data'''
# way1
book_obj = models.Book(title= 'Python', price= '99', publish= 'Hammer Press', publish_data= '2022-2-2')
book_obj.save()
# way2
models.Book.objects.create(title= 'Java', price= '88', publish= 'Hans Press', publish_data= '2021-2-1')
return HttpResponse ('<p>Data added successfully</p>')
ORM Query Data
all Query
all()
method queries all data and returns a QuerySet object, similar to a list, which can be indexed to get the objects in the list, negative indexing is not supported here
from django.shortcuts import render,HttpResponse
from app01 import models
def book(request):
book_obj = models.Book.objects.all()
print(book_obj) # <QuerySet [<Book: Book object (1)>, <Book: Book object (2)>]>
print(book_obj [0 ]) # Book object (1)
print(book_obj.first()) # Book object (1 ) is equivalent to [0]
print(book_obj.first().title) # Python get value
return HttpResponse ('<p>Data query successful</p>')
filter Query
The filter() method is used to query for data that matches the criteria
returns a QuerySet type of data, similar to a list, which contains the objects of the model class that meet the conditions, and can be used to retrieve the objects of the model class with index subscripts.
def book(request):
book_obj = models.Book.objects.filter( pk=2 )
print(book_obj)
print(book_obj.first().title) # JavaScript
book_obj1 = models.Book.objects.filter(title= 'JavaScript', price= '99')
print(book_obj1.first().pk) # 2
return HttpResponse ('<p>Data query successful</p>')
exclude Inverse Query
exclude() method is used to query the data that does not meet the conditions, the return is a QuerySet type data, similar to a list, which is placed in the model class does not meet the conditions of the object, the index subscript can be used to remove the model class objects.
def book(request):
book_obj = models.Book.objects.exclude( pk=2 )
print(book_obj) # <QuerySet [<Book: Python>, <Book: C>, <Book: PHP>, <Book: C++>, <Book: Data Structs>]>
for i in book_obj.all():
print(i.title) # Python C PHP C++ data structures
return HttpResponse ('<p>Data query successful</p>')
get Query
The get() method is used to query for a qualified object of the returned model class. The qualified object can only be one, and if more than one or none of the objects meet the filter criteria, an error is thrown
The difference between filter() if you do not get the eligible data, return an empty object, no error
from django.shortcuts import render,HttpResponse
from app01 import models
def book(request):
book_obj = models.Book.objects.get( pk=1 )
print(book_obj)
return HttpResponse ('<p>Data query successful</p>')
order_by Sorting
order_by() method is used to sort the query results, the returned data is a QuerySet type, similar to a list, which puts the sorted model class objects, you can use the index subscript to retrieve the model class objects
- The field name of the parameter should be quoted
- descending order is to add a negative sign in front of the field -
def book(request):
# sort ascending
book_obj = models.Book.objects.order_by ('id')
for i in book_obj:
print(i.title) # Python JavaScript C PHP C++ data structures
# Sort in descending order
book_obj1 = models.Book.objects.order_by ('-id')
for i in book_obj1:
print(i.title) # Data structures C++ PHP C JavaScript Python
return HttpResponse ('<p>Data query successful</p>')
**reverse Reverse **
The reverse() method is used to reverse the query result, and returns data of type QuerySe t, similar to a list, in which the inverted model class objects are placed, and the index subscript can be used to remove the model class objects
Sort before using the reverse function
def book(request):
# Sort by price in ascending order: descend and invert
books = models.Book.objects.order_by ("-price" ).reverse()
return HttpResponse ("<p>Find Success! </p>" )
count Aggregate quantity
The count() method is used to query the number of data returned as an integer
def book(request):
book_obj = models.Book.objects.count()
print(book_obj) # Query the number of all data >>>>6
book_obj1 = models.Book.objects.filter( price=200 ).count() # Query the number of eligible data
return HttpResponse ('<p>Data query successful</p>')
exists Determination
The exists() method is used to determine if the query has data in the QuerySet list, the returned data type is boolean, true for yes, false for no.
from django.shortcuts import render,HttpResponse
from app01 import models
def book(request):
books = models.Book.objects.exists()
# books = models.
books = models.Book.objects.count().exists()
# Error, the data type can only be QuerySet type data, not integer type
books = models.Book.objects.first().exists()
# Error, the data type can only be QuerySet type data, not model class objects
return HttpResponse ("<p>Find Success! </p>" )
values Query Field
Returns QuerySet type data, similar to a list, which is not a model class object, but an iterable dictionary sequence, where the keys are fields and the values are data
def book(request):
book_obj = models.Book.objects.values ('price','title')
print(book_obj) # Return all price and title fields as a list over a dictionary
# <QuerySet [{'price': Decimal('99.00'), 'title': 'Python'}]
book_obj1 = models.Book.objects.filter( pk=1 ).values ('title')
print(book_obj1,book_obj [0 ].get ('title') ) # <QuerySet [{'title': 'Python'}]> Python
return HttpResponse ('<p>Data query successful</p>')
values_list Query partial fields
The values_list( ) method is used to query the data of a partial field. values_list() method is used to query the data of a partial field
def book(request):
book_obj = models.Book.objects.values_list ('price','title')
print(book_obj) # Return all price and title fields as a list over a tuple
# <QuerySet [(Decimal('99.00'), 'Python')]
for title in book_obj:
print(title [1 ]) # Get all titles
return HttpResponse ('<p>Data query successful</p>')
Difference between values and values_list
- values query returns a list of dictionaries, where both field names and data can be obtained.
- values_list query returns a list over a tuple, only data is returned
distinct De-duplication
The distinct() method is used to de-duplicate the data and returns the QuerySet type data.
Note that
- There is no point in de-duplicating the objects of the model class, because each object is a different existence (primary key is not the same), the premise of de-duplication is that the data must be exactly the same
- distinct() is generally used in conjunction with values or values_list
from django.shortcuts import render,HttpResponse
from app01 import models
def book(request):
# Query how many publishers there are
books = models.Book.objects.values_list ("publish" ).distingu() # can be de-duplicated
books = models.Book.objects.distingu()
# There is no point in de-duplicating the objects of the model class, because each object is a different being, with a different primary key
return HttpResponse ("<p>Find Success! </p>" )
ORM double underscore method
__in
similar to sql member operation, used to read interval, = sign followed by a list
Note: The only operation symbols you can use in the filter are equal =, not greater than >, less than <, etc.
- Format: field __in = [a,b]
def book(request):
book_obj = models.Book.objects.filter(price__in= [100,300 ])
print(book_obj) # <QuerySet [<Book: Book object (2)>, <Book: Book object (22)>]>
return HttpResponse ('<p>Data query successful</p>')
__gt __gte
Greater than, greater than or equal to
# Data with price greater than 100
book_obj = models.Book.objects.filter( price__gt=100 )
# Data with a price greater than or equal to 100
book_obj = models.Book.objects.filter( price__gte=100 )
__lt __lte
Less than, less than or equal to
# Data with a price less than 100
book_obj = models.Book.objects.filter( price__lt=100 )
# Data with a price less than or equal to 100
book_obj = models.Book.objects.filter( price__lte=100 )
__range
range, between ---, similar to sql's between--and, left-closed-right interval, = sign followed by a list of two elements
# data with prices between 100 and 200
book_obj = models.Book.objects.filter(price__range= [100,300 ])
__contains
contains relationship, = sign followed by a string, similar to sql fuzzy query, LIKE
# Query for book titles containing Py characters
book_obj = models.Book.objects.filter(title__contains= 'Py')
__icontains
case-insensitive, same as contains
# Query for book titles containing Py characters
book_obj = models.Book.objects.filter(title__contains='py' )
__startswith
query starts with the specified character, followed by the = sign
book_obj = models.Book.objects.filter(title__startswith='P' )
__endswith
The query ends with the specified character, followed by a string after the = sign
book_obj = models.Book.objects.filter(title__endswith= 'Construct')
__year
__year is the year of the DateField data type, with the = sign followed by a number
# Data published in 2022
book_obj = models.Book.objects.filter( publish_date__year=2022 )
__month
Query month
# Data published in March
book_obj = models.Book.objects.filter( publish_date__month=3 )
__day
__day is the number of days of the DateField data type, = sign followed by a number
# All data published on the 17th
book_obj = models.Book.objects.filter( publish_date__day=17 )
ORM Delete Data
Way 1: Use models.object. delete()
Return value: tuple, the first element is the number of affected rows
book_obj=models.book.objects. filter ( pk=8 ). first (). delete ()
way 2: use QuerySet type data.delete() (recommended)
Return value: tuple, the first element is the number of affected rows.
books=models.Book.objects.filter(pk__in= [1,2 ]).delete()
Caution.
- (a) When Django deletes data, it mimics the behavior of the SQL constraint ON DELETE CASCADE, which means that when it deletes an object, it also deletes the foreign key object associated with it.
- The delete() method is a method of the QuerySet data type, but does not apply to the Manager itself. That is, you cannot delete all data without writing all.
ORM Modify Data
Way I.
The object of the model class. property = the changed property value object of the model class.save()
def book(request):
book_obj = models.Book.objects.filter( pk=23 ).first()
print(book_obj)
book_obj.title= 'How to be a rich woman, from the beginning to the flock'
book_obj.save()
return HttpResponse ('<p>Successful in getting married</p>')
Way 2: QuerySet type data.update(field name=changed data) (recommended)
Return value: integer, number of rows affected
def book(request):
book_obj = models.Book.objects.filter( pk=23 ).update(title= 'People live a breath of air, not to die in the flock')
return HttpResponse(book_obj) # 1
ORM reversal to SQL
Way I.
If you want to print the sql of the orm conversion process, you need to configure the following in settings.
'''settings.py'''
LOGGING = {
'version': 1,
'disable_existing_loggers': False,
'handlers': {
'console': {
'level':'DEBUG',
'class': 'logging.StreamHandler',
},
},
'loggers': {
'django.db.backends': {
'handlers':['console'],
'propagate': True,
'level': 'DEBUG',
},
}
}
ps: the essence is the monitoring of the log, you can test under test
way two: use object.query to get the native SQL ()
from app01 import models
res = models.Book.objects.filter(pk=2)
print(res.query)
'''
SELECT `app01_book`. `id`, `app01_book`. `title`, `app01_book`. `price`, `app01_book`. `publish`, `app01_book`. `publish_date` FROM `app01_book` WHERE `app01_book`. `id` = 2
'''