055.Python front end Django model ORM

In the process of centos experiment, pymql always had attribute errors, which made it difficult to find out the problems. A ubuntu desktop system was built again, and pycharm development tool was used as the learning and development tool. The specific reason was that there was a problem in project naming, which was in conflict with its own module, so pymql could not succeed

Introduction to ORM

Object Relational Mapping (ORM for short).

Simply put, ORM uses metadata that describes the mapping between objects and databases to automatically persist objects in a program into a relational database. ORM acts as a bridge between business logic layer and database layer.

A diagram is used to introduce a relationship between the database and python code:

1.1 ORM features

Advantages of ORM

  • Without writing tedious SQL statements and using the familiar python code, we can operate the data and improve the development efficiency;
  • You can smooth the operation and switch the database.

Disadvantages of ORM

  • When the ORM code is converted into SQL statement, it takes a certain time, and the execution efficiency will be reduced;
  • Writing ORM code for a long time will weaken the ability to write SQL statements.  

1.2 improvement

If you want to print sql during orm conversion, you need to configure it in settings as follows:

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

Two single table operation

The process of django using MySQL:

2.1 create MySQL database

Note: ORM cannot operate to the database level, only to the data table.

Install mysql

apt-get install mysql-server mysql-common mysql-client

root@darren-virtual-machine:~# systemctl status mysql

mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Sun 2020-04-05 11:46:45 CST; 58s ago
 Main PID: 16219 (mysqld)
    Tasks: 27 (limit: 11869)
   CGroup: /system.slice/mysql.service
           └─16219 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

4 month 05 11:46:44 darren-virtual-machine systemd[1]: Starting MySQL Community Server...
4 month 05 11:46:45 darren-virtual-machine systemd[1]: Started MySQL Community Server.

root@darren-virtual-machine:~# mysql -V

mysql  Ver 14.14 Distrib 5.7.29, for Linux (x86_64) using  EditLine wrapper

root@darren-virtual-machine:~# cat /etc/mysql/debian.cnf

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = gpitUmpta6ImT8hh
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = gpitUmpta6ImT8hh
socket   = /var/run/mysqld/mysqld.sock

You can use the user name and password above, or you can log in directly

Login mysql

2.2 change password

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set authentication_string=PASSWORD("123456")  where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> update user set plugin="mysql_native_password";
Query OK, 1 row affected (0.00 sec)
Rows matched: 4  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit

2.3 log in database and create database

root@darren-virtual-machine:~# mysql -uroot -p123456

mysql> create database django default charset=utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| django             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

III. create project connection database

3.1 create a new project

root@darren-virtual-machine:~# cd /root/PycharmProjects/

root@darren-virtual-machine:~/PycharmProjects# django-admin startproject mystie

root@darren-virtual-machine:~/PycharmProjects# cd mysite/

root@darren-virtual-machine:~/PycharmProjects/mysite# tree ./*

./manage.py [error opening dir]
./mysite
├── asgi.py
├── __init__.py
├── settings.py
├── urls.py
└── wsgi.py

3.2 create an app

root@darren-virtual-machine:~/PycharmProjects/mysite# python3 manage.py startapp app01

root@darren-virtual-machine:~/PycharmProjects/mysite# vim mysite/settings.py

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'app01.apps.App01Config',
]

3.3 set database connection in settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'django',
        'HOST': '127.0.0.1',
        'PORT': 3306,
        'USER': "root",
        'PASSWORD': "123456",
    }
}

3.4 pymysql operation

root@darren-virtual-machine:~/PycharmProjects/mysite#  pip3 install pymysql

Collecting pymysql
  Downloading PyMySQL-0.9.3-py2.py3-none-any.whl (47 kB)
     |████████████████████████████████| 47 kB 175 kB/s
Installing collected packages: pymysql
Successfully installed pymysql-0.9.3

3.4.1 tell Django to use pymysql module to connect mysql database

root@darren-virtual-machine:~/PycharmProjects/mysite# vim mysite/__init__.py 

# In "init". Py "under the same directory as settings.py
import pymysql
pymysql.install_as_MySQLdb()

3.4.2 create model (write class in models.py)

[root@node10 pymysql]# cat app01/models.py

from django.db import models

# Create your models here.
class Book(models.Model):
        title = models.CharField(max_length=32)
        author = models.CharField(max_length=32)
        publisher = models.CharField(max_length=32)
        price = models.DecimalField(max_digits=5, decimal_places=2)
        pub_date = models.DateField()

Extension: more fields and parameters

Each field has some unique parameters. For example, CharField needs the max? Length parameter to specify the size of VARCHAR database fields. There are also general parameters that apply to all fields. These parameters are defined in detail in the document. Here we will briefly introduce some of the most commonly used parameters:

More fields:

<1> CharField
        String field for shorter strings
        CharField requires a parameter maxlength to limit the maximum number of characters allowed for this field from the database layer and Django verification layer
 
<2> IntegerField
       #Used to hold an integer
 
