Django model layer

Today, let's talk about Django's model layer. Django provides an abstract model ("models") layer to build and manipulate the data of Web applications.

1. The concept of model:

1. Model introduction

The model can accurately and uniquely describe the data. Contains important fields and behaviors for stored data.
In general, each model maps to a database table.

Let's define a model:

from django.db import models

class News(models.Model):
    news_title = models.CharField(max_length=20)
    news_details = models.CharField(max_length=40)

analysis:

Through the model defined above, it can be found that a model is a Python class, which inherits django.db.models.Model
Each attribute of the model class is equivalent to a database field.
When data needs to be called, it can be obtained through the API function provided by Django, and this is an API that automatically generates and accesses the database;

news_title,news_details are two fields of the database, each field is specified as a class attribute, and each attribute is mapped to a database column

If we use the sql statement to create a table, it is as follows:

CREATE TABLE myapp_news (
    "id" serial NOT NULL PRIMARY KEY,
    "first_name" varchar(30) NOT NULL,
    "last_name" varchar(30) NOT NULL
);

The name of the data table above is myapp_news is automatically derived from some model metadata, and you can rewrite it.
Let's talk about the table name:

In Django, to save you time, Django will automatically export the name of the database table from the name of your model class and the application containing it. The database table name of a model is connected by the "app label" (the name you use in manage.py startapp) of the model and the class name of the model, and underlined between them.

For example:

If you have an application news (created by manage.py startapp mynews), a model defined as class News will have a model named mynews_ Database table for news.

To override the database table name, use dB in class Meta_ Table parameter.

It doesn't matter if your database table name is a reserved word of SQL or contains characters (especially hyphens) that are not allowed in Python variable names. Django references column names and table names behind the scenes.

Use lowercase table names in MariaDB and MySQL

When you pass DB_ When table overrides table names, it is strongly recommended that you use lowercase table names, especially when you use MySQL backend.

Table name reference for Oracle

In order to meet Oracle's 30 character limit on table names and comply with Oracle database practices, Django may shorten the table names and make them all uppercase. To prevent this transition, use quoted names as dB_ Value of table:

db_table = '"name_left_in_lowercase"'

During the automatic table creation process, an id field will be automatically added, but this behavior can be overwritten (if you want to specify the primary key yourself, set the parameter primary_key=True on the field you want to set as the primary key).
The syntax for creating a data table in this example is in PostgreSQL format. It is worth noting that Django generates the corresponding SQL statements according to the database backend specified in the configuration file.

2. Field type

field option

The following parameters are valid for all field types and are optional.

null

Field.null

If null=True, Django will store null values in the database. The default is False.

Avoid using NULL on string based fields, such as CharField and TextField. If a string based field has null=True, this means that it has two possible "no data" values. NULL, and empty string. In most cases, two possible values of "no data" are redundant. Django's convention is to use empty strings instead of NULL.

An exception is when a CharField has both unique=True and blank=True set. In this case, null=True is needed to avoid violating the unique constraint when saving multiple objects with blank values.

Whether it is a string based field or a non string field, if you want to allow null values in the form, you also need to set blank=True, because the null parameter only affects the storage of the database.

annotation

When using the Oracle database backend, NULL values are stored to represent empty strings regardless of this property.

blank

Field.blank

If True, this field is allowed to be empty. The default is False.

Note that this is different from null. Null is purely database related, while blank is related to validation. If a field has blank=True, form validation will allow you to enter a null value. If a field has blank=False, the field is required.

choices

Field.choices

A sequence itself consists of iteration items of exactly two items (for example [(a, B), (a, B)...]) as the selection of this field. If choices are given, they will be enforced by model validation, and the default form part will be a selection box with these choices instead of a standard text field.

The first element in each tuple is the actual value to be set on the model, and the second element is a human readable name. For example:

