Learn the Path to Python – Django-ROM

ORM- Query operation -1

Introduction of the query

• Queries to the database need to be made using manager objects
• The query method is invoked through the myModel.Objects manager method

• All () method

Select * from tabel; select* from tabel; select* from tabel; QuerySet from bookstore. Models import Book books = book.objescts.all () for Book in books: Print (' title ', book.title, 'publisher ', book.pub)Copy the code

• You can define _ in a model classstr_ method. Customize the output format in QuerySet
  • For example, the Book model is defined as follows:

def __str__(self):
     return '%s_$s_%s_%s'%(self.title, self,price, self.pub, self.market_price)
Copy the code

• In your Django shell, you get the following output

>>> from bookstore.models import Book >>> a1 = Book.objects.all() >>> a1 <QuerySet [<Book: Python_ tsinghua university press _20.00_25.00>, <Book: Django_ tsinghua university press _70.00_75.00>, <Book: query _ university of mechanical engineering press _90.00_85.00>, <Book: Linux_ University of China Machine Press _80.00_65.00>, <Book: HTML5_ Tsinghua University Press _90.00_105.00>]> >>>Copy the code

• Values (‘ 1’, ‘2’)

Usage: MyModel) objects) values (...). QuerySet QuerySet QuerySet QuerySet QuerySet QuerySet QuerySet QuerySet QuerySetCopy the code

• Values_list (‘ column 1’, ‘column 2’)

Usage: MyModel. Objects. Values_list (...). Return value: QuerySet container object, which stores' meta-ancestor '. It will encapsulate the queried data to the meta-ancestor, and then to the QuerySet QuerySetCopy the code

• order_by()

Usage: mymodel.objects.order_by ('- columns', 'columns ') function: unlike all(), it uses the orderby subdistance of SQL statements to sort the query results selectively by a field. Descending sort requires a '-' expression before the columnCopy the code

ORM- Query operation -2

Conditional query – method

• The filter (condition)

Syntax: mymodel.objects.filer (attribute 1= value 1, attribute 2= value 2) Returns the entire data set containing this condition.Copy the code

The sample

From bookstore. Models import Book >>> A2 = book.objects. Filter (pud=' tsinghua University Press ') >>> for Book in  a2: ... Print (' title: ', book.title)... Title: Python Title: Django Title: HTML5Copy the code

• Exclude

Syntax: mymodel.objects.get (conditional) Return to satisfy conditions can only return a data query results in a surplus is thrown, the Model. The abnormal MultipleObjectsReturned query results if no data is thrown Model. DoesNotExst anomaliesCopy the code

The sample

>>> b1 = book.objects.get (id=1) >>> b1 <Book: PYTHon_tsinghua University Press _20.00_25.00>Copy the code

A query predicate

• Definition: Query predicates are required to make more flexible conditional queries
• Note: Each query predicate is an independent query function
• __exatc: equivalence matching

The sample

Author.objects.filter(id__exact=1) # = select * from Author where id =1Copy the code

• __contains: contains the specified value

Author.objects.filter(name__contains= 'w') # equivalent to select * from Author where name like '%w%'Copy the code

• __statswith: starts with XXX
• __endswith: endswith XXX
• __gt: greater than the specified value

Select * from Author where age > 50Copy the code

• __gte: greater than or equal to
• __lt: less than
• __lte: less than or equal to
• __in: Queries whether data is in the specified range

Select * from Author where country in ('中国',' 中国',' 中国',' 中国',' 中国')Copy the code

• __range: Finds whether data is in the specified range

Filter (age__range=(35,50)) # equivalent to SELELCT.... WHERE Author BETWEEN 35 and 50;Copy the code

ORM- Update operation

Updating individual data

• Modifying some fields of a single entity is a step
  1. check

Get () to get the entity object to be modifiedCopy the code

2. change

- Modify data by object. PropertiesCopy the code

3. save

Save data by object.save()Copy the code

Batch Update Data

• Call QuerySet update(attribute = value) directly for batch modification
• Example:

Filter (id__gt = 3) books.update(price =0) # change the retail price of all books to 100 books = Book.objects.all() books.update(market_price=100)Copy the code

ORM- Delete operation

Single data deletion

steps

• Find a data object corresponding to the query result
• The delete is done by calling the delete() method of the data object

Try: auth = author.objects.get (id=1) auth.delete() except: print(delete failed)Copy the code

Batch delete

steps

• Find all QuerySet objects that meet the criteria in the query result set
• The delete is implemented by calling the delete() method of the query collection object

Auths = author.objects.filter (age__get=65) auths.dalete()Copy the code

Pseudo delete

• It is usually not easy to actually delete data in the business. Instead, it is a pseudo-delete, which is to add a Boolean field (is_active) to the table. The default value is true. When performing a deletion. Set the is_active field value of the data to be deleted to False
• Note: When using pseudo delete, make sure that where data is displayed, is_active=true is used to filter the query

Exercise 3 make a ‘Delete book’ page

• Click ‘Delete’ on the ‘View All Books’ page; Delete current book (pseudo delete)
• View function delete_book
• Routing 127.0.0.1 http:// : 8000 / bookstore/detele? book_id=xx
• Note: Where relevant queries fetch data, filter out active data