<3> FloatField
        A floating-point number. Two parameters must be provided:
         
        Parameter description
        Max? Digits (excluding decimal point and symbol)
        Decimal place
                For example, to save a maximum value of 999 (2 decimal places), you define the fields as follows:
                 
                models.FloatField(..., max_digits=5, decimal_places=2)
                To save a maximum value of one million (10 decimal places), you need to define it as follows:
                 
                models.FloatField(..., max_digits=19, decimal_places=10)
                admin uses a text box (< input type = "text" >
 
<4> AutoField
        An IntegerField that grows automatically when you add records. You usually don't need to use this field directly;
        Customize a primary key: my_id=models.AutoField(primary_key=True)
        If you do not specify a primary key, the system will automatically add a primary key field to your model
 
<5> BooleanField
        A true/false field. admin uses a checkbox to represent such fields
 
<6> TextField
        A large text field
        admin represents the field data with a < textarea > (a multiline edit box)
 
<7> EmailField
        A CharField with check Email validity does not accept maxlength parameter
 
<8> DateField
        A date field. There are the following additional optional parameters:
        Argument description
        Auto now automatically sets the value of this field to the current time when the object is saved. It is usually used to represent the "last modified" timestamp
        When an object is created for the first time, the value of this field is automatically set to the current time. It is usually used to represent the creation time of the object
<1> CharField
        String field for shorter strings
        CharField requires a parameter maxlength to limit the maximum number of characters allowed for this field from the database layer and Django verification layer
 
<2> IntegerField
       #Used to hold an integer
 
<3> FloatField
        A floating-point number. Two parameters must be provided:
         
        Parameter description
        Max? Digits (excluding decimal point and symbol)
        Decimal place
                For example, to save a maximum value of 999 (2 decimal places), you define the fields as follows:
                 
                models.FloatField(..., max_digits=5, decimal_places=2)
                To save a maximum value of one million (10 decimal places), you need to define it as follows:
                 
                models.FloatField(..., max_digits=19, decimal_places=10)
                admin uses a text box (< input type = "text" >
 
<4> AutoField
        An IntegerField that grows automatically when you add records. You usually don't need to use this field directly;
        Customize a primary key: my_id=models.AutoField(primary_key=True)
        If you do not specify a primary key, the system will automatically add a primary key field to your model
 
<5> BooleanField
        A true/false field. admin uses a checkbox to represent such fields
 
<6> TextField
        A large text field
        admin represents the field data with a < textarea > (a multiline edit box)
 
<7> EmailField
        A CharField with check Email validity does not accept the maxlength parameter
 
<8> DateField
        A date field. There are the following additional optional parameters:
        Argument description
        Auto now automatically sets the value of this field to the current time when the object is saved. It is usually used to represent the "last modified" timestamp
        When an object is created for the first time, the value of this field is automatically set to the current time. It is usually used to represent the creation time of the object
<14> SlugField
       "slug" is a newspaper term. slug is a small mark (short label) of something, containing only letters, numbers, underscores and hyphens. 3535; they are usually used for URLs
       If you use Django development version, you can specify maxlength. If maxlength is not specified, Django will use the default length: 50
       In the previous Django version, there was no way to change the length of 50
       This implies db_index=True
       It takes an extra parameter: prepopulate from, which is a list of fields from which to auto - ා
       the slug, via JavaScript,in the object's admin form: models.SlugField
       (prepopulate from = ("pre name", "name")) prepopulate from does not accept DateTimeFields
 
<13> XMLField
        A TextField that verifies whether the value is legal XML must provide the parameter: schema? Path, which is the file system path of RelaxNG schema? Used to verify the text
 
<14> FilePathField
        The optional project is the file name in a specific directory. It supports three special parameters, the first of which must be provided
        Parameter description
        Path required parameter. The absolute file system path of a directory. FilePathField gets the optional item accordingly
        Example: "/home/images".
        match optional parameter. A regular expression, as a string, FilePathField will use it to filter filenames 
        Note that this regular expression only applies to base filename instead of
        Full path name. Example: "foo.*\.txt ^", will match file foo23.txt but not bar.txt or foo23.gif
        recursive optional parameter. Either True or False. The default value is False. Include all subdirectories under path
        These three parameters can be used at the same time
        match only applies to the base filename, not the full path name. Then, this example:
        FilePathField(path="/home/images", match="foo.*", recursive=True)
        ... will match / home/images/foo.gif and not / home/images/foo/bar.gif
 
<15> IPAddressField
        An IP address in the form of a string, (i.e. "24.124.1.30")
<16> CommaSeparatedIntegerField
        Used to store comma separated integer values. Similar to CharField, maxlength parameter is required

More parameters:

(1)null
 
If True, Django uses NULL to store NULL values in the database. The default is False
 
(1)blank
 
If it is True, the field is allowed to be left blank. The default is False.
Note that this is different from null. Null is purely a database category, while blank is a data validation category.
If a field's blank=True, the form's validation allows the field to be null. If blank=False, the field is required.
 
(2)default
 
The default value of the field. It can be a value or a callable object. If callable, every new object created will be called.
 
(3)primary_key
 
If True, this field is the primary key of the model. If you do not specify the primary_key=True of any field,
Django will automatically add an IntegerField field as the primary key, so unless you want to override the default primary key behavior,
Otherwise, it is unnecessary to set the primary_key=True of any field.
 
(4)unique
 
If the value is set to True, the value of this data field must be unique throughout the table
 
(5)choices
 An iteratable object (such as a list or tuple) consisting of two tuples, used to provide a selection for a field. If choices is set, the default form will be a selection box instead of a standard text box, < br > and the options of this selection box are those in choices.

3.4.3 execute data migration command

root@darren-virtual-machine:~/PycharmProjects/mysite#  python3 manage.py  makemigrations

The following is the wrong report.

Currently, MySQL client only supports Python 3.4, so if you use a later version of python, you need to modify it as follows:

root@darren-virtual-machine:~/PycharmProjects/mysite# vim /usr/local/lib/python3.6/dist-packages/django/db/backends/mysql/base.py

#if version < (1, 3, 13):
#    raise ImproperlyConfigured('mysqlclient 1.3.13 or newer is required; you have %s.' % Database.__version__)

Just comment it out.

Continue execution

root@darren-virtual-machine:~/PycharmProjects/mysite#  python3 manage.py  makemigrations

Migrations for 'app01':
  app01/migrations/0001_initial.py
    - Create model Book

root@darren-virtual-machine:~/PycharmProjects/mysite# ll app01/migrations/

-rw-r--r-- 1 root root  739 4 month   5 16:40 0001_initial.py
-rw-r--r-- 1 root root    0 4 month   5 14:11 __init__.py
drwxr-xr-x 2 root root 4096 4 month   5 16:40 __pycache__/

root@darren-virtual-machine:~/PycharmProjects/mysite# cat app01/migrations/0001_initial.py

# Generated by Django 3.0.5 on 2020-04-05 08:40

from django.db import migrations, models


class Migration(migrations.Migration):

    initial = True

    dependencies = [
    ]

    operations = [
        migrations.CreateModel(
            name='Book',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('title', models.CharField(max_length=32)),
                ('author', models.CharField(max_length=32)),
                ('publisher', models.CharField(max_length=32)),
                ('price', models.DecimalField(decimal_places=2, max_digits=5)),
                ('pub_date', models.DateField()),
            ],
        ),
    ]

3.4.4 migration operation

root@darren-virtual-machine:~/PycharmProjects/mysite# python3 manage.py  migrate

Operations to perform:
  Apply all migrations: admin, app01, auth, contenttypes, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying app01.0001_initial... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying sessions.0001_initial... OK

view the database

mysql> show tables;
+----------------------------+
| Tables_in_django           |
+----------------------------+
| app01_book                 |
| auth_group                 |
| auth_group_permissions     |
| auth_permission            |
| auth_user                  |
| auth_user_groups           |
| auth_user_user_permissions |
| django_admin_log           |
| django_content_type        |
| django_migrations          |
| django_session             |
+----------------------------+
11 rows in set (0.00 sec)

mysql> desc app01_book;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| title     | varchar(32)  | NO   |     | NULL    |                |
| author    | varchar(32)  | NO   |     | NULL    |                |
| publisher | varchar(32)  | NO   |     | NULL    |                |
| price     | decimal(5,2) | NO   |     | NULL    |                |
| pub_date  | date         | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

IV. operation on Database

4.1 add table record

Configure url

root@darren-virtual-machine:~/PycharmProjects/mysite# vim mysite/urls.py 

from django.contrib import admin
from django.urls import path,include

urlpatterns = [
    path('admin/', admin.site.urls),
    path('app01/', include("app01.urls")),
]

root@darren-virtual-machine:~/PycharmProjects/mysite# vim app01/urls.py

from django.urls import path,re_path
from app01 import views
urlpatterns = [
    path('add_book/', views.add_book),
]

view file

root@darren-virtual-machine:~/PycharmProjects/mysite# vim app01/views.py 

from django.shortcuts import render,HttpResponse
from app01 import models

# Create your views here.
def add_book(request):
    if request.method == "GET":
        return render(request,"add_book.html")
    else:
    #Method 1: instantiate the object by class. Note: be sure to object. save()
        title = request.POST.get("title")
        author = request.POST.get("author")
        publisher = request.POST.get("publisher")
        price = request.POST.get("price")
        pub_date = request.POST.get("pub_date")
        book_obj = models.Book(title=title,author=author,publisher=publisher,price=price,pub_date=pub_date)
        book_obj.save()
    return HttpResponse("New success")

html template file

Create templates directory

root@darren-virtual-machine:~/PycharmProjects/mysite# mkdir templates

Register template

root@darren-virtual-machine:~/PycharmProjects/mysite# vim mysite/settings.py

TEMPLATES = [
    {
        'BACKEND': 'django.template.backends.django.DjangoTemplates',
        'DIRS': [os.path.join(BASE_DIR,"templates")],
        'APP_DIRS': True,
        'OPTIONS': {
            'context_processors': [
                'django.template.context_processors.debug',
                'django.template.context_processors.request',
                'django.contrib.auth.context_processors.auth',
                'django.contrib.messages.context_processors.messages',
            ],
        },
    },
]

root@darren-virtual-machine:~/PycharmProjects/mysite# vim templates/add_book.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<h3>New books</h3>
<form action="" method="post">
    {% csrf_token %}
    <p>Title:<input type="text" name="title"></p>
    <p>Author:<input type="text" name="author"></p>
    <p>Press:<input type="text" name="publisher"></p>
    <p>Price:<input type="text" name="price"></p>
    <p>Published:<input type="text" name="pub_date"></p>
    <input type="submit">
</form>

</body>
</html>

Visit http://127.0.0.1:8000/app01/add_book

Submission

 

view the database

mysql> select * from app01_book;

+----+-----------+-----------+-----------------+--------+------------+
| id | title     | author    | publisher       | price  | pub_date   |
+----+-----------+-----------+-----------------+--------+------------+
|  1 | The Dream of Red Mansion     | Cao Xueqin     | Tsinghua Press       | 165.00  | 2018-12-12 |
+----+-----------+-----------+-----------------+--------+------------+
1 row in set (0.00 sec)

Mode 2: it is implemented through the method create provided by the objects provided by ORM.

root@darren-virtual-machine:~/PycharmProjects/mysite# cat app01/views.py 

from django.shortcuts import render,HttpResponse
from app01 import models

# Create your views here.
def add_book(request):
    if request.method == "GET":
        return render(request,"add_book.html")
    else:
    #Method 1:
        title = request.POST.get("title")
        author = request.POST.get("author")
        publisher = request.POST.get("publisher")
        price = request.POST.get("price")
        pub_date = request.POST.get("pub_date")
        #book_obj = models.Book(title=title,author=author,publisher=publisher,price=price,pub_date=pub_date)
        #book_obj.save()
    #Mode 2, recommended
        book_obj = models.Book.objects.create(title=title,author=author,publisher=publisher,price=price,pub_date=pub_date)

Access insert data http://127.0.0.1:8000/app01/add_book

Database view record

mysql> select * from app01_book;

+----+--------------+-----------+-----------------+--------+------------+
| id | title        | author    | publisher       | price  | pub_date   |
+----+--------------+-----------+-----------------+--------+------------+
|1 | dream of Red Mansions | Cao Xueqin | Tsinghua Press | 165.00 | December, 2018|
|2 | Three Kingdoms | Luo Guanzhong | Peking University Press | 199.00 | 2018-12-23|
+----+--------------+-----------+-----------------+--------+------------+

4.2 query operation

4.2.1 query API

<1> All(): query all results
 <2> Filter (* * kwargs): it contains objects that match the given filter criteria
 <3> Get (* * kwargs): returns the object matching the given filter criteria. Only one result is returned. If more than one object meets the filter criteria or does not meet the filter criteria, an error will be thrown. 
<4> Exclude (* * kwargs): it contains objects that do not match the given filter criteria 
<5> Order_by (* field): sort query results
 <6> Reverse(): reverse Sorting Query Results 
<7> Count(): returns the number of objects in the database that match the query (QuerySet).  
<8> First(): returns the first record  
<9> Last(): returns the last record  
<10> Exists(): returns True if the QuerySet contains data, False otherwise 
<11> Values (* field): returns a ValueQuerySet -- a special QuerySet. What you get after running is not a series of model instantiated objects, but an iterative dictionary sequence
 <12> Values_list (* field): it is very similar to values(), which returns a tuple sequence and values returns a dictionary sequence 
<13> Distinct(): remove duplicate records from the returned results

4.2.2 fuzzy query based on double underscores

Book.objects.filter(price__in=[100,200,300])
Book.objects.filter(price__gt=100)
Book.objects.filter(price__lt=100)
Book.objects.filter(price__range=[100,200])
Book.objects.filter(title__contains="method")
Book.objects.filter(title__icontains="python")  # Case insensitive
Book.objects.filter(title__startswith="Nine")
Book.objects.filter(pub_date__year=2018)

4.2.3 example

Add test data

root@darren-virtual-machine:~/PycharmProjects/mysite# cat app01/views.py

from django.shortcuts import render,HttpResponse
from app01 import models

# Create your views here.
def add_book(request):
    if request.method == "GET":
        return render(request,"add_book.html")
    else:
    #Method 1:
        title = request.POST.get("title")
        author = request.POST.get("author")
        publisher = request.POST.get("publisher")
        price = request.POST.get("price")
        pub_date = request.POST.get("pub_date")
        #book_obj = models.Book(title=title,author=author,publisher=publisher,price=price,pub_date=pub_date)
        #book_obj.save()
    #Mode 2, recommended
        #book_obj = models.Book.objects.create(title=title,author=author,publisher=publisher,price=price,pub_date=pub_date)
        #Add data and delete it in time after adding
        models.Book.objects.create(title="Water Margin",author="Shi Nai an",price=180, publisher="Beidian press", pub_date="2019-1-12")
        models.Book.objects.create(title="Journey to the West",author="Wu Cheng en",price=185, publisher="Peking University Press", pub_date="2019-2-12")
        models.Book.objects.create(title="The lonely nine sword", author="Feng Qingyang",price = 190, publisher = "Huashan Publishing House", pub_date = "2019-1-15")
        models.Book.objects.create(title="Sunflower collection", author="Invincible Eastern", price=120, publisher="Heimuya press", pub_date="2019-1-20")
        models.Book.objects.create(title="Muscle bone strengthening exercise", author="Master Abbot", price=140, publisher="Shaolin press", pub_date="2019-1-23")
        models.Book.objects.create(title="The Scripture of Ethics", author="Lao Tzu", price=176, publisher="Peking University Press", pub_date="2019-2-23")
        models.Book.objects.create(title="Analects", author="Confucius Di Zi", price=165, publisher="Beidian press", pub_date="2019-2-13")
        models.Book.objects.create(title="nine men's power", author="Zhang Wuji", price=130, publisher="Shaolin press", pub_date="2018-1-23")
        models.Book.objects.create(title="Jiu Yin Zhen Jing", author="Huang shang", price=145, publisher="Song Dynasty press", pub_date="2017-3-23")
    return HttpResponse("New success")

Visit http://127.0.0.1:8000/app01/add_book , click submit

view the database

mysql> select * from app01_book;

mysql> select * from app01_book;
+----+--------------+--------------+--------------------+--------+------------+
| id | title        | author       | publisher          | price  | pub_date   |
+----+--------------+--------------+--------------------+--------+------------+
|  1 | The Dream of Red Mansion       | Cao Xueqin       | Tsinghua Press         | 165.00 | 2018-12-12 |
|  2 | Romance of the Three Kingdoms     | Luo Guanzhong       | Peking University Press         | 199.00 | 2018-12-23 |
|  3 | Water Margin       | Shi Nai an       | Beidian press         | 180.00 | 2019-01-12 |
|  4 | Journey to the West       | Wu Cheng en       | Peking University Press         | 185.00 | 2019-02-12 |
|  5 | The lonely nine sword     | Feng Qingyang       | Huashan Publishing House         | 190.00 | 2019-01-15 |
|  6 | Sunflower collection     | Invincible Eastern     | Heimuya press       | 120.00 | 2019-01-20 |
|  7 | Muscle bone strengthening exercise       | Master Abbot     | Shaolin press         | 140.00 | 2019-01-23 |
|  8 | The Scripture of Ethics       | Lao Tzu         | Peking University Press         | 176.00 | 2019-02-23 |
|  9 | Analects         | Confucius Di Zi     | Beidian press         | 165.00 | 2019-02-13 |
| 10 | nine men's power     | Zhang Wuji       | Shaolin press         | 130.00 | 2018-01-23 |
| 11 | Jiu Yin Zhen Jing     | Huang shang         | Song Dynasty press         | 145.00 | 2017-03-23 |
+----+--------------+--------------+--------------------+--------+------------+

views.py must be restored, otherwise it will be added repeatedly

Edit routing

root@darren-virtual-machine:~/PycharmProjects/mysite# cat app01/urls.py

from django.urls import path,re_path
from app01 import views
urlpatterns = [
    path('add_book/', views.add_book),
    path('query_book',views.query_book),
]

Define view functions

root@darren-virtual-machine:~/PycharmProjects/mysite# cat app01/views.py

from django.shortcuts import render,HttpResponse
from app01 import models

# Create your views here.
def add_book(request):
    if request.method == "GET":
        return render(request,"add_book.html")
    else:
    #Method 1:
        title = request.POST.get("title")
        author = request.POST.get("author")
        publisher = request.POST.get("publisher")
        price = request.POST.get("price")
        pub_date = request.POST.get("pub_date")
        #book_obj = models.Book(title=title,author=author,publisher=publisher,price=price,pub_date=pub_date)
        #book_obj.save()
    #Mode 2, recommended
        book_obj = models.Book.objects.create(title=title,author=author,publisher=publisher,price=price,pub_date=pub_date)
    return HttpResponse("New success")
def query_book(request):
    #All, get all results
    books = models.Book.objects.all()
    print(books)

    return HttpResponse("query was successful")

Visit http://127.0.0.1:8000/app01/query_book

Output result

[05/Apr/2020 11:59:21] "GET /app01/add_book/ HTTP/1.1" 200 618
<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>, <Book: Book object (3)>, <Book: Book object (4)>, <Book: Book object (5)>, <Book: Book object (6)>, <Book: Book object (7)>, <Book: Book object (8)>, <Book: Book object (9)>, <Book: Book object (10)>, <Book: Book object (11)>]>
[05/Apr/2020 11:59:26] "GET /app01/query_book HTTP/1.1" 200 12

