django Series 5 - QuerySet API operation

QuerySet API usage

QuerySet can construct, filter, slice, and approximate results without actually accessing the database. Unless you do something to evaluate the query set, no query activity actually occurs to the database.

(1) Basic properties

  1. iteration
    The QuerySet is iterative, and its database query is executed the first time it is iterated. For example:

    for e in Entry.objects.all():
	 print(e.headline)

  2. section

    You can use python's array slicing syntax to slice the QuerySet set.

  3. len

    Returns the length of the QuerySet list.

  4. list

    Use list() for forced conversion. entry_list = list(Entry.objects.all())

  5. exists

    Determine whether at least one result exists in the result set. Entry.objects.all().exists().

(2) Query

  1. filter

    A condition lookup is a result set. Multiple conditions are separated by commas (,). Entry.objects.filter(id=3,name = 'Zhang San').

  2. exclude

    The result that does not match the given search parameter is a result set separated by commas (,). Entry.objects.exclude(id=3,name = 'Zhang San'). The parameters in parentheses belong to the and relationship. Write more than one exclude() when you want to use the or relationship.

  3. annotate

    An expression can be a simple value, a reference to a field on a model (or any related model), or an aggregate expression (average, sum, etc.) calculated for an object related to an object in an object

    from django.db.models import Count
q = Blog.objects.annotate(Count('entry'))

  4. order_by

    By default, you can sort in the ordering tuple given by Meta, and you can use order_by() overrides the ordering method and sorts the query results additionally.

    For example:

    Entry.objects.order_by('headline')

    A minus sign (-) before the field name indicates descending order.

    If you want to sort randomly, you can use "? As follows:

    Entry.objects.order_by('?')

    **Note: * * order_by('?') queries are slow.

    Use order multiple times_ By will invalidate the previous collation.

  5. reverse

    Use reverse() to flip the return order of query set elements. For example: retrieve the last five items in the query set, you can do the following:

    Entry.objects.order_by('headline').reverse()[:5]

  6. distinct

    Eliminate duplicate rows.

    Entry.objects.order_by('pub_date').distinct('pub_date')
Entry.objects.order_by('blog').distinct('blog')

  7. values

    Returns the dictionary returned when QuerySet is used as an iterator, not the model instance. Each of these dictionaries represents an object, and the key corresponds to the property name of the model object.

    Blog.objects.filter(name__startswith='Beatles')
#<QuerySet [<Blog: Beatles Blog>]>
Blog.objects.filter(name__startswith='Beatles').values()
#<QuerySet [{'id': 1, 'name': 'Beatles Blog', 'tagline': 'All the latest Beatles news.'}]>

    The values() method can take optional location parameters. If you specify a field, each dictionary will only contain the key / value of the specified field. If you do not specify a field, each field will contain a key and value for each field in the database table. For example:

    Blog.objects.values()
#<QuerySet [{'id': 1, 'name': 'Beatles Blog', 'tagline': 'All the latest Beatles news.'}]>

Blog.objects.values('id', 'name')
#<QuerySet [{'id': 1, 'name': 'Beatles Blog'}]>

    Aggregations in the values() clause are applied before other parameters in the same values() clause. If you need to group by another value, add it to an earlier values() clause. For example:

    from django.db.models import Count
Blog.objects.values('entry__authors',entries=Count('entry'))
#<QuerySet [{'entry__authors': 1, 'entries': 20}, {'entry__authors': 1, 'entries': 13}]

Blog.objects.values('entry__authors').annotate(entries=Count('entry'))
#<QuerySet [{'entry__authors': 1, 'entries': 33}]>

  8. value_list

    Similar to values, but returns data in the form of tuples, each containing data passed from to values_ The value of the corresponding field or expression that is called in list ().

     Entry.objects.values_list('id', 'headline')
#<QuerySet [(1, 'First entry'), ...]>

from django.db.models.functions import Lower	
Entry.objects.values_list('id', Lower('headline'))
#<QuerySet [(1, 'first entry'), ...]>

    If you pass only a single field, you can also pass the flat parameter. If flat is True, the result is a single value, not a tuple.

    Entry.objects.values_list('id').order_by('id')
