Tweet
Tweet

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

@m_holtermann Our Database

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

@m_holtermann Filter for objects

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

@m_holtermann Counting objects

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

@m_holtermann Count filtered objects

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

@m_holtermann Check for an object’s existence

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

@m_holtermann Related objects

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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?

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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?

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

@m_holtermann “reverse” related objects

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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?

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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?

Slide 31

Slide 31 text

@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()

Slide 32

Slide 32 text

@m_holtermann Counting. Again.

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

>>> from django.db.models.aggregates import Avg >>> from django.db.models.functions import Length >>> ... ... What’s the average length of all book title an author wrote?

Slide 50

Slide 50 text

>>> from django.db.models.aggregates import Avg >>> from django.db.models.functions import Length >>> Author.objects. ... Length('books__title') ... What’s the average length of all book title an author wrote?

Slide 51

Slide 51 text

>>> from django.db.models.aggregates import Avg >>> from django.db.models.functions import Length >>> Author.objects. ... avg=Avg(Length('books__title')) ... What’s the average length of all book title an author wrote?

Slide 52

Slide 52 text

>>> from django.db.models.aggregates import Avg >>> from django.db.models.functions import Length >>> Author.objects.annotate( ... avg=Avg(Length('books__title')) ... ) What’s the average length of all book title an author wrote?

Slide 53

Slide 53 text

>>> 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') What’s the average length of all book title an author wrote?

Slide 54

Slide 54 text

@m_holtermann Summary

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

Q&A?

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

>>> 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() Wrong

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

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

Slide 71

Slide 71 text

>>> 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() Correct

Slide 72

Slide 72 text

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

Slide 73

Slide 73 text

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

Slide 74

Slide 74 text

Q&A?

Slide 75

Slide 75 text

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

Slide 76

Slide 76 text

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?

Slide 77

Slide 77 text

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

Slide 78

Slide 78 text

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

Slide 79

Slide 79 text

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

Slide 80

Slide 80 text

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

Slide 81

Slide 81 text

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

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

Q&A?

Slide 84

Slide 84 text

Thanks Markus Holtermann @m_holtermann markusholtermann.eu