model returns a title Value

root@darren-virtual-machine:~/PycharmProjects/mysite# cat app01/models.py

from django.db import models

# Create your models here.
class Book(models.Model):
        title = models.CharField(max_length=32)
        author = models.CharField(max_length=32)
        publisher = models.CharField(max_length=32)
        price = models.DecimalField(max_digits=5, decimal_places=2)
        pub_date = models.DateField()
        def __str__(self):
                return self.title

Visit again to view

[05/Apr/2020 12:04:48] "GET /app01/query_book HTTP/1.1" 200 12
 < queryset [< book: dream of Red Mansions >, < book: Romance of the Three Kingdoms >, < book: Water Margin >, < book: Journey to the West >, < book: Dugu Jiujian >, < book: sunflower Scripture >, < book: Yijinjing >, < book: Daodejing >, < book: Analects >, < book: Jiuyang Shengong >, < book: Jiuyin Scripture >] >

root@darren-virtual-machine:~/PycharmProjects/mysite# cat app01/views.py

from django.shortcuts import render,HttpResponse
from app01 import models

# Create your views here.
def add_book(request):
    if request.method == "GET":
        return render(request,"add_book.html")
    else:
    #Method 1:
        title = request.POST.get("title")
        author = request.POST.get("author")
        publisher = request.POST.get("publisher")
        price = request.POST.get("price")
        pub_date = request.POST.get("pub_date")
        #book_obj = models.Book(title=title,author=author,publisher=publisher,price=price,pub_date=pub_date)
        #book_obj.save()
    #Mode 2, recommended
        book_obj = models.Book.objects.create(title=title,author=author,publisher=publisher,price=price,pub_date=pub_date)
    return HttpResponse("New success")
