ORM operation
The basic operations include addition, deletion, modification and query, that is, crud operation.
The core of ORM CRUD is the model class Manager object

Manager object
Each inherits from models The model class of model will also have an objects object inherited. This object is called the manager object. The addition, deletion, modification and query of the database are realized through the manager of the model.

class MyModel(models.model):
	....
# Objects are manager objects
MyModel.objects.create(...)  

django Shell
django provides an interactive tool django shell, which can perform corresponding operations with project code in interactive mode.
When the project code changes, you need to re-enter the django shell
Startup method: Django manage py shell

Create data

Create data

ORM takes the data (rows) in the database table as python objects. Creating each record in the data is to create an object

Option 1

MyModel.objects.create(Attribute 1=Value 1, attribute 2=Value 2,....)
	Success: returns the created entity object
	Failed: exception thrown

[root@sdns zaishu]# python manage.py shell
Python 3.6.8 (default, Nov 16 2020, 16:55:22) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-44)] on linux
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from bookstore.models import Book
>>> # Option 1
>>> b1 = Book.objects.create(title='Python',info="python curriculum",)
>>> b1
<Book: Book object (1)>

Option 2

establish MyModel Instance object and call save()Save
obj = MyModel(attribute=Value,...)
obj.attribute=value
obj.save()

>>> # Option 2
>>> b2=Book(title='Mysql',info="Mysql curriculum")
>>> b2.save()

practice

Use the django shell to add data to the book table

from django.db import models


# Create your models here.
class Book(models.Model):
    title = models.CharField("title", max_length=50, default='', unique=True)
    pub = models.CharField("press", max_length=50, default="")
    price = models.DecimalField("Price", max_digits=7, decimal_places=2)
    market_price = models.DecimalField(
        "retail price", max_digits=7, decimal_places=2, default=0.0)
    class Meta:
        db_table="book"

class Author(models.Model):
    name=models.CharField("author",max_length=10,default='')
    emai=models.EmailField("mailbox")
    age=models.IntegerField("Age")
    class Meta:
        db_table="author"

[root@sdns zaishu]# python manage.py shell
Python 3.6.8 (default, Nov 16 2020, 16:55:22) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-44)] on linux
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from bookstore.models import Book
>>> b1 = Book.objects.create(title='Python',pub='Tsinghua Publishing House',price=22,market_price=32)
>>> from bookstore.models import Author
>>> b1 = Author.objects.create(name='Coach',age=19)

Query operation

Query introduction

The query of the database needs to be carried out by using the manager object; Via mymodel The objects manager method calls the query method

method	explain
all()	Query all records and return QuerySet query object
get()	Query qualified single record
filter()	Query multiple qualified records
exclude()	Query multiple records other than qualified ones
...

all method

Usage: mymode objects. all()
Function: query all data in MyModel instance, which is equivalent to select * from tab
Return value: QuerySet container object, which stores MyModel instances
queryset is similar to array. Just treat it as an array operation

>>> from bookstore.models import Book
>>> all = Book.objects.all()
>>> all
<QuerySet [<Book: Book object (1)>]>
>>> b2 = Book.objects.create(title='MySQL',pub='People's Publishing House',price=22,market_price=32)
>>> b3 = Book.objects.create(title='Oracle',pub='press',price=10,market_price=12)
>>> all
<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>, <Book: Book object (3)>]>
>>> for book in all:
...     print(book.title)
... 
Python
MySQL
Oracle

__ str__ Output format

Can be defined in the model class__ str__ Method to customize the output format in the QuerySet:

class Book(models.Model):
    title = models.CharField("title", max_length=50, default='', unique=True)
    pub = models.CharField("press", max_length=100, default="",null=True)
    price = models.DecimalField("Price", max_digits=7, decimal_places=2)
    market_price = models.DecimalField(
        "retail price", max_digits=7, decimal_places=2, default=0.0)

    class Meta:
        db_table = "booknew"

    def __str__(self):
        return "%s_%s_%s_%s"%(self.title,self.pub,self.price,self.market_price)

Re enter the shell

