1.ORM introduction

• An important part of MVC or MVC framework is ORM, which realizes the decoupling of data model and database. That is to say, the design of data model does not need to depend on specific database, and the database can be easily replaced by simple configuration, which greatly reduces the workload of developers and does not need to face factors. Invalid Labor Caused by Change of Database
• ORM is short for object-relation-mapping. Object Relational Mapping (ORM) is similar to him in the future, but django's ORM is not used by others independently. Although its function is more powerful than sqlalchemy, it is not used by others.
• Class object - > sql - > pymysql - > MySQL server - > disk, ORM is actually an engine to translate the grammar of class object into SQL statement, understand what ORM is, the rest is how to use orm, how to write class object relational statement.

Comparison of orm code between native sql and python

#Tables in sql                                                      
#Create table:
CREATE TABLE employee(                                     
    id INT PRIMARY KEY auto_increment ,                    
    name VARCHAR (20),                                      
    gender BIT default 1,                                  
    birthday DATA ,                                         
    department VARCHAR (20),                                
    salary DECIMAL (8,2) unsigned,                          
);

#Table records in sql                                                  

#Add a table record:                                                          
INSERT employee (name,gender,birthday,salary,department)            
VALUES   ("alex",1,"1985-12-12",8000,"Cleaning Department");               

#Query a table record:                                                           
SELECT * FROM employee WHERE age=24;                               

#Update a table record:                                                           
UPDATE employee SET birthday="1989-10-24" WHERE id=1;              

#Delete a table record:                                                          
DELETE FROM employee WHERE name="alex"                             

#Class python
class Employee(models.Model):
    id=models.AutoField(primary_key=True)
    name=models.CharField(max_length=32)
    gender=models.BooleanField()
    birthday=models.DateField()
    department=models.CharField(max_length=32)
    salary=models.DecimalField(max_digits=8,decimal_places=2)

#python class object
      #Add a table record:
          emp=Employee(name="alex",gender=True,birthday="1985-12-12",epartment="Cleaning Department")
          emp.save()
      #Query a table record:
          Employee.objects.filter(age=24)
      #Update a table record:
          Employee.objects.filter(id=1).update(birthday="1989-10-24")
      #Delete a table record:
          Employee.objects.filter(name="alex").delete()

2. Single Form Operation

2.1 create table

2.1.1 Creating Model

Create an app called book and create a model in models.py under book:

from django.db import models

# Create your models here.


class Book(models.Model):
     id=models.AutoField(primary_key=True) #If there is no primary key in the table, it will automatically generate a self-increasing primary key field called id field. orm requires that a primary key be written in each table.
     title=models.CharField(max_length=32)  #Like varchar(32), 32 characters
     state=models.BooleanField()
     pub_date=models.DateField() #This format "2018-12-12" must be saved
     price=models.DecimalField(max_digits=8,decimal_places=2) #What is the decimal_places decimal part of max_digits?
     publish=models.CharField(max_length=32)

