Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Less Obvious Things To Do With Django's ORM (Py...

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

Markus H

March 23, 2019
Tweet

More Decks by Markus H

Other Decks in Programming

Transcript

  1. 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
  2. >>> from literature.models import Author, Book, Genre >>> Author.objects.count() 1142

    >>> Book.objects.count() 3578 >>> Genre.objects.count() 412
  3. >>> 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!
  4. >>> 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.
  5. >>> 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
  6. >>> 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'}, ...]
  7. >>> 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
  8. >>> 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
  9. >>> 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
  10. >>> from django.db.models import Prefetch >>> prefetch_qs = Book.objects.filter(title__startswith="H") >>>

    authors = Author.objects.prefetch_related( ... Prefetch("books", queryset=prefetch_qs) ... )
  11. >>> 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
  12. 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";
  13. SUM

  14. >>> 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
  15. 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
  16. >>> 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>, ...]>
  17. >>> 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}, ...]>
  18. >>> 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.
  19. >>> 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")
  20. >>> 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
  21. 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;
  22. >>> 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}")
  23. 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