Introduction

In the process of developing web applications with Django framework, it is inevitable that data management operations (such as add, delete, change, check) will be involved. When talking about data management operations, database management software, such as mysql, oracle, Microsoft SQL Server, and so on, will be needed.

If the application needs to manipulate data, such as storing user registration information permanently, then we need to write native sql statements in the application and then use the pymysql module to remotely manipulate the mysql database

However, writing native sql statements directly has two problems, which seriously affect the efficiency of development, as follows:

1. Execution efficiency of sql statements: Application developers spend a large part of their energy optimizing sql statements
 2. Database Migration: sql statements developed for mysql cannot be directly applied to oracle databases, once the database needs to be migrated, cross-platform issues need to be considered

To solve the above problems, django introduced the concept of ORM, which is called Object Relational Mapping, an object-relational mapping. It encapsulates pymysq on top of pymysq. For data operations, we do not need to write native SQL anymore, instead of writing classes, objects, invoking methods based on object-oriented thinking, ORM will convert/map them toNative SQL then handed over to pymysql for execution

An example of the relationship between native SQL and ORM is as follows

In this way, developers do not need to consider the optimization of native SQL, nor the migration of databases. ORM helps us optimize and support multiple databases, which greatly improves our development efficiency

Common fields and parameters in ORM

Common fields

• AutoField

Self-adding columns, which can be interpreted as ID primary key fields, note that the parameter primary_key=True must be filled in
If there are no self-adding columns in the model, a column named id is automatically created

id = models.AutoField(primary_key=True) 

• IntegerField

Shaping field, enclosed in -2147483648 to 2147483647.(Normally you don't use it to store your mobile phone number (not enough digits), but use a string directly,)

num = models.IntegerField()  # Parameters may not be specified 

• CharField

Character field, you must provide the max_length parameter, which represents the character length.

title = models.CharField(max_length=255)
# Note: CharField in Django corresponds to varchar type in MySQL database

• DecimalField

Small number field, max_digits parameter and decimal_places parameter must be provided

price = models.DecimalField(max_digits=8,decimal_places=2)

• DateField

The date field, in the date format YYYY-MM-DD, is equivalent to the datetime.date() instance in Python.

date = models.DateField(auto_now_add=True) 

• DateTimeField

Date-time field in the format YYYY-MM-DD HH:MM[:ss[.uuuuu][TZ], equivalent to the datetime.datetime() instance in Python.

Field parameters

• verbose_name

Name the field

• null

Used to indicate that a field can be null.null=True

• unique

If set to unique=True, the field must be unique in this table.

• db_index

If db_index=True, it means setting an index for this field

• default

Set default values for this field

• auto_now_add

Configure auto_now_add=True to add the current time to the database when creating a data record.

• auto_now

Configure auto_now=True, which updates the field each time a data record is updated.

Relational Fields

• ForeignKey

Foreign key types are used in ORM to represent foreign key associations, typically setting the ForeignKey field on the'many'side of the'one-to-many'.
ForeignKey can relate to other tables as well as to itself.

parameter

To set the table to be associated

publish = models.ForeignKey(to='Publish')  
# The default is a one-to-many foreign key association with the primary key field of the publish table

to_field
Setting the fields of the table to be associated
​on_delete
The behavior of the current table and its associated rows when data in the associated table is deleted.

• OneToOneField

Typically, one-to-one fields are used to extend existing fields.(Generally speaking, all information of a person is not placed in one table, simple information is one table, private information is another table, and is related by one-to-one foreign key)
ps: Can be replaced with ForeignKey(unique=True)

parameter

To set the table to be associated with.

author_detail = models.OneToOneField(to='AuthorDetail')

to_field
Set the fields to be associated.

on_delete
The behavior of the current table and its associated rows when data in the associated table is deleted.

• ManyToManyField

Many-to-many fields
Is a virtual field, not explicit in the table.
Effect:
1. Tell orm to create a third table automatically
2. Help orm query across tables
​

authors = models.ManyToManyField(to='Author') 

Break Association Multi-Table Relationships

1. Foreign key position:
One-to-many-the one with more foreign keys
One-to-one logic considerations, such as author table and author detail table, delete cascade delete details by author, delete details by author still exists, so foreign keys are recommended in detail table
Many-to-many-foreign keys in relational tables
2. ORM forward direction join table lookup:
Forward: through the foreign key field eg: author_detial_obj.author
Inverse: via the value eg:author_obj.detail of related_name

3. Joining table operation relations:
1) Author deletion, Author detail cascade - on_delete=models.CASCADE #deleted by data in related tables, corresponding data in main related tables deleted
2) Author deletion, author details are empty - null=True, on_delete=models.SET_NULL #is deleted by the data in the associated table, corresponding fields in the main associated table are empty (null)
3) Author deletion, Author details reset - default=0, on_delete=models.SET_DEFAULT #is deleted by the data in the associated table, corresponding data fields in the main associated table can be changed to 0 by default=0, which is defined in advance.
4) Author deletion, author details remain unchanged - on_delete=models.DO_NOTHING #is deleted by the data in the associated table, data in the main associated table is not processed
Note: Take the author and author details table as an example
4. Parameters for Foreign Key Associated Fields - How to Break Associations, Current Intertable Operational Relationships, Directional Query Fields

 - In a book list:
    # Foreign Key Fields of Associated Press
    publish = models.ForeignKey(
        to='Publish',
        db_constraint=False,  # The book and publish tables are related in the middle of the library, but logically
        related_name='books',  # Name defined by related_name when spanning tables in reverse
        on_delete=models.DO_NOTHING,  # Break cascade, when the publisher deletes, the corresponding book does not do anything
    )
    # Associated Author Foreign Key Field
    authors = models.ManyToManyField(
        to='Author',
        db_constraint=False,
        related_name='books',
    )
    - Author Details Table
    author = models.OneToOneField(
        to='Author',
        db_constraint=False,
        related_name='detail',
        on_delete=models.CASCADE,  # cascade
    )