The next step is to create the corresponding data, connect to the corresponding database, then execute the command to create tables, translate them into the corresponding sql, and then execute them in the database to create the corresponding tables. One step more is the process of translating ORM into sql, which is inefficient, but not too harmful and can be tolerated. When you can't bear it, you can write native SQL statements by yourself. Ordinary scenarios are enough. They are faster to develop, more close to application development. Another point is database upgrade or application development. Change, then you used SQL statements to write database operations before, then you need to modify all SQL statements, but if you use orm, you don't need to worry about this problem, whether you change from Mysql to oracle or from oracle to mysql, if you use ORM to do it, you just need to modify the engine of orm.( Just change some configurations in the configuration file and you'll get it done.) All the ORM statements you wrote before will still be translated into SQL statements for the corresponding database automatically.

Brief mention of sqlite database: (ignore first)

2.1.2 More fields and parameters

Each field has some unique parameters, for example, CharField needs the max_length parameter to specify the size of the VARCHAR database field. There are also general parameters for all fields. These parameters are defined in detail in the document. Here we will briefly introduce some of the most commonly used:

More fields:

'''
<1> CharField
        String field for shorter strings.
        CharField requires a parameter maxlength to limit the maximum number of characters allowed in the field from the database layer and the Django checkout layer.
 
<2> IntegerField
       # Used to save an integer.
 
<3> DecimalField
        A floating point number. Two parameters must be provided:
         
        Parameter description
        Total number of max_digits (excluding decimal points and symbols)
        decimal_places decimal digits
                For example, to save a maximum value of 999 (two decimal places after the decimal point), you need to define fields like this:
                 
                models.DecimalField(..., max_digits=5, decimal_places=2)
                To save a maximum of one million decimal points (10 digits after decimal points), you need to define as follows:
                 
                Model. DecimalField (..., max_digits = 17, decimal_places = 10) #max_digits greater than or equal to 17 can store more than a million numbers
                admin uses a text box (<input type="text">) to represent the data saved in this field.
 
<4> AutoField
        An IntegerField, which automatically grows when records are added. You usually don't need to use this field directly.
        Customize a primary key: my_id=models.AutoField(primary_key=True)
        If you do not specify the primary key, the system will automatically add a primary key field to your model.
 
<5> BooleanField
        A true/false field. admin uses checkbox to represent such fields.
 
<6> TextField
        A large text field.
        admin uses a < textarea > (text area) to represent the field data. (a multi-line edit box).
 
<7> EmailField
        A CharField that checks the validity of an Email does not accept the maxlength parameter.
 
<8> DateField
        A date field. There are the following additional optional parameters:
        Argument description
        When an object is saved (update or add), auto_now automatically sets the value of the field to the current time. Usually used to represent the "last-modified" timestamp.
        auto_now_add automatically sets the value of this field to the current time when the object is first created. Usually used to indicate the time of object creation.
        (Only meaningful in admin...)
 
<9> DateTimeField
         A date-time field. Similar to DateField supports the same additional options.
 
<10> ImageField
        FileField-like, but to verify whether the uploaded object is a legitimate image. #It has two optional parameters: height_field and width_field.
        If these two parameters are provided, the picture will be saved according to the height and width specifications provided.    
<11> FileField
     A file upload field.
     Require a required parameter: upload_to, a local file system path for saving uploaded files. This path must contain strftime formatting.
     The format will be uploaded to date/time of the file
     So that uploaded files don't fill up the given directory.
     admin uses a <input type="file"> widget to represent the data stored in the field (a file upload widget).
 
     Note: Using FileField or ImageField in a model requires the following steps:
            (1) In your settings file, define a complete path to MEDIA_ROOT so that Django can save the upload file here.
            (For performance reasons, these files are not saved to the database.) Define MEDIA_URL as the public URL of the directory. Make sure that the directory pairs
             WEB server user account is writable.
            (2) Add FileField or ImageField to your model and make sure that the upload_to option is defined to tell Django
             Which subdirectory of MEDIA_ROOT is used to save uploaded files. All you need to save in your database is the path of the file (relative to MEDIA_ROOT).
             You'd love to use Django's get < # field name > URL function out of habit. For example, if your ImageField
             Called mug_shot, you can get the absolute path of the image in the template by {object.  get_mug_shot_url}}.
 
<12> URLField
      Used to save URLs. If the verify_exists parameter is True (default), a given URL checks in advance whether it exists (that is, whether the URL is validly loaded and
      No 404 response was returned).
      admin uses a < input type= "text"> text box to represent the data saved by the field (a single line edit box)
 
<13> NullBooleanField
       Similar to Boolean Field, but NULL is allowed as one of the options. It is recommended to use this field instead of using Boolean Field plus null=True option.
       admin represents this field data with a selection box < Select > (three optional values: "Unknown", "Yes" and "No").
 
<14> SlugField
       slug is a newspaper term. slug is a small tag (short tag) for something that contains only letters, numbers, underscores and hyphens.  They are usually used for URLs.
       If you use the Django development version, you can specify maxlength. If MaxLength is not specified, Django will use the default length: 50.  in
       In previous Django versions, there was no way to change the length of 50.
       This implies db_index=True.
       It accepts an additional parameter: prepopulate_from, which is a list of fields from which to auto-#populate
       the slug, via JavaScript,in the object's admin form: models.SlugField
       (prepopulate_from=("pre_name", "name"))prepopulate_from does not accept DateTime Fields.
 
<13> XMLField
        Whether a validation value is a TextField of legitimate XML must provide a parameter: schema_path, which is a file system path of RelexNG schema# used to validate text.
 
<14> FilePathField
        Optional items are file names in a particular directory. Three special parameters are supported, the first of which must be provided.
        Parameter description
        The path required parameter. The absolute file system path of a directory. FilePathField gets the optional items accordingly.
        Example: "/home/images".
        A regular expression, as a string, FilePathField will use it to filter filenames. 
        Note that this regular expression only applies to base filename, not to base filename.
        Example: "foo. * TXT ^" matches the file foo23.txt but does not match bar.txt or foo23.gif.
        The recursive parameter is optional. Either True or False. The default value is False. Does it include all subdirectories under path?
        These three parameters can be used simultaneously.
        match applies only to base filename, not to the full path name.
        FilePathField(path="/home/images", match="foo.*", recursive=True)
        ... matches / home/images/foo.gif instead of / home/images/foo/bar.gif
 
<15> IPAddressField
        A string IP address, (i.e. "24.124.1.30").
<16> CommaSeparatedIntegerField
        For storing comma-separated integer values. Similar to CharField, you must have the maxlength parameter.
 
'''　　

More parameters:

(1)null
 
If True, Django will use NULL to store null values in the database. The default value is False.
 
(1)blank
 
If True, this field is not allowed to fill in. The default is False.
Note that this is different from null. Null is purely a database category, while blank is a data validation category.
If blank=True for a field, form validation will allow that field to be null. If the field blank=False, the field is mandatory.
 
(2)default
 
Default values for fields. It can be a value or a callable object. If callable, it will be called whenever a new object is created. If your field is not set to be empty, then if we add a field later, the field will be given a default value.
 
(3)primary_key
 
If True, then this field is the primary key of the model. If you do not specify primary_key=True for any field,
Django automatically adds an IntegerField field as the primary key, so unless you want to override the default primary key behavior,
Otherwise, there is no need to set primary_key=True for any field.
 
(4)unique
 
If the value is set to True, the value of the data field must be unique throughout the table
 
(5)choices
 An iterative object (for example, a list or tuple) consisting of two tuples is used to provide fields with options. If choices are set, the default form will be a selection box rather than a standard text box, <br> and the option of this selection box is the option in choices.
(6)db_index
 If db_index=True, it means setting a database index for this field.

DatetimeField, DateField, TimeField, these three time fields, can set the following properties.

(7)auto_now_add
    Configure auto_now_add=True to add the current time to the database when creating data records.

(8)auto_now
    Configure auto_now=True, which updates the field each time the data record is updated, indicating the last modification time of the record.

What you need to know about auto_now

When updating time is needed, we try to create the current time through the datetime module, and save or update it to the database. See the following analysis:
If our table structure is like this

class User(models.Model):
    username = models.CharField(max_length=255, unique=True, verbose_name='username')
    is_active = models.BooleanField(default=False, verbose_name='active state')

Then we can use the following two methods to modify the user name and status:

Method 1:

User.objects.filter(id=1).update(username='nick',is_active=True)

Method two:

_t = User.objects.get(id=1)
_t.username='nick'
_t.is_active=True
_t.save()

Method 1 is suitable for updating a batch of data, similar to the mysql statement update user set username ='nick'where id = 1

Method 2 is suitable for updating one data or only one data. It is recommended to use this method when there is only one data update. In addition, this method has another advantage. Let's go on to look at it.

Updates with auto_now attribute fields
 We usually add three default fields to the table 
- This django has been added by default, just like the table-building statement above. Although only the fields of username and is_activity are written, there will be a default self-increasing ID field after the table is built. 
- Creation time, which identifies the creation time of this record, has the auto_now_add attribute, which automatically fills in the current time to this field when creating a record 
- Modification time, which identifies the last modification time of this record, has the auto_now attribute, filling the current time to this field when the record changes

Table structures like the ones below

class User(models.Model):
    Create_time = models. DateTime Field (auto_now_add = True, verbose_name='create time')
    Update_time = models. DateTime Field (auto_now = True, verbose_name='update time')
    username = models.CharField(max_length=255, unique=True, verbose_name='username')
    is_active = models.BooleanField(default=False, verbose_name='active state')

When a table has a field with the auto_now attribute and you want it to be updated automatically, you must use the updating method 2 above, otherwise the auto_now field will not be updated, that is:

_t = User.objects.get(id=1)
_t.username='nick'
_t.is_active=True
_t.save()

json/dict type data update field
 At present, the mainstream web open mode pays attention to front-end and back-end separation. After separation, the data format of front-end and back-end interaction mostly uses the common jason type. So how to update the json format data to the database with the least code conveniently? The following two methods can also be used:

Method 1:

data = {'username':'nick','is_active':'0'}
User.objects.filter(id=1).update(**data)

Similarly, this method does not automatically update the values of fields with auto_now attributes
 Usually we add an asterisk (*) before the variable to indicate that the variable is a tuple / list, and two asterisks to indicate that the parameter is a dictionary.
Method two:

data = {'username':'nick','is_active':'0'}
_t = User.objects.get(id=1)
_t.__dict__.update(**data)
_t.save()

Method 2, together with the method, cannot automatically update the value of the auto_now field
 Note that a _dict_ method is used here
 Method three:

_t = User.objects.get(id=1)
_t.role=Role.objects.get(id=3)
_t.save()

# It is inconvenient to use save method to update data automatically when auto_now updates data, so we'd better not use auto_now method when creating or updating time automatically. It's disgusting and supports us to update time for this field:
models.py:
class Book(models.Model):
    name = models.CharField(max_length=32)
    date1 = models.DateTimeField(auto_now=True,null=True)
    date2 = models.DateTimeField(auto_now_add=True,null=True)

views.py:
        import datetime
        models.Book.objects.filter(id=1).update(
            name='chao',
            date1=datetime.datetime.now(),
            date2=datetime.datetime.now(),
        )

2.1.3 settings configuration

To convert the model to a table in the mysql database, you need to configure it in settings:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME':'bms',     　　 　  # To connect to a database, you need to create it before connecting to it.
        'USER':'root',　　　　　　  # User Name for Connecting to Database
        'PASSWORD':'',　　　　　　  # Password to connect to database
        'HOST':'127.0.0.1',       # Connect host, default level
        'PORT': 3306    　　　     #  Port default 3306
    }
}

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME':'bms',     　　 　  # To connect to a database, you need to create it before connecting to it.
        'USER':'root',　　　　　　  # User Name for Connecting to Database
        'PASSWORD':'',　　　　　　  # Password to connect to database
        'HOST':'127.0.0.1',       # Connect host, default level
        'PORT': 3306    　　　     #  Port default 3306
    }，
    'app01': { #You can configure your own data for each app, and the database can also specify something else, that is, not necessarily mysql, but sqlite and other databases.
        'ENGINE': 'django.db.backends.mysql',
        'NAME':'bms',     　　 　  # To connect to a database, you need to create it before connecting to it.
        'USER':'root',　　　　　　  # User Name for Connecting to Database
        'PASSWORD':'',　　　　　　  # Password to connect to database
        'HOST':'127.0.0.1',       # Connect host, default level
        'PORT': 3306    　　　     #  Port default 3306
    }
}

