The ORM profile

ORM, or Object-relational Mapping, provides a high-level abstraction for Relational databases, eliminating the need for developers to write SQL. You can create, read, update, and delete data in the database by writing code. Developers can use programming languages they are familiar with to work with databases without having to write SQL statements or stored procedures.

The main tasks of ORM are:

  • Generate a table structure based on the type of the object
  • Convert object and list operations into SQL statements
  • Convert SQL query results into objects and lists

Advantages of ORM:

  • Hiding the details of data access, ORM provides a mapping to the database, without directly coding in SQL, and the ability to retrieve data from the database as if it were an object.
  • Improved development efficiency, almost all ORM frameworks provide the ability to construct a relational database structure through an object model.
  • The decoupling of the data model from the database is realized, that is, the design of the data model does not need to depend on a specific database, and the database can be easily replaced through simple configuration.

Disadvantages of ORM:

  • There is a performance cost, and automation means mapping and association management at the expense of performance. Various ORM frameworks are now trying to mitigate this (LazyLoad, Cache) in various ways.
  • For complex queries, such as calculated column, case, Group, Having, Order by, exists, etc.
  • It consumes more memory. Using ORM, all the data of an object is extracted into an in-memory object, and then filtered and processed, which can easily cause performance problems.
  • Shifting complexity from the database to the application code increases Python’s total code volume by not splitting the code between the application and the database stored procedures.

Common Python ORM:

  • Django ORM: Django ORM comes with the Django framework and is suitable for simple to moderately complex database operations. The ORM is based on complex query operations converted into SQL statements, compared to directly written in SQL or SQLAlchemy generated statements, more cumbersome.
  • SQLAlchemy: Is a highly acclaimed Python ORM. Compared to Django ORM, it simplifies writing complex database queries.
  • Peewee: “simpler, smaller and more hackable” than SQLAlchemy. Peewee is to SQLAlchemy what SQLite is to PostgreSQL. An ORM does not need to be applicable to all usage situations to be considered useful.

Django ORM vs. SQLAlchemy

The two most common implementations of ORM are ActiveRecord and DataMapper. First, the two persistent modes have the following characteristics:

  • Active Record is a 1:1 storage relationship between domain model object fields and data table fields, that is, one model field corresponds to one data table field; The model object then provides a save() method to persist the model object to the storage layer. The model is aware of the data layer, which is coupled to the data persistence layer.
  • Data Mapper is a completely loose coupling between domain model objects and Data tables. Domain objects are only responsible for processing business logic and do not know the Data layer, that is, they are decoupled from the Data layer. Use an entity manager to persist model objects to the storage tier; The fields of the model object can be any name, as long as they fit the business model, and can be mapped to different fields of the data layer table.

Django ORM uses an active record implementation-one you’ll find in most ORMs. Basically, every row in the database maps directly to an object in the code, and vice versa. ORM frameworks like Django don’t need to define schemas up front in order to use properties in code, just use them, because the framework can “understand” the structure by looking at the database schema. Alternatively, you can save the record to the database only, because it also maps to specific rows in the table.

SQLAlchemy implements data mapping – when implemented this way, there is a gap between the database structure and the object structure (unlike the 1:1 implementation of active records). In most cases, an additional persistence layer must be used to maintain interaction with the database (for example, to save objects). So you can’t just call the save() method when implementing active records, but on the other hand, the code doesn’t need to know how the entire relational structure works in the database because there is no direct relationship between the code and the database.

Obviously, Active Record is simple but not flexible, while Data Mapper is flexible but adds an entity manager and complexity. If your application is mostly CRUD (Create, read, update, delete) and you don’t use difficult and complex rules between different data entities, you should use an active record implementation (Django). If there are many “business rules” and constraints, it is best to use a data mapping model because it does not tie and force strict compliance with activity records.

  • Using complex queries In some cases, Django and SQLAlchemy can be used together. In the real world, you’ll see Django as the main use case for all regular CRUD operations, while SQLAlchemy is used for more complex queries, usually read-only queries.
  • Another difference between the two frameworks is that Django can automatically create primary keys for tables, whereas SQLAlchemy can’t. You must manually create a primary key for each table.
  • Auto-commit By default, Django auto-commits, SQLAlchemy doesn’t. Automatic referrals affect how the framework is used (transactions, rollback, and so on).
  • The supported databases are available for MySQL, PostgreSQL, Oracle, and SQLite. If you are using MSSQL, you should use SQLAlchemy as it fully supports MSSQL and more information and documentation can be found.
  • Learning curve Django is easier to learn. Because it is usually used for use cases that are not particularly complex.
  • Community size SQLAlchemy has the largest community of any Python ORM framework. If community is important to you, SQLAlchemy should be the place to go.
  • Performance The way you use framework features can have a big impact on the overall performance of the data layer in your application. So don’t choose frameworks by performance, but learn how to use them properly.