YEAR_IN_SCHOOL_CHOICES = [
    ('FR', 'Freshman'),
    ('SO', 'Sophomore'),
    ('JR', 'Junior'),
    ('SR', 'Senior'),
    ('GR', 'Graduate'),
]

It is best to define the selection within the model class and define a constant with an appropriate name for each value:

from django.db import models

class Student(models.Model):
    FRESHMAN = 'FR'
    SOPHOMORE = 'SO'
    JUNIOR = 'JR'
    SENIOR = 'SR'
    GRADUATE = 'GR'
    YEAR_IN_SCHOOL_CHOICES = [
        (FRESHMAN, 'Freshman'),
        (SOPHOMORE, 'Sophomore'),
        (JUNIOR, 'Junior'),
        (SENIOR, 'Senior'),
        (GRADUATE, 'Graduate'),
    ]
    year_in_school = models.CharField(
        max_length=2,
        choices=YEAR_IN_SCHOOL_CHOICES,
        default=FRESHMAN,
    )

    def is_upperclass(self):
        return self.year_in_school in {self.JUNIOR, self.SENIOR}

You can also collect your available choices into named groups that can be used for organizational purposes:

MEDIA_CHOICES = [
    ('Audio', (
            ('vinyl', 'Vinyl'),
            ('cd', 'CD'),
        )
    ),
    ('Video', (
            ('vhs', 'VHS Tape'),
            ('dvd', 'DVD'),
        )
    ),
    ('unknown', 'Unknown'),
]

The first element in each tuple is the name applied to the group. The second element is an iteration of a binary tuple, each containing a value and a readable option name. Grouped options can be combined with ungrouped options in a single list (such as the 'unknown' option in this example).

Enumeration type

from django.utils.translation import gettext_lazy as _

class Student(models.Model):

    class YearInSchool(models.TextChoices):
        FRESHMAN = 'FR', _('Freshman')
        SOPHOMORE = 'SO', _('Sophomore')
        JUNIOR = 'JR', _('Junior')
        SENIOR = 'SR', _('Senior')
        GRADUATE = 'GR', _('Graduate')

    year_in_school = models.CharField(
        max_length=2,
        choices=YearInSchool.choices,
        default=YearInSchool.FRESHMAN,
    )

    def is_upperclass(self):
        return self.year_in_school in {
            self.YearInSchool.JUNIOR,
            self.YearInSchool.SENIOR,
        }

Choices can be defined concisely by subclassing them

db_column

Field.db_column

The name of the database column to use for this field. If no column name is given, Django will use the field name.
It doesn't matter if your database column name is a reserved word of SQL or contains characters that are not allowed in Python variable names -- especially hyphens. Django references column names and table names behind the scenes.

db_index

Field.db_index

If True, a database index is created for the field.

db_tablespace

Field.db_tablespace

If the field has an index, the name of the database tablespace to use for the index of the field. The default is the default of the project_ INDEX_ Tablespace setting (if any), or dB of the model_ Tablespace (if any). If the backend does not support indexed tablespaces, this option is ignored.

default

Field.default

The default value for this field. It can be a value or a callable object. If it is a callable object, it will be called every time the model is instantiated.

For example, if you want to specify a default dict for JSONField, use a function:

def contact_default():
    return {"email": "to1@example.com"}

contact_info = JSONField("ContactInfo", default=contact_default)

editable

Field.editable

If False, this field will not be displayed in admin or any other ModelForm. It is also skipped in model validation. The default is True.

error_messages

Field.error_messages

error_ The messages parameter allows you to override the default messages raised by this field. Pass in a dictionary of key values that match the error message you want to overwrite.

Relation field

Django also defines a set of fields that represent relationships.

ForeignKey

class ForeignKey(to, on_delete, **options)

A many to one relationship. Two positional parameters are required: model related classes and on_delete option.
To create a recursive relationship (an object that has a many to one relationship with itself), use models.ForeignKey('self ', on_delete=models.CASCADE).

If you need to create a relationship on an undefined model, you can use the name of the model instead of the model object itself:

from django.db import models

class Car(models.Model):
    manufacturer = models.ForeignKey(
        'Manufacturer',
        on_delete=models.CASCADE,
    )
    # ...

class Manufacturer(models.Model):
    # ...
    pass

3. Index

Index option

class Index(*expressions, fields=(), name=None, db_tablespace=None, opclasses=(), condition=None, include=None)

Create an index (B-tree) in the database.

expressions

Index.expressions

For example:

Index(Lower('title').desc(), 'pub_date', name='lower_title_date_idx')

fields

Index.fields

A list of names or tuples of indexed fields is required.
By default, indexes are created in ascending order for each column. To define a descending index for a column, add a hyphen before the field name.

For example, Index(fields = ['headline', 'pub_date']) will create SQL as (headline, pub_date DESC). Index sorting is not supported on MySQL. In this case, a descending index is created like a normal index.

name

Index.name

The name of the index. If no name is provided, Django will automatically generate a name. In order to be compatible with different databases, the index name cannot exceed 30 characters and should not start with a number (0-9) or an underscore ().

db_tablespace

Index.db_tablespace

The name of the database tablespace to use for this index. For a single field index, if DB is not provided_ Tablespace, DB in the field_ Create an index in a tablespace.

If field.db is not specified_ Tablespace (or if the index uses multiple fields), DB in the class Meta of the model_ Create an index for the tablespace specified in the tablespace option. If neither table space is set, the index is created in the same table space as the table.

opclasses

Index.opclasses

The name of the PostgreSQL operator class to use for this index. If you need a custom action class, you must provide an action class for each field in the index.

For example, GinIndex(name ='json_index ', fields = ['jsonfield'], opclasses = ['jsonb_path_ops']) uses jsonb_ path_ Ops creates a gin index on jsonfield.

4.Meta options

abstract

Options.abstract

If abstract = True, the model will be an abstract base class.

app_label

Options.app_label

If installed_ A model other than an application is defined in apps. It must declare which application it belongs to:

app_label = 'myapp'
If you want to use app_label.object_name or app_label.model_name to represent a model. You can use model_ Meta.label or model_ meta.label_ lower.

base_manager_name

Options.base_manager_name

The attribute name of the manager, for example, 'objects', is used for the model_ base_manager.

db_table

Options.db_table

Name of the database table used for the model:
db_table = 'music_album'

5.Model class

DoesNotExist

exception Model.DoesNotExist

The ORM throws this exception when the expected object is not found. For example, QuerySet.get() will throw the exception when the given lookup object is not found.

MultipleObjectsReturned

exception Model.MultipleObjectsReturned

QuerySet.get() throws this exception when multiple objects are found for a given lookup

objects

Model.objects

Each non abstract Model class must have a Manager instance added to it. Django ensures that at least one default Manager is specified in your Model class. If you don't add your own Manager ', Django will add an attribute objects, which contains the default Manager instance. If you add your own Manager instance properties, the default will not appear.

example:

from django.db import models

class Person(models.Model):
    # Add manager with another name
    people = models.Manager()

2, QuerySet:

1. Execute query

Once the data model is created, Django automatically gives you a set of database abstraction API s that allow you to create, retrieve, update and delete objects.

We will refer to the following models, which contain a Webblog application:

from django.db import models

class Blog(models.Model):
    name = models.CharField(max_length=100)
    tagline = models.TextField()

    def __str__(self):
        return self.name

class Author(models.Model):
    name = models.CharField(max_length=200)
    email = models.EmailField()

    def __str__(self):
        return self.name

class Entry(models.Model):
    blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
    headline = models.CharField(max_length=255)
    body_text = models.TextField()
    pub_date = models.DateField()
    mod_date = models.DateField()
    authors = models.ManyToManyField(Author)
    number_of_comments = models.IntegerField()
    number_of_pingbacks = models.IntegerField()
    rating = models.IntegerField()

    def __str__(self):
        return self.headline

