QuerySet API reference (stock portfolio example)

An in-depth guide to Django QuerySet (Django 3.0, updated June 2020).

When QuerySets are evaluated

Internally, a QuerySet can be constructed, filtered, sliced, and generally passed around without actually hitting the database. No database activity actually occurs until you do something to evaluate the queryset. Examples of that "something" that cause a QuerySet to evaluate:

Iteration: QuerySet executes its database query the first time you iterate over it
```
for q in Quote.objects.all():
    print(q.symbol)
```
Slicing: Slicing an unevaluated QuerySet usually returns another unevaluated QuerySet, but Django will execute the database query if you use the “step” parameter of slice syntax, and will return a list
repr(): A QuerySet is evaluated wh
en you call repr() on it. This is for convenience in the Python interactive interpreter, so you can immediately see your results when using the API interactively.
len(): A QuerySet is evaluated when you call len() on it. This, as you might expect, returns the length of the result list. Note: use .count() instead.

list(): Forces evaluation of QuerySet

list(Quote.objects.all())
# [<Quote: G3B.SI: 223.00 units @2.66>, <Quote: RTX: 15.00 units @60.53>]

Testing QuerySet in a boolean context, such as using bool(), or, and or an if statement, will cause the query to be executed.

# doesn't evaluate:
Quote.objects.filter(symbol="RTX")
# evaluate (notice the if statement):    
if Quote.objects.filter(symbol="RTX"):
    print("Raytheon in portfolio")

QuerySet API

The QuerySet class has two public attributes you can use for introspection:

ordered
- True if the QuerySet is ordered — i.e. has an order_by() clause or a default ordering on the model. False otherwise.
db
- The database that will be used if this query is executed now.

from apps.pipeline.models import Portfolio, Quote
from apps.users.models import CustomUser

CustomUser.objects.exists() # True

type(CustomUser) # <class 'django.db.models.base.ModelBase'>
type(CustomUser.objects) # <class 'django.contrib.auth.models.UserManager'>
type(CustomUser.objects.exists) # <class 'method'>
type(CustomUser.objects.all()) # <class 'django.db.models.query.QuerySet'>

CustomUser.objects.all().ordered # False
CustomUser.objects.all().db # default

Methods that return new QuerySets

filter(): Returns a new QuerySet using the lookup parameters (**kwargs) specified in the Field lookups format. Multiple parameters are joined via AND in the underlying SQL statement.
- Field lookups are how you specify the meat of an SQL WHERE clause. They're specified as keyword arguments to the QuerySet methods filter(), exclude() and get().
- When no lookup type is provided, it assumes exact for exact match

exclude(): Returns a new QuerySet containing objects that do not match the lookup parameters (**kwargs). Pay attention to the difference:

# exclude trades that are done in 2019 AND with commission >= 0.5  
Quote.objects.exclude(trade_date__year=2019, commission__lte=0.5)
# exclude trades that are done in 2019 OR with commission >= 0.5
Quote.objects.exclude(trade_date__year=2019).exclude(commission__lte=0.5)

annotate(): Annotates each object with the provided list of query expressions, see Query Expressions section for reference
order_by() and reverse()
disinct()
values()and values_list()
dates() and datetimes()
none() and all()
union(), intersection(), difference()
select_related() and prefetch_related()
extra()
defer() and only()
using()
select_for_update()
raw()

Some filtering operations using all Field lookups in practice:

# Notice it assumes exact if no lookup type is specified
Quote.objects.get(id=1)
# <Quote: G3B.SI: 223.00 units @2.66>
    
# sql: SELECT ... WHERE id = 1;
Quote.objects.get(id__exact=1)
# <Quote: G3B.SI: 223.00 units @2.66>
    
# sql: SELECT ... WHERE symbol ILIKE 'Rtx';
Quote.objects.get(symbol__iexact='Rtx')
    
# case-sensitive containment test
Quote.objects.get(symbol__contains='SI')
    
# case-insensitive containment test
# sql: SELECT ... WHERE symbol ILIKE '%Si%'
Quote.objects.get(symbol__icontains='Si')
    