Django’s Model driver pays very little attention to implementation details at the database level, and the process by which developers define models is very declarative rather than procedural, which may make Django Model more appealing than SQLAlchemy for new projects. The traditional use of SQLAlchemy is not to invade the model, in a separate place to define table structure, mapping rules, and then use SQLAlchemy driver injection into the model class, this method can completely avoid model and database coupling, but the definition of cumbersome, Understand engine, metadata, table, column, and Mapper. Now SQLAlchemy provides a declarative approach, much like the Django Model, but not quite as declarative. Django Model declarative is much simpler than SQLAlchemy Declarative. For example, for class associations, Django automatically generates association objects by declaring foreign keys, whereas SQLAlcyhemy defines foreign keys, relationship objects, and its own join rules if there are multiple foreign keys.

Django-supported databases

Djangos built-in database engine includes:

  • ‘the django. Db. Backends. Postgresql’
  • ‘the django. Db. Backends. Mysql’
  • ‘the django. Db. Backends. Sqlite3’
  • ‘the django. Db. Backends. Oracle’

Other supported third-party databases are:

  • SAP SQL Anywhere
  • IBM DB2
  • Microsoft SQL Server
  • firebird
  • ODBC
  • ADSDB

Reference links:

  • Docs.djangoproject.com/en/2.0/ref/…
  • Docs.djangoproject.com/en/2.0/ref/…
  • Docs.djangoproject.com/en/2.0/topi…

Djangos model class definition

The first task of developing a Django model is to define the model classes and their properties. Each model class can be mapped to a data table in the database, and class attributes are mapped to data fields. In addition, primary keys, foreign keys, constraints, and so on of database tables are also defined through class attributes.

The basic structure of the model definition is as follows:

from django.db import models class ModelName(models.Model): field1 = models.XXField(...) field2 = models.XXField(...) . class Meta: db_table = ... other_metas = ...Copy the code

  • All Django models inherit from the db.models.Model class
  • Class attributes define model fields, which must be of some XXField type
  • The Meta subclass of the class defines the metadata for the model, such as the database table name, database default sorting, and so on

