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

    View full-size slide

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

    View full-size slide

  3. @m_holtermann
    Our Database

    View full-size 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 full-size slide

  5. @m_holtermann
    Filter for objects

    View full-size slide

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

    filter() and get()

    View full-size 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 full-size slide

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

    __startswith

    View full-size slide

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

    View full-size slide

  10. @m_holtermann
    Counting objects

    View full-size slide

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

    View full-size slide

  12. @m_holtermann
    Count filtered objects

    View full-size slide

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

    View full-size slide

  14. @m_holtermann
    Check for an object’s existence

    View full-size slide

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

    View full-size slide

  16. @m_holtermann
    Related objects

    View full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size slide

  24. @m_holtermann
    “reverse” related objects

    View full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size slide

  32. @m_holtermann
    Counting. Again.

    View full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size slide

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

    View full-size slide

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

    View full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size slide

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

    View full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size slide

  54. @m_holtermann
    Summary

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  60. >>> 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 full-size slide

  61. 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 full-size slide

  62. >>> 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 full-size 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') \
    ... .filter(books__title__icontains='die')
    The order of filter() and annotate()

    View full-size 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')
    ('Heinrich von Kleist', 140), ('Heinrich Harrer', 60)]>
    The order of filter() and annotate()

    View full-size 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()
    Wrong

    View full-size slide

  66. 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 full-size slide

  67. >>> 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 full-size slide

  68. >>> 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 full-size 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')
    ('Heinrich von Kleist', 7), ('Heinrich Harrer', 3)]>
    The order of filter() and annotate()

    View full-size 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()
    Correct

    View full-size slide

  71. 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 full-size slide

  72. 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 full-size slide

  73. >>> 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 full-size slide

  74. 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 full-size slide

  75. >>> 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 full-size slide

  76. >>> 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 full-size slide

  77. 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 full-size slide

  78. >>> 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 full-size slide

  79. >>> 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 full-size slide

  80. >>> 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 full-size slide

  81. Thanks
    Markus Holtermann
    @m_holtermann
    markusholtermann.eu

    View full-size slide