Less Obvious Things To Do With The ORM (DjangoCPH 2018)

Transcript

1. Less Obvious Things To Do
   With The ORM
   Markus Holtermann
   @m_holtermann
   markusholtermann.eu
   Django Copenhagen 2018
   #djangocph
   

   View Slide

2. @m_holtermann
   I am
   Markus Holtermann
   ● Senior Software Engineer at LaterPay
   ● Django Core Developer
   

   View Slide

3. @m_holtermann
   Our Database
   

   View Slide

4. from django.db import models
   class Author(models.Model):
   name = models.CharField(max_length=200)
   class Book(models.Model):
   title = models.CharField(max_length=200)
   author = models.ForeignKey(Author, related_name='books')
   

   View Slide

5. @m_holtermann
   Filter for objects
   

   View Slide

6. >>> Author.objects.filter(name='Astrid Lindgren')
   ]>
   >>> Author.objects.get(name='Astrid Lindgren')
   
   filter() and get()
   

   View Slide

7. >>> Author.objects.filter(name='Astrid')
   
   >>> Author.objects.get(name='Astrid')
   Traceback (most recent call last):
   File "", line 1, in
   File "django/db/models/manager.py", line 85, in
   manager_method
   return getattr(self.get_queryset(), name)(*args, **kwargs)
   File "django/db/models/query.py", line 380, in get
   self.model._meta.object_name
   literature.models.DoesNotExist: Author matching query does not
   exist.
   

   View Slide

8. >>> Author.objects.filter(name__startswith='Astrid')
   ]>
   >>> Author.objects.get(name__startswith='Astrid')
   
   __startswith
   

   View Slide

9. >>> Author.objects.filter(name='Markus').first()
   None
   first()
   

   View Slide

10. @m_holtermann
    Counting objects
    

    View Slide

11. >>> Author.objects.count()
    100
    >>> Book.objects.count()
    1960
    

    View Slide

12. @m_holtermann
    Count filtered objects
    

    View Slide

13. >>> Author.objects.filter(name__startswith='Joanne').count()
    1
    

    View Slide

14. @m_holtermann
    Check for an object’s existence
    

    View Slide

15. >>> Author.objects.filter(name__startswith='Markus').exists()
    False
    >>> Author.objects.filter(name__startswith='Joanne').exists()
    True
    

    View Slide

16. @m_holtermann
    Related objects
    

    View Slide

17. >>> books = Book.objects.all()
    >>> for book in books:
    ... print(f'{book.title} by {book.author.name}')
    What’s the author’s name for each book?
    

    View Slide

18. >>> books = Book.objects.all()
    >>> for book in books:
    ... print(f'{book.title} by {book.author.name}')
    What’s the author’s name for each book?
    Wrong
    

    View Slide

19. SELECT id, title, author_id FROM book;
    SELECT id, name FROM author WHERE id = 1;
    SELECT id, name FROM author WHERE id = 1;
    SELECT id, name FROM author WHERE id = 2;
    SELECT id, name FROM author WHERE id = 2;
    SELECT id, name FROM author WHERE id = 2;
    SELECT id, name FROM author WHERE id = 3;
    SELECT id, name FROM author WHERE id = 4;
    SELECT id, name FROM author WHERE id = 4;
    SELECT id, name FROM author WHERE id = 4;
    ...
    What’s the author’s name for each book?
    

    View Slide

20. >>> books = Book.objects.select_related('author').all()
    >>> for book in books:
    ... print(f'{book.title} by {book.author.name}')
    What’s the author’s name for each book?
    

    View Slide

21. >>> books = Book.objects.select_related('author').all()
    >>> for book in books:
    ... print(f'{book.title} by {book.author.name}')
    What’s the author’s name for each book?
    Correct
    

    View Slide

22. SELECT
    book.id, book.title, book.author_id, author.id, author.name
    FROM
    book
    INNER JOIN
    author
    ON book.author_id = author.id;
    What’s the author’s name for each book?
    

    View Slide

23. @m_holtermann
    • Follows ForeignKey und OneToOneField
    relations
    • Use if “the other side” of a relation has at most
    one object
    select_related()
    

    View Slide

24. @m_holtermann
    “reverse” related objects
    

    View Slide

25. >>> authors = Author.objects.all()
    >>> for author in authors:
    ... print(f'{author.name}:')
    ... books = author.books.all()
    ... for book in books:
    ... print(f'- {book.title}')
    Which books did an author write?
    

    View Slide