# In a given iterable, often a list, tuple or queryset
# SELECT ... WHERE id IN ('RTX', 'LMT', 'BA');
Quote.objects.get(symbol__in=['RTX', 'LMT', 'BA'])

# sql: SELECT * FROM pipeline_quote WHERE portfolio_id IN 
#   (SELECT id FROM pipeline_portfolio WHERE owner_id=3);
currentuserpf = Portfolio.objects.filter(owner_id=3)
Quote.objects.filter(portfolio__in=currentuserpf)
# <CopyQuerySet [<Quote: RTX: 15.00 units @60.53>, <Quote: G3B.SI: 223.00 units @2.66>]>
    
# .values('id', 'name') will throw error. Use one field value for filter
currentuserpf = Portfolio.objects.filter(name__contains='US').values('id')
Quote.objects.filter(portfolio__in=currentuserpf)
    
# gt, gte (>=), lt, lte (<=)
Quote.objects.filter(commission__gte=2)
    
# startswith, istartwith (case-insensitive), endswith, iendswith
Quote.objects.filter(comment__startswith='DBS')
    
# range
# sql: SELECT ... WHERE trade_date BETWEEN '2020-03-01' and '2020-06-30';
startdate = datetime.date(2020,3,1)
enddate = datetime.date(2020,6,30)
Quote.objects.filter(trade_date__range=(startdate, enddate))
# <CopyQuerySet [<Quote: RTX: 15.00 units @60.53>, <Quote: G3B.SI: 223.00 units @2.66>]>

# date to cast value of a DateTime field into Date; Doesn't work on DateField 
# since no casting is required / valid
Quote.objects.filter(trade_timestamp__date=datetime.date(2020, 5, 28))
Quote.objects.filter(trade_timestamp__date__gt=datetime.date(2020, 5, 28))

# year (date) and iso_year (datetime), month and day (date and datetime)
Quote.objects.filter(trade_date__year=2020)
Quote.objects.filter(trade_date__year__gte=2020)
Quote.objects.filter(trade_timestamp__iso_year=2020)
Quote.objects.filter(trade_timestamp__iso_year__gt=2019)
Quote.objects.filter(trade_date__month__gte=5)
Quote.objects.filter(trade_date__day__lte=29)

# week returns 1-52 or 53 according to ISO-8601, starts on Monday.
# week_day ranges from 1 (sunday) to 7 (saturday) 
# quarter ranges from 1 to 4
Quote.objects.filter(trade_date__week__gte=10, trade_date__week__lte=52)
Quote.objects.filter(trade_date__week_day=1)
Quote.objects.filter(trade_date__quarter=1)

# When USE_TZ is True, fields converted to current timezone before filtering
Portfolio.objects.filter(last_updated__time__gte=datetime.time(12,5))
Portfolio.objects.filter(last_updated__hour=12)
Portfolio.objects.filter(last_updated__hour__gte=11)
Portfolio.objects.filter(last_updated__minute__gte=9)
Portfolio.objects.filter(last_updated__second__gte=9)

# isnull takes either True/False, correspond to SQL's IS NULL and IS NOT NULL
Quote.objects.filter(comment__isnull=True)

# regex (case-sensitive) and iregex (case-insensitive)
Quote.objects.filter(comment__regex=r'^(DBS|Dbs) +')
Quote.objects.filter(comment__iregex=r'^(dbs|posb) +')

As a reminder, these are the field lookups that instruct our SQL WHERE clause and they are specified as arguments to the QuerySet methods filter(), exclude() and get() even though examples in the snippet above demonstrate their usage with the filter() method.

Operators that return new QuerySets

Methods that do not return QuerySets

Many QuerySet methods evaluate the QuerySet and then return something other than a QuerySet:

type(Quote.objects.get(id=1))
<class 'apps.pipeline.models.Quote'>

type(Quote.objects.filter(id=1))
<class 'postgres_copy.managers.CopyQuerySet'>

Notice in the example above get() returns the object, in this case an instance of Quote. It doesn't return a QuerySet.

get()
create()

onlyphantom/querysets.md