python-day59 Object Relational Mapping (ORM)

Object Relational Mapping (ORM) ORM introduces ORM Concept

The object-relational mapping (objects relational Mapping, ORM) pattern is a technique to solve the mismatch between object-oriented and relational databases.

In a nutshell, ORM automatically persists objects in a program to a relational database by using metadata that describes the mapping between the object and the database.

ORM acts as a bridge between the business logic layer and the database layer.

ORM Origin

Let's start with the O/R. The letter O originates from "object", and R is derived from "relationship" (relational).

In almost all software development processes, objects and relational databases are involved. At the user level and at the business logic level, we are object-oriented. When the information of the object changes, we need to keep the object's information in the relational database.

Developing in the same way as before, programmers will have a lot of SQL statements in their business logic code to add, read, modify, and delete related data, and the code is often duplicated.

The benefits of ORM

The main problem with ORM resolution is the mapping of objects and relationships. It usually corresponds to a class and a table one by one, each instance of the class corresponds to a record in the table, and each property of the class corresponds to each field in the table.

ORM provides a mapping of the database without writing the SQL code directly, manipulating the data from the database just like an object.

Enable software developers to focus on the processing of business logic and improve the efficiency of development.

The disadvantage of ORM

The disadvantage of ORM is that it sacrifices the execution efficiency of the program to some extent.

ORM with more SQL statements will not write, relational database related skills degradation ...

ORM Summary

ORM is just a tool that can really solve some repetitive, simple work. There is no denying that.

But we cannot expect a tool to solve all problems once and for all, and some special problems need special treatment.

However, in the entire software development process needs special processing should be very few, otherwise the so-called tool also lost its existence significance.

The Ormdjango project in Django uses the MySQL database

1. In the settings.py file of the Django project, configure the database connection information:

DATABASES = {    "default": {        "ENGINE": "Django.db.backends.mysql", "        name": "Your Database name",  # You need to create the database yourself manually        "user": "Database user name",        "PASSWORD": "Database Password",        "HOST": "Database IP",        "POST": 3306    }}

2. In the Django project's __init__.py file, write the following code to tell Django to use the Pymysql module to connect to the MySQL database:

Import Pymysqlpymysql.install_as_mysqldb ()

Model

In Django, model is a single, unambiguous source of information about your data. It contains important fields and behaviors for the data you store. Typically, a model maps to a database table,

Basic situation:

• Each model is a Python class, which is a subclass of Django.db.models.Model.
• Each property of the model represents a database field.
• In summary, Django provides you with an automatically generated database access API that details the official documentation links.

Quick Start

The following example defines a person model that contains first_name and last_name.

From django.db import modelsclass person (models. Model):    first_name = models. Charfield (max_length=30)    last_name = models. Charfield (max_length=30)

first_name and last_name are the fields of the model. Each field is specified as a class property, and each property is mapped to a database column.

The person model above will create a database table like this:

CREATE TABLE Myapp_person (    "id" serial NOT NULL PRIMARY KEY,    "first_name" varchar (+) NOT NULL,    "last_name "varchar (+) not NULL);

Some notes:

• The name of the table Myapp_person is automatically generated, and if you want to customize the table name, you need to specify the db_table parameter in the model's meta class, it is strongly recommended to use the lowercase table name, especially when using MySQL as the backend database.
• The ID field is added automatically, and if you want to specify a custom primary key, simply specify Primary_key=true in one of the fields. If Django finds that you have explicitly set Field.primary_key, it will not add an automatic ID column.
• The Create TABLE SQL in this example is formatted with the PostgreSQL syntax, but it's worth noting that Django generates the appropriate SQL statement based on the database backend type specified in the configuration file.
• Django supports MySQL5.5 and later versions.

Django ORM characters commonly used segment and parameter characters commonly used segment Autofield

int self-increment column, must fill in parameter primary_key=true. When the model does not have a self-increment column, it automatically creates a column named ID.

Integerfield

An integer type that ranges from -2147483648 to 2147483647.

Charfield

Character type, the Max_length parameter must be supplied, and max_length represents the character length.

Datefield

Date field, date format yyyy-mm-dd, equivalent to the datetime.date () instance in Python.

Datetimefield

datetime field, Format Yyyy-mm-dd Hh:mm[:ss[.uuuuuu]][tz], equivalent to a datetime.datetime () instance in Python.

Field collection (for memory)

   Autofield (Field)-int self-increment column, must be filled in parameter Primary_key=true Bigautofield (Autofield)-bigint self-increment column, must be filled with parameters Primary_k Ey=true Note: When the model does not have a self-increment column, a column named ID is automatically created from the Django.db Import models Class UserInfo (models. Model): # automatically creates a column named ID and an integer column with an increment of username = models. Charfield (MAX_LENGTH=32) class Group (models. Model): # Custom Auto-increment nid = models. Autofield (primary_key=true) name = models. Charfield (max_length=32) Smallintegerfield (Integerfield):-Small integer -32768 ~ 32767 Positivesmallintegerfield (Posi Tiveintegerreldbtypemixin, Integerfield)-positive small integer 0 ~ 32767 integerfield (Field)-integer sequence (signed)-2147483648 ~ 2 147483647 Positiveintegerfield (Positiveintegerreldbtypemixin, Integerfield)-positive integer 0 ~ 2147483647 BigIntegerFi    Eld (Integerfield):-Long Integer (Signed) -9223372036854775808 ~ 9223372036854775807 Booleanfield (Field)-Boolean type Nullbooleanfield (Field):-Nullable Boolean value CharField-character type-the max_length parameter must be supplied, Max_length represents the character length TextField (field)-Text type Emailfield (Cha Rfield):-String type, Django Admin and Modelform provide validation mechanism Ipaddressfield (Field)-string type, Django admin and Modelform are provided in the Certificate IPV4 mechanism Genericipaddressfield (Field)-string type, Django admin and Modelform provide validation Ipv4 and Ipv6-parameters: PR Otocol, used to specify Ipv4 or Ipv6, ' both ', ' IPv4 ', ' IPv6 ' Unpack_ipv4, if specified as true, enter:: ffff:192.0.2.1, can be resolved to 192.0.2.1, turn on this function, need p Rotocol= "Both" Urlfield (Charfield)-string type, Django admin and Modelform provide authentication URL Slugfield (Charfield)-string    Types, Django Admin and Modelform provide validation support letters, numbers, underscores, connectors (minus) Commaseparatedintegerfield (Charfield)-string types, formats must be comma-delimited numbers Uuidfield (field)-String type, Django Admin and Modelform provide validation of UUID format Filepathfield (field)-String, Django Admin and                Modelform provides the ability to read files under Folders-parameters: path, folder path Match=none,               Regular match Recursive=false, recursively under the folder Allow_files=true, allow file Allow_folders=false,            Allow folder Filefield (Field)-string, path saved in database, file upload to specified directory-parameter: upload_to = "" "Save path of uploaded file Storage = None Storage component, default Django.core.files.storage.FileSystemStorage ImageField (Filefield)-string, path saved in database , the file is uploaded to the specified directory-parameter: upload_to = "" The Save path of the uploaded file storage = None Storage component, default DJANGO.CORE.FILES.S Torage. Filesystemstorage Width_field=none, upload a picture of the height of the Saved database field name (string) Height_field=none the width of the uploaded picture save the 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]] Durationfi Eld (field)-long integer, time interval, database in accordance with bigint storage, ORM Gets the value of Datetime.timedelta type Floatfield (field)-floating-point type Decimalfiel D (Field)- 10 Decimal Fractions-parameters: max_digits, fractional total length decimal_places, decimal length Binaryfield (Field)-binary type 

Custom fields (known as primary)

Class Unsignedintegerfield (models. Integerfield):    def db_type (self, Connection):        return ' integer UNSIGNED '

Custom Char type fields:

Class Fixedcharfield (models. field): "" "    Custom Char-type fields Class" "    def __init__ (self, max_length, *args, **kwargs):        self.max_ Length = Max_length        super (Fixedcharfield, self). __init__ (Max_length=max_length, *args, **kwargs)    def db_ Type (self, Connection): "" "        qualifies the build database table with a field type of char with a length of max_length specified value        " "" "        Return ' char (%s) '% Self.max_lengthclass Class (models. Model):    id = models. Autofield (primary_key=true)    title = models. Charfield (max_length=25)    # using a custom Char type field    CNAME = Fixedcharfield (max_length=25)

The table structure created:

The corresponding relationship between the ORM field and the actual field of the database

Correspondence RelationshipField parameter 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 represents an index set for this field.

default

Set the default value for this field.

Datefield and Datetimefieldauto_now_add

Configure Auto_now_add=true to add the current time to the database when the data record is created.

Auto_now

Configured on Auto_now=true, the field is updated each time the data record is updated.

Relationship Field ForeignKey

Foreign key types are used in ORM to represent foreign key associations, and the ForeignKey field is generally set to the ' many ' side of the ' one-to-many '.

ForeignKey can be associated with other tables and can also be associated with itself.

Field parameter to

Set the table to be associated

To_field

Set the fields of the table to be associated

Related_name

In reverse operation, the name of the field used in place of the original reverse query when the ' table name _set '.

For example:

Class Classes (models. Model):    name = models. Charfield (MAX_LENGTH=32) class Student (models. Model):    name = models. Charfield (max_length=32)    Theclass = models. ForeignKey (to= "Classes")

When we want to query all students associated with a class (reverse query), we write:

Models. Classes.objects.first (). Student_set.all ()

When we add the parameter Related_name in the ForeignKey field,

Class Student (models. Model):    name = models. Charfield (max_length=32)    Theclass = models. ForeignKey (to= "Classes", related_name= "students")

When we want to query all students associated with a class (reverse query), we write:

Models. Classes.objects.first (). Students.all ()

Related_query_name

The connection prefix used when the reverse query operation is used to replace the table name.

On_delete

The behavior of the row associated with the current table when the data in the associated table is deleted.

Models. CASCADE
Deleting associated data, associating with it also deletes

Models. Do_nothing
Delete associated data, raise error Integrityerror

Models. PROTECT
Delete associated data, raise error Protectederror

Models. Set_null
Delete the associated data, and the value associated with it is set to null (if the FK field needs to be set to nullable)

Models. Set_default
Delete the associated data, set the value associated with it to the default value (if the FK field needs to be set default)

Models. SET

Delete the associated data,
A. The value associated with it is set to the specified value, set: Models. SET (value)
B. The value associated with it is set to the return value of the executable object, set: Models. SET (Executable object)

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

Db_constraint

Whether to create a foreign key constraint in the database, which is true by default.

Onetoonefield

A pair of fields.

A pair of fields is usually used to extend an existing field.

Field parameter to

Sets the table to be associated.

To_field

Sets the field to be associated.

On_delete

The same ForeignKey field.

Manytomanyfield

Used to represent a many-to-many association relationship. A third table is established in the database to establish an association relationship.

Field parameter to

Set the table to be associated

Related_name

The same ForeignKey field.

Related_query_name

The same ForeignKey field.

Symmetrical

For many-to-many autocorrelation, specify whether to create a field for the reverse operation internally. The default is true.

As an example:

Class person (models. Model):    name = models. Charfield (max_length=16)    friends = models. Manytomanyfield ("Self")

At this point, the person object has no Person_set property.

Class person (models. Model):    name = models. Charfield (max_length=16)    friends = models. Manytomanyfield ("Self", Symmetrical=false)

At this point, the person object can now use the Person_set property for a reverse query.

Through

When you use the Manytomanyfield field, Django automatically generates a table to manage many-to-many affinity relationships.

But we can also manually create a third table to manage many-to-many relationships, and you need to specify the table name for the third table by through.

Through_fields

Sets the associated field.

Db_table

The name of the table in the database when the third table is created by default.

Meta information

The ORM class contains another meta class, and the meta class encapsulates some database information. The main fields are as follows:

db_table

ORM the table name in the database is the App_ class name by default, and the table name can be overridden by db_table .

Index_together

The Federated Index.

Unique_together

The Federated unique index.

Ordering

Specifies what fields are sorted by default.

Only if this property is set, the results we query can be reverse ().

python-day59 Object Relational Mapping (ORM)