create object

>>> from blog.models import Blog
>>> b = Blog(name='Beatles Blog', tagline='All the latest Beatles news.')
>>> b.save()

Django does not operate the database until you explicitly call save().
The save() method has no return value.

Save changes to object

To save the changes to an existing object in the database, use save().

There is a Blog instance b5 that has been stored in the database. This example renames it and updates its records in the database:

>>> b5.name = 'New name'
>>> b5.save()

2.QuerySet method reference

When is QuerySet executed

QuerySet itself can be constructed, filtered, sliced, copied, assigned, etc. there is no need to access the database. You only need to access the database when you need to retrieve data from the database or store data into the database.

For example:

for e in Entry.objects.all():
    print(e.headline)

3. Query expression

The lookup API has two components: one is the RegisterLookupMixin class, which is used for registration and lookup; The other is the query expression API. If a class wants to register as a lookup, it must implement a set of methods.

Django has two base classes that follow the query expression API, from which all built-in lookups in Django are derived.

Lookup: find a field (such as the exact of field_name_exact)
Transform: transform a field

A lookup expression consists of three parts:

Field part (such as book.objects.filter (author_best_friends_first_name...);
Conversion part (can be omitted) (e.g. _lower_first3cars_reversed);
Find (such as _icontains). If omitted, it defaults to _exact.

There are also registration APIs and query expression APIs.

Three, example of the model:

1. Example method

For example, if you want to instance a method, it can be as follows:

class Model(**kwargs)

2. Access associated objects

You can access associated objects through class RelatedManager:

The "other side" of the ForeignKey relationship. That is:

from django.db import models

class Blog(models.Model):
    # ...
    pass

class Entry(models.Model):
    blog = models.ForeignKey(Blog, on_delete=models.CASCADE, null=True)

Both sides of the ManyToManyField relationship:

class Topping(models.Model):
    # ...
    pass

class Pizza(models.Model):
    toppings = models.ManyToManyField(Topping)

4, Migration:

1. Migration overview

Migration is the way Django applies your changes to the model (such as adding a field and deleting a model) to the database schema. They are designed to be as automated as possible, but you still need to know when to build and run the migration, and you also need to know some common problems.

The main commands are:

migrate, responsible for applying and revoking migration.
Make migrations, create migrations based on model modifications.
sqlmigrate, showing the SQL statements used in the migration.
Show migrations, which lists the migration and migration status of the project.

2. Operation reference

CreateModel

class CreateModel(name, fields, options=None, bases=None, managers=None)

Create a new model in the project history and create the corresponding table matching it in the database.

DeleteModel

class DeleteModel(name)

Delete the model from the project history and its tables from the database.

RenameModel

class RenameModel(old_name, new_name)

Change the name of the model from the old name to the new name.

3.SchemaEditor

Each database back end in Django provides its own version of SchemaEditor, and always through the connection.schema_editor() context manager:

with connection.schema_editor() as schema_editor:
    schema_editor.delete_model(MyModel)

4. Write migration

For example, we can use the following method to run migration for a specific database

from django.db import migrations

def forwards(apps, schema_editor):
    if schema_editor.connection.alias != 'default':
        return
    # Your migration code goes here

class Migration(migrations.Migration):

    dependencies = [
        # Dependencies to other migrations
    ]

    operations = [
        migrations.RunPython(forwards),
    ]

5, Advanced:

1. Manager

class Manager

Manager is an interface that gives the Django model the ability to manipulate databases. Each model in the Django application has at least one manager.

2. Original SQL

In order to illustrate what the original sql is, it is best to use an example to explain it. Suppose you have the following model:

class Person(models.Model):
    first_name = models.CharField(...)
    last_name = models.CharField(...)
    birth_date = models.DateField(...)

Then you can execute custom SQL like this:

>>> for p in Person.objects.raw('SELECT * FROM myapp_person'):
...     print(p)
John Smith
Jane Jones

3. Services

Django default transaction behavior

Django's default transaction behavior is auto commit. Unless the transaction is in progress, each query will be automatically submitted to the database immediately, as detailed below.

Django automatically uses transaction or restore points to ensure the consistency of ORM operations requiring multiple queries, especially delete() and update() operations.

For performance reasons, Django's TestCase class also encapsulates each test transaction.

4. Polymerization

For example, there are the following models

from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)
    age = models.IntegerField()