Note 1: NAME is the name of the database. The database must be created before mysql connection, while db.sqlite3 under the sqlite database above is the user name and PASSWORD of the project automatically creating USER and PASSSWORD respectively. After setting up, we need to activate our mysql before starting our Django project. Then, start the project and report an error: no module named MySQLdb. This is because Django defaults that the driver you imported is MySQLdb, but MySQLdb has a big problem with py3, so the driver we need is PyMySQL, so we just need to find the _init_ under the project name file and write in it:.

import pymysql
pymysql.install_as_MySQLdb()

Finally, tables can be created in the specified database by two database migration commands:

python manage.py makemigrations  #Generate records, and each time you modify the contents of the models or add a new app, the new app writes the contents of the models, you need to execute these two
python manage.py migrate         #The record of executing the above statement is used to create the table. The name of the generated table is preceded by the name of the application. For example, your book table is called app01_book table in mysql.

On the Simple Principle of Synchronization Instruction Execution:

    When python manager.py magrations are executed, django generates a python script file under the migration folder of the corresponding app 
    django generates database tables only when python manager.py migrte is executed. How does django generate database tables?
    django generates data tables based on script files under migration
    There are many scripts under each migration folder, so how does Django know which file to execute? Django has a django-migrations table, which records the scripts that have been executed. If there are no scripts in the table, then only those that are not recorded in the table will be executed when migrate is executed. Script.
    Sometimes when migrate is executed, if the corresponding table is not generated, you can look in the django-migrations table to see if the script has been executed.
    You can delete the records in the django-migrations table and the corresponding tables in the database, and then re-execute them.

