Catalog

Query data

• The query of database needs to use manager object

• Call the query interface through the MyModel.objects manager method

• If the query result returns a QuerySet, you can print QuerySet.query to obtain the sql corresponding to the orm

  Method	Explain
  all()	Query all records and return QuerySet query object
  get()	Query a single record that meets the criteria
  filter()	Query multiple records that meet the criteria
  exclude()	Query all records except those meeting the conditions
  ...

1. all() method

   • Method: all()

   • Usage: MyModel.objects.all()

   • Function: query all data in MyModel entity

     • Equate to
       • select * from tabel

   • Return value: QuerySet container object, where MyModel instance is stored internally

   • Example:

     from bookstore import models
     books = models.Book.objects.all()
     for book in books:
         print("Title", book.title, 'Press:', book.pub)
     

2. Define def STR self in the model class: method can customize the default string

   class Book(models.Model):
       title = ...
       def __str__(self):
           return "Title: %s, Press: %s, Price: %s" % (self.title, self.pub, self.price)
   

3. Query returns specified column (Dictionary representation)

   • Methods: values('column 1 ',' column 2 ')

   • Usage: MyModel.objects.values( )

   • Function: query the data of some columns and return

     • select column 1, column 2 from xxx

   • Return value: QuerySet

     • Return the query result container and the container memory dictionary. Each dictionary represents a piece of data,
     • Format: {'column 1': value 1, 'column 2': value 2}

   • Example:

     from bookstore import models
     books = models.Book.objects.values("title", "pub")
     for book in books:
         print("Title", book["title"], 'Press:', book['pub'])
         print("book=", book)
     

4. Query returns specified column (tuple representation)

   • Methods: values_list('column 1 ',' column 2 ')

   • Usage: mymodel. Objects. Values? List ( )

   • Effect:

     • Return query results in tuple form

   • Return value: QuerySet container object, storing tuples internally

     • The queried data will be encapsulated in tuples and then in query set QuerySet

   • Example:

     from bookstore import models
     books = models.Book.objects.values_list("title", "pub")
     for book in books:
     print("book=", book)  # ('Python ',' Tsinghua University Press')
     

5. Sort query

   • Method: order u by

   • Usage: mymodel. Objects. Order u by ('- column', 'column')

   • Effect:

     • Unlike the all() method, it uses the ORDER BY clause of the SQL statement to sort the query results selectively according to a certain field

   • Explain:

   • By default, it is sorted in ascending order. In descending order, you need to add '-' before the column

   • Example:

     from bookstore import models
     books = models.Book.objects.order_by("price")
     for book in books:
     print("Title:", book.title, 'Price:', book.price)
     

6. Query multiple records by criteria

   • Method: filter (condition)

   • Syntax:

     Mymodel. Objects. Filter (property 1 = value 1, property 2 = value 2)
     

   • Return value:

     • QuerySet container object, where MyModel instance is stored internally

   • Explain:

     • When multiple attributes are together, it is an "and" relationship, that is, when Books.objects.filter(price=20, pub = "Tsinghua University Press") returns all books with a price of 20 and a publishing house of "Tsinghua University Press"

   • Example:

     # Query the books in which the press is "Tsinghua University Press"
     from bookstore import models
     books = models.Book.objects.filter(pub="tsinghua university press ")
     for book in books:
         print("Title:", book.title)
     
     2. query Author In entityidby1also isActive byTrueOf
         - authors=Author.objects.filter(id=1,isActive=True)
     

Field lookup

• Field query refers to how to specify the content of WHERE clause in SQL statement.

• Field query needs to be specified through the keyword parameter of QuerySet filter(), exclude() and get().

• To construct non equivalent conditions, field query is needed

• Example:

  # Query authors older than 30
  Author.objects.filter(age__gt=30)
  # Corresponding
  # SELECT .... WHERE AGE > 35;
  

query predicate

• Each query predicate is an independent query function

1. __exact: equivalent match

   Author.objects.filter(id__exact=1)
   # Equivalent to select * from author where id = 1
   

2. __Contains: contains the specified value

   Author.objects.filter(name__contains='w')
   # Equivalent to select * from author where name like '%w%'
   

3. __Startswitch: start with XXX

4. __Endswitch: end with XXX

5. __gt: greater than the specified value

   Author.objects.filer(age__gt=50)
   # Equivalent to select * from author where age > 50
   

6. __gte: greater than or equal to

7. __lt: less than

8. __lte: less than or equal to

9. __in: find whether the data is within the specified range

   • Example

   Author.objects.filter(country__in=['China','Japan','The Republic of Korea'])
   # Equivalent to select * from author where country in ('China ',' Japan ',' South Korea ')
   