class Publisher(models.Model):
    name = models.CharField(max_length=300)

class Book(models.Model):
    name = models.CharField(max_length=300)
    pages = models.IntegerField()
    price = models.DecimalField(max_digits=10, decimal_places=2)
    rating = models.FloatField()
    authors = models.ManyToManyField(Author)
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
    pubdate = models.DateField()

class Store(models.Model):
    name = models.CharField(max_length=300)
    books = models.ManyToManyField(Book)

Set query can be performed through quick query table:

# Total number of books.
>>> Book.objects.count()
2452

# Total number of books with publisher=BaloneyPress
>>> Book.objects.filter(publisher__name='BaloneyPress').count()
73

# Average price across all books.
>>> from django.db.models import Avg
>>> Book.objects.all().aggregate(Avg('price'))
{'price__avg': 34.35}

# Max price across all books.
>>> from django.db.models import Max
>>> Book.objects.all().aggregate(Max('price'))
{'price__max': Decimal('81.20')}

# Difference between the highest priced book and the average price of all books.
>>> from django.db.models import FloatField
>>> Book.objects.aggregate(
...     price_diff=Max('price', output_field=FloatField()) - Avg('price'))
{'price_diff': 46.85}

# All the following queries involve traversing the Book<->Publisher
# foreign key relationship backwards.

# Each publisher, each with a count of books as a "num_books" attribute.
>>> from django.db.models import Count
>>> pubs = Publisher.objects.annotate(num_books=Count('book'))
>>> pubs
<QuerySet [<Publisher: BaloneyPress>, <Publisher: SalamiPress>, ...]>
>>> pubs[0].num_books
73

# Each publisher, with a separate count of books with a rating above and below 5
>>> from django.db.models import Q
>>> above_5 = Count('book', filter=Q(book__rating__gt=5))
>>> below_5 = Count('book', filter=Q(book__rating__lte=5))
>>> pubs = Publisher.objects.annotate(below_5=below_5).annotate(above_5=above_5)
>>> pubs[0].above_5
23
>>> pubs[0].below_5
12

# The top 5 publishers, in order by number of books.
>>> pubs = Publisher.objects.annotate(num_books=Count('book')).order_by('-num_books')[:5]
>>> pubs[0].num_books
1323

5. Search

You can query through standard text:

>>> Author.objects.filter(name__contains='Terry')
[<Author: Terry Gilliam>, <Author: Terry Jones>]

6. Custom fields

Encapsulates a Python object that represents the details of the bridge in hand. Don't worry, you don't need to know how to play bridge to learn this example. You only need to know that 52 cards are divided into 4 players, generally called North, East, South and West. Our class length is as follows:

class Hand:
    """A hand of cards (bridge style)"""

    def __init__(self, north, east, south, west):
        # Input parameters are lists of cards ('Ah', '9s', etc.)
        self.north = north
        self.east = east
        self.south = south
        self.west = west

    # ... (other possibly useful methods omitted) ...

7. Multiple databases

Two databases can be defined in the project - the default PostgreSQL database and MySQL database named users.

DATABASES = {
    'default': {
        'NAME': 'app_data',
        'ENGINE': 'django.db.backends.postgresql',
        'USER': 'postgres_user',
        'PASSWORD': 's3krit'
    },
    'users': {
        'NAME': 'user_data',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'mysql_user',
        'PASSWORD': 'priv4te'
    }
}

8. User defined query

