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

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

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

Markus H

March 16, 2018
Tweet

More Decks by Markus H

Other Decks in Programming

Transcript

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

    @m_holtermann markusholtermann.eu Django Copenhagen 2018 #djangocph
  2. 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')
  3. >>> 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.
  4. >>> 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?
  5. >>> 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
  6. 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?
  7. >>> 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?
  8. >>> 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
  9. 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?
  10. @m_holtermann • Follows ForeignKey und OneToOneField relations • Use if

    “the other side” of a relation has at most one object select_related()
  11. >>> 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?
  12. >>> 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
  13. 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?
  14. >>> 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?
  15. >>> 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
  16. 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?
  17. @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()
  18. >>> 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”?
  19. >>> 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
  20. 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”?
  21. >>> 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”?
  22. >>> 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
  23. 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”?
  24. >>> 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?
  25. @m_holtermann • Annotates additional information on a model instance. •

    Use for aggregation like “Avg()”, “Count()”, “Max()”, “Min()”, “Sum()” etc. .annotate()
  26. >>> 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?
  27. >>> 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?
  28. >>> 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?
  29. >>> 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
  30. >>> 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?
  31. >>> 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
  32. @m_holtermann • Use for aggregation like “Avg()”, “Count()”, “Max()”, “Min()”,

    “Sum()” etc. • Returns a dict “dict()” with the corresponding values .aggregate()
  33. >>> 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?
  34. >>> 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?
  35. >>> 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?
  36. >>> 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?
  37. >>> 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?
  38. @m_holtermann • .filter() / .get() / .first() / .last() •

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

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

    .count() / .exists() • .select_related() / .prefetch_related() • .annotate() / .aggregate() • .values() / .values_list()
  41. >>> 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()
  42. 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()
  43. >>> 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()
  44. >>> 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()
  45. >>> 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()
  46. >>> 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
  47. 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()
  48. >>> 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()
  49. >>> 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()
  50. >>> 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()
  51. >>> 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
  52. 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()
  53. 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()
  54. >>> 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?
  55. 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?
  56. >>> 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
  57. >>> 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
  58. 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
  59. >>> 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
  60. >>> 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
  61. >>> 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