10. __Range: find whether the data is within the specified range

    # Find all authors in a certain age range
    Author.objects.filter(age__range=(35,50))
    # Equivalent to select... Where author between 35 and 50;
    

11. For details, please refer to: https://docs.djangoproject.com/en/1.11/ref/models/querysets/#field-lookups

• Example

  MyModel.objects.filter(id__gt=4)
  # Equivalent to select... Where id > 4;
  

• Practice:

  1. Query the Book table for information with price greater than or equal to 50

  2. Query the information of the person surnamed Wang in the Author table

  3. Query the information of the person whose Email in the Author table contains "w"

1. Unequal conditional screening

   • Syntax:
     Mymodel.objects.exclude (condition)

   • Effect:

     • Returns all datasets that do not contain this condition

   • Example:

     • Query Tsinghua University Press, price more than 50 books

     books = models.Book.objects.exclude(pub="tsinghua university press ", price__gt=50)
     for book in books:
         print(book)
     

2. Query a specified piece of data

   • Syntax:
     Mymodel.objects.get (condition)

   • Effect:

     • Return the only data that meets the condition

   • Return value:

     • MyModel object

   • Explain:

     • This method can only return one piece of data
     • If there is more than one piece of data in the query result, an exception of model.multipleobjectsreturn will be thrown
     • If there is no data in the query result, a Model.DoesNotExist exception will be thrown

   • Example:

     from bookstore import models
     book = models.Book.objects.get(id=1)
     print(book.title)
     

Modify data record

1. To modify some field values for a single entity:

   1. check
      • Get the entity object to be modified through get()
   2. change
      • Modify data by object. Attribute
   3. Preservation
      • Save data through object. save()

   • Such as:

     from bookstore import models
     abook = models.Book.objects.get(id=10)
     abook.market_price = "10.5"
     abook.save()
     

2. Batch modify all corresponding fields through QuerySet

   • Directly call Update (property = value) of QuerySet to realize batch modification

   • Such as:

     # Set the price of all books with id greater than 3 to 0 yuan
     books = Book.objects.filter(id__gt=3)
     books.update(price=0)
     # Set the retail price of all books at 100 yuan
     books = Book.objects.all()
     books.update(market_price=100)
     

Exercise: revise the retail price of books

Route / bookstore/mod/5

Delete record

• Deleting a record means deleting one or more records in a database
• To delete a single MyModel object or to delete all objects in a query result set, the delete() method is called

1. Delete single object

   • step

     1. Find a data object corresponding to the query result
     2. Call the delete() method of this data object to delete

   • Example:

     try:
         auth = Author.objects.get(id=1)
         auth.delete()
     except:
         print(Delete failed)
     

2. Delete query result set

   • step

     1. Find all QuerySet query collection objects that meet the conditions in the query result set
     2. Call delete() method of query collection object to delete

   • Example:

     # Delete all information older than 65 from all authors
     auths = Author.objects.filter(age__gt=65)
     auths.delete()
     

Aggregate query

• Aggregate query refers to the statistical query of part or all of the data of a field in a data table, the query of the average price of all books in the book store book data table, the query of the total number of all books, etc., all of which need to use aggregate query

