Django Web framework teaching notes

Catalog

Article directory

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;')
        
Published 25 original articles, praised 0, visited 151
Private letter follow

Tags: Django Database SQL less

Posted on Thu, 16 Jan 2020 10:21:54 -0500 by scotchegg78