def query_book(request):
    #The filter Query object, similar to a list, obtains the objects that meet the conditions. The filter can meet multiple conditions, and the relationship between them is and
    books_filter = models.Book.objects.filter(pk=3)
    print(books_filter)
    #Get, get object, no result or multiple results will report an error
    books_get = models.Book.objects.get(pk=3)
    print(books_get,type(books_get))
    #exclude, query type, as opposed to filter, excludes types that meet the conditions
    books_exclude = models.Book.objects.exclude(pk=3)
    print(books_exclude)
    #order_by Default ascending, adding before descending-that will do
    books_order_by = models.Book.objects.order_by("price")
    print(books_order_by)
    books_order_by = models.Book.objects.order_by("-price")
    print(books_order_by)
    #reverse
    books_reverse = models.Book.objects.order_by("-price").reverse()
    print(books_reverse)
    #First get the first
    book_first= models.Book.objects.filter(pk=4).first()
    print(book_first)
    #exist to determine whether the result is present. If yes, return true
    books_exist = models.Book.objects.exclude(pk=4)
    print(books_exist,type(books_exist))
    #vaules,QUeryset type, similar to dictionary
    books_vaules = models.Book.objects.values("title","price")
    print(books_vaules,type(books_vaules))
    # vaules_list,QUeryset type, similar to Yuanzu
    books_vaules1 = models.Book.objects.values_list("title","price")
    print(books_vaules1,type(books_vaules1))
    #distinct, remove duplicates
    books = models.Book.objects.values("publisher").distinct()
    print(books,type(books))

    return HttpResponse("query was successful")