#<QuerySet[(1,), (2,), (3,), ...]>

Entry.objects.values_list('id', flat=**True**).order_by('id')
#<QuerySet [1, 2, 3, ...]>

    You can get the result named tuple() by using named=True

    Entry.objects.values_list('id', 'headline', named=**True**)
QuerySet [Row(id=1, headline='First entry'), ...]>

    If not to values_ If any value is passed in list(), all fields in the model will be returned in the declared order. Usually, you need to get the specific field value of a model instance, so you need values_list() is used with get().

    Entry.objects.values_list('headline', flat=**True**).get(pk=1)
#'First entry'

  9. dates

    dates (field, kind, order = 'ASC'), the returned result is a QuerySet, and the result is a datetime.date Object list, which represents all available date querysets of a certain kind in the content.

    The Field should be of DateField type. The kind can be year, month, week, day.

    • Year returns a list of all the different year values for the field.
    • "Month" returns a list of all the different year / month values for this field.
    • Week returns a list of all the different year / week values for this field. All dates are Monday.
    • "Day" returns a list of all the different year / month / day values in this field.

  10. none

    Create an empty query set that does not return any objects and does nothing to access the results.

    Entry.objects.none()
<QuerySet []>
from django.db.models.query import EmptyQuerySet

isinstance(Entry.objects.none(), EmptyQuerySet)
True

  11. all

    Query all data.

  12. raw

    Executing the query will make the original SQL query.

    for p in Person.objects.raw('SELECT * FROM myapp_person'):     		
	print(p)  #John SmithJane Jones

  13. Q

    and (&) connector:

    from django.db.models import Q

    The following are equivalent queries:

    Model.objects.filter(x=1) & Model.objects.filter(y=2)
Model.objects.filter(x=1, y=2) 
Model.objects.filter(Q(x=1) & Q(y=2))
//Equivalent to SELECT ... WHERE x=1 AND y=2

  14. or(|)

    Model.objects.filter(x=1) | Model.objects.filter(y=2)
from django.db.models import Q
Model.objects.filter(Q(x=1) | Q(y=2))

    SQL equivalent:

    SELECT ... WHERE x=1 OR y=2;

  15. contains

    Fuzzy query, case sensitive.

    Entry.objects.get(headline__contains='Lennon')

  16. icontains

    Fuzzy query, case insensitive.

    Entry.objects.get(headline__icontains='Lennon')

  17. in

    Queries the data in a list, tuple, or other query set, or receives a string.

    Entry.objects.filter(id__in=[1, 3, 4])

Entry.objects.filter(headline__in='abc')

    Equivalent to:

    SELECT ... WHERE id IN (1, 3, 4);

SELECT ... WHERE headline IN ('a', 'b', 'c');

  18. gt

    Greater than.

    Entry.objects.filter(id__gt=4)

  19. gte

    Greater than or equal to.

    Entry.objects.filter(id__gte=4)

  20. lt

    Less than.

    Entry.objects.filter(id__lt=4)

  21. lte

    Less than or equal to.

    Entry.objects.filter(id__lte=4)

  22. startswith

    Case sensitive to start

    Entry.objects.filter(headline__startswith='Lennon')

  23. istartswidth

    Case insensitive to start

    Entry.objects.filter(headline__istartswith='Lennon')

  24. endswith

    Case sensitive to end

    Entry.objects.filter(headline__endswith='Lennon')

  25. iendswith

    Case sensitive to end

    Entry.objects.filter(headline__iendswith='Lennon')

  26. range

    Scope query.

    import datetime

start_date = datetime.date(2005, 1, 1)

end_date = datetime.date(2005, 3, 31)

Entry.objects.filter(pub_date__range=(start_date, end_date))

  27. F

    F allows Django to have a reference to the value of a database field without actually linking the data. Usually, when updating data, the original data will be taken out from the database and put in memory, and then some properties will be edited for submission. For example:

    order = Order.objects.get(orderid='123456789')