Execute management.py-related instructions through the functions provided by pycharm:

Add some data to the previous field, then add a field, and then execute the above two instructions to see the effect.

Note 2: Make sure that the name of the app we created is written in INSTALLED_APPS in the configuration file

INSTALLED_APPS = [
    'django.contrib.admin',  #This is the configuration of some special functions provided by django to you (application, but we can't see it), and it's also configured here. If you cancel these functions, we will not generate tables that come with django when we execute synchronous database instructions. Because when executing database synchronization statements, django will look for all the applications in it and find their models to create tables.
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    "book"  #Write the name of app directly, or'app01.apps.App01Config'.
]

Note 3: If an error is reported as follows:

django.core.exceptions.ImproperlyConfigured: mysqlclient 1.3.3 or newer is required; you have 0.7.11.None

MySQLclient currently supports only Python 3.4, so if you use a higher version of python, you need to modify it as follows:

Find the path C: Programs Python Python 36-32 Lib site-packages Django-2.0-py3.6. egg django ends db backsql MySQL
Files in this path

if version < (1, 3, 3):
     raise ImproperlyConfigured("mysqlclient 1.3.3 or newer is required; you have %s" % Database.__version__)

The comment is OK.

Note 4: If you want to print sql in the orm conversion process, you need to configure it in settings as follows: (learn how to configure statements with additional records in the past)

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console':{
            'level':'DEBUG',
            'class':'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'propagate': True,
            'level':'DEBUG',
        },
    }
}　　

There's another way to view sql statements

from app01 import models