//Be careful: 
    1.ManyToManyField Unable to set on_delete，OneToOneField,ForeignKey Must be set on_delete(django1.x The system cascades by default, but django2.x Must be explicit manually)
    2.OneToOneField Essentially inherited ForeignKey

Use of ORM

Step by step table creation

• models.py

class Employee(models.Model): # Must be a subclass of models.Model
    id=models.AutoField(primary_key=True)

    name=models.CharField(max_length=16)

    gender=models.BooleanField(default=1)

    birth=models.DateField()

    department=models.CharField(max_length=30)

    salary=models.DecimalField(max_digits=10,decimal_places=1)

django's orm supports multiple databases and needs to be configured in settings.py if you want to turn the above model into a table in a mysql database

• settings.py

# Delete\Comment out the original DATABASES configuration item and add the following configuration
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql', # Using mysql database
        'NAME': 'db1',          # Database to connect to
        'USER': 'root',         # Used name of linked database
        'PASSWORD': '',         # Used name of linked database                  
        'HOST': '127.0.0.1',    # ip listened by mysql service  
        'PORT': 3306,           # Port on which the mysql service listens 
        'CHARSET':'utf8', 
        'ATOMIC_REQUEST': True, #Set to True to represent all SQLs corresponding to the same http request executed in one transaction 
                                #(Either all succeeds or all fails), this is a global configuration if you want to
                                #http requests to drain water (then customize the transaction), using the non_atomic_requests modifier 
        'OPTIONS': {
            "init_command": "SET storage_engine=INNODB", #Set the storage engine for creating tables to INNODB
        }
    }
}

Before linking mysql database, the database must be created

mysql> create database db1; # The database name must correspond to the name specified in settings.py

In fact, when the Python interpreter runs the Django program, the python module of the orm underlying operational database of Django defaults to mysqldb instead of pymysql. However, for the interpreter, the module of the operational database supported by the python 2.x interpreter is mysqldb, while the module of the operational database supported by the python 3.x interpreter is pymysql. There is no doubt that our Django programs are currently runningUnder the python 3.x interpreter, we need to modify the module of django's orm default operation database to be pymysql, as follows

Ensure that the name of the app we created is added to INSTALLED_APPS in the configuration file settings.py, and that django2.x is handled differently than django1.x

# django1.x, just add our app name to the list below
INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'app01',
    # 'app02' # If there are new app s, add them in turn
]

# The Django 2.x version might help us add app s automatically, just in a different way
INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'app01.apps.App01Config', # If the default has already been added, there is no need to add it again
    # 'app02.apps.App02Config', # If there are new app s, add them in turn according to the rules
]

If you want to print sql during orm conversion, you need to configure the log in settings:

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

Finally, two database migration commands are executed from the command line to create tables in the specified database db1:

python manage.py makemigrations
python manage.py migrate

Be careful:

1. makemigrations only generates a file of database migration records, and migrate is the file that actually commits the changes to the database for execution

2. Files of database migration records are stored in the migrations folder under app01

3. Understand: Using the command python manage.py showmigrations, you can view files that did not perform a migrate

Supplement:

When using django1.x, the following error is reported

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

That's because MySQLclient currently only supports Python 3.4, and if you use a higher version of python, you need to find the file C:\Programs\Python\Python36-32\Lib\site-packages\Django-2.0-py3.6.egg\django\dbends\backends\mysqlbase.py
Files in this path (mac or linux execute pip show django at the terminal to see django installation path, find base.py)

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

When we look directly at the generated tables in the database, it is perfectly normal that the tables in the database do not conform to the orm rules. In fact, the field constraints of orm are not all reflected in the tables in the database, such as default=1, which we set for the field gender. When we look in the database, we will find that the default part of the field is null.

mysql> desc app01_employee; # Tags in the database are prefixed with app01_
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int(11)       | NO   | PRI | NULL    | auto_increment |
| name       | varchar(16)   | NO   |     | NULL    |                |
| gender     | tinyint(1)    | NO   |     | NULL    |                |
| birth      | date          | NO   |     | NULL    |                |
| department | varchar(30)   | NO   |     | NULL    |                |
| salary     | decimal(10,1) | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

Although the database does not increase the default value, when we use the orm insert value, we insert the null for the gender field entirely, and orm will convert the null to the default value according to its own constraints before submitting it to the database for execution