Query results

< queryset [< book: outlaws of the marsh >] >
Water Margin < class' app01. Models. Book '>
< queryset [< book: dream of Red Mansions >, < book: Romance of the Three Kingdoms >, < book: Journey to the West >, < book: Dugu nine swords >, < book: sunflower Scripture >, < book: Yijinjing >, < book: Daodejing >, < book: Analects >, < book: Nine Yang magical skill >, < book: Nine Yin Scripture >] >
< queryset [< book: sunflower Scripture >, < book: Nine Yang magical skill >, < book: Yijinjing >, < book: Nine Yin Scripture >, < book: dream of Red Mansions >, < book: Analects >, < book: Daodejing >, < book: outlaws of the marsh >, < book: Journey to the West >, < book: Dugu nine swords >, < book: Romance of the Three Kingdoms >] >
< queryset [< book: Romance of the Three Kingdoms >, < book: Dugu Jiujian >, < book: Journey to the West >, < book: outlaws of the marsh >, < book: Tao Te Jing >, < book: dream of the red chamber >, < book: Analects >, < book: Nine Yin Scripture >, < book: Yijinjing >, < book: Nine Yang Shengong >, < book: sunflower Scripture >] >
< queryset [< book: sunflower Scripture >, < book: Nine Yang magical skill >, < book: Yijinjing >, < book: Nine Yin Scripture >, < book: dream of Red Mansions >, < book: Analects >, < book: Daodejing >, < book: outlaws of the marsh >, < book: Journey to the West >, < book: Dugu nine swords >, < book: Romance of the Three Kingdoms >] >
Journey to the West
 < queryset [< book: dream of Red Mansions >, < book: Romance of the Three Kingdoms >, < book: Water Margin >, < book: Dugu nine swords >, < book: sunflower Scripture >, < book: Yijinjing >, < book: Daodejing >, < book: Analects >, < book: Nine Yang Shengong >, < book: Nine Yin Scripture >], < class' Django. DB. Models. Query. Queryset '>
{'title': 'sunflower Dictionary', 'price': decimal ('120.00 ')}, Django. DB. Models. Query. Queryset '>
, final ('165.00 '), (' Nine Yang skill ', Decision ('130.00 '), ('nine Yin Scripture', decision ('145.00 '))] > < class' Django. DB. Models. Query. Queryset' >
< queryset [{'publisher': 'Tsinghua Press'}, {' publisher ':' Peking University Press'}, {'publisher': 'Nortel press'}, {' publisher ':' Huashan press'}, {'publisher': 'heimuya press'}, {' publisher ':' Shaolin press'}, {'publisher': 'song press'}] > < class' Django. DB. Models. Query. Queryset' >
[05/Apr/2020 12:37:56] "GET /app01/query_book HTTP/1.1" 200 1