def add_book(request):
    '''
    //Add table records
    :param request: http Request information
    :return:
    '''
    book_obj = models.Book(title='python',price=123,pub_date='2012-12-12',publish='People's Publishing House')
    book_obj.save()
    from django.db import connection  #In this way, you can also view the sql statements executed
    print(connection.queries)
    return HttpResponse('ok')

2.1.4 Custom Fields (Understanding)

class UnsignedIntegerField(models.IntegerField):
    def db_type(self, connection):
        return 'integer UNSIGNED'

Custom char type fields:

class FixedCharField(models.Field):
    """
    //Field class of custom char type
    """
    def __init__(self, max_length, *args, **kwargs):
        super().__init__(max_length=max_length, *args, **kwargs)
        self.length = max_length

    def db_type(self, connection):
        """
        //Restrict the field type of the generated database table to char and the length to the value specified by length
        """
        return 'char(%s)' % self.length


class Class(models.Model):
    id = models.AutoField(primary_key=True)
    title = models.CharField(max_length=25)
    # Use the fields of the char type defined above
    cname = FixedCharField(max_length=25)

Create a table structure:

The corresponding relationship between the ORM field and the actual field of the database is attached:

'AutoField': 'integer AUTO_INCREMENT',
    'BigAutoField': 'bigint AUTO_INCREMENT',
    'BinaryField': 'longblob',
    'BooleanField': 'bool',
    'CharField': 'varchar(%(max_length)s)',
    'CommaSeparatedIntegerField': 'varchar(%(max_length)s)',
    'DateField': 'date',
    'DateTimeField': 'datetime',
    'DecimalField': 'numeric(%(max_digits)s, %(decimal_places)s)',
    'DurationField': 'bigint',
    'FileField': 'varchar(%(max_length)s)',
    'FilePathField': 'varchar(%(max_length)s)',
    'FloatField': 'double precision',
    'IntegerField': 'integer',
    'BigIntegerField': 'bigint',
    'IPAddressField': 'char(15)',
    'GenericIPAddressField': 'char(39)',
    'NullBooleanField': 'bool',
    'OneToOneField': 'integer',
    'PositiveIntegerField': 'integer UNSIGNED',
    'PositiveSmallIntegerField': 'smallint UNSIGNED',
    'SlugField': 'varchar(%(max_length)s)',
    'SmallIntegerField': 'smallint',
    'TextField': 'longtext',
    'TimeField': 'time',
    'UUIDField': 'char(32)',　

2.2 Add Table Records

In python, there are three corresponding relationships of orm:

Class - ------> Table

Class objects - ------> rows (records)

Class attributes - ------> fields of tables (emphasis)

First of all, if you want to add, delete and modify the operation table, you need to import the table.

#Import the tables you want to manipulate into the logical code
from app01 import models

def add_book(request):
    '''
    //Add table records
    :param request: http Request information
    :return:
    '''
    models.Book(title='python',price=123,pub_date='2012-12-12',publish='People's Publishing House') #pub_date=datetime.datetime.now(), and this field can also be used directly to date and time type data.

2.2.1 mode 1

book_obj=Book(title="python Sunflower collection",state=True,price=100,publish="Apple Publishing House",pub_date="2012-12-12") #Instantiate an object to represent a row of records. If only date is written, the default time is 00.00.00. Note that the date must be written in 2012-12-12 format.
book_obj.save() #That commit Submission from pymysql

2.2.2 Mode 2 (used more)

# The return value book_obj of the create method is the python sunflower book record object inserted into the book table
  Book_obj = Book.objects. create (title = Python sunflower treasure book), state=True,price=100,publish = Apple Press, pub_date="2012-12-12") Like a manager of a Book table, it provides a way to add, delete, and check all print(book_obj.title)# based on this object to get the attribute value dic1 = {title':'linux','state'= True,'price': 100,'publish'='2018-12'} of the new ly added record object. To request, there is a csrf_token key pair to delete, and request.POST can not be directly modified and deleted in request.POST. data = request.POST.dict() is converted into a general dictionary - > Book.objects. create (** data) Book.objects. create (** dic1)

2.2.3 Mode 3: Batch insertion

    book_list = []
    for i in range(10):
        bk_obj = models.Book(
            name='chao%s'%i,
            addr='Beijing%s'%i
        )
        book_list.append(bk_obj)

    models.Book.objects.bulk_create(book_list) #Batch insertion, fast speed

update_or_create: Update if you have, create if you haven't, and get_or_create, query if you have, create if you haven't.

obj,created = models.UserToken.objects.update_or_create(
    user=user, # Find screening conditions
    defaults={ # Adding or updating data
　　　　　　"token":random_str,
　　　　}
    )    

2.3 Query Table Records

Remember that table class. Objects is like a manager, which provides the method of adding, deleting, and checking. Book.objects.all() gets all the books and inquires who calls the following methods here.

2.3.1 Query API (both focus)

< 1 > all (): Query all results, and the result is queryset type
  
<2> filter (** kwargs): It contains objects that match the given filter conditions, and the result is also a queryset type Book.objects.filter(title='linux',price=100), where multiple conditions are separated by commas, and these conditions must be valid. It is the relationship of and, or, which we will learn later, directly here. Writing is indecisive.
  
<3> get (** kwargs): Returns an object that matches the given filter condition, not a queryset type, but a row record object with one and only one result.
                            Errors are thrown if more than one or none of the filtered objects meet the criteria. Catch exception try. Book.objects.get(id=1)
  
<4> exclude (** kwargs): Exclusion means that it contains objects that do not match the given filter conditions, and no operation is not unequal. With this exclude, the return value is Book.objects.exclude(id=6) of the queryset type, returning all objects whose ID is not equal to 6, or calling them on the basis of queryset, book. objects. all (). E. Xclude (id=6)
 　　　　　　　　　　　　　　　　
<5> order_by(*field): Data invocation of queryset type, sorting query results by default in ascending order by id, return value or queryset type
 Model. Book. objects. all (). order_by('price','id') # directly writes price. By default, order_by('price','id') is arranged in ascending order according to price, and in descending order according to field. Then order_by('price','id') is arranged in multi-condition order, and the price phase is ascended in ascending order according to price. The same data, in ascending order according to ID
        
<6> reverse (): Data invocation of queryset type, reverse sort of query results, return value or queryset type
  
<7> count (): Data call of queryset type returns the number of objects matching query (QuerySet) in the database.
  
< 8 > first (): Data call of queryset type returns the first record Book.objects.all()[0] = Book.objects.all().first(), all of which are model objects, not queryset objects.
  
<9> last (): Data call of queryset type returns the last record
  
<10> exists (): Data call of queryset type, if QuerySet contains data, return True, otherwise return False
 Empty queryset data also has Boolean True and False values, but it is not usually used to judge whether there is data in the database. If there is a large amount of data, you use it to judge, then you need to query all the data, which is too inefficient, using count or exits.
Example: all_books = models.Book.objects.all().exists()# translated into sql is SELECT (1) AS `a `FROM `app01_book `LIMIT 1, that is, through limit 1, take one to see if there is data.

<11> values (* field): More frequently used, queryset type data calls, returning a ValueQuerySet - a special QuerySet, run after not a series of
                            The instantiated object of model is an iterative dictionary sequence. As long as it is the queryset type returned, it can continue to call other queryset type lookup methods in a chain. The same is true for other methods.
<12> values_list(* field): It is very similar to values(), it returns a tuple sequence, and values returns a dictionary sequence.
 
<13> distinct (): Data calls of queryset type obtained by values and values_list, eliminating duplicate records from the returned results

Queyset method Daquan:

##################################################################
# PUBLIC METHODS THAT ALTER ATTRIBUTES AND RETURN A NEW QUERYSET #
##################################################################

def all(self)
    # Get all data objects

def filter(self, *args, **kwargs)
    # Conditional query
    # Conditions can be: parameters, dictionary, Q

def exclude(self, *args, **kwargs)
    # Conditional query
    # Conditions can be: parameters, dictionary, Q

def select_related(self, *fields)
    //Performance-related: join tables between tables to get associated data at one time.

    //Conclusion:
    1. select_related One-to-one and many-to-one relationships are optimized.
    2. select_related Use SQL Of JOIN Statements are optimized by reducing SQL The number of queries to optimize and improve performance.

def prefetch_related(self, *lookups)
    //Performance-related: Multi-table table table join operation will be slow, using it to execute multiple SQL queries in Python code to achieve table join operation.

    //Conclusion:
    1. For many-to-many fields( ManyToManyField)And a pair of multi-fields, you can use prefetch_related()To optimize.
    2. prefetch_related()The optimization is to query each table separately and then use Python Deal with their relationship.

def annotate(self, *args, **kwargs)
    # Used to implement aggregated group by queries

    from django.db.models import Count, Avg, Max, Min, Sum

    v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id'))
    # SELECT u_id, COUNT(ui) AS `uid` FROM UserInfo GROUP BY u_id

    v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id')).filter(uid__gt=1)
    # SELECT u_id, COUNT(ui_id) AS `uid` FROM UserInfo GROUP BY u_id having count(u_id) > 1

    v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id',distinct=True)).filter(uid__gt=1)
    # SELECT u_id, COUNT( DISTINCT ui_id) AS `uid` FROM UserInfo GROUP BY u_id having count(u_id) > 1

def distinct(self, *field_names)
    # Used for distinct weight removal
    models.UserInfo.objects.values('nid').distinct()
    # select distinct nid from userinfo

    //Note: Only in PostgreSQL can distinct be used for de-duplication

def order_by(self, *field_names)
    # Used for sorting
    models.UserInfo.objects.all().order_by('-id','age')

def extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None)
    # Construct additional query conditions or mappings, such as: sub-queries

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

 def reverse(self):
    # Reverse order
    models.UserInfo.objects.all().order_by('-nid').reverse()
    # Note: If order_by exists, reverse is inverted, and if multiple orders are inverted one by one.


 def defer(self, *fields):
    models.UserInfo.objects.defer('username','id')
    //or
    models.UserInfo.objects.filter(...).defer('username','id')
    #Exclude a column of data in a map

 def only(self, *fields):
    #Take only data from a table
     models.UserInfo.objects.only('username','id')
     //or
     models.UserInfo.objects.filter(...).only('username','id')

 def using(self, alias):
     //Specify the database to be used with an alias parameter (settings in settings)


