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

1. 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()

2. 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 **

 
 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 
 '''