Py def delete_book(request): Book_id = request.get.GET ('book_id') if not book_id: Return HttpResponse('-- ') try: book = book.objects. get(id=book_id, is_active=True) except Exception as e: Print ('----delete book get error %s' %(e)) return HttpResponse('-- the book ID is error') # is_active = False Book.is_active = False book.save() return HttpResponseRedirect('/bookstore/all_book') # 302 redirect to all_book # add field models Is_active = models.BooleanField(' active ', default=True) <a href="/bookstore/delete_book? Book_id ={{book.id}}"> Delete </a>Copy the code

F object and Q object

F the object

• An F object represents information about a field of a record in the database

• Function:

  • It is common to operate on field values in the database without fetching them
  • Used for comparisons between class attributes (fields)

• grammar

From django.db.models import F(' django.db.models import F ')Copy the code

• Example 1 updates all retail prices in the Book example by $10

Book.objects.all().update(market_price=F('market_price')+10) 'UPDATE `bookstore_book` SET 'market_price' =(' bookstore_book '. 'market_price' +10)' book.market_price = book.marget_price+10 book.save()Copy the code

Q object

• In the query results were obtained using complex logic or |, logical ~ waiting for the operating system can be operated with the aid of Q object
• To find all the books priced below 20 yuan or tsinghua University Press

Book. Objects. The filter (Q (price__lt = 20) | Q (pud = 'tsinghua university press))Copy the code

• The Q object is in the packet django.db.models. It needs to be imported before use
• Function: Used in conditions to implement or (I) or not(~) operations other than and (&)

Operator: & and | or operation ~ not operation grammar: The from the django. Db. Models import Q Q (1) (2) | Q # conditions set up 1 or 2 (1) set up Q & Q (2) the conditions of # 1 and 2 at the same time set up Q (1) & ~ Q (2) # conditions established 1 and 2Copy the code

• The sample

The from the django. Db. Models import Q # for tsinghua university press Book or Book. The Book price is lower than 50 objects. The filter (Q (market_price__lt = 50) | Q (pud_house = 'tsinghua university press)) Objects.filter (Q(market_price__lt=50)&~Q(pud_house=' mechanical Press '))Copy the code

Aggregate queries and native database operations

Aggregation query

• Aggregate query is used to query the data of a field in a data table, such as the average price of all books in the bookstore_book data table, the total number of books, etc

• Aggregated queries are divided into

  • The whole table polymerization
  • Group integration

Aggregate query – Aggregate table

• An aggregated query without grouping is a guide to a centralized statistical query of all data

• Aggregate function [import required] :

  • Models import * from django.db.models import *
  • Aggregate functions: Sum, Avg, Count, Max, Min

• Grammar: MyModel. Objects. Aggregate (result variable name = aggregation function (‘ column ‘))

  • Return result: dictionary of result variable names and values
  • Format: {‘ Result variable name ‘: value}

Aggregate query – Group aggregation

• Group aggregation refers to the collection of all associated objects by calculating each object in the query result. This results in a total value (which can also be an average or a sum), which generates an aggregation for each item in the query set

  • Grammar:

    • Queryset.annotate (result variable name = aggregate function (‘ column ‘))

  • The return value:

    • QuerySet

• Example 1 uses the query result myModel.objects. values first to find the columns to be grouped and aggregated by the query

  Mymodel.objects.values (‘ 1’, ‘2’)

pud_set = Book.objects.values('pud')
print(pud_set)
Copy the code

• Example 2 gets the grouping result by grouping the aggregation using the Queryset.annotate method that returns the result

Queryset.annotate (name = aggregate function (‘ column ‘))

pud_count_set = pud_set.annotate(myCount=Count('pud'))
print(pud_count_set)
Copy the code

Native database operations

• Django also supports communicating with databases directly using SQL statements
• Query: use mymodel.objects.raw () for database query operations
• Syntax: myModel.objects. raw(SQL statement, concatenation parameters
• Return value: RawQuerySet collection object [supports basic operations, such as loops]

books = models.Book.objects.raw('select  * from bookstore_book')
for book in books:
    print(books)
Copy the code

Native database operations – SQL injection

• Definition: A user submits malicious SQL statements to the server through data upload. In order to achieve the effect of attack
• Case 1: The user enters’ 1OR1 = 1′ in the search form for friends

s1 = Book.objects.raw('select * from bookstore_book where id=%S'%('1or1 = 1'))

Copy the code

Native database operations – CURSOR

1. Import the cursor package

from django.db import connection

2. Create a cursor object, and then use the cursor object. To ensure that the cursor resources can be released in the event of an exception, the with statement is usually used to create the cursor

From django.db import connection with connection.cursor() as cur: cur.execute(' SQL statement ',' concatenate parameter ')Copy the code

The sample

Django.db import connection with connection.cursor() as cur: Cur. execute('update bookstore_book set pud_house = 'XXX publisher' where id=10; Cur.execute ('delete from bookstore_book where ID =10') with connection.cursor() as cur:Copy the code