order.amount += 1
order.save()

    The SQL statement at this time is equivalent to:

    UPDATE core_order SET ..., amount = 22 WHERE core_order.orderid = '123456789' 
# ... represents other values in the Order, which will be reassigned here; 22 represents the calculated result

    When using the F() function:

    from django.db.models import F
from core.models import Order
 
order = Order.objects.get(orderid='123456789')
order.amount = F('amount') - 1
order.save()

    At this time, the SQL statement is equivalent to:

    UPDATE core_order SET ..., amount = amount + 1 WHERE core_order.orderid = '123456789'

    After using this method to follow the new data, you need to reload the data to make the values in the database correspond to the values in the program:

    order= Order.objects.get(pk=order.pk) 
 
#  Or use a simpler approach:
order.refresh_from_db()

(3) Queries not suitable for caching

  1. count

    Returns an integer representing the number of matching objects in the database.

  2. in_bulk

    Get field value (id_list) and filed_ Name a list of these values and returns a dictionary that maps each value to an object instance of a specific given field value. If Id_ If list is not provided, all objects in the query set will be returned. Filed_ Name must be a unique field and default to primary key.

     Blog.objects.in_bulk([1])

#{1: <Blog: Beatles Blog>} 

Blog.objects.in_bulk([1, 2])

#{1:<Blog:BeatlesBlog>,2:<Blog:CheddarTalk>}

Blog.objects.in_bulk([]){}Blog.objects.in_bulk()

#{1: <Blog: Beatles Blog>, 2: <Blog: Cheddar Talk>, 3: <Blog: Django Weblog>}

Blog.objects.in_bulk(['beatles_blog'], field_name='slug')

#{'beatles_blog': <Blog: Beatles Blog>}

    be careful:

    If in_ If you pass an empty list in bluk, you get an empty dictionary.

  3. latest

    Returns the latest object in the list based on the given field. For example:

    Entry.objects.latest('pub_date')

  4. first

    Returns the first object matched in the result set.

    p = Article.objects.order_by('title', 'pub_date').first()

  5. last

    Similar to first, returns the last in the result set.

  6. aggregate

    Returns the dictionary QuerySet of the calculated total (average, sum, and so on). Each parameter specifies a value that will be included in the returned dictionary. For example:

    from django.db.models import Count

q = Blog.objects.aggregate(Count('entry'))
{'entry__count': 16}

  7. get

    Query a single piece of data according to the criteria, and an error will be reported if it is not found. If you want to return a row in the result set, you can use get() to return the object that changed the row without any parameters:

    entry = Entry.objects.filter(...).exclude(...).get()

(4) Save

  1. create

    You can create objects in one step and save them all:

    p = Person.objects.create(first_name="Bruce", last_name="Springsteen")

  2. save

    p = Person(first_name="Bruce", last_name="Springsteen")

p.save(force_insert=**True**)

    Or:

    P=Person()
p.first_name="Bruce"
p.last_name="Springsteen"
p.save()

  3. get_or_create

    A convenient way to use a given lookup object, kwargs (empty if all fields of the model have default values), and create an object if necessary.

  4. update_or_create

    A convenient way to update an object with a given object, the update_ Or_ The Create method attempts to get the object kwargs from the database based on the given value. If a match is found, it updates the fields passed in the defaults dictionary.

(5) Modification

  1. update

    Performs a SQL update query on the specified field and returns the number of matching rows (if some rows already have new values, the number may not be equal to the number of updated rows)

    Entry.objects.filter(pub_date__year=2010).update(comments_on=False, headline='This is old')

  2. save

    Modify using the save method.

    e = Entry.objects.get(id=10)

e.comments_on = False
e.save()

(6) Delete

  1. delete

    Delete QuerySet. For example:

    Entry.objects.filter(blog=b).delete()

#(4, {'weblog.Entry': 2, 'weblog.Entry_authors': 2})

Keywords: Django Database SQL less

Added by Phire on Tue, 16 Jun 2020 06:59:36 +0300

Popular Keywords

©2024 Programming VIP