4.3 deleting records

The delete method is delete(). It immediately deletes the object at run time without returning any value. For example:

model_obj.delete()

You can also delete multiple objects at once. Each QuerySet has a delete() method that deletes all objects in the QuerySet at once.

For example, the following code will delete the Book object that pub_date is in 2018:

Book.objects.filter(pub_date__year=2018).delete()

Design url

from django.urls import path,re_path
from app01 import views
urlpatterns = [
    path('add_book/', views.add_book),    #New operation
    path('query_book/',views.query_book), #Wrong query
    path('del_book/',views.del_book)      #Delete operation
]

View function

root@darren-virtual-machine:~/PycharmProjects/mysite# cat app01/views.py

from django.shortcuts import render,HttpResponse
from app01 import models

# Create your views here.
def add_book(request):
    if request.method == "GET":
        return render(request,"add_book.html")
    else:
    #Method 1:
        title = request.POST.get("title")
        author = request.POST.get("author")
        publisher = request.POST.get("publisher")
        price = request.POST.get("price")
        pub_date = request.POST.get("pub_date")
        #book_obj = models.Book(title=title,author=author,publisher=publisher,price=price,pub_date=pub_date)
        #book_obj.save()
    #Mode 2, recommended
        book_obj = models.Book.objects.create(title=title,author=author,publisher=publisher,price=price,pub_date=pub_date)
    return HttpResponse("New success")
def query_book(request):
    #Fuzzy query based on double line dash
    books = models.Book.objects.filter(price_in=[120,160,150])
    print(books)
    books = models.Book.objects.filter(price_range=[120, 160])
    print(books)

    return HttpResponse("query was successful")

def del_book(request):
    pk = request.GET.get('pk')
    print(pk)
    #Method 1 delete in the form of object
    book_obj = models.Book.objects.filter(pk=pk).first()
    book_obj.delete()
    return HttpResponse("Delete successful")

Visit http://127.0.0.1:8000/app01/del_book/?pk=3

view the database

mysql> select * from app01_book;

+----+--------------+--------------+--------------------+--------+------------+
| id | title        | author       | publisher          | price  | pub_date   |
+----+--------------+--------------+--------------------+--------+------------+
|1 | dream of Red Mansions | Cao Xueqin | Tsinghua Press | 165.00 | December, 2018|
|2 | Three Kingdoms | Luo Guanzhong | Peking University Press | 199.00 | 2018-12-23|
|4 | journey to the West | Wu Chengen | Peking University Press | 185.00 | February 12, 2019|
|5 | Dugu Jiujian | elegant | Huashan publishing house | 190.00 | January 15, 2019|
|6 | sunflower Scripture | invincible in the East | heimuya press | 120.00 | January 20, 2019|
|7 | Yijinjing | Abbot master | Shaolin press | 140.00 | January 23, 2019|
|8 | Daodejing | Laozi | Peking University Press | 176 | 2019-02-23|
|9 | Analects | Confucius disciple | Beidian press | 165.00 | 2019-02-13|
|10 | Nine Yang magical skill | Zhang Wuji | Shaolin press | 130.00 | 2018-01-23|
|11 | nine Yin Scripture | huang shang | Song Dynasty press | 145.00 | 2017-03-23|
+----+--------------+--------------+--------------------+--------+------------+

Method two

