Common fields and parameters in Django ORM

Some notes:

  • Table myapp_ The name of person is automatically generated. If you want to customize the table name, you need to specify it in the Meta class of model   db_table   Parameter, it is strongly recommended to use lowercase table names, especially when MySQL is used as the back-end database.
  • The ID field is automatically added. If you want to specify a custom primary key, you only need to specify it in one of the fields   primary_key=True   Just. If Django finds that you have explicitly set Field.primary_key, it will not add the automatic ID column.
  • The CREATE TABLE SQL in this example uses PostgreSQL syntax to format, but it is worth noting that Django will generate corresponding SQL statements according to the database back-end type specified in the configuration file.
  • Django supports MySQL 5.5 and later.

Django ORM common fields and parameters

Common fields

AutoField

int auto increment column, the parameter primary must be filled in_ key=True. If there is no auto increment column in the model, a column named id will be automatically created.

IntegerField

An integer type ranging from - 2147483648 to 2147483647. (generally, you don't need it to save the mobile phone number (the number of digits is not enough), and you can save it directly in string.)

CharField

Character type, Max must be provided_ Length parameter, max_length indicates the character length.

What we need to know here is that the CharField in Django corresponds to the varchar type in the MySQL database. The field corresponding to the char type is not set, but Django allows us to customize new fields. Let me define the char type corresponding to the database

Custom fields may be frequently used in actual project applications. You need to leave an impression on them here!

from django.db import models

# Create your models here.
#There is no corresponding char type field in Django, but we can create it ourselves
class FixCharField(models.Field):
    '''
    Custom char Field class of type
    '''
    def __init__(self,max_length,*args,**kwargs):
        self.max_length=max_length
        super().__init__(max_length=max_length,*args,**kwargs)

    def db_type(self, connection):
        '''
        Limit the field type of the generated database table char,Count Reg max_length Specified value
        :param connection:
        :return:
        '''
        return 'char(%s)'%self.max_length
#Apply the char type defined above
class Class(models.Model):
    id=models.AutoField(primary_key=True)
    title=models.CharField(max_length=32)
    class_name=FixCharField(max_length=16)
    gender_choice=((1,'male'),(2,'female'),(3,'secrecy'))
    gender=models.SmallIntegerField(choices=gender_choice,default=3)

DateField

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

DateTimeField

Date time field, format yyyy-mm-dd HH: mm [: SS [. Uuuuuuuuuuuu]] [TZ], equivalent to datetime.datetime() instance in Python.

Field collection (striving for memory)

AutoField(Field)
        - int Auto increment column, parameter must be filled in primary_key=True

    BigAutoField(AutoField)
        - bigint Auto increment column, parameter must be filled in primary_key=True

        Note: when model If there is no self incrementing column in, a column named id Columns of
        from django.db import models

        class UserInfo(models.Model):
            # Automatically create a self incrementing integer column with column name id
            username = models.CharField(max_length=32)

        class Group(models.Model):
            # Custom Auto increment column
            nid = models.AutoField(primary_key=True)
            name = models.CharField(max_length=32)

    SmallIntegerField(IntegerField):
        - Small integer -3276832767

    PositiveSmallIntegerField(PositiveIntegerRelDbTypeMixin, IntegerField)
        - Positive small integer 032767
    IntegerField(Field)
        - Integer column(Signed) -21474836482147483647

    PositiveIntegerField(PositiveIntegerRelDbTypeMixin, IntegerField)
        - positive integer 02147483647

    BigIntegerField(IntegerField):
        - Long integer(Signed) -92233720368547758089223372036854775807

    BooleanField(Field)
        - Boolean type

    NullBooleanField(Field):
        - Boolean value that can be empty

    CharField(Field)
        - Character type
        - Must provide max_length Parameters, max_length Represents the character length

    TextField(Field)
        - Text type

    EmailField(CharField): 
        - String type, Django Admin as well as ModelForm Authentication mechanism is provided in

    IPAddressField(Field)
        - String type, Django Admin as well as ModelForm Provide validation in IPV4 mechanism

    GenericIPAddressField(Field)
        - String type, Django Admin as well as ModelForm Provide validation in Ipv4 and Ipv6
        - Parameters:
            protocol,Used to specify Ipv4 or Ipv6, 'both',"ipv4","ipv6"
            unpack_ipv4, If specified as True,Then enter::ffff:192.0.2.1 When, it can be resolved to 192.0.2.1,To enable this function, you need to protocol="both"

    URLField(CharField)
        - String type, Django Admin as well as ModelForm Provide validation in URL

    SlugField(CharField)
        - String type, Django Admin as well as ModelForm Provides authentication support for letters, numbers, underscores, and connectors (minus signs)

    CommaSeparatedIntegerField(CharField)
        - String type, the format must be a comma separated number

    UUIDField(Field)
        - String type, Django Admin as well as ModelForm For UUID Format validation

    FilePathField(Field)
        - character string, Django Admin as well as ModelForm Provides the function of reading files under folders
        - Parameters:
                path,                      Folder path
                match=None,                Regular matching
                recursive=False,           Recursive folder below
                allow_files=True,          Allow file
                allow_folders=False,       Allow folder

    FileField(Field)
        - String, the path is saved in the database, and the file is uploaded to the specified directory
        - Parameters:
            upload_to = ""      Save path of uploaded file
            storage = None      Storage components, default django.core.files.storage.FileSystemStorage

    ImageField(FileField)
        - String, the path is saved in the database, and the file is uploaded to the specified directory
        - Parameters:
            upload_to = ""      Save path of uploaded file
            storage = None      Storage components, default django.core.files.storage.FileSystemStorage
            width_field=None,   Highly saved database field name (string) of the uploaded picture
            height_field=None   Width of uploaded picture saved database field name (string)

    DateTimeField(DateField)
        - date+Time format YYYY-MM-DD HH:MM[:ss[.uuuuuu]][TZ]

    DateField(DateTimeCheckMixin, Field)
        - Date format      YYYY-MM-DD

    TimeField(DateTimeCheckMixin, Field)
        - Time format      HH:MM[:ss[.uuuuuu]]

    DurationField(Field)
        - Long integer, time interval, in the database according to bigint Storage, ORM The value obtained in is datetime.timedelta type

    FloatField(Field)
        - float 

    DecimalField(Field)
        - 10 Hexadecimal decimal
        - Parameters:
            max_digits,Total decimal length
            decimal_places,Decimal length

    BinaryField(Field)
        - Binary type
Correspondence:
    '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)',

Field parameters

null

Used to indicate that a field can be empty.

unique

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

db_index

If db_index=True means that the index is set for this field.

default

Set the default value for this field.

DateField and DateTimeField

auto_now_add

Configure auto_now_add=True, the current time will be added to the database when creating the data record.

auto_now

Configure auto on_ Now = true, this field will be updated every time the data record is updated.

Relation field

ForeignKey

The foreign key type is used to represent the foreign key association relationship in ORM. Generally, the ForeignKey field is set on the "many" side of "one to many".

ForeignKey can be associated with other tables and itself.

Field parameters

to

Sets the table to associate

to_field

Set the fields of the table to associate

on_delete

When data in an associated table is deleted, the behavior of the current table and its associated rows.

models.CASCADE

Delete the associated data and delete the associated data

db_constraint

Whether to create a foreign key constraint in the database. The default is True.

models.DO_NOTHING
 Deleting associated data raises an error IntegrityError


models.PROTECT
 Deleting associated data raises an error ProtectedError


models.SET_NULL
 Delete the associated data and set the value associated with it to null(premise FK (field needs to be set to nullable)


models.SET_DEFAULT
 Delete the associated data and set the associated value as the default value (premise FK Field (default value needs to be set)


models.SET

Delete associated data,
a. The value associated with it is set to the specified value, setting: models.SET(value)
b. The value associated with it is set as the return value of the executable object. Set: models.SET(Executable object)
def func():
    return 10

class MyModel(models.Model):
    user = models.ForeignKey(
        to="User",
        to_field="id",
        on_delete=models.SET(func)
    )

OneToOneField

One to one field.

Usually, one-to-one fields are used to extend existing fields. (generally speaking, all information of a person is not put in one table. Simple information is in one table and private information is in another table, which are associated through one-to-one foreign keys)

Field parameters

to

Set the table to associate.

to_field

Set the fields to associate.

on_delete

When data in an associated table is deleted, the behavior of the current table and its associated rows. (refer to the example above)

General operation

During the general operation, first configure the parameters so that we can run our test script directly in the Django page

Invoking the Django environment in Python script

 

In this way, you can directly run your test.py file to run the test

Article 13

Before the following operations, we have created the data table. Here we mainly demonstrate the following operations, and we will not talk about the creation preparation process in detail

<1> All (): query all results

<2> Filter (* * kwargs): it contains objects that match the given filter criteria

<3> Get (* * kwargs): returns the objects that match the given filter criteria. There is only one returned result. If there are more than one or no objects that meet the filter criteria, an error will be thrown.

<4> Exclude (* * kwargs): it contains objects that do not match the given filter criteria

<5> Values (* field): returns a ValueQuerySet -- a special QuerySet. After running, the result is not a series of model instantiated objects, but an iterative dictionary sequence

<6> values_ List (* field): it is very similar to values(). It returns a tuple sequence and values returns a dictionary sequence

<7> order_ By (* field): sort query results

<8> Reverse(): reverse sort the query results. Please note that reverse() can only be called on QuerySet with defined order (specify ordering in Meta of model class or call order_by() method).

<9> Distinct(): eliminate duplicate records from the returned results (if your query spans multiple tables, you may get duplicate results when calculating QuerySet. You can use distinct() at this time. Note that de duplication by field is only supported in PostgreSQL.)

<10> Count(): returns the number of objects matching the query (QuerySet) in the database.

<11> First (): returns the first record

<12> Last (): returns the last record

<13> Exists(): returns True if the QuerySet contains data; otherwise, returns False

Summary of 13 required operations

The methods that return the QuerySet object are

all()

filter()

exclude()

order_by()

reverse()

distinct()

Special QuerySet

values()         Returns an iteratable dictionary sequence

values_list() returns an iteratable ancestor sequence

Returns the of a specific object

get()

first()

last()

The methods to return Boolean values are:

exists()

The methods to return numbers are

count()

Print SQL statement on Django terminal

If you want to know how Django executes its sql statements when you operate on the database, you can add the following configuration to view it

In the settings.py file of Django project, copy and paste the following code at the end:

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

After configuration, when executing any statement that operates on the database, the sql statement executed by Django will be automatically printed to the pycharm terminal

Supplement:

In addition to configuration, you can also view query statements by clicking. Query. The specific operations are as follows:

Magic double underline of single table query

models.Tb1.objects.filter(id__lt=10, id__gt=1)   # Gets a value whose id is greater than 1 and less than 10
 
models.Tb1.objects.filter(id__in=[11, 22, 33])   # Get id equal to 1122,33 Data
models.Tb1.objects.exclude(id__in=[11, 22, 33])  # not in
 
models.Tb1.objects.filter(name__contains="ven")  # Get the name field contains"ven"of
models.Tb1.objects.filter(name__icontains="ven") # icontains are case insensitive
 
models.Tb1.objects.filter(id__range=[1, 3])      # The id range is 1 to 3, which is equivalent to the between and of SQL
 
Similar are: startswith,istartswith, endswith, iendswith 

date Fields can also:
models.Class.objects.filter(first_day__year=2017)
date Fields can be added by adding__year,__month,__day Wait to get date Characteristics and partial data
# date
        #
        # Entry.objects.filter(pub_date__date=datetime.date(2005, 1, 1))
        # Entry.objects.filter(pub_date__date__gt=datetime.date(2005, 1, 1))

        # year
        #
        # Entry.objects.filter(pub_date__year=2005)
        # Entry.objects.filter(pub_date__year__gte=2005)

        # month
        #
        # Entry.objects.filter(pub_date__month=12)
        # Entry.objects.filter(pub_date__month__gte=6)

        # day
        #
        # Entry.objects.filter(pub_date__day=3)
        # Entry.objects.filter(pub_date__day__gte=3)

        # week_day
        #
        # Entry.objects.filter(pub_date__week_day=2)
        # Entry.objects.filter(pub_date__week_day__gte=2)
It should be noted that when expressing the time of a year, we usually use 52 weeks, because the number of days is uncertain. Foreigners calculate their salary by week~

ForeignKey operation

Forward lookup (two methods)

1. Object lookup (cross table)

Syntax:

Object. Associated field. Field

Important: first get the object, and then check the corresponding foreign key field through the object. There are two steps

Example:

book_obj = models.Book.objects.first()  # First book object (step 1)
print(book_obj.publisher)  # Get the publisher object associated with this book
print(book_obj.publisher.name)  # Get the name of the publisher object

2. Field lookup (cross table)

Syntax:

Associated field__ field

Important: use the magic double underline search method provided by Django

Example:

models.Book.objects.all().values("publisher__name")
#Get the name of the publishing house corresponding to all the data, and the magical underline helps us query the table

Reverse operation (two modes)

1. Object search

Syntax:

obj. Table name_ set

Important: first get an object in the foreign key Association many to one and one. Since the foreign key field is set on the more side, we still use the double underline provided by Django to find it here

Example:

publisher_obj = models.Publisher.objects.first()  # Find the first publisher object
books = publisher_obj.book_set.all()  # Find all the books published by the first publishing house
titles = books.values_list("title")  # Find the titles of all the books published by the first publishing house

Conclusion: if you want to find more than one party through one party, because the foreign key field is not in one party, you can use__ set to find it

2. Field search

Syntax:

Table name__ field

Key points: directly use the double sliding line to complete the exaggeration operation

titles = models.Publisher.objects.values("book__title")

ManyToManyField

class RelatedManager

An association manager is a manager used in a one to many or many to many association context.

It exists in the following two cases:

  1. Reverse query of foreign key relationship
  2. Many to many Association

To put it simply, the following method can only be used when there is a many to many table relationship and Django automatically helps you build this many to many table relationship.

method

create()

Create an association object and automatically write it to the database, and automatically create a new correspondence between the two parties in the third association table of both parties.

models.Author.objects.first().book_set.create(title="Steal tower script")
What did the above sentence do:
1.From an object in the author table to the book comparison table
2.Add a book named steal tower script and save it
3.Add a many to many relationship between the author and the book in the third table and save it

add()

Add the specified model object to the third association table.

Add object

>>> author_objs = models.Author.objects.filter(id__lt=3)
>>> models.Book.objects.first().authors.add(*author_objs)

Add id

>>> models.Book.objects.first().authors.add(*[1, 2])

set()

Update the associated object of an object in the third table. Unlike the above, add is added, and set is equivalent to reset

>>> book_obj = models.Book.objects.first()
>>> book_obj.authors.set([2, 3])

remove()

Remove the executed model object from the associated object set (remove the relationship between the object and an associated object in the third table)

>>> book_obj = models.Book.objects.first()
>>> book_obj.authors.remove(3)

clear()

Removes all objects from the associated object set. (remove all object related relationship information)

>>> book_obj = models.Book.objects.first()
>>> book_obj.authors.clear()

be careful:

For ForeignKey objects, the clear() and remove() methods exist only when null=True.

for instance:

When the ForeignKey field is not set to null=True,

class Book(models.Model):
    title = models.CharField(max_length=32)
    publisher = models.ForeignKey(to=Publisher)

No clear() and remove() methods:

>>> models.Publisher.objects.first().book_set.clear()
Traceback (most recent call last):
  File "<input>", line 1, in <module>
AttributeError: 'RelatedManager' object has no attribute 'clear'

When the ForeignKey field is set to null=True,

class Book(models.Model):
    name = models.CharField(max_length=32)
    publisher = models.ForeignKey(to=Class, null=True)

At this point, there are clear() and remove() methods:

>>> models.Publisher.objects.first().book_set.clear()

Once again:

  1. For all types of associated fields, add(), create(), remove(), and clear(),set() will immediately update the database. In other words, there is no need to call the save() method at either end of the association.

Aggregate query and group query

Aggregation (using aggregate functions)

aggregate() is a QuerySet   A terminating clause of, which means that it returns a dictionary containing some key value pairs.

The name of the key is the identifier of the aggregate value, and the value is the calculated aggregate value. Key names are automatically generated according to the names of fields and aggregate functions.

Built in functions used:

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

Example:

>>> from django.db.models import Avg, Sum, Max, Min, Count
>>> models.Book.objects.all().aggregate(Avg("price"))
{'price__avg': 13.233333}

If you want to specify a name for the aggregate value, you can provide it to the aggregate clause.

>>> models.Book.objects.aggregate(average_price=Avg('price'))
{'average_price': 13.233333}

If you want to generate more than one aggregate, you can add another parameter to the aggregate() clause. Therefore, if you also want to know the maximum and minimum prices of all books, you can query as follows:

>>> models.Book.objects.all().aggregate(Avg("price"), Max("price"), Min("price"))
{'price__avg': 13.233333, 'price__max': Decimal('19.90'), 'price__min': Decimal('9.90')}

grouping

Let's review the grouping of SQL statements.

Suppose there is a company employee table:

  We use the native SQL statement to calculate the average salary by partial grouping:

select dept,AVG(salary) from employee group by dept;

ORM query:

from django.db.models import Avg
Employee.objects.values("dept").annotate(avg=Avg("salary").values(dept, "avg")
What needs to be noted here is annotate The grouping is based on the value in front of him,
If there is no characteristic field in front, it defaults to ID grouping,
here you are dept Field, so follow dept Field grouping

Grouping of linked table query:

SQL query:

select dept.name,AVG(salary) from employee inner join dept on (employee.dept_id=dept.id) group by dept_id;

ORM query:

from django.db.models import Avg
models.Dept.objects.annotate(avg=Avg("employee__salary")).values("name", "avg")

More examples:

Example 1: count the number of authors of each book

>>> book_list = models.Book.objects.all().annotate(author_num=Count("author"))
>>> for obj in book_list:
...     print(obj.author_num)
...
1

Example 2: calculate the price of the cheapest book bought by each publishing house

>>> publisher_list = models.Publisher.objects.annotate(min_price=Min("book__price"))
>>> for obj in publisher_list:
...     print(obj.min_price)
...     
9.90
19.90

Method 2:

>>> models.Book.objects.values("publisher__name").annotate(min_price=Min("price"))
<QuerySet [{'publisher__name': 'Shahe Publishing House', 'min_price': Decimal('9.90')}, {'publisher__name': 'People's Publishing House', 'min_price': Decimal('19.90')}]>

Example 3: count books with more than one author

>>> models.Book.objects.annotate(author_num=Count("author")).filter(author_num__gt=1)
<QuerySet [<Book: Tomato Story>]>

Example 4: query set based on the number of authors of a Book   Sort by QuerySet

>>> models.Book.objects.annotate(author_num=Count("author")).order_by("author_num")
<QuerySet [<Book: Banana Story>, <Book: Orange story>, <Book: Tomato Story>]>

Example 5: query the total price of books published by each author

>>> models.Author.objects.annotate(sum_price=Sum("book__price")).values("name", "sum_price")
<QuerySet [{'name': 'Elf', 'sum_price': Decimal('9.90')}, {'name': 'Fairy', 'sum_price': Decimal('29.80')}, {'name': 'Little witch', 'sum_price': Decimal('9.90')}]>

  summary

The parameters in value correspond to the fields to be found and displayed by select in the sql statement,

The parameters in filter are equivalent to the filter conditions in where or having

annotate itself represents the role of group by. In the front, find the grouping basis, place and display the aggregation expression that may be used, followed by filter to add restrictions, and the last value represents the field value you want to find after grouping

Posted on Tue, 23 Nov 2021 20:00:25 -0500 by mark110384