>>> from bookstore.models import Book
>>> all = Book.objects.all()
>>> all
<QuerySet [<Book: MySQL_People's Publishing House_22.00_32.00>, <Book: Oracle_press_10.00_12.00>]>
>>> 

value('column 1 ',' column 2 ',...)

Usage: mymodel objects. values(…)
Function: query the data of some columns and return
Equivalent to select column 1, column 2 from tab
Return value: QuerySet
Return the query result container. The container stores dictionaries. Each dictionary represents a piece of data. The format is: {column 1 ': value 2,' column 2 ': value 2}

>>> a2=Book.objects.values('title','pub')
>>> a2
<QuerySet [{'title': 'MySQL', 'pub': 'People's Publishing House'}, {'title': 'Oracle', 'pub': 'press'}]>
>>> for book in a2:
...     print(book['title'])
... 
MySQL
Oracle

value_list('column 1 ',' column 2 ',...)

Usage: mymodel objects. values_ list(…)
Function: returns query results in the form of Yuanzu
Equivalent to select column 1, column 2 from tab
Return value: QuerySet container object, which is stored in Yuanzu
The data returned from the query is encapsulated in Yuanzu and then encapsulated in the query set QuerySet

>>> a3=Book.objects.values_list('title','pub')
>>> a3
<QuerySet [('MySQL', 'People's Publishing House'), ('Oracle', 'press')]>
>>> for book in a3:
...     print(book[0])
... 
MySQL
Oracle

order_by()

Usage: mymodel objects. order_ By ('- column', 'column')
Function: different from the all() method, it uses the order by clause of the SQL statement to sort the query results selectively according to a certain field
Note: the default is ascending order. If it is a flashback, you need to add it in front of the column-

>>> a4=Book.objects.order_by('-price')
>>> a4
<QuerySet [<Book: MySQL_People's Publishing House_22.00_32.00>, <Book: Oracle_press_10.00_12.00>]>

>>> a5 = Book.objects.values_list('title','pub').order_by('-price')
>>> a5
<QuerySet [('MySQL', 'People's Publishing House'), ('Oracle', 'press')]>

Corresponding SQL statement

>>> a5
<QuerySet [('MySQL', 'People's Publishing House'), ('Oracle', 'press')]>
>>> print(a5.query)
SELECT `booknew`.`title`, `booknew`.`pub` FROM `booknew` ORDER BY `booknew`.`price` DESC

Practical operation - Book page

vscode input in Chinese and English mode! Then press the tab key to quickly generate html pages

route

• zaishu

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

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

• bookstore

from django.urls import path
from . import views
urlpatterns = [
    path('all_book',views.all_book)
]

View layer

from django.shortcuts import render
from .models import Book
# Create your views here.

def all_book(request):
    all_book = Book.objects.all()
    return render(request,'bookstore/all_book.html',locals())

Model layer

<body>
    <table border="1">
        <tr>
            <th>id</th>
            <th>title</th>
            <th>pub</th>
            <th>price</th>
            <th>market_price</th>
            <th>op</th>
        </tr>
        {% for book in all_book %}
            <tr>
                <td>{{ book.id }}</td>
                <td>{{ book.title }}</td>
                <td>{{ book.pub }}</td>
                <td>{{ book.price }}</td>
                <td>{{ book.market_price }}</td>
                <td>
                    <a href="">to update</a>
                    <a href="">delete</a>
                </td>
            </tr>
        {% endfor %}
    </table>
</body>

Filter (condition)

Syntax: mymodel objects. Filter (attribute 1 = value 1, attribute 2 = value 2)
Function: returns all data sets containing this condition
Return value: QuerySet container object, which stores MyModel instances
Note: when multiple attributes are together, it is an and relationship

>>> from bookstore.models import Book
>>>> b2=Book.objects.filter(title='mysql')
>>> 
>>> b1 = Book.objects.filter(title='MySQL',price=22)
>>> b1
<QuerySet [<Book: MySQL_People's Publishing House_22.00_32.00>]>
>>> print(b1.query)
SELECT `booknew`.`id`, `booknew`.`title`, `booknew`.`pub`, `booknew`.`price`, `booknew`.`market_price` FROM `booknew` WHERE (`booknew`.`price` = 22 AND `booknew`.`title` = MySQL)
>>> 

Exclude (condition)

Syntax: mymodel objects. Exclude (attribute 1 = value 1, attribute 2 = value 2)
Function: returns all data sets that do not contain this condition
Return value: QuerySet container object, which stores MyModel instances

>>> b1 = Book.objects.exclude(title='MySQL',price=22)
>>> b1
<QuerySet [<Book: Oracle_press_10.00_12.00>]>

get (condition)

Syntax: mymodel objects. Get (property 1 = value 1, property 2 = value 2)
Function: returns the only piece of data that meets the conditions
Note: this method can only return one piece of data. If more than one piece of data is returned, an error will be reported; If no data is returned, an error will be reported.

>>> m1 = Book.objects.get(pub='Tsinghua Publishing House')
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/local/lib64/python3.6/site-packages/django/db/models/manager.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/usr/local/lib64/python3.6/site-packages/django/db/models/query.py", line 412, in get
    (self.model._meta.object_name, num)
bookstore.models.Book.MultipleObjectsReturned: get() returned more than one Book -- it returned 2!

>>> m1 = Book.objects.get(title='MySQL')
>>> m1
<Book: MySQL_People's Publishing House_22.00_32.00>
>>> 

Non equivalent filtering query

For example, where id > 1
Query predicate, each query predicate is an independent query function

expression	meaning
__exact	Equivalent matching=
__contains	Contains the specified value Author. objects. Filter (name__continans = 'w') is equivalent to select * from author where name like '%w%'
__startswith	Start with xxx xxx%
__endswith	End with xxx% xxx
__gt	Greater than the specified value Author.objects.filter(age__gt=50) select * from author where age > 50
__gte	Greater than or equal to
__lt	less than
__lte	Less than or equal to
__in	Query whether the data is within the specified range in
__range	Whether the number of queries is within the specified range between

>>> from bookstore.models import Book
>>> b2=Book.objects.filter(id__gt=1)
>>> b2
<QuerySet [<Book: Oracle_press_10.00_12.00>, <Book: Python_Tsinghua Publishing House_22.00_32.00>, <Book: Html_Tsinghua Publishing House_22.00_32.00>]>

>>> print (b2.query)
SELECT `booknew`.`id`, `booknew`.`title`, `booknew`.`pub`, `booknew`.`price`, `booknew`.`market_price` FROM `booknew` WHERE `booknew`.`id` > 1

ORM update operation

Update steps

To update individual data

1. check
   Get the entity object to be modified through get()
2. change
   By object Property to modify data
3. preservation
   By object save() saves the data

>>> b1=Book.objects.get(id=1)
>>> b1.price=100
>>> b1.save()

Batch update data

Directly call the update of QuerySet (attribute = value) to realize batch modification

>>> # The price of books with modification ID > 1 is changed to 1
>>> b1=Book.objects.filter(id__gt=1)
>>> b1.update(price=1)
>>> 3		## Number of records updated
>>> b1=Book.objects.filter(id__gt=1)
>>> for i in b1:
...     print(i.price)
... 
1.00
1.00
1.00

>>> b1=Book.objects.all()
>>> b1.update(price=10)
4		#Modify all
>>> for i in b1:
...     print(i.price)
... 
10.00
10.00
10.00
10.00

Perfect book page - add update operation

Routing layer

from django.urls import path
from . import views
urlpatterns = [
    path('all_book',views.all_book),
    path('update_book/<int:book_id>',views.update_book)
]

• all_book

<body>
    <table border="1">
        <tr>
            <th>id</th>
            <th>title</th>
            <th>pub</th>
            <th>price</th>
            <th>market_price</th>
            <th>op</th>
        </tr>
        {% for book in all_book %}
            <tr>
                <td>{{ book.id }}</td>
                <td>{{ book.title }}</td>
                <td>{{ book.pub }}</td>
                <td>{{ book.price }}</td>
                <td>{{ book.market_price }}</td>
                <td>
                    <a href="/bookstore/update_book/{{ book.id }}">to update</a>
                    <a href="">delete</a>
                </td>
            </tr>
        {% endfor %}
    </table>
</body>

• update_book

<body>
    <form action="/bookstore/update_book/{{ book.id }}" method="post">
        <p>
            title <input type="text" value="{{ book.title }}" disabled="disabled">
        </p>
        <p>
            pub <input type="text" value="{{ book.pub }}" disabled="disabled">
        </p>
        <p>
            price <input type="text" name="price" value="{{ book.price }}" >
        </p>
        <p>
            market_price <input type="text" name="market_price" value="{{ book.market_price }}" >
        </p>                        
        <p>
            <input type="submit" value="to update">
        </p>
    </form>
</body>

• View layer

from django.http import HttpResponse, HttpResponseRedirect
from django.shortcuts import render
from .models import Book
# Create your views here.


def all_book(request):
    all_book = Book.objects.all()
    return render(request,'bookstore/all_book.html',locals())

def update_book(request,book_id):
    # bookstore/update_book/1
    try:
        book=Book.objects.get(id=book_id)
    except Exception as e:
        print('---update book error is %s'%(e))
        return HttpResponse('---the book is not existed')


    if request.method == "GET":
        return render(request,'bookstore/update_book.html',locals())
    elif request.method == "POST":
        price = request.POST["price"]
        market_price = request.POST["market_price"]
        # change
        book.price= price
        book.market_price=market_price
        # preservation
        book.save()
        # Jump
        return HttpResponseRedirect("/bookstore/all_book")

ORM delete data

Single data deletion

Find a data object corresponding to the query result
 To call this data object delete() Method to implement deletion
try:
	auth = Author.object.get(id=1)
	auth.delete()
except:
	print(Delete failed)

Batch data deletion

Find all corresponding to query results queryset Query collection object
 To call this data object delete() Method to implement deletion
try:
	auth = Author.object.filter(id__gt=12)
	auth.delete()
except:
	print(Delete failed)

>>> from bookstore.models import Book
>>> b1 = Book.objects.get(id=1)
>>> b1.delete()
(1, {'bookstore.Book': 1})

Pseudo deletion

It is usually not easy to delete the data in the business example. Instead, pseudo deletion is performed, that is, a Boolean field (such as is_active) is added to the table. The default is True. When deleting, set the field value to False
Note: when deleting with pseudo data, make sure that is is is added where the data is displayed_ Filter query with active = true.

Perfect book page - add delete operation

• Add field

class Book(models.Model):
    title = models.CharField("title", max_length=50, default='', unique=True)
    pub = models.CharField("press", max_length=100, default="",null=True)
    price = models.DecimalField("Price", max_digits=7, decimal_places=2)
    market_price = models.DecimalField(
        "retail price", max_digits=7, decimal_places=2, default=0.0)
    is_active=models.BooleanField("Is it active",default=True)
    class Meta:
        db_table = "book"

    def __str__(self):
        return "%s_%s_%s_%s"%(self.title,self.pub,self.price,self.market_price)
python manage.py makemigrations
 python manage.py migrate

Template layer
all_book.html

<body>
    <table border="1">
        <tr>
            <th>id</th>
            <th>title</th>
            <th>pub</th>
            <th>price</th>
            <th>market_price</th>
            <th>op</th>
        </tr>
        {% for book in all_book %}
            <tr>
                <td>{{ book.id }}</td>
                <td>{{ book.title }}</td>
                <td>{{ book.pub }}</td>
                <td>{{ book.price }}</td>
                <td>{{ book.market_price }}</td>
                <td>
                    <!--The first way of labeling is through path converter --> 
                    <a href="/bookstore/update_book/{{ book.id }}">to update</a>
                    <!--The second way of labeling is through string splicing url Up pass --> 
                    <a href="/bookstore/delete_book?book_id={{ book.id }}">delete</a>
                </td>
            </tr>
        {% endfor %}
    </table>
</body>

View layer

def delete_book(request):
    # By getting the query string book_id get the book id to delete
    book_id = request.GET.get("book_id")
    if not book_id:
        return HttpResponse('----Request exception----')

    try:
        book = Book.objects.get(id=book_id, is_active=True)
    except Exception as e:
        print('---delete book error is %s' % (e))
        return HttpResponse('---the book is error')

    # Put it as_ Change active to false
    book.is_active = False
    book.save()
    # 302 jump to all_book
    return HttpResponseRedirect("/bookstore/all_book")

Routing layer

from django.urls import path
from . import views
urlpatterns = [
    path('all_book',views.all_book),
    path('update_book/<int:book_id>',views.update_book),
    path('delete_book',views.delete_book)

F object

F It is equivalent to the table in the actual library
 One F Object represents the field information of a record in the database.
Function: it is usually used to operate the field values in the database without obtaining them; Used for comparison between class attributes (fields)

Scenario: for example, if 1000 people like a comment at the same time, the final result of the first method may only be increased by 1, because their initial environment is the same and the final data is the same
Using F is equivalent to operating table, with table lock, 1000 likes, avoiding dirty data, and + 1000, which is equivalent to transaction control.

grammar

from django.db.models import F 
F('Listing')

# Update all retail prices in the Book by 10 yuan
    """
        If not F
        books=Book.objects.all()
        for book in books:
            book.market_price=book.market_price+10
            book.save()


        use F
        Book.objects.all().update(market_price=F('market_price')+10)
        Equivalent to direct operation table
        update bookstore_book set market=market+10 
    """

>>> from django.db.models import F
>>> from bookstore.models import Book
>>> Book.objects.all().update(market_price=F('market_price')+10)
3
>>> Book.objects.all()
<QuerySet [<Book: Oracle_press_10.00_22.00>, <Book: Python_Tsinghua Publishing House_40.00_80.00>, <Book: Html_Tsinghua Publishing House_10.00_42.00>]>

Q object

When complex logic or | logic non ~ is used to obtain query results, the operation can be carried out with the help of Q object.

operator	operation
&	And operation
|	Or operation
~	Non operation

from django.db.models import Q

Q operator	operation
Q (condition 1) | Q (condition 2)	or
Q (condition 1) & Q (condition 2)	and
Q (condition 1) ~ Q (condition 2)	Condition 1 holds and condition 2 does not hold

Examples

>>> Book.objects.filter(Q(market_price__gt=70) | Q(title='Oracle'))
<QuerySet [<Book: Oracle_press_10.00_22.00>, <Book: Python_Tsinghua Publishing House_40.00_80.00>]>

Aggregate query

Aggregate query refers to a statistical query on the data of a field in a table. For example, check the average price of books and the total number of books
Aggregate queries are divided into:

Whole table aggregation
 Grouping aggregation

Whole table aggregation

Aggregate query without grouping refers to the centralized statistical query of all data
Aggregate function:
Import method: from Django db. models import *
Aggregate function: Sum Avg Count Max Min

Syntax:
MyModel. objects. Aggregate (result variable name = aggregate function ('column '))
The format is: {"result variable name": value}

>>> from django.db.models import *
>>> Book.objects.aggregate(res=Count('id'))
{'res': 3}
>>> a
{'res': 3}

Grouping aggregation

Grouping aggregation refers to calculating the aggregate of objects associated with each object in the query results to obtain the total calculation (average or sum, etc.), that is, generating aggregation for each item of the query set

Syntax:
QuerySet. Annotate (result variable name = aggregate function ('column '))
Return value:
QuerySet

1. First, through the query result model objects. Values ('column 1 ',' column 2 ') Find query columns to group and aggregate

>>> pub_set= Book.objects.values('pub')
>>> pub_set
<QuerySet [{'pub': 'press'}, {'pub': 'Tsinghua Publishing House'}, {'pub': 'Tsinghua Publishing House'}]>

1. Through the queryset that returns the result The group result is obtained by grouping and aggregation with annotate method

>>> pub_set.annotate(res=Count('id'))
<QuerySet [{'pub': 'press', 'res': 1}, {'pub': 'Tsinghua Publishing House', 'res': 2}]>

1. Filter aggregation results

>>> pub_set.annotate(res=Count('id')).filter(res__gt=1)
<QuerySet [{'pub': 'Tsinghua Publishing House', 'res': 2}]>

>>> sub_query=pub_set.annotate(res=Count('id')).filter(res__gt=1)
>>> print(sub_query.query)
SELECT `book`.`pub`, COUNT(`book`.`id`) AS `res` FROM `book` GROUP BY `book`.`pub` HAVING COUNT(`book`.`id`) > 1 ORDER BY NULL

Native database operation

grammar

Django also supports the direct communication of databases with sql statements
Query: using mymodel objects. Raw() to query the database
Syntax: mymodel objects. Raw (SQL statement, splicing parameter)
Return value: RawQuerySet collection object (only basic operations, such as loops, are supported)

books = models.Book.object.raw(' select * from book')
for book in books:
	print(book)

SQL injection

Be careful with SQL injection when using native statements
Definition: users submit malicious SQL statements to the server through data upload, so as to achieve the attack effect
For example:

s1 = Book.objects.raw('select * from book where id = 1 or 1=1'))

>>> s1=Book.objects.raw('select * from book where id=%s'%('1 or 1=1'))
>>> s1
<RawQuerySet: select * from book where id=1 or 1=1>
>>> for i in s1:
...     print(i)
... 
Oracle_press_10.00_22.00
Python_Tsinghua Publishing House_40.00_80.00
Html_Tsinghua Publishing House_10.00_42.00

cursor

Fully operate the database across model classes - query, update and delete

1. Import the package of cursor

from django.db import connection

1. Use the constructor of the wearable cursor class to create the cursor object, and then use the cursor object. In order to ensure that the cursor resource can be released in case of an exception, the with statement is usually used for the creation operation

with connection.cursor() as cur:
	cur.execute('sql','Splicing parameters')

>>> from django.db import connection
>>> with connection.cursor() as cur:
...     cur.execute('select * from book;')
... 
3

>>> with connection.cursor() as cur:
...     cur.execute('update book set pub="Hello, publishing house" where id= 2;')
... 
1