Get best of the week

Django ORM für Anfänger | Wir optimieren Anfragen



Django ORM (Object Relational Mapping) ist eine der leistungsstärksten Funktionen von Django. Dies ermöglicht es uns, mit der Datenbank unter Verwendung von Python-Code und nicht von SQL zu interagieren.

Zur Demonstration werde ich das folgende Modell beschreiben:

from django.db import models

class Blog(models.Model):
    name = models.CharField(max_length=250)
    url = models.URLField()

    def __str__(self):
        return self.name

class Author(models.Model):
    name = models.CharField(max_length=250)

    def __str__(self):
        return self.name

class Post(models.Model):
    title = models.CharField(max_length=250)
    content = models.TextField()
    published = models.BooleanField(default=True)
    blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
    authors = models.ManyToManyField(Author, related_name="posts")

Ich werde Django-Erweiterungen verwenden , um nützliche Informationen zu erhalten mit:

python manage.py shell_plus --print-sql

Beginnen wir also:

>>> post = Post.objects.all()
>>> post
SELECT "blog_post"."id",
       "blog_post"."title",
       "blog_post"."content",
       "blog_post"."blog_id"
  FROM "blog_post"
 LIMIT 21
Execution time: 0.000172s [Database: default]
<QuerySet [<Post: Post object (1)>]>

1. Verwenden Sie ForeignKey-Werte direkt


>>> Post.objects.first().blog.id
SELECT "blog_post"."id",
       "blog_post"."title",
       "blog_post"."content",
       "blog_post"."blog_id"
  FROM "blog_post"
 ORDER BY "blog_post"."id" ASC
 LIMIT 1
Execution time: 0.000225s [Database: default]
SELECT "blog_blog"."id",
       "blog_blog"."name",
       "blog_blog"."url"
  FROM "blog_blog"
 WHERE "blog_blog"."id" = 1
 LIMIT 21
Execution time: 0.000144s [Database: default]
1

Und so erhalten wir 1 Abfrage in der Datenbank:

>>> Post.objects.first().blog_id
SELECT "blog_post"."id",
       "blog_post"."title",
       "blog_post"."content",
       "blog_post"."blog_id"
  FROM "blog_post"
 ORDER BY "blog_post"."id" ASC
 LIMIT 1
Execution time: 0.000155s [Database: default]
1

2. OneToMany-Beziehungen


Wenn wir die OneToMany-Beziehung verwenden, verwenden wir das ForeignKey-Feld und die Abfrage sieht ungefähr so ​​aus:

>>> post = Post.objects.get(id=1)
SELECT "blog_post"."id",
       "blog_post"."title",
       "blog_post"."content",
       "blog_post"."blog_id"
  FROM "blog_post"
 WHERE "blog_post"."id" = 1
 LIMIT 21
Execution time: 0.000161s [Database: default]

Und wenn wir über das Post-Objekt auf das Blog-Objekt zugreifen möchten, können wir Folgendes tun:

>>> post.blog
SELECT "blog_blog"."id",
       "blog_blog"."name",
       "blog_blog"."url"
  FROM "blog_blog"
 WHERE "blog_blog"."id" = 1
 LIMIT 21
Execution time: 0.000211s [Database: default]
<Blog: Django tutorials>

Dies löste jedoch eine neue Anfrage aus, Informationen aus dem Blog abzurufen. Verwenden Sie also select_related , um dies zu vermeiden. Um es zu verwenden, können wir unsere ursprüngliche Anfrage aktualisieren:

>>> post = Post.objects.select_related("blog").get(id=1)
SELECT "blog_post"."id",
       "blog_post"."title",
       "blog_post"."content",
       "blog_post"."blog_id",
       "blog_blog"."id",
       "blog_blog"."name",
       "blog_blog"."url"
  FROM "blog_post"
 INNER JOIN "blog_blog"
    ON ("blog_post"."blog_id" = "blog_blog"."id")
 WHERE "blog_post"."id" = 1
 LIMIT 21