##################################################
# PUBLIC METHODS THAT RETURN A QUERYSET SUBCLASS #
##################################################

def raw(self, raw_query, params=None, translations=None, using=None):
    # Execute native SQL
    models.UserInfo.objects.raw('select * from userinfo')

    # If SQL is another table, the name must be set to the primary key column name of the current UserInfo object
    models.UserInfo.objects.raw('select id as nid from Other tables')

    # Setting parameters for native SQL
    models.UserInfo.objects.raw('select id as nid from userinfo where nid>%s', params=[12,])

    # Converts the acquired column name to the specified column name
    name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'}
    Person.objects.raw('SELECT * FROM some_other_table', translations=name_map)

    # Specify a database
    models.UserInfo.objects.raw('select * from userinfo', using="default")

    ################### Native SQL ###################
    from django.db import connection, connections
    cursor = connection.cursor()  # cursor = connections['default'].cursor()
    cursor.execute("""SELECT * from auth_user where id = %s""", [1])
    row = cursor.fetchone() # fetchall()/fetchmany(..)


def values(self, *fields):
    # Get each row of data in dictionary format

def values_list(self, *fields, **kwargs):
    # Get each row of data as a meta-ancestor

def dates(self, field_name, kind, order='ASC'):
    # Re-locate and intercept specified content according to time
    # kind can only be "year", "month", "day" (year-month-day).
    # order can only be "ASC" and "DESC"
    # And get the time after conversion
        - year : year-01-01
        - month: year-month-01
        - day  : year-month-day

    models.DatePlus.objects.dates('ctime','day','DESC')