1. Aggregation without grouping

   • The aggregation query without grouping is to guide the centralized statistical query of all data

   • Aggregate function:

     • Define module: django.db.models
     • Usage: from django.db.models import*
     • Aggregate function:
       • Sum, Avg, Count, Max, Min

   • Syntax:

     • Mymodel.objects.aggregate (result variable name = aggregate function ('column '))

   • Return result:

     • Dictionary of result variable names and values
     • The format is:
       • `{"result variable name": value}

   • Example:

     # Get the average price of all the books
     from bookstore import models
     from django.db.models import Count
     result = models.Book.objects.aggregate(myAvg=Avg('price'))
     print("The average price is:", result['myAvg'])
     print("result=", result)  # {"myAvg": 58.2}
     
     # How many books are there in the data sheet
     from django.db.models import Count
     result = models.Book.objects.aggregate(mycnt=Count('title'))
     print("The total number of data records is:", result['mycnt'])
     print("result=", result)  # {"mycnt": 10}
     
     

2. Group aggregation

   • Grouping aggregation refers to the calculation of the collection of objects associated with each object in the query result, so as to get the total value (or the average value or the sum), that is, to generate aggregation for each item in the query set.

   • Syntax:

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

   • Usage steps:

     1. Query the columns to be grouped and aggregated by first using the query result MyModel.objects.value

        • MyModel.objects.value('column 1 ',' column 2 ')

        • Such as:

          pub_set = models.Book.objects.values('pub')
          print(books)  # < queryset [{'pub': 'Tsinghua University Press'}, {' pub ':' Tsinghua University Press'}, {'pub {' pub ':' Mechanical Industry Press'}, {'pub': 'Tsinghua University Press'}] >
          
          

     2. The grouping result is obtained by grouping and aggregating the returned result with QuerySet.annotate method

        • Queryset.annotate (name = aggregate function ('column '))

        • Return QuerySet result set, internal dictionary for storing results

        • Such as:

          pub_count_set = pub_set.annotate(myCount=Count('pub'))
          print(pub_count_set)  # < queryset [{'pub': 'Tsinghua University Press',' myCount': 7}, {'pub ':' Machinery Industry Press', 'MYCOUNT': 3}] >
          

     • . values('query column name ')

   • Example:

     • How many books are published by which publishing house

     def test_annotate(request):
        from django.db.models import Count
        from . import models
     
         # Get query set of all publishers QuerySet
         pub_set = models.Book.objects.values('pub')
         # According to publishing house query group, publishing house and Count group aggregate query set
         pub_count_set = pub_set.annotate(myCount=Count('pub'))  # Return to query collection
         for item in pub_count_set:
             print("Press:", item['pub'], "The books are:", item['myCount'])
         return HttpResponse('Please check the server console for results')
     

F object

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

1. Effect:

   • It is usually to operate on the field value in the database without getting it
   • Used for comparison between class properties (fields).

2. usage

   • F object is in the data package django.db.models, which needs to be imported before use
     • from django.db.models import F

3. Syntax:

   from django.db.models import F
   F('Column names')
   

4. Explain:

   • An F() object represents the value of a model's field
   • F objects usually operate on the database server side directly without loading the field values in the database into memory

5. Example 1

   • Update all retail prices in the Book instance by 10 yuan

   models.Book.objects.all().update(market_price=F('market_price')+10)
   # The following is better than the following code
   books = models.Book.objects.all()
   for book in books:
       book.update(market_price=book.marget_price+10)
       book.save()
   

6. Example 2

   • Compare the values of the two fields in the database and list which books have a retail price higher than the price?

   from django.db.models import F
   from bookstore import models
   books = models.Book.objects.filter(market_price__gt=F('price'))
   for book in books:
       print(book.title, 'Price:', book.price, 'Present price:', book.market_price)
   

Q object - Q()

• When using complex logic or operations such as |, logical non ~

• For example, if you want to find out all the books priced below 20 yuan or Tsinghua University Press, you can write

  models.Book.objects.filter(Q(price__lt=20)|Q(pub="tsinghua university press "))
  

• The Q object is in the package django.db.models. Need to import before using

  • from django.db.models import Q

1. Effect

   • Used in conditions to implement or(|) or not(~) operations other than and (&)

2. Operator:

   • & amp; operation
   • Operation or operation
   • ~ non operation

3. grammar

   from django.db.models import Q
   Q(condition1)|Q(condition2)  # Condition 1 or condition 2
   Q(condition1)&Q(condition2)  # Condition 1 and condition 2 hold at the same time
   Q(condition1)&~Q(condition2)  # Condition 1 holds and condition 2 does not
   ...
   

4. Example

   from django.db.models import Q
   # Find books from Tsinghua University Press or books with prices below 50
   models.Book.objects.filter(Q(market_price__lt=50) | Q(pub_house='tsinghua university press '))
   # Search for books that are not published by the mechanical industry press and are priced below 50
   models.Book.objects.filter(Q(market_price__lt=50) & ~Q(pub_house='Mechanical Industry Press'))
   

Native database operation method

• Use MyModel.objects.raw() to query database query operation

  • In django, the raw method of model manager can be used to execute select statement for data query
  1. Syntax:
     • MyModel.objects.raw(sql statement)
  2. usage
     • MyModel.objects.raw('sql statement ')
  3. Return value:
     • RawQuerySet collection object [only supports basic operations, such as loops]

1. Example

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

• Using cursor in django to add, delete and modify database

  • In Django, SQL statements such as update and delete can be used to operate the database.

  • Using the above non query statements in Django requires the use of cursors

  • Use steps:

    1. Import the package of cursor

       • The cursor in Django is defined in django.db.connection package, which needs to be imported before use
       • Such as:
         • from django.db import connection

    2. Create the cursor object with the constructor of creating the cursor class, and then use the cursor object. In order to ensure that the cursor resource can be released in the event of an exception, the with statement is usually used for creation

       • Such as:

         from django.db import connection
         with connection.cursor() as cur:
             cur.execute('implement SQL Sentence')
         

    • Example

      # Change the publishing house of the book with id 10 to "XXX Publishing House" with SQL statement
      from django.db import connection
      with connection.cursor() as cur: 
          cur.execute('update bookstore_book set pub_house="XXX Press" where id=10;')
      
      with connection.cursor() as cur:
          # Delete a record with id 1
          cur.execute('delete from bookstore_book where id=10;')