The Meta attribute names are predefined by Django. The common Meta attributes are as follows:

  • Abstract: True or False indicates whether this class is abstract
  • App_label: Defines the application to which this class belongs, for example app_label= ‘myapp’
  • Base_manager_name: The name of the _base_manager manager for the custom model. The Model Manager is where Django provides apis for models.
  • Db_table: specifies the name of the mapped database table. If this parameter is not provided, the database table is automatically named in the format of “application name_model name”.
  • Db_tablespace: The name of the tablespace to be mapped. It is only useful in certain databases (such as Oracle) that have the concept of tablespace, otherwise it will be ignored
  • Default_manager_name: The name of the manager used by the _default_manager of the model.
  • Default_related_name: Defines the reverse relational reference name for this model. Default is the same as the model name. This name is used by default for a relationship between an associated object and the current object. The default value is < model_name > _set
  • Get_latest_by: Defines which field values are arrayed to get the start or end records of the model. This property typically executes a date or integer for a model field.
  • Managed: True or False, which defines whether Djangos py command line tool manages this model. The default is True. If set to False, the database tables of the cost model will not be generated in the database when Python Manage Migrate is run.
  • Order_with_respect_to: Define that this model can be ordered by the relation of a foreign key reference.
  • If the order is in descending order, add a minus sign () to the front of the field name. If the order is in descending order, it can be set without ordering.
  • Permissions: Sets the additional permissions in the permissions table when an object is created. It is a tuple or list of tuples in the format (permission_code, human_readable_permission_name).
  • Default_permissions: Model operation permissions, default (‘ add ‘, ‘change’, ‘delete’)
  • Proxy: True or False, whether this model and all submodels that inherit from this model are proxy models.
  • Required_db_features: Defines the required features of the underlying database. For example required_DB_features =[‘ gIS_Enabled ‘], only the data model will be generated in the database that meets gIS_Enabled.
  • Required_db_vendor: Defines the types of underlying databases, such as SQLite, PostgreSQL, MySQL, and Oracle. If this property is defined, the model can only be maintained in the database in which it is declared.
  • Select_on_save: this option is to determine whether the Django USES 6 Django before. The models. The Model. The save () method. Older algorithms use SELECT to determine if there are rows that need to be updated. Newer algorithms try to use UPDATE directly. In some rare cases, an existing row UPDATE operation is not visible to Django. For example, PostgreSQL’s ON UPDATE trigger returns NULL. In this case, the new algorithm performs an INSERT at the end, even though the row already exists in the database. Normally this property does not need to be set. The default is False.
  • Indexes: A list of indexes to be defined on the model.
  • Unique_together: set unique_together = ((” driver “, “restaurant”)
  • Index_together: index_together = [[” pub_date “, “deadline”],]
  • Verbose_name: specifies a singular object name that is easy to understand and express. If this is not set, Django splits the class name into its own name. For example, CamelCase becomes CamelCase,
  • Verbose_name_plural: Specifies an object name that is plural and easy to understand and formulate. If this is not set, Django uses verbose_name + ‘s’.

Read-only Meta attributes:

  • Label: representation of an object that returns object_name, e.g. ‘polls.Question’.
  • Label_lower: representation of the model, returns model_name, for example ‘polls. Question’.

Reference links: docs.djangoproject.com/en/2.0/ref/…

Generic field types in the Django model

All Field types must be derived from Django.db.models.Field. Developers can define their own Field types derived from this class, or they can use django’s predefined set of Field subclasses.

Django Predefined subclasses

  • AutoField
  • BigAutoField
  • SmallIntegerField
  • PositiveSmallIntegerField
  • IntegerField
  • PositiveIntegerField
  • BigIntegerField
  • FloatField
  • DecimalField: The max_digits parameter is passed,decimal_places mandatory.
  • DateField
  • TimeField
  • DateTimeField
  • DurationField: Stores time periods, built using Python’s timedelta type.
  • BooleanField
  • NullBooleanField: Similar to BooleanField, but with more None options.
  • CharField
  • TextField
  • SlugField: An input field that can contain only letters, digits, underscores, and hyphens, usually used in urls.
  • URLField
  • EmailField
  • GenericIPAddressField
  • UUIDField
  • BinaryField: A binary data field that can only be assigned by bytes.
  • FilePathField
  • FileField: A file upload field. Parameter upload_to must be passed when defining this field to save the path of the server file system where the file is uploaded.
  • ImageField: Similar to FileField, and verifies that the uploaded image is a valid one. There are two optional parameters, height_field and width_field. If these parameters are provided, the image will be saved to the provided width and height. This field requires the Installation of the Python Imaging library.

Reference address: docs.djangoproject.com/en/2.0/ref/…

Common field parameters for Django models

  • Null: Defines whether database fields are allowed to be null. The default is False.
  • Blank: Defines whether a field can be empty for form validation and whether no data can be entered. The default is False.
  • Choices: Defines the optional values of the field, which is a tuple of two-dimensional elements, where the first value of each element is the actual stored value and the second value is the value displayed when the selection is made in HTML.
  • Db_column: The name used to represent this field in the database. If not, Django will use the Field name as the Field name
  • Db_index: If True, an index will be created for this field.
  • Db_tablespace: The name of the database tablespace will be used as the index name for the field if it has an index. DEFAULT_INDEX_TABLESPACE is specified by DEFAULT_INDEX_TABLESPACE if DEFAULT_INDEX_TABLESPACE is already set, and db_TABLESPACE if not set. This option is ignored if the background database does not support table Spaces or indexes
  • Default: indicates the default value
  • Editable: If set to False, this field will not appear in admin or others who will also skip model validation. The default is True
  • Error_messages: The error_messages parameter lets you override error messages thrown by default. Identify the error message you want to override by specifying a key.
  • Help_text: HELP string for HTML page input controls
  • Primary_key: Defines whether the field is the primary key, True or False
  • Unique: Whether the constraint is unique to the field definition
  • unique_for_date
  • unique_for_month
  • unique_for_year
  • Verbose_name: The more readable name of a field. If the user doesn’t set a redundant name field, Django automatically converts the underscore in the field’s attribute name to a space and uses it to create a redundant name. For details, see Verbose field names.
  • Validators: A list of validators that this field will run. See the Validators documentation for more information.
  • Nameless parameter: Sets the personalized name of the field in the HTML page

Reference links: docs.djangoproject.com/en/2.0/ref/…

Custom fields for Django models

Django provides a lot of fields, but sometimes they don’t meet our needs. Django does, however, provide ways to customize fields. As it is not commonly used, details are not covered here.

The sample code: djangosnippets.org/snippets/28…

Reference links: docs.djangoproject.com/en/2.0/howt…

Relationship Settings for Django models

The most important function of relational database is to make use of the relationship between data tables for data modeling and business development. The three relational models of the Django model (1:1, 1:N, and M:N) have strong support.

One-to-one relationship

In the SQL language, a one-to-one relationship is accomplished by having the same primary key between two tables. The OneToOneField field can be defined in any model in the Django model layer.

from django.db import models
 
class Account(models.Model):
    user_name = models.CharField(max_length=80)
    password = models.CharField(max_length=255)
    reg_date = models.DateField()
 
class Contact(models.Model):
    account = models.OneToOneField(
        Account,
        on_delete=models.CASCADE,
        primary_key=True
    )
    zip_code = models.CharField(max_length=10)
    address = models.CharField(max_length=80)
mobile = models.CharField(max_length=20)Copy the code

The generated SQL is:

BEGIN;
--
-- Create model Account
--
CREATE TABLE "app_account" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "user_name" varchar(80) NOT NULL, "password" varchar(255) NOT NULL, "reg_date" date NOT NULL);
--
-- Create model Contact
--
CREATE TABLE "app_contact" ("account_id" integer NOT NULL PRIMARY KEY REFERENCES "app_account" ("id") DEFERRABLE INITIALLY DEFERRED, "zip_code" varchar(10) NOT NULL, "address" varchar(80) NOT NULL, "mobile" varchar(20) NOT NULL);
COMMIT;Copy the code

