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

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

Cd7648c536b4dbe940246b74044fbc52?s=128

Markus H

March 16, 2018
Tweet

Transcript

  1. Less Obvious Things To Do With The ORM Markus Holtermann

    @m_holtermann markusholtermann.eu Django Copenhagen 2018 #djangocph
  2. @m_holtermann I am Markus Holtermann • Senior Software Engineer at

    LaterPay • Django Core Developer
  3. @m_holtermann Our Database

  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')
  5. @m_holtermann Filter for objects

  6. >>> Author.objects.filter(name='Astrid Lindgren') <QuerySet [<Author: Astrid Lindgren>]> >>> Author.objects.get(name='Astrid Lindgren')

    <Author: Astrid Lindgren> filter() and get()
  7. >>> Author.objects.filter(name='Astrid') <QuerySet []> >>> Author.objects.get(name='Astrid') Traceback (most recent call

    last): File "<console>", line 1, in <module> 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.
  8. >>> Author.objects.filter(name__startswith='Astrid') <QuerySet [<Author: Astrid Lindgren>]> >>> Author.objects.get(name__startswith='Astrid') <Author: Astrid

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

  10. @m_holtermann Counting objects

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

  12. @m_holtermann Count filtered objects

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

  14. @m_holtermann Check for an object’s existence

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

  16. @m_holtermann Related objects

  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?
  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
  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?
  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?
  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
  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?
  23. @m_holtermann • Follows ForeignKey und OneToOneField relations • Use if

    “the other side” of a relation has at most one object select_related()
  24. @m_holtermann “reverse” related objects

  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?
  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
  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?
  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?
  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
  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?
  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()
  32. @m_holtermann Counting. Again.

  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”?
  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
  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”?
  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”?
  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
  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”?
  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?
  40. @m_holtermann • Annotates additional information on a model instance. •

    Use for aggregation like “Avg()”, “Count()”, “Max()”, “Min()”, “Sum()” etc. .annotate()
  41. @m_holtermann Counting is still fun ... ... Statistics as well

  42. >>> from django.db.models.functions import Length >>> Book.objects.annotate(title_length=Length('title')) \ ... .values_list('title_length')

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

    flat=True) <QuerySet [19, 45, 43, 36, 25, 19, 21, 15, 21, 29, 33, 33, '...(remaining elements truncated)...']> What’s the minimum, maximum and average length of all book titles?
  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?
  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
  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?
  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
  48. @m_holtermann • Use for aggregation like “Avg()”, “Count()”, “Max()”, “Min()”,

    “Sum()” etc. • Returns a dict “dict()” with the corresponding values .aggregate()
  49. >>> from django.db.models.aggregates import Avg >>> from django.db.models.functions import Length

    >>> ... ... <QuerySet [{'name': 'Astrid Lindgren', 'avg': 28.25}, {'name': '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?
  50. >>> from django.db.models.aggregates import Avg >>> from django.db.models.functions import Length

    >>> Author.objects. ... Length('books__title') ... <QuerySet [{'name': 'Astrid Lindgren', 'avg': 28.25}, {'name': '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?
  51. >>> from django.db.models.aggregates import Avg >>> from django.db.models.functions import Length

    >>> Author.objects. ... avg=Avg(Length('books__title')) ... <QuerySet [{'name': 'Astrid Lindgren', 'avg': 28.25}, {'name': '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?
  52. >>> from django.db.models.aggregates import Avg >>> from django.db.models.functions import Length

    >>> Author.objects.annotate( ... avg=Avg(Length('books__title')) ... ) <QuerySet [{'name': 'Astrid Lindgren', 'avg': 28.25}, {'name': '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?
  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') <QuerySet [{'name': 'Astrid Lindgren', 'avg': 28.25}, {'name': '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?
  54. @m_holtermann Summary

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

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

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

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

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

    .count() / .exists() • .select_related() / .prefetch_related() • .annotate() / .aggregate() • .values() / .values_list()
  60. Q&A?

  61. >>> from django.db.models.aggregates import Count >>> Author.objects \ ... .filter(name__startswith='Heinrich')

    \ ... .annotate(book_count=Count('books')) \ ... .values_list('name', 'book_count') <QuerySet [('Heinrich Heine', 20), ('Heinrich Böll', 20), ('Heinrich von Kleist', 20), ('Heinrich Harrer', 20)]> The order of filter() and annotate()
  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()
  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()
  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()
  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') <QuerySet [('Heinrich Heine', 40), ('Heinrich Böll', 40), ('Heinrich von Kleist', 140), ('Heinrich Harrer', 60)]> The order of filter() and annotate()
  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') <QuerySet [('Heinrich Heine', 40), ('Heinrich Böll', 40), ('Heinrich von Kleist', 140), ('Heinrich Harrer', 60)]> The order of filter() and annotate() Wrong
  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()
  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()
  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()
  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') <QuerySet [('Heinrich Heine', 2), ('Heinrich Böll', 2), ('Heinrich von Kleist', 7), ('Heinrich Harrer', 3)]> The order of filter() and annotate()
  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') <QuerySet [('Heinrich Heine', 2), ('Heinrich Böll', 2), ('Heinrich von Kleist', 7), ('Heinrich Harrer', 3)]> The order of filter() and annotate() Correct
  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()
  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()
  74. Q&A?

  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?
  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?
  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
  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
  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
  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
  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
  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
  83. Q&A?

  84. Thanks Markus Holtermann @m_holtermann markusholtermann.eu