Execution time: 0.000159s [Database: default]

Bitte beachten Sie, dass Django jetzt JOIN verwendet! Und die Ausführungszeit der Abfrage ist kürzer als zuvor. Außerdem wird jetzt post.blog zwischengespeichert!

>>> post.blog
<Blog: Django tutorials>

select_related funktioniert auch mit QurySets:

>>> posts = Post.objects.select_related("blog").all()
>>> for post in posts:
...     post.blog
...
SELECT "blog_post"."id",
       "blog_post"."title",
       "blog_post"."content",
       "blog_post"."blog_id",
       "blog_blog"."id",
       "blog_blog"."name",
       "blog_blog"."url"
  FROM "blog_post"
 INNER JOIN "blog_blog"
    ON ("blog_post"."blog_id" = "blog_blog"."id")
Execution time: 0.000241s [Database: default]
<Blog: Django tutorials>

3. Viele zu vielen Beziehungen


Um Postautoren zu bekommen, verwenden wir ungefähr Folgendes:

>>> for post in Post.objects.all():
...     post.authors.all()
...
SELECT "blog_post"."id",
       "blog_post"."title",
       "blog_post"."content",
       "blog_post"."blog_id"
  FROM "blog_post"
Execution time: 0.000242s [Database: default]
SELECT "blog_author"."id",
       "blog_author"."name"
  FROM "blog_author"
 INNER JOIN "blog_post_authors"
    ON ("blog_author"."id" = "blog_post_authors"."author_id")
 WHERE "blog_post_authors"."post_id" = 1
 LIMIT 21
Execution time: 0.000125s [Database: default]
<QuerySet [<Author: Dmytro Parfeniuk>, <Author: Will Vincent>, <Author: Guido van Rossum>]>
SELECT "blog_author"."id",
       "blog_author"."name"
  FROM "blog_author"
 INNER JOIN "blog_post_authors"
    ON ("blog_author"."id" = "blog_post_authors"."author_id")
 WHERE "blog_post_authors"."post_id" = 2
 LIMIT 21
Execution time: 0.000109s [Database: default]
<QuerySet [<Author: Dmytro Parfeniuk>, <Author: Will Vincent>]>

Anscheinend haben wir für jedes Post-Objekt eine Anfrage erhalten. Daher müssen wir prefetch_related verwenden . Dies ähnelt select_related , wird jedoch mit ManyToMany-Feldern verwendet:

>>> for post in Post.objects.prefetch_related("authors").all():
...     post.authors.all()
...
SELECT "blog_post"."id",
       "blog_post"."title",
       "blog_post"."content",
       "blog_post"."blog_id"
  FROM "blog_post"
Execution time: 0.000300s [Database: default]
SELECT ("blog_post_authors"."post_id") AS "_prefetch_related_val_post_id",
       "blog_author"."id",
       "blog_author"."name"
  FROM "blog_author"
 INNER JOIN "blog_post_authors"
    ON ("blog_author"."id" = "blog_post_authors"."author_id")
 WHERE "blog_post_authors"."post_id" IN (1, 2)
Execution time: 0.000379s [Database: default]
<QuerySet [<Author: Dmytro Parfeniuk>, <Author: Will Vincent>, <Author: Guido van Rossum>]>
<QuerySet [<Author: Dmytro Parfeniuk>, <Author: Will Vincent>]>

Was ist gerade passiert ??? Wir haben die Anzahl der Abfragen von 2 auf 1 reduziert, um 2 QuerySet-a zu erhalten!

4. Objekt vorab abrufen


prefetch_related ist in den meisten Fällen ausreichend, hilft jedoch nicht immer, zusätzliche Anforderungen zu vermeiden. Wenn wir beispielsweise die Filterung verwenden, kann Django unsere zwischengespeicherten Posts nicht verwenden , da sie nicht gefiltert wurden, als sie in der ersten Anforderung angefordert wurden. Und wir bekommen:

>>> authors = Author.objects.prefetch_related("posts").all()
>>> for author in authors:
...     print(author.posts.filter(published=True))
...
SELECT "blog_author"."id",
       "blog_author"."name"
  FROM "blog_author"
Execution time: 0.000580s [Database: default]
SELECT ("blog_post_authors"."author_id") AS "_prefetch_related_val_author_id",
       "blog_post"."id",
       "blog_post"."title",
       "blog_post"."content",
       "blog_post"."published",
       "blog_post"."blog_id"
  FROM "blog_post"
 INNER JOIN "blog_post_authors"
    ON ("blog_post"."id" = "blog_post_authors"."post_id")
 WHERE "blog_post_authors"."author_id" IN (1, 2, 3)
Execution time: 0.000759s [Database: default]
SELECT "blog_post"."id",
       "blog_post"."title",
       "blog_post"."content",
       "blog_post"."published",
       "blog_post"."blog_id"
  FROM "blog_post"
 INNER JOIN "blog_post_authors"
    ON ("blog_post"."id" = "blog_post_authors"."post_id")
 WHERE ("blog_post_authors"."author_id" = 1 AND "blog_post"."published" = 1)
 LIMIT 21
Execution time: 0.000299s [Database: default]
<QuerySet [<Post: Post object (1)>, <Post: Post object (2)>]>
SELECT "blog_post"."id",
       "blog_post"."title",
       "blog_post"."content",
       "blog_post"."published",
       "blog_post"."blog_id"
  FROM "blog_post"
 INNER JOIN "blog_post_authors"
    ON ("blog_post"."id" = "blog_post_authors"."post_id")
 WHERE ("blog_post_authors"."author_id" = 2 AND "blog_post"."published" = 1)
 LIMIT 21
Execution time: 0.000336s [Database: default]
<QuerySet [<Post: Post object (1)>, <Post: Post object (2)>]>
SELECT "blog_post"."id",
       "blog_post"."title",
       "blog_post"."content",
       "blog_post"."published",
       "blog_post"."blog_id"
  FROM "blog_post"
 INNER JOIN "blog_post_authors"
    ON ("blog_post"."id" = "blog_post_authors"."post_id")
 WHERE ("blog_post_authors"."author_id" = 3 AND "blog_post"."published" = 1)
 LIMIT 21
Execution time: 0.000412s [Database: default]
<QuerySet [<Post: Post object (1)>]>

Das heißt, wir haben prefetch_related verwendet , um die Anzahl der Anforderungen zu reduzieren, aber wir haben sie tatsächlich erhöht. Um dies zu vermeiden, können wir die Anforderung mithilfe des Prefetch- Objekts anpassen :

>>> authors = Author.objects.prefetch_related(
...     Prefetch(
...             "posts",
...             queryset=Post.objects.filter(published=True),
...             to_attr="published_posts",
...     )
... )
>>> for author in authors:
...     print(author.published_posts)
...
SELECT "blog_author"."id",
       "blog_author"."name"
  FROM "blog_author"
Execution time: 0.000183s [Database: default]
SELECT ("blog_post_authors"."author_id") AS "_prefetch_related_val_author_id",
       "blog_post"."id",
       "blog_post"."title",
       "blog_post"."content",
       "blog_post"."published",
       "blog_post"."blog_id"
  FROM "blog_post"
 INNER JOIN "blog_post_authors"
    ON ("blog_post"."id" = "blog_post_authors"."post_id")
 WHERE ("blog_post"."published" = 1 AND "blog_post_authors"."author_id" IN (1, 2, 3))
Execution time: 0.000404s [Database: default]
[<Post: Post object (1)>, <Post: Post object (2)>]
[<Post: Post object (1)>, <Post: Post object (2)>]
[<Post: Post object (1)>]

Wir haben eine bestimmte Anforderung verwendet, um Beiträge über den Anforderungsparameter zu empfangen, und die gefilterten Nachrichten in einem neuen Attribut gespeichert. Wie wir sehen können, haben wir jetzt nur noch 2 Abfragen an die Datenbank.

More articles:


All Articles