As you can see from the code above:

  • The two model relationships are defined by the Account field in the Contact model
  • The OneToOneField parameter is defined as: class OneToOneField(to, on_delete, parent_link=False, **options)
    • The first parameter is the name of the associated class
    • The value of the second on_delete is defined by db.models. Deletion.
      • CASCADE: Simulates the ON DELETE CASCADE constraint in SQL to simultaneously DELETE model objects with foreign keys (default and most commonly used)
      • -Leonard: DO_NOTHING
      • PROTECT: Prevents the deletion operation. When the deletion is performed, a ProtectedError exception is raised
      • SET: SET to a value passed to SET() or the return value of a callback function.
      • SET_DEFAULT: Sets the foreign key field to the default value. This parameter is available only when the default parameter is set for the field.
      • SET_NULL: Sets the foreign key field to NULL. This value can be used only when the field is set to NULL =True.

One-to-many relationship

In SQL, the 1:N relationship is accomplished by setting a foreign key reference to the “primary table” in the “schedule”. Foreign keys can be defined in the Django model layer using fields of type Models.foreignKey.

In the above example, if the relationship between Account and Concact is one-to-many, then simply change the code to:

from django.db import models class Account(models.Model): user_name = models.CharField(max_length=80) password = models.CharField(max_length=255) reg_date = models.DateField() class Contact(models.Model): account = models.ForeignKey( Account, on_delete=models.CASCADE ) zip_code = models.CharField(max_length=10) address = models.CharField(max_length=80) mobile =  models.CharField(max_length=20)Copy the code

The generated SQL statement is as follows:

BEGIN;
--
-- Create model Account
--
CREATE TABLE "app_account" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "user_name" varchar(80) NOT NULL, "password" varchar(255) NOT NULL, "reg_date" date NOT NULL);
--
-- Create model Contact
--
CREATE TABLE "app_contact" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "zip_code" varchar(10) NOT NULL, "address" varchar(80) NOT NULL, "mobile" varchar(20) NOT NULL, "account_id" integer NOT NULL REFERENCE
S "app_account" ("id") DEFERRABLE INITIALLY DEFERRED);
CREATE INDEX "app_contact_account_id_64f8cb42" ON "app_contact" ("account_id");
COMMIT;Copy the code

Many-to-many relationships

In THE SQL language, the M:N relationship is accomplished by creating an intermediate relational table in which the foreign keys to the two primary tables are defined. So in Django’s model, developers can also choose to define M:N relationships with two 1:N relationships. Also, the Django model defines a more direct way to model M:N relationships by defining fields of type Models.manytomanyField in either of the two models.

In the above example, if the Account and Concact are many-to-many, then simply change the code to:

from django.db import models
 
class Account(models.Model):
    user_name = models.CharField(max_length=80)
    password = models.CharField(max_length=255)
    reg_date = models.DateField()
 
class Contact(models.Model):
    account = models.ManyToManyField(Account)
    zip_code = models.CharField(max_length=10)
    address = models.CharField(max_length=80)
    mobile = models.CharField(max_length=20)Copy the code

The generated SQL is:

BEGIN;
--
-- Create model Account
--
CREATE TABLE "app_account" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "user_name" varchar(80) NOT NULL, "password" varchar(255) NOT NULL, "reg_date" date NOT NULL);
--
-- Create model Contact
--
CREATE TABLE "app_contact" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "zip_code" varchar(10) NOT NULL, "address" varchar(80) NOT NULL, "mobile" varchar(20) NOT NULL);
CREATE TABLE "app_contact_account" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "contact_id" integer NOT NULL REFERENCES "app_contact" ("id") DEFERRABLE INITIALLY DEFERRED, "account_id" integer NOT NULL REFE
RENCES "app_account" ("id") DEFERRABLE INITIALLY DEFERRED);
CREATE UNIQUE INDEX app_contact_account_contact_id_account_id_17ac7ce6_uniq ON "app_contact_account" ("contact_id", "account_id");
CREATE INDEX "app_contact_account_contact_id_f253b307" ON "app_contact_account" ("contact_id");
CREATE INDEX "app_contact_account_account_id_fed8aab3" ON "app_contact_account" ("account_id");
COMMIT;Copy the code

Object-oriented ORM for Django models

One of the strengths of Django’s model-layer ORM is its support for model inheritance, which combines Python’s object-oriented programming approach with the database’s relational table-oriented data structures. Django supports three styles of model inheritance:

  • Abstract class inheritance: The parent class inherits the word Model, but does not generate the corresponding tables in the underlying database. The attribute columns of the parent class are stored in the data tables of its subclasses.
  • Multi-table inheritance: Each model class of multi-table inheritance generates the corresponding data table management data in the underlying database.
  • Proxy schema inheritance: The parent class is used to manage data tables in the underlying database, while the subclass does not define data columns, but only metadata such as how to sort the query database set.

Abstract class inheritance

The abstract base class is defined by defining the attribute abstract=True in the model’s Meta.

from django.db import models
 
class MessageBase(models.Model):
    id = models.AutoField(primary_key=True)
    content = models.CharField(max_length=100)
    user_name = models.CharField(max_length=80)
    pub_date = models.DateField()
 
    class Meta:
        abstract = True
 
class Moment(MessageBase):
    headline = models.CharField(max_length=50)
 
class Comment(MessageBase):
score = models.IntegerField()Copy the code

This example actually generates two tables in the database:

  • The moment table contains the id, content, user_name, pub_date, and headline fields
  • Data table COMMENT: contains five fields: ID, content, user_name, PUB_date and score

Multi-table inheritance

In multi-table inheritance, both parent and child tables maintain model data with corresponding tables in the database, and fields of the parent class are not repeatedly defined in related tables of multiple subclasses. Multi-table inheritance does not require special keywords.

from django.db import models
 
class MessageBase(models.Model):
    id = models.AutoField(primary_key=True)
    content = models.CharField(max_length=100)
    user_name = models.CharField(max_length=80)
    pub_date = models.DateField()
 
class Moment(MessageBase):
    headline = models.CharField(max_length=50)
 
class Comment(MessageBase):
    score = models.IntegerField()Copy the code

This example actually generates 3 tables in the database:

  • Messagebase: contains id, content, user_name, and pub_date fields
  • Headline headline for messagebase_ptr_ID
  • Data table COMMENT: there are two fields messagebase_ptr_id and score

Proxy model inheritance

In proxy model inheritance, subclasses are used only to manage the data of their parent class, not to actually store the data. Proxy model inheritance is implemented by defining the proxy=True attribute in the Meta of the subclass.

from django.db import models
 
class Moment(models.Model):
    id = models.AutoField(primary_key=True)
    headline = models.CharField(max_length=50)
    content = models.CharField(max_length=100)
    user_name = models.CharField(max_length=80)
    pub_date = models.DateField()
 
class OrderedMoment(Moment):
    class Meta:
        proxy = True
        ordering = ["-pub_date"]Copy the code

The reason for using proxy model inheritance is that new characteristics in subclasses do not affect the behavior of existing code in the parent model machine.