Django provides a variety of built-in queries (for example, exact and icontains).

class NotEqual(Lookup):
    lookup_name = 'ne'

    def as_sql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params
        return '%s <> %s' % (lhs, rhs), params

9. Query expression

Django supports negative, addition, subtraction, multiplication, division, modulus operations, and power operators for query expressions, using Python constants, variables, and even other expressions.

from django.db.models import Count, F, Value
from django.db.models.functions import Length, Upper

# Find companies that have more employees than chairs.
Company.objects.filter(num_employees__gt=F('num_chairs'))

# Find companies that have at least twice as many employees
# as chairs. Both the querysets below are equivalent.
Company.objects.filter(num_employees__gt=F('num_chairs') * 2)
Company.objects.filter(
    num_employees__gt=F('num_chairs') + F('num_chairs'))

# How many chairs are needed for each company to seat all employees?
>>> company = Company.objects.filter(
...    num_employees__gt=F('num_chairs')).annotate(
...    chairs_needed=F('num_employees') - F('num_chairs')).first()
>>> company.num_employees
120
>>> company.num_chairs
50
>>> company.chairs_needed
70

# Create a new company using expressions.
>>> company = Company.objects.create(name='Google', ticker=Upper(Value('goog')))
# Be sure to refresh it if you need to access the field.
>>> company.refresh_from_db()
>>> company.ticker
'GOOG'

# Annotate models with an aggregated value. Both forms
# below are equivalent.
Company.objects.annotate(num_products=Count('products'))
Company.objects.annotate(num_products=Count(F('products')))

# Aggregates can contain complex computations also
Company.objects.annotate(num_offerings=Count(F('products') + F('services')))

# Expressions can also be used in order_by(), either directly
Company.objects.order_by(Length('name').asc())
Company.objects.order_by(Length('name').desc())
# or using the double underscore lookup syntax.
from django.db.models import CharField
from django.db.models.functions import Length
CharField.register_lookup(Length)
Company.objects.order_by('name__length')

# Boolean expression can be used directly in filters.
from django.db.models import Exists
Company.objects.filter(
    Exists(Employee.objects.filter(company=OuterRef('pk'), salary__gt=10))
)

10 conditional expression

To illustrate the conditional expression, we first build a model:

from django.db import models

class Client(models.Model):
    REGULAR = 'R'
    GOLD = 'G'
    PLATINUM = 'P'
    ACCOUNT_TYPE_CHOICES = [
        (REGULAR, 'Regular'),
        (GOLD, 'Gold'),
        (PLATINUM, 'Platinum'),
    ]
    name = models.CharField(max_length=50)
    registered_on = models.DateField()
    account_type = models.CharField(
        max_length=1,
        choices=ACCOUNT_TYPE_CHOICES,
        default=REGULAR,
    )

when

>>> from django.db.models import F, Q, When
>>> # String arguments refer to fields; the following two examples are equivalent:
>>> When(account_type=Client.GOLD, then='name')
>>> When(account_type=Client.GOLD, then=F('name'))
>>> # You can use field lookups in the condition
>>> from datetime import date
>>> When(registered_on__gt=date(2014, 1, 1),
...      registered_on__lt=date(2015, 1, 1),
...      then='account_type')
>>> # Complex conditions can be created using Q objects
>>> When(Q(name__startswith="John") | Q(name__startswith="Paul"),
...      then='name')
>>> # Condition can be created using boolean expressions.
>>> from django.db.models import Exists, OuterRef
>>> non_unique_account_type = Client.objects.filter(
...     account_type=OuterRef('account_type'),
... ).exclude(pk=OuterRef('pk')).values('pk')
>>> When(Exists(non_unique_account_type), then=Value('non unique'))

case