def datetimes(self, field_name, kind, order='ASC', tzinfo=None):
    # To retrieve and intercept the specified content according to a part of the time, and convert the time to the specified time zone
    # kind can only be "year", "month", "day", "hour", "minute" and "second".
    # order can only be "ASC" and "DESC"
    # tzinfo time zone object
    models.DDD.objects.datetimes('ctime','hour',tzinfo=pytz.UTC)
    models.DDD.objects.datetimes('ctime','hour',tzinfo=pytz.timezone('Asia/Shanghai'))

    """
    pip3 install pytz
    import pytz
    pytz.all_timezones
    pytz.timezone('Asia/Shanghai')
    """

def none(self):
    # Empty QuerySet object


####################################
# METHODS THAT DO DATABASE QUERIES #
####################################

def aggregate(self, *args, **kwargs):
   # Aggregation function to get dictionary type aggregation results
   from django.db.models import Count, Avg, Max, Min, Sum
   result = models.UserInfo.objects.aggregate(k=Count('u_id', distinct=True), n=Count('nid'))
   ===> {'k': 3, 'n': 4}

def count(self):
   # Get number

def get(self, *args, **kwargs):
   # Getting a single object

def create(self, **kwargs):
   # create object

def bulk_create(self, objs, batch_size=None):
    # Batch insertion
    # batch_size denotes the number of inserts at a time
    objs = [
        models.DDD(name='r11'),
        models.DDD(name='r22')
    ]
    models.DDD.objects.bulk_create(objs, 10)

def get_or_create(self, defaults=None, **kwargs):
    # If it exists, get it, otherwise create it
    # defaults specifies the values of other fields when created
    obj, created = models.UserInfo.objects.get_or_create(username='root1', defaults={'email': '1111111','u_id': 2, 't_id': 2})

def update_or_create(self, defaults=None, **kwargs):
    # If it exists, it is updated, otherwise, it is created
    # defaults specifies other fields when created or updated
    obj, created = models.UserInfo.objects.update_or_create(username='root1', defaults={'email': '1111111','u_id': 2, 't_id': 1})

def first(self):
   # Get the first

def last(self):
   # Get the last one

def in_bulk(self, id_list=None):
   # Search by primary key ID
   id_list = [11,21,31]
   models.DDD.objects.in_bulk(id_list)

def delete(self):
   # delete

def update(self, **kwargs):
    # To update

def exists(self):
   # Is there a result?

Examples of the usage and return results of values:

    all_books = models.Book.objects.all().values('id','title')
    print(all_books) #<QuerySet [{title':'linux','id': 6}, {title':'hello','id': 7}, {title':'linux','id': 8}, {title':'xxx','id': 9}, {title':'gogo','id': 10}>
    '''
        values What to do:
        ret = [] #queryset type
        for obj in Book.objects.all():
            temp = {  #Elements are dictionary types
                'id':obj.id,
                'title':obj.title
            }
            ret.append(temp)

    '''

Examples of values_list usage and return results

    all_books = models.Book.objects.all().values_list('id','title')
    print(all_books) #<QuerySet [(6,'linux'), (7,'hello'), (8,'linux', (9,'xxx'), (10,'gogogo')]>
    '''
        values What to do:
        ret = [] #queryset type
        for obj in Book.objects.all():
            temp = (  #Elements are meta-ancestor types
                obj.id,obj.title
            )
            ret.append(temp)

    '''

Examples of distinct usage and return results:

    # all_books = models.Book.objects.all().distinct() #This means that all fields in a record are duplicated before duplication, but we know that there are primary keys, so it is impossible to duplicate all field data.
    # all_books = models.Book.objects.all().distinct('price') #Error reporting, field name cannot be added to distinct
    # all_books = models.Book.objects.all().values('price').distinct()#<QuerySet [(Decimal('11.00'),), (Decimal('111.00'),), (Decimal('120.00'),), (Decimal('11111.00'),)]>
    all_books = models.Book.objects.all().values_list('price').distinct()#<QuerySet [{price': Decimal ('11.00')}, {price': Decimal ('111.00')}, {price': Decimal ('120.00')}, {price': Decimal ('11111.00')}> can only be used for valuse and value_list de-duplication.