View function

root@darren-virtual-machine:~/PycharmProjects/mysite# cat app01/views.py

. . . . . . 

def del_book(request):
    pk = request.GET.get('pk')
    print(pk)
    #Method 1 delete in the form of object
    #book_obj = models.Book.objects.filter(pk=pk).first()
    #book_obj.delete()
    #Mode two
    books = models.Book.objects.filter(pk=pk).delete()      #The return value is the number of rows affected
    print(books)
    return HttpResponse("Delete successful"

When Django deletes an object, it mimics the SQL constraint ON DELETE CASCADE. In other words, deleting an object also deletes the foreign key object associated with it. For example:

b = Book.objects.get(pk=1)
# This will delete the Blog and all of its Entry objects.
b.delete()

Note that the delete () method is a method on the QuerySet, but does not apply to the Manager itself. This is a protection mechanism to avoid accidentally calling the Entry.objects.delete() method to cause all records to be deleted by mistake. If you are sure you want to delete all objects, you must explicitly call:

Book.objects.all().delete()  

Visit http://127.0.0.1:8000/app01/del_book/?pk=4

mysql> select * from app01_book;

+----+--------------+--------------+--------------------+--------+------------+
| id | title        | author       | publisher          | price  | pub_date   |
+----+--------------+--------------+--------------------+--------+------------+
|1 | dream of Red Mansions | Cao Xueqin | Tsinghua Press | 165.00 | December, 2018|
|2 | Three Kingdoms | Luo Guanzhong | Peking University Press | 199.00 | 2018-12-23|
|5 | Dugu Jiujian | elegant | Huashan publishing house | 190.00 | January 15, 2019|
|6 | sunflower Scripture | invincible in the East | heimuya press | 120.00 | January 20, 2019|
|7 | Yijinjing | Abbot master | Shaolin press | 140.00 | January 23, 2019|
|8 | moral classics | Laozi | Peking University Press | 176.00 | 2019-02-23|
|9 | Analects | Confucius disciple | Beidian press | 165.00 | 2019-02-13|
|10 | Jiuyang magic skill | Zhang Wuji | Shaolin press | 130.00 | 2018-01-23|
|11 | nine Yin Scripture | huang shang | Song Dynasty press | 145.00 | 2017-03-23|
+----+--------------+--------------+--------------------+--------+------------+

Debug output

4
(1, {'app01.Book': 1})
[05/Apr/2020 13:26:15] "GET /app01/del_book/?pk=4 HTTP/1.1" 200 12 

4.4 modification form record

Design url

from django.urls import path,re_path
from app01 import views
urlpatterns = [
    path('add_book/', views.add_book),    #New operation
    path('query_book/',views.query_book), #Wrong query
    path('del_book/',views.del_book),     #Delete operation
    path('edit_book/',views.edit_book),    #Editing operation
] 

View function

from django.shortcuts import render,HttpResponse
from app01 import models

# Create your views here.
def add_book(request):
    if request.method == "GET":
        return render(request,"add_book.html")
    else:
    #Method 1:
        title = request.POST.get("title")
        author = request.POST.get("author")
        publisher = request.POST.get("publisher")
        price = request.POST.get("price")
        pub_date = request.POST.get("pub_date")
        #book_obj = models.Book(title=title,author=author,publisher=publisher,price=price,pub_date=pub_date)
        #book_obj.save()
    #Mode 2, recommended
        book_obj = models.Book.objects.create(title=title,author=author,publisher=publisher,price=price,pub_date=pub_date)
    return HttpResponse("New success")
def query_book(request):
    #Fuzzy query based on double line dash
    books = models.Book.objects.filter(price_in=[120,160,150])
    print(books)
    books = models.Book.objects.filter(price_range=[120, 160])
    print(books)

    return HttpResponse("query was successful")

def del_book(request):
    pk = request.GET.get('pk')
    print(pk)
    #Method 1 delete in the form of object
    #book_obj = models.Book.objects.filter(pk=pk).first()
    #book_obj.delete()
    #Mode two
    books = models.Book.objects.filter(pk=pk).delete()
    print(books)
    return HttpResponse("Delete successful")

def edit_book(request):
    pk = request.GET.get("pk")
    if request.method == "GET":
        book_obj = models.Book.objects.filter(pk=pk).first()
        return render(request,"edit_book.html",{"book_obj":book_obj})

html template file

root@darren-virtual-machine:~/PycharmProjects/mysite# cat templates/edit_book.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<h3>Edit data</h3>

<form action="" method="post">
    {% csrf_token %}
    <p>Title:<input type="text" name="title" value="{{ book_obj.title }}"></p>
    <p>Author:<input type="text" name="author" value="{{ book_obj.author }}"></p>
    <p>Press:<input type="text" name="publisher" value="{{ book_obj.publisher }}"></p>
    <p>Price:<input type="text" name="price" value="{{ book_obj.price }}"></p>
    <p>Published:<input type="text" name="pub_date" value="{{ book_obj.pub_date|date:"Y-m-d" }}"></p>
    <input type="submit">
</form>

</body>
</html>

Visit http://127.0.0.1:8000/app01/edit_book/?pk=5

Modify display time

<form action="" method="post">
    {% csrf_token %}
    <p>Title:<input type="text" name="title" value="{{ book_obj.title }}"></p>
    <p>Author:<input type="text" name="author" value="{{ book_obj.author }}"></p>
    <p>Press:<input type="text" name="publisher" value="{{ book_obj.publisher }}"></p>
    <p>Price:<input type="text" name="price" value="{{ book_obj.price }}"></p>
    <p>Published:<input type="text" name="pub_date" value="{{ book_obj.pub_date|date:"Y-m-d" }}"></p>
    <input type="submit">
</form>

Refresh

 

Editing operation

Method 1:

By modifying the properties of the instance object. Note: be sure to object. save()

def edit_book(request):
    pk = request.GET.get("pk")
    if request.method == "GET":
        book_obj = models.Book.objects.filter(pk=pk).first()
        return render(request,"edit_book.html",{"book_obj":book_obj})
    else:
    #Method 1:
        title = request.POST.get("title")
        author = request.POST.get("author")
        publisher = request.POST.get("publisher")
        price = request.POST.get("price")
        pub_date = request.POST.get("pub_date")
        book_obj = models.Book.objects.filter(pk=pk).first()
        book_obj.price = price
        book_obj.save()
    return HttpResponse("Editorial success")

Visit http://127.0.0.1:8000/app01/edit_book/?pk= 5 and modify

Database query

+----+--------------+--------------+--------------------+--------+------------+
| id | title        | author       | publisher          | price  | pub_date   |
+----+--------------+--------------+--------------------+--------+------------+
|1 | dream of Red Mansions | Cao Xueqin | Tsinghua Press | 165.00 | December, 2018|
|2 | Three Kingdoms | Luo Guanzhong | Peking University Press | 199.00 | 2018-12-23|
|5 | Dugu Jiujian | elegant | Huashan publishing house | 143.00 | January 15, 2019|
|6 | sunflower Scripture | invincible in the East | heimuya press | 120.00 | January 20, 2019|
|7 | Yijinjing | Abbot master | Shaolin press | 140.00 | January 23, 2019|
|8 | Daodejing | Laozi | Peking University Press | 176 | 2019-02-23|
|9 | Analects | Confucius disciple | Beidian press | 165.00 | 2019-02-13|
|10 | Jiuyang magic skill | Zhang Wuji | Shaolin press | 130.00 | 2018-01-23|
|11 | nine Yin Scripture | huang shang | Song Dynasty press | 145.00 | 2017-03-23|
+----+--------------+--------------+--------------------+--------+------------+

Mode two:

It is implemented through the method update provided by the objects provided by ORM.

from django.shortcuts import render,HttpResponse
from app01 import models

# Create your views here.
def add_book(request):
    if request.method == "GET":
        return render(request,"add_book.html")
    else:
    #Method 1:
        title = request.POST.get("title")
        author = request.POST.get("author")
        publisher = request.POST.get("publisher")
        price = request.POST.get("price")
        pub_date = request.POST.get("pub_date")
        #book_obj = models.Book(title=title,author=author,publisher=publisher,price=price,pub_date=pub_date)
        #book_obj.save()
    #Mode 2, recommended
        book_obj = models.Book.objects.create(title=title,author=author,publisher=publisher,price=price,pub_date=pub_date)
    return HttpResponse("New success")
def query_book(request):
    #Fuzzy query based on double line dash
    books = models.Book.objects.filter(price_in=[120,160,150])
    print(books)
    books = models.Book.objects.filter(price_range=[120, 160])
    print(books)

    return HttpResponse("query was successful")

def del_book(request):
    pk = request.GET.get('pk')
    print(pk)
    #Method 1 delete in the form of object
    #book_obj = models.Book.objects.filter(pk=pk).first()
    #book_obj.delete()
    #Mode two
    books = models.Book.objects.filter(pk=pk).delete()
    print(books)
    return HttpResponse("Delete successful")

def edit_book(request):
    pk = request.GET.get("pk")
    if request.method == "GET":
        book_obj = models.Book.objects.filter(pk=pk).first()
        return render(request,"edit_book.html",{"book_obj":book_obj})
    else:
    #Mode two:
        title = request.POST.get("title")
        author = request.POST.get("author")
        publisher = request.POST.get("publisher")
        price = request.POST.get("price")
        pub_date = request.POST.get("pub_date")
        books = models.Book.objects.filter(pk=pk).update(price=price)    #Returns the number of rows that affect things
        print(books)
    return HttpResponse("Editorial success")

In addition, the update() method is valid for any result set, which means that you can update multiple records at the same time. The update() method will return an integer value indicating the number of affected records.  

Visit http://127.0.0.1:8000/app01/edit_book/?pk= 6 and modify

view the database

mysql> select * from app01_book;
+----+--------------+--------------+--------------------+--------+------------+
| id | title        | author       | publisher          | price  | pub_date   |
+----+--------------+--------------+--------------------+--------+------------+
|  1 | The Dream of Red Mansion       | Cao Xueqin       | Tsinghua Press         | 165.00 | 2018-12-12 |
|  2 | Romance of the Three Kingdoms     | Luo Guanzhong       | Peking University Press         | 199.00 | 2018-12-23 |
|  5 | The lonely nine sword     | Feng Qingyang       | Huashan Publishing House         | 143.00 | 2019-01-15 |
|  6 | Sunflower collection     | Invincible Eastern     | Heimuya press       | 179.00 | 2019-01-20 |
|  7 | Muscle bone strengthening exercise       | Master Abbot     | Shaolin press         | 140.00 | 2019-01-23 |
|  8 | The Scripture of Ethics       | Lao Tzu         | Peking University Press         | 176.00 | 2019-02-23 |
|  9 | Analects         | Confucius Di Zi     | Beidian press         | 165.00 | 2019-02-13 |
| 10 | nine men's power     | Zhang Wuji       | Shaolin press         | 130.00 | 2018-01-23 |
| 11 | Jiu Yin Zhen Jing     | Huang shang         | Song Dynasty press         | 145.00 | 2017-03-23 |
+----+--------------+--------------+--------------------+--------+------------+

Edit successful

Tags: Django MySQL Database vim

Posted on Sun, 05 Apr 2020 21:30:19 -0700 by OM2