>>> from datetime import date, timedelta
>>> from django.db.models import Case, Value, When
>>> Client.objects.create(
...     name='Jane Doe',
...     account_type=Client.REGULAR,
...     registered_on=date.today() - timedelta(days=36))
>>> Client.objects.create(
...     name='James Smith',
...     account_type=Client.GOLD,
...     registered_on=date.today() - timedelta(days=5))
>>> Client.objects.create(
...     name='Jack Black',
...     account_type=Client.PLATINUM,
...     registered_on=date.today() - timedelta(days=10 * 365))
>>> # Get the discount for each Client based on the account type
>>> Client.objects.annotate(
...     discount=Case(
...         When(account_type=Client.GOLD, then=Value('5%')),
...         When(account_type=Client.PLATINUM, then=Value('10%')),
...         default=Value('0%'),
...     ),
... ).values_list('name', 'discount')
<QuerySet [('Jane Doe', '0%'), ('James Smith', '5%'), ('Jack Black', '10%')]>

For example, condition update:

>>> a_month_ago = date.today() - timedelta(days=30)
>>> a_year_ago = date.today() - timedelta(days=365)
>>> # Update the account_type for each Client from the registration date
>>> Client.objects.update(
...     account_type=Case(
...         When(registered_on__lte=a_year_ago,
...              then=Value(Client.PLATINUM)),
...         When(registered_on__lte=a_month_ago,
...              then=Value(Client.GOLD)),
...         default=Value(Client.REGULAR)
...     ),
... )
>>> Client.objects.values_list('name', 'account_type')
<QuerySet [('Jane Doe', 'G'), ('James Smith', 'R'), ('Jack Black', 'P')]>

Conditional aggregation

>>> # Create some more Clients first so we can have something to count
>>> Client.objects.create(
...     name='Jean Grey',
...     account_type=Client.REGULAR,
...     registered_on=date.today())
>>> Client.objects.create(
...     name='James Bond',
...     account_type=Client.PLATINUM,
...     registered_on=date.today())
>>> Client.objects.create(
...     name='Jane Porter',
...     account_type=Client.PLATINUM,
...     registered_on=date.today())
>>> # Get counts for each value of account_type
>>> from django.db.models import Count
>>> Client.objects.aggregate(
...     regular=Count('pk', filter=Q(account_type=Client.REGULAR)),
...     gold=Count('pk', filter=Q(account_type=Client.GOLD)),
...     platinum=Count('pk', filter=Q(account_type=Client.PLATINUM)),
... )
{'regular': 2, 'gold': 1, 'platinum': 3}

11. Database functions

Comparison and conversion functions:

Cast，Coalesce，Collate，Greatest
For example, the following code:

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Cast
>>> Author.objects.create(age=25, name='Margaret Smith')
>>> author = Author.objects.annotate(
...    age_as_float=Cast('age', output_field=FloatField()),
... ).get()
>>> print(author.age_as_float)
25.0

6, Others:

1. Supported databases

Django officially supports the following databases:

PostgreSQL
MariaDB
MySQL
Oracle
SQLite

2. Old database

Although Django is suitable for developing new applications, it can also be used to integrate old databases.

3. Provide initialization data

If you want to initialize data:

[
  {
    "model": "myapp.person",
    "pk": 1,
    "fields": {
      "first_name": "John",
      "last_name": "Lennon"
    }
  },
  {
    "model": "myapp.person",
    "pk": 2,
    "fields": {
      "first_name": "Paul",
      "last_name": "McCartney"
    }
  }
]

4. Optimize database access

There are the following steps to optimize database access:

1. First, performance analysis
2. Use standard database optimization techniques
3. Understand QuerySet
4. Understand the execution process of QuerySet
5. Understand cache properties
6. Use the with template label
7. Use iterator()
8. Use explain()
9. Use RawSQL
10. Use native SQL
11. Retrieve a single object using a unique index column
12. Use QuerySet.select_related() and prefetch_ Related, etc

5. Unique functions of PostgreSQL

django.contrib.postgres

PostgreSQL has many features that Django does not support. This optional module contains some model fields and form fields of PostgreSQL specific data types.