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

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

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. View Slide



  2. View Slide

  3. View Slide

  4. class Author(models.Model):
    name = models.CharField(max_length=200)
    def __str__(self):
    return self.name

    View Slide

  5. class Genre(models.Model):
    name = models.CharField(max_length=200, unique=True)
    def __str__(self):
    return self.name

    View Slide

  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

    View Slide

  7. View Slide

  8. >>> from literature.models import Author, Book, Genre
    >>> Author.objects.count()
    1142
    >>> Book.objects.count()
    3578
    >>> Genre.objects.count()
    412

    View Slide

  9. View Slide

  10. >>> Author.objects.get()
    Traceback (most recent call last):
    File "", line 1, in
    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!

    View Slide

  11. >>> Author.objects.first()

    View Slide

  12. >>> Author.objects.filter(name="Markus").first()
    >>> Author.objects.filter(name="Markus").get()
    Traceback (most recent call last):
    File "", line 1, in
    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.

    View Slide

  13. View Slide

  14. >>> Author.objects.filter(name__startswith="Lisa")
    , ,
    ...]>

    View Slide

  15. >>> Author.objects.filter(name__icontains="tom")
    , ,
    , ...]>

    View Slide

  16. View Slide

  17. View Slide

  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

    View Slide

  19. View Slide

  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'}, ...]

    View Slide

  21. View Slide

  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

    View Slide

  23. View Slide

  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

    View Slide

  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

    View Slide

  26. >>> books = Book.objects.filter(author_id__in=[...])

    View Slide

  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)
    ... )

    View Slide

  28. View Slide

  29. COUNT

    View Slide

  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

    View Slide

  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";

    View Slide

  32. SUM

    View Slide

  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

    View Slide

  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

    View Slide

  35. View Slide

  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")
    , Meredith>, ...]>

    View Slide

  37. >>> Author.objects.annotate(
    ... first_name=substr_exp
    ... ).values(
    ... "first_name"
    ... ).annotate(
    ... book_count=Count("books")
    ... ).order_by("-book_count")
    {'first_name': 'David', 'book_count': 53}, ...]>

    View Slide

  38. View Slide

  39. View Slide

  40. View Slide

  41. >>> books_qs = Book.objects.all()
    >>> authors = Author.objects.prefetch_related(
    ... Prefetch("books", queryset=books_qs)
    ... )

    View Slide

  42. >>> books_qs = Book.objects.order_by("-votes")
    >>> authors = Author.objects.prefetch_related(
    ... Prefetch("books", queryset=books_qs)
    ... )

    View Slide

  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.

    View Slide

  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")

    View Slide

  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

    View Slide

  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;

    View Slide

  47. View Slide

  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}")

    View Slide

  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

    View Slide

  50. View Slide

  51. View Slide