　　all_books = models.Book.objects.all().values_list('title','price').distinct() #A duplicate record is a duplicate record when both title and price are duplicated at the same time.

** Print an object to display a readable value, str, models.py. Define a _str_ method in the table class.

#_ The Use of str_ Method
class MyClass:
    def __init__(self,name,age):
        self.name = name
        self.age = age
    def __str__(self):
        return self.name + '>>>' + str(self.age)

a = MyClass('chao',18)
b = MyClass('wc',20)
print(a)
print(b)

Writing of _str_ in ** models.py: **

from django.db import models

# Create your models here.

class Book(models.Model):
    id = models.AutoField(primary_key=True)
    title = models.CharField(max_length=32)
    price = models.DecimalField(max_digits=8,decimal_places=2,)
    pub_date = models.DateTimeField() #This format "2012-12-12" must be saved
    publish = models.CharField(max_length=32)
    def __str__(self): #After adding this str method, there is no need to re-execute the instructions for synchronizing the database
        return self.title #When we print the object of this class, the title value is displayed.

2.3.2 Fuzzy Query Based on Double Underlines

Book.objects.filter(price__in=[100,200,300]) #The price value is equal to any of the three objects
Book.objects.filter(price__gt=100)  #Price GTE = 100, don't write price > 100, this parameter is not supported
Book.objects.filter(price__lt=100)
Book.objects.filter(price__range=[100,200])  #sql between, greater than or equal to 100, less than or equal to 200
Book.objects.filter(title__contains="python")  #The title Value contains python's
Book.objects.filter(title__icontains="python") #Case insensitive
Book.objects.filter(title__startswith="py") #Starting with what, istartswith is case-insensitive
Book.objects.filter(pub_date__year=2012)

Date query example:

   # all_books = models.Book.objects.filter(pub_date__year=2012) #Find all the books in 2012
    # all_books = models.Book.objects.filter(pub_date__year__gt=2012)#Find all books larger than 2012
    all_books = models.Book.objects.filter(pub_date__year=2019,pub_date__month=2)#Look for all the books in January 2019. If you have a clear result, you can't find the result. It's because the time zone of mysql database is different from that of our django. Just understand. All you need to do is change USE_TZ = True in settings configuration file of Django to False, and you can find the result. In the future, this value will be changed to False, and it is because the mysql database we use will have this problem, other databases do not have this problem.

2.4 Delete table records

The caller of the delete() method can be either a model object or a queryset collection.

The delete method is delete(). When it runs, it immediately deletes the object without returning any value. For example:

model_obj.delete()

You can also delete multiple objects at once. Each QuerySet has a delete() method that deletes all objects in the QuerySet at once.

For example, the following code deletes pub_date as an Entry object in 2005:

Entry.objects.filter(pub_date__year=2005).delete()

When learning foreign keys, Django deletes objects, it imitates the behavior of the SQL constraint ON DELETE CASCADE. In other words, when deleting an object, it also deletes the foreign key objects associated with it. For example:

b = Blog.objects.get(pk=1)
# This will delete the Blog and all of its Entry objects.
b.delete()

Note that the delete() method is a method on QuerySet, but it does not apply to the Manager itself. This is a protection mechanism to avoid accidental calls to the Entry.objects.delete() method causing all records to be deleted by mistake. If you confirm that you want to delete all objects, you must explicitly call:

Entry.objects.all().delete()　

If you don't want to cascade deletion, you can set it to:

pubHouse = models.ForeignKey(to='Publisher', on_delete=models.SET_NULL, blank=True, null=True)　　　　

2.5 Amendment of Table Records

# Mode 1 Book. objects. filter (title_ start with = "py"). update (price = 120). update can only be invoked by querset type, model object can not directly invoke update method, so it is not possible to update when using get method to obtain object. The way of 2
 Book_obj = Book. objects. filter (title_ starts with = "py") book_obj. price = 100book_obj. save () is also a way to modify records, but this way will reassign all field data (not field values that need to be updated), which is inefficient, but it is also a way.

In addition, the update() method is valid for any result set (QuerySet), which means that you can update multiple records simultaneously and return an integer value representing the number of affected records.

Note: For the input tag of type='date', the value of value must be in the format of'Y-m-d', so the tag can be recognized and assigned. Therefore, the format should be changed by filtering through date.

Configuration: (Ignore this by yourself)

LANGUAGE_CODE = 'zh-hans'

TIME_ZONE = 'Asia/Shanghai'

USE_I18N = True

USE_L10N = True

USE_TZ = False
#USE_TZ = False  #It's not a cross-time-zone application. Instead of considering the time-zone problem, change this value to False. Mysql is insensitive to time-zone. When django sends data to mysql, if the value here is True, then MySQL will be forced to use UTC time. Then we store the time of entry. When you query, you will send it. Now, it's 8 hours late, that is to say, when you save time, it's changed to UTC time. The local area is Dongba District, which is 8 hours longer than UTC time.