Less Obvious Things To Do With Django's ORM (PyCon SK 2019)

My talk from PyCon SK 2019

Speaker notes and examples: https://markusholtermann.eu/2019/03/less-obvious-things-to-do-with-djangos-orm/

Full code: https://github.com/MarkusH/talk-orm/

Transcript

2. • •
4. class Author(models.Model): name = models.CharField(max_length=200) def __str__(self): return self.name
5. class Genre(models.Model): name = models.CharField(max_length=200, unique=True) def __str__(self): return self.name
6. class Book(models.Model): title = models.CharField(max_length=200) author = models.ForeignKey( Author, on_delete=models.CASCADE, related_name="books" ) votes = models.PositiveIntegerField() genres = models.ManyToManyField( "Genre", related_name="books" ) def __str__(self): return self.title
8. >>> from literature.models import Author, Book, Genre >>> Author.objects.count() 1142 >>> Book.objects.count() 3578 >>> Genre.objects.count() 412
10. >>> Author.objects.get() Traceback (most recent call last): File "<console>", line 1, in <module> File ".../django/db/models/manager.py", line 82, in manager_method return getattr(self.get_queryset(), name)(*args, **kwargs) File ".../django/db/models/query.py", line 403, in get (self.model._meta.object_name, num) literature.models.Author.MultipleObjectsReturned: get() returned more than one Author -- it returned 1142!
11. >>> Author.objects.first() <Author: Bill Bryson>
12. >>> Author.objects.filter(name="Markus").first() >>> Author.objects.filter(name="Markus").get() Traceback (most recent call last): File "<console>", line 1, in <module> File ".../django/db/models/query.py", line 399, in get self.model._meta.object_name literature.models.Author.DoesNotExist: Author matching query does not exist.
14. >>> Author.objects.filter(name__startswith="Lisa") <QuerySet [<Author: Lisa Unger>, <Author: Lisa Scottoline>, ...]>
15. >>> Author.objects.filter(name__icontains="tom") <QuerySet [<Author: Robert Tombs>, <Author: Tom Barbash>, <Author: Tom Sweterlitsch>, ...]>
18. >>> books = Book.objects.all() >>> for book in books: ... print(f"Title: {book.title} -- Author: {book.author.name}") Title: Harry Potter and the Sorcerer's Stone -- Author: J.K. Rowling Title: Harry Potter and the Prisoner of Azkaban -- Author: J.K. Rowling Title: A Short History of Nearly Everything -- Author: Bill Bryson Title: The Lord of the Rings -- Author: J.R.R. Tolkien Title: The Fellowship of the Ring -- Author: J.R.R. Tolkien Title: One Hundred Years of Solitude -- Author: Gabriel García Márquez Title: Angels & Demons -- Author: Dan Brown
20. >>> from django.db import connection >>> connection.queries [{'sql': 'SELECT COUNT(*) AS "__count" FROM "literature_author"', 'time': '0.000'}, {'sql': 'SELECT COUNT(*) AS "__count" FROM "literature_book"', 'time': '0.000'}, ...]
22. >>> books = Book.objects.select_related("author").all() >>> for book in books: ... print(f"Title: {book.title} -- Author: {book.author.name}") Title: Harry Potter and the Sorcerer's Stone -- Author: J.K. Rowling Title: Harry Potter and the Prisoner of Azkaban -- Author: J.K. Rowling Title: A Short History of Nearly Everything -- Author: Bill Bryson Title: The Lord of the Rings -- Author: J.R.R. Tolkien Title: The Fellowship of the Ring -- Author: J.R.R. Tolkien Title: One Hundred Years of Solitude -- Author: Gabriel García Márquez Title: Angels & Demons -- Author: Dan Brown
24. >>> authors = Author.objects.all() >>> for author in authors: ... print(f"Author: {author.name}") ... for book in author.books.all(): ... print(f" - Title: {book.title}") Author: Bill Bryson - Title: A Short History of Nearly Everything Author: Edith Wharton - Title: Ethan Frome Author: Patricia A. McKillip - Title: In the Forests of Serre - Title: The Bards of Bone Plain
25. >>> authors = Author.objects.prefetch_related("books") >>> for author in authors: ... print(f"Author: {author.name}") ... for book in author.books.all(): ... print(f" - Title: {book.title}") Author: Bill Bryson - Title: A Short History of Nearly Everything Author: Edith Wharton - Title: Ethan Frome Author: Patricia A. McKillip - Title: In the Forests of Serre - Title: The Bards of Bone Plain
26. >>> books = Book.objects.filter(author_id__in=[...])
27. >>> from django.db.models import Prefetch >>> prefetch_qs = Book.objects.filter(title__startswith="H") >>> authors = Author.objects.prefetch_related( ... Prefetch("books", queryset=prefetch_qs) ... )
29. COUNT
30. >>> from django.db.models import Count >>> authors = Author.objects.annotate(book_cnt=Count("books")) >>> for author in authors: ... print(f"Author: {author.name}: {author.book_cnt}") Author: Jen Wang: 10 Author: Sarah MacLean: 12 Author: Charles Soule: 12 Author: A.S. King: 1 Author: Jesmyn Ward: 1 Author: Victor LaValle: 2
31. SELECT "literature_author"."id", "literature_author"."name", COUNT("literature_book"."id") AS "book_cnt" FROM "literature_author" LEFT OUTER JOIN "literature_book" ON "literature_author"."id" = "literature_book"."author_id" GROUP BY "literature_author"."id", "literature_author"."name";
32. SUM
33. >>> from django.db.models import Sum >>> authors = Author.objects.annotate( ... sum_votes=Sum("books__votes") ... ).order_by("-sum_votes")[:5] >>> for author in authors: ... print(f"Author: {author.name}: {author.sum_votes} votes") Author: J.K. Rowling: 10354107 votes Author: Suzanne Collins: 10270371 votes Author: Rick Riordan: 5860434 votes Author: John Green: 5694398 votes Author: Stephen King: 5181285 votes
34. SELECT "literature_author"."id", "literature_author"."name", SUM("literature_book"."votes") AS "sum_votes" FROM "literature_author" LEFT OUTER JOIN "literature_book" ON "literature_author"."id" = "literature_book"."author_id" GROUP BY "literature_author"."id", "literature_author"."name" ORDER BY "sum_votes" DESC LIMIT 5
36. >>> from django.db.models import CharField, Value >>> from django.db.models.functions import StrIndex, Substr >>> substr_exp = Substr("name", 1, ... StrIndex("name", Value(" ")) - Value(1), ... output_field=CharField(max_length=100)) >>> Author.objects.annotate(first_name=substr_exp).annotate( ... book_count=Count("books") ... ).order_by("-book_count") <QuerySet [<Author: Stephen King>, <Author: Peter Meredith>, ...]>
37. >>> Author.objects.annotate( ... first_name=substr_exp ... ).values( ... "first_name" ... ).annotate( ... book_count=Count("books") ... ).order_by("-book_count") <QuerySet [{'first_name': 'Lisa', 'book_count': 56}, {'first_name': 'David', 'book_count': 53}, ...]>
41. >>> books_qs = Book.objects.all() >>> authors = Author.objects.prefetch_related( ... Prefetch("books", queryset=books_qs) ... )
42. >>> books_qs = Book.objects.order_by("-votes") >>> authors = Author.objects.prefetch_related( ... Prefetch("books", queryset=books_qs) ... )
43. >>> books_qs = Book.objects.order_by("-votes")[:3] >>> list( ... Author.objects.prefetch_related( ... Prefetch("books", queryset=books_qs) ... ) ... ) Traceback (most recent call last): ... AssertionError: Cannot filter a query once a slice has been taken.
44. >>> from django.db.models import OuterRef, Subquery >>> book_sub_qs = Book.objects.filter( ... author_id=OuterRef("author_id") ... ).order_by("-votes").values_list("id", flat=True)[:3] >>> books_qs = Book.objects.filter( ... pk__in=Subquery(book_sub_qs) ... ).order_by("-votes")
45. >>> from django.db.models import OuterRef, Subquery >>> book_sub_qs = Book.objects.filter(author_id=OuterRef("author_id") ... ).order_by("-votes").values_list("id", flat=True)[:3] >>> books_qs = Book.objects.filter(pk__in=Subquery(book_sub_qs) ... ).order_by("-votes") >>> authors = Author.objects.prefetch_related( ... Prefetch("books", queryset=books_qs)) >>> for author in authors: ... print(f"Author: {author.name}") ... for book in author.books.all(): ... print(f" - {book.title}") Author: Bill Bryson - A Short History of Nearly Everything Author: Patricia A. McKillip - In the Forests of Serre - The Bards of Bone Plain
46. SELECT "literature_book"."id", "literature_book"."title", "literature_book"."author_id", "literature_book"."votes" FROM "literature_book" WHERE "literature_book"."id" IN ( SELECT U0."id" FROM "literature_book" U0 WHERE U0."author_id" = "literature_book"."author_id" ORDER BY U0."votes" DESC LIMIT 3 ) AND "literature_book"."author_id" IN (7, 16, ..., 18885860) ORDER BY "literature_book"."votes" DESC;
48. >>> book_sub_qs = Book.objects.filter( ... genres=OuterRef("genres") ... ).order_by("-votes").values_list("pk", flat=True)[:3] >>> book_qs = Book.objects.distinct().filter( ... pk__in=Subquery(book_sub_qs)).order_by("-votes") >>> genres = Genre.objects.prefetch_related( ... Prefetch("books", queryset=book_qs)) >>> for genre in genres: ... print(f"Genre: {genre.name}") ... for book in genre.books.all(): ... print(f" - {book.title}")
49. SELECT DISTINCT "literature_book"."id", "literature_book"."title", "literature_book_genres"."genre_id" AS "_prefetch_related_val_genre_id", "literature_book"."author_id", "literature_book"."votes" FROM "literature_book" INNER JOIN "literature_book_genres" ON "literature_book"."id" = "literature_book_genres"."book_id" INNER JOIN "literature_book_genres" T4 ON "literature_book"."id" = T4."book_id" WHERE "literature_book"."id" IN ( SELECT U0."id" FROM "literature_book" U0 INNER JOIN "literature_book_genres" U1 ON U0."id" = U1."book_id" WHERE U1."genre_id" = "literature_book_genres"."genre_id" ORDER BY U0."votes" DESC LIMIT 3 ) AND T4."genre_id" IN (1, 2, ..., 411, 412) ORDER BY "literature_book"."votes" DESC