26. >>> authors = Author.objects.all()
    >>> for author in authors:
    ... print(f'{author.name}:')
    ... books = author.books.all()
    ... for book in books:
    ... print(f'- {book.title}')
    Which books did an author write?
    Wrong
    

    View Slide

27. SELECT id, name FROM author;
    SELECT id, title, author_id FROM book WHERE author_id = 1;
    SELECT id, title, author_id FROM book WHERE author_id = 2;
    SELECT id, title, author_id FROM book WHERE author_id = 3;
    SELECT id, title, author_id FROM book WHERE author_id = 4;
    SELECT id, title, author_id FROM book WHERE author_id = 5;
    SELECT id, title, author_id FROM book WHERE author_id = 6;
    ...
    Which books did an author write?
    

    View Slide

28. >>> authors = Author.objects.prefetch_related('books').all()
    >>> for author in authors:
    ... print(f'{author.name}:')
    ... books = author.books.all()
    ... for book in books:
    ... print(f'- {book.title}')
    Which books did an author write?
    

    View Slide

29. >>> authors = Author.objects.prefetch_related('books').all()
    >>> for author in authors:
    ... print(f'{author.name}:')
    ... books = author.books.all()
    ... for book in books:
    ... print(f'- {book.title}')
    Which books did an author write?
    Correct
    

    View Slide

30. SELECT id, name FROM author;
    SELECT id, title, author_id FROM book WHERE author_id IN (1, 2, 3, 4, …);
    Which books did an author write?
    

    View Slide

31. @m_holtermann
    • Follows reverse ForeignKey relations as well
    as ManyToManyField relations
    • Use if “the other side” of the relation can have
    more than one object
    prefetch_related()
    

    View Slide

32. @m_holtermann
    Counting. Again.
    

    View Slide

33. >>> authors = Author.objects.filter(name__startswith='Joh')
    >>> for author in authors:
    ... print(f'{author.name}: {author.books.count()}')
    How many books did each author write
    whose name starts with “Joh”?
    

    View Slide

34. >>> authors = Author.objects.filter(name__startswith='Joh')
    >>> for author in authors:
    ... print(f'{author.name}: {author.books.count()}')
    How many books did each author write
    whose name starts with “Joh”?
    Wrong
    

    View Slide

35. SELECT id, name FROM author WHERE name LIKE 'Jo%';
    SELECT COUNT(*) AS __count FROM book WHERE author_id = 3;
    SELECT COUNT(*) AS __count FROM book WHERE author_id = 48;
    SELECT COUNT(*) AS __count FROM book WHERE author_id = 60;
    How many books did each author write
    whose name starts with “Joh”?
    

    View Slide

36. >>> from django.db.models.aggregates import Count
    >>> authors = Author.objects.filter(name__startswith='Joh') \
    ... .annotate(books_count=Count('books'))
    >>> for author in authors:
    ... print(f'{author.name}: {author.books_count}')
    How many books did each author write
    whose name starts with “Joh”?
    

    View Slide

37. >>> from django.db.models.aggregates import Count
    >>> authors = Author.objects.filter(name__startswith='Joh') \
    ... .annotate(books_count=Count('books'))
    >>> for author in authors:
    ... print(f'{author.name}: {author.books_count}')
    How many books did each author write
    whose name starts with “Joh”?
    Correct
    

    View Slide

38. SELECT
    author.id, author.name, COUNT(book.id) AS books_count
    FROM
    author
    LEFT OUTER JOIN
    book ON author.id = book.author_id
    WHERE
    author.name LIKE 'Joh%'
    GROUP BY
    author.id, author.name;
    How many books did each author write
    whose name starts with “Joh”?
    

    View Slide

39. >>> from django.db.models.aggregates import Count
    >>> authors = Author.objects.annotate(
    ... books_count=Count('books'))
    >>> for author in authors:
    ... print(f'{author.name}: {author.books_count}')
    How many books did each author write?
    

    View Slide

40. @m_holtermann
    • Annotates additional information on a model
    instance.
    • Use for aggregation like “Avg()”, “Count()”,
    “Max()”, “Min()”, “Sum()” etc.
    .annotate()
    

    View Slide

41. @m_holtermann
    Counting is still fun ...
    ... Statistics as well
    

    View Slide

42. >>> from django.db.models.functions import Length
    >>> Book.objects.annotate(title_length=Length('title')) \
    ... .values_list('title_length')
    (15,), (21,), (29,), (33,), (33,), '...(remaining elements
    truncated)...']>
    What’s the minimum, maximum and
    average length of all book titles?
    

    View Slide

43. >>> from django.db.models.functions import Length
    >>> Book.objects.annotate(title_length=Length('title')) \
    ... .values_list('title_length', flat=True)
    '...(remaining elements truncated)...']>
    What’s the minimum, maximum and
    average length of all book titles?
    

    View Slide

44. >>> from django.db.models.functions import Length
    >>> lengths = Book.objects.annotate(
    ... title_length=Length('title')
    ... ).values_list('title_length', flat=True)
    >>> print(f'min={min(lengths)}, max={max(lengths)}, '
    ... f'avg={sum(lengths)/len(lengths)}')
    What’s the minimum, maximum and
    average length of all book titles?
    

    View Slide

45. >>> from django.db.models.functions import Length
    >>> lengths = Book.objects.annotate(
    ... title_length=Length('title')
    ... ).values_list('title_length', flat=True)
    >>> print(f'min={min(lengths)}, max={max(lengths)}, '
    ... f'avg={sum(lengths)/len(lengths)}')
    What’s the minimum, maximum and
    average length of all book titles?
    Wrong
    

    View Slide

46. >>> from django.db.models.aggregates import Avg, Max, Min
    >>> from django.db.models.functions import Length
    >>> stats = Book.objects.annotate(title_length=Length('title')
    ... ).aggregate(avg=Avg('title_length'),
    ... min=Min('title_length'), max=Max('title_length'))
    >>> stats
    {'avg': 33.361224489795916, 'max': 211, 'min': 2}
    >>> print(f'min={stats["min"]}, max={stats["max"]}, '
    ... f'avg={stats["avg"]}')
    What’s the minimum, maximum and
    average length of all book titles?
    

    View Slide

47. >>> from django.db.models.aggregates import Avg, Max, Min
    >>> from django.db.models.functions import Length
    >>> stats = Book.objects.annotate(title_length=Length('title')
    ... ).aggregate(avg=Avg('title_length'),
    ... min=Min('title_length'), max=Max('title_length'))
    >>> stats
    {'avg': 33.361224489795916, 'max': 211, 'min': 2}
    >>> print(f'min={stats["min"]}, max={stats["max"]}, '
    ... f'avg={stats["avg"]}')
    What’s the minimum, maximum and
    average length of all book titles?
    Correct
    

    View Slide

48. @m_holtermann
    • Use for aggregation like “Avg()”, “Count()”,
    “Max()”, “Min()”, “Sum()” etc.
    • Returns a dict “dict()” with the corresponding
    values
    .aggregate()
    

    View Slide

49. >>> from django.db.models.aggregates import Avg
    >>> from django.db.models.functions import Length
    >>>
    ...
    ...
    'J. R. R. Tolkien', 'avg': 45.5}, {'name': 'Erich Kästner',
    'avg': 21.4}, {'name': 'George Orwell', 'avg': 22.3},
    '...(remaining elements truncated)...']>
    What’s the average length of all book title
    an author wrote?
    

    View Slide

50. >>> from django.db.models.aggregates import Avg
    >>> from django.db.models.functions import Length
    >>> Author.objects.
    ... Length('books__title')
    ...
    'J. R. R. Tolkien', 'avg': 45.5}, {'name': 'Erich Kästner',
    'avg': 21.4}, {'name': 'George Orwell', 'avg': 22.3},
    '...(remaining elements truncated)...']>
    What’s the average length of all book title
    an author wrote?
    

    View Slide

51. >>> from django.db.models.aggregates import Avg
    >>> from django.db.models.functions import Length
    >>> Author.objects.
    ... avg=Avg(Length('books__title'))
    ...
    'J. R. R. Tolkien', 'avg': 45.5}, {'name': 'Erich Kästner',
    'avg': 21.4}, {'name': 'George Orwell', 'avg': 22.3},
    '...(remaining elements truncated)...']>
    What’s the average length of all book title
    an author wrote?
    

    View Slide

52. >>> from django.db.models.aggregates import Avg
    >>> from django.db.models.functions import Length
    >>> Author.objects.annotate(
    ... avg=Avg(Length('books__title'))
    ... )
    'J. R. R. Tolkien', 'avg': 45.5}, {'name': 'Erich Kästner',
    'avg': 21.4}, {'name': 'George Orwell', 'avg': 22.3},
    '...(remaining elements truncated)...']>
    What’s the average length of all book title
    an author wrote?
    

    View Slide

53. >>> from django.db.models.aggregates import Avg
    >>> from django.db.models.functions import Length
    >>> Author.objects.annotate(
    ... avg=Avg(Length('books__title'))
    ... ).values('name', 'avg')
    'J. R. R. Tolkien', 'avg': 45.5}, {'name': 'Erich Kästner',
    'avg': 21.4}, {'name': 'George Orwell', 'avg': 22.3},
    '...(remaining elements truncated)...']>
    What’s the average length of all book title
    an author wrote?
    

    View Slide

54. @m_holtermann
    Summary
    

    View Slide

55. @m_holtermann
    • .filter() / .get() / .first() / .last()
    

    View Slide

56. @m_holtermann
    • .filter() / .get() / .first() / .last()
    • .count() / .exists()
    

    View Slide

57. @m_holtermann
    • .filter() / .get() / .first() / .last()
    • .count() / .exists()
    • .select_related() / .prefetch_related()
    

    View Slide

58. @m_holtermann
    • .filter() / .get() / .first() / .last()
    • .count() / .exists()
    • .select_related() / .prefetch_related()
    • .annotate() / .aggregate()
    

    View Slide

59. @m_holtermann
    • .filter() / .get() / .first() / .last()
    • .count() / .exists()
    • .select_related() / .prefetch_related()
    • .annotate() / .aggregate()
    • .values() / .values_list()
    

    View Slide

60. Q&A?
    

    View Slide

61. >>> from django.db.models.aggregates import Count
    >>> Author.objects \
    ... .filter(name__startswith='Heinrich') \
    ... .annotate(book_count=Count('books')) \
    ... .values_list('name', 'book_count')
    ('Heinrich von Kleist', 20), ('Heinrich Harrer', 20)]>
    The order of filter() and annotate()
    

    View Slide

62. SELECT author.name, COUNT(author.id) AS book_count
    FROM author
    LEFT OUTER JOIN book
    ON author.id = book.author_id
    WHERE author.name LIKE 'Heinrich%'
    GROUP BY author.id, author.name;
    The order of filter() and annotate()
    

    View Slide

63. >>> from django.db.models.aggregates import Count
    >>> Author.objects \
    ... .filter(name__startswith='Heinrich') \
    ... .annotate(book_count=Count('books')) \
    ... .values_list('name', 'book_count')
    The order of filter() and annotate()
    

    View Slide

64. >>> from django.db.models.aggregates import Count
    >>> Author.objects \
    ... .filter(name__startswith='Heinrich') \
    ... .annotate(book_count=Count('books')) \
    ... .values_list('name', 'book_count') \
    ... .filter(books__title__icontains='die')
    The order of filter() and annotate()
    

    View Slide

65. >>> from django.db.models.aggregates import Count
    >>> Author.objects \
    ... .filter(name__startswith='Heinrich') \
    ... .annotate(book_count=Count('books')) \
    ... .values_list('name', 'book_count') \
    ... .filter(books__title__icontains='die')
    ('Heinrich von Kleist', 140), ('Heinrich Harrer', 60)]>
    The order of filter() and annotate()
    

    View Slide

66. >>> from django.db.models.aggregates import Count
    >>> Author.objects \
    ... .filter(name__startswith='Heinrich') \
    ... .annotate(book_count=Count('books')) \
    ... .values_list('name', 'book_count') \
    ... .filter(books__title__icontains='die')
    ('Heinrich von Kleist', 140), ('Heinrich Harrer', 60)]>
    The order of filter() and annotate()
    Wrong
    

    View Slide

67. SELECT author.name, COUNT(book.id) AS book_count
    FROM author
    LEFT OUTER JOIN book
    ON author.id = book.author_id
    INNER JOIN book T3
    ON author.id = T3.author_id
    WHERE author.name LIKE 'Heinrich%' AND T3.title LIKE '%die%'
    GROUP BY author.id, author.name;
    The order of filter() and annotate()
    

    View Slide

68. >>> from django.db.models.aggregates import Count
    >>> Author.objects \
    ... .filter(name__startswith='Heinrich') \
    ... .annotate(book_count=Count('books')) \
    ... .values_list('name', 'book_count')
    The order of filter() and annotate()
    

    View Slide

69. >>> from django.db.models.aggregates import Count
    >>> Author.objects \
    ... .filter(name__startswith='Heinrich',
    ... books__title__icontains='die') \
    ... .annotate(book_count=Count('books')) \
    ... .values_list('name', 'book_count')
    The order of filter() and annotate()
    

    View Slide

70. >>> from django.db.models.aggregates import Count
    >>> Author.objects \
    ... .filter(name__startswith='Heinrich',
    ... books__title__icontains='die') \
    ... .annotate(book_count=Count('books')) \
    ... .values_list('name', 'book_count')
    ('Heinrich von Kleist', 7), ('Heinrich Harrer', 3)]>
    The order of filter() and annotate()
    

    View Slide

71. >>> from django.db.models.aggregates import Count
    >>> Author.objects \
    ... .filter(name__startswith='Heinrich',
    ... books__title__icontains='die') \
    ... .annotate(book_count=Count('books')) \
    ... .values_list('name', 'book_count')
    ('Heinrich von Kleist', 7), ('Heinrich Harrer', 3)]>
    The order of filter() and annotate()
    Correct
    

    View Slide

72. SELECT author.name, COUNT(book.id) AS book_count
    FROM author
    INNER JOIN book
    ON author.id = book.author_id
    WHERE author.name LIKE 'Heinrich%' AND book.title LIKE '%die%'
    GROUP BY author.id, author.name
    The order of filter() and annotate()
    

    View Slide

73. SELECT author.name, COUNT(book.id) AS book_count
    FROM author
    LEFT OUTER JOIN book
    ON author.id = book.author_id
    INNER JOIN book T3
    ON author.id = T3.author_id
    WHERE author.name LIKE 'Heinrich%' AND T3.title LIKE '%die%'
    GROUP BY author.id, author.name;
    The order of filter() and annotate()
    

    View Slide

74. Q&A?
    

    View Slide

75. >>> authors = Author.objects.prefetch_related('books').all()
    >>> for author in authors:
    ... print(f'{author.name}:')
    ... books = author.books.all()
    ... for book in books:
    ... print(f'- {book.title}')
    Which books did an author write?
    

    View Slide

76. SELECT id, name FROM author;
    SELECT id, title, author_id FROM book WHERE author_id IN (1, 2, 3, 4, …);
    Which books did an author write?
    

    View Slide

77. >>> authors = Author.objects.prefetch_related('books').all()
    >>> for author in authors:
    ... print(f'{author.name}:')
    ... books = author.books.filter(
    ... title__startswith='S').all()
    ... for book in books:
    ... print(f'- {book.title}')
    Filter prefetched data
    

    View Slide

78. >>> authors = Author.objects.prefetch_related('books').all()
    >>> for author in authors:
    ... print(f'{author.name}:')
    ... books = author.books.filter(
    ... title__startswith='S').all()
    ... for book in books:
    ... print(f'- {book.title}')
    Filter prefetched data
    Wrong
    

    View Slide

79. SELECT id, name FROM author;
    SELECT id, title, author_id FROM book WHERE author_id = 1 AND title LIKE 'S%';
    SELECT id, title, author_id FROM book WHERE author_id = 2 AND title LIKE 'S%';
    SELECT id, title, author_id FROM book WHERE author_id = 3 AND title LIKE 'S%';
    SELECT id, title, author_id FROM book WHERE author_id = 4 AND title LIKE 'S%';
    SELECT id, title, author_id FROM book WHERE author_id = 5 AND title LIKE 'S%';
    SELECT id, title, author_id FROM book WHERE author_id = 6 AND title LIKE 'S%';
    ...
    Filter prefetched data
    

    View Slide

80. >>> from django.db.models import Prefetch
    >>> books_pf = Prefetch('books',
    ... queryset=Book.objects.filter(title__startswith='S'))
    >>> authors = Author.objects.prefetch_related(books_pf).all()
    >>> for author in authors:
    ... print(f'{author.name}:')
    ... for book in author.books:
    ... print(f'- {book.title}')
    Filter prefetched data
    

    View Slide

81. >>> from django.db.models import Prefetch
    >>> books_pf = Prefetch('books',
    ... queryset=Book.objects.filter(title__startswith='S'))
    >>> authors = Author.objects.prefetch_related(books_pf).all()
    >>> for author in authors:
    ... print(f'{author.name}:')
    ... for book in author.books:
    ... print(f'- {book.title}')
    Filter prefetched data
    Correct
    

    View Slide

82. >>> books_a = Prefetch('books',
    ... queryset=Book.objects.filter(title__startswith='A'),
    ... to_attr='books_a')
    >>> books_b = Prefetch('books',
    ... queryset=Book.objects.filter(title__startswith='B'),
    ... to_attr='books_b')
    >>> authors = Author.objects.prefetch_related(
    ... books_pf_a, books_pf_b).all()
    >>> for author in authors:
    ... print(f'{author.name}:')
    ... for book in chain(author.books_a, author.books_b):
    ... print(f'- {book.title}')
    Filter prefetched data
    

    View Slide

83. Q&A?
    

    View Slide

84. Thanks
    Markus Holtermann
    @m_holtermann
    markusholtermann.eu
    

    View Slide