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