SQL в DjangoORM22.04.2021 11:32

Меня зовут Алексей Казаков, я техлид команды «Клиентские коммуникации» в ДомКлик. В большинстве приложений, с которыми мне приходилось иметь дело, при взаимодействии с БД не ограничиваются лишь драйвером, который позволяет выполнять сырые запросы. Для удобства и избавления от SQL-запросов внутри, например, Python-кода дополнительно используют библиотеки (Object Relational Mapper, ORM).

4941989a5e0f3eeeea01b8a41c43997c.png

Это первая статья в серии, посвященной различным ORM. Начнём мы с DjangoORM:

  • посмотрим на схему данных (сырой SQL);

  • опишем эту схему с помощью Django-моделей;

  • познакомимся с несколькими хитростями для удобной отладки;

  • и изучим примеры запросов.

Во время знакомства с запросами мы уделим пристальное внимание сырому запросу, который генерируют библиотеки, ведь именно конечный SQL-код определяет производительность запросов и их количество.

В прошлой статье я рассказывал про хитрые вопросы о Python, которые могут встретиться вам на собеседовании. Здесь же будет гораздо более практичный материал, необходимый в повседневной разработке. Хотя это не отменяет его актуальности и в техническом интервью.

Следующие статьи планирую написать по SQLAlchemy и go-pg.

Схема

Начнём со схемы данных, с которой нам предстоит работать.

Пусть у нас есть приложение, в котором пользователи (User) могут задавать вопросы (Question) внутри разных тем (Topic). У темы есть картинка (Image). Доступ пользователя к теме определяется через many-to-many отношение TopicUser, которое кроме ForeignKey (FK) на пользователя и FK на тему содержит дополнительную информацию: роль пользователя в теме.

Схема данных.Схема данных.

Создать базу данных можно таким SQL-ем: 

CREATE TABLE "image"
(
    "id"   serial NOT NULL PRIMARY KEY,
    "name" text   NOT NULL
);

CREATE TABLE "topic"
(
    "id"       serial  NOT NULL PRIMARY KEY,
    "title"    text    NOT NULL,
    "image_id" integer REFERENCES "image" ("id") NOT NULL
);

CREATE TABLE "user"
(
    "id"   serial NOT NULL PRIMARY KEY,
    "name" text   NOT NULL
);

CREATE TABLE "topic_user"
(
    "id"       serial  NOT NULL PRIMARY KEY,
    "role"     text    NOT NULL,
    "topic_id" integer REFERENCES "topic" ("id") NOT NULL,
    "user_id"  integer REFERENCES "user" ("id") NOT NULL
);

CREATE TABLE "question"
(
    "id"       serial  NOT NULL PRIMARY KEY,
    "text"     text    NOT NULL,
    "topic_id" integer REFERENCES "topic" ("id") NOT NULL 
);

Django models  

Теперь опишем эту схему внутри ORM: 

from django.db import models


class Image(models.Model):
    class Meta:
        db_table = 'image'

    name = models.TextField()


class Topic(models.Model):
    class Meta:
        db_table = 'topic'

    title = models.TextField()
    image = models.ForeignKey(Image, on_delete=models.DO_NOTHING)
    users = models.ManyToManyField('User', through='TopicUser')


class Question(models.Model):
    class Meta:
        db_table = 'question'

    text = models.TextField()
    topic = models.ForeignKey(Topic, on_delete=models.DO_NOTHING, related_name='questions')


class TopicUser(models.Model):
    class Meta:
        db_table = 'topic_user'

    topic = models.ForeignKey(Topic, on_delete=models.DO_NOTHING)
    user = models.ForeignKey('User', on_delete=models.DO_NOTHING)
    role = models.TextField(max_length=64)


class User(models.Model):
    class Meta:
        db_table = 'user'

    name = models.TextField()

Все модели наследуются от models.Model. В Meta.db_table советую явно указывать названия таблиц, иначе Django сгенерирует их для вас самостоятельно, а такие имена не всегда то, что вам нужно. models.ForeignKey породит колонку с названием _id. И к полю с таким именем всегда можно обратиться, хоть мы его явно и не указываем при описании модели.

В Django есть встроенный мощный инструмент миграций. В большинстве случаев он делает именно то, что вы от него ожидаете. Но лучше всегда проверять SQL-запрос, который будет выполнен миграцией.

// генерируем миграцию
python manage.py makemigrations  // появится миграция с номером 0001

// смотрим, какой SQL исполнится в базе при выполнении миграции
python manage.py sqlmigrate topics 0001

Подробно на этом останавливаться не буду. Лишь ещё раз посоветую всегда проверять SQL миграций.

Подготовка

Кроме моделей, олицетворяющих таблицы в коде нашего приложения, нам понадобится дополнительная настройка библиотек, чтобы мы могли видеть в логе все SQL-запросы, которые будут отправляться в БД на исполнение. Смотреть на SQL-код, получаемый из ORM-запросов, крайне полезно, потому что далеко не всегда без должного опыта сразу получается добиться от библиотеки именно того поведения, которое мы ожидаем.

Удобно тестировать наши запросы внутри тестов (не приходится слать запросы в приложение и удобно пользоваться встроенным в IDE-отладчиком). Чтобы Django выводила SQL-запросы, необходимо провести ряд манипуляций.

Во-первых, добавить логгер в settings.LOGGING: 

        'django.db.backends': {
            'level': 'DEBUG',
            'handlers': ['console'],
            'propagate': False,
        },

Также задать обработчику console уровень отладки DEBUG.

Во-вторых, нужно задекорировать тест: 

from django.test import override_settings

@override_settings(DEBUG=True)
def test_i(self):

В последующих примерах приводимый код будет как раз блоком . Эти настройки я частенько применяю, когда запускаю проект локально при разработке, чтобы посмотреть, оптимально ли написаны запросы.

Мы готовы приступать к написанию запросов.

Пример 1

Хотим получить десять последних вопросов из определенной темы: 

SELECT *
FROM question
WHERE topic_id = 1
ORDER BY id DESC
LIMIT 10;
questions = Question.objects.filter(topic_id=self.t1_id).order_by('-id')[:10]

В консоли увидим SQL, который сами написали выше, с некоторыми модификациями, внесёнными ORM. Обратите внимание, как непривычно (по крайней мере для меня) выглядит limit в этой ORM.

Пример 2

Теперь мы хотим найти в первой теме такие вопросы, в тексте которых либо содержится подстрока fart, либо не содержится упоминания dog, вне зависимости от их регистра.

Мы легко изобразим это на SQL: 

SELECT *
FROM question
WHERE (
  topic_id = 1 AND (
      text ILIKE '%fart%' OR
      NOT (text ILIKE '%dog%')
  )
);

Django предоставляет для этих целей всевозможные field lookups, одним из которых мы сейчас и воспользуемся: 

questions = Question.objects.filter(
    Q(topic_id=1),
    Q(text__icontains='fart') | ~Q(text__icontains='dog')
)

Но если больше ничего не написать в тесте, то в консоли вы не увидите SQL-представления нашего запроса, хотя туда должны выводиться все запросы, отправленные в БД. Дело в том, что queryset-ы в Django ленивые. То есть пока приложению не нужны реальные данные из базы, запрос не исполнится. А исполняется он, например, при итерации, взятии длины или слайса (как в предыдущем примере). Подробнее можете ознакомиться в документации.

Также хочу отметить, что при использовании отладчика (например, встроенного в PyCharm) результат может отличаться, потому что отладчик может провоцировать более раннюю отправку запроса в базу. За счет этого он может показать нам содержимое объекта. Не дайте ввести себя в заблуждение!

Итак, добавим к примеру что-нибудь, что вызовет исполнение запроса, и увидим в консоли: 

SELECT "question"."id", "question"."text", "question"."topic_id"
FROM "question"
WHERE (
    "question"."topic_id" = 1 AND
    (
        UPPER("question"."text"::text) LIKE UPPER('%fart%') OR
        NOT (UPPER("question"."text"::text) LIKE UPPER('%dog%'))
    )
);

Хоть в описании icontains и говорится, что его использование в коде приведет к использованию ILIKE, при написании этой статьи я из раза в раз получал вариант с UPPER. Это один из многочисленных аргументов в пользу того, чтобы проверять запросы, которые генерирует ORM. В Python-коде заслуживает упоминание отрицание условия через ~ и компоновка условий через |.

Пример 3

Третий пример подводит нас к теме оптимизации запросов. Допустим, мы хотим получить данные не только самого вопроса, но и связанной с ним темы. В Django для этого не нужно прикладывать никаких усилий! 

q = Question.objects.filter(id=self.q1_id).first()  # dj.3.1
print(q.topic.title)  # dj.3.2

Но если посмотреть в лог, то увидим дополнительный запрос к базе за темой. То есть и dj.3.1, и dj.3.2 порождают запросы к базе. Часто мы заранее знаем, что нам понадобится связанная сущность. В этом случае можно попросить ORM при первом запросе «подтянуть» тему с помощью JOIN-а. 

q = Question.objects.select_related('topic').filter(id=self.q1_id).first()  # dj.3.3
print(q.topic.title)  # dj.3.4

Теперь к базе уходит только один запрос. Он содержит JOIN, за счет которого ORM и получает данные темы.

SELECT "question"."id",
       "question"."text",
       "question"."topic_id",
       "topic"."id",
       "topic"."title",
       "topic"."image_id"
FROM "question"
         INNER JOIN "topic" ON ("question"."topic_id" = "topic"."id")
WHERE "question"."id" = 1
ORDER BY "question"."id" ASC
LIMIT 1;

Особенно заметный выигрыш в производительности мы получим, когда вопросы будем обрабатывать в цикле. Без select_related мы бы получили N+1 запросов, где N — кол-во вопросов вместо одного.

Пример 4

Этот пример углубит наши знания методики нетерпеливой подгрузки. Пусть нам нужна не только связанная с вопросом тема, но еще и изображение этой темы. Как мы видели, Django позволяет нам получать связанные сущности без дополнительных действий: 

for q in Question.objects.all():
    print(q.topic.title)  # dj.4.1
    print(q.topic.image.name)  # dj.4.2

Но какой ценой? Этот код вызывает по два дополнительных запроса на каждом шаге цикла: dj.4.1 — за темой, dj.4.2 — за изображением темы. Чтобы этого избежать, снова используем select_related: 

for q in Question.objects.select_related('topic__image').all():  # dj.4.3
    print(q.topic.title)
    print(q.topic.image.name)

Так мы «подгрузим» и топик, и соответствующее ему изображение. Мы получили более сложный запрос с двумя JOIN-ами, но только один поход в базу вместо (1 + 2*N).

SELECT "question"."id",
       "question"."text",
       "question"."topic_id",
       "topic"."id",
       "topic"."title",
       "topic"."image_id",
       "image"."id",
       "image"."name"
FROM "question"
         INNER JOIN "topic" ON ("question"."topic_id" = "topic"."id")
         INNER JOIN "image" ON ("topic"."image_id" = "image"."id");

Здесь стоит внимательно приглядеться к JOIN-ам. В обоих соединениях таблиц используется INNER JOIN. Это следствие того, что FK объявлены как не nullable. Если при описании темы с изображением явно указать возможность отсутствия картинки: 

image = models.ForeignKey(Image, on_delete=models.DO_NOTHING, null=True)

то запрос, генерируемый строчкой dj.4.3, заменит второй INNER на OUTER. Это нужно для того, чтобы оставить в выборке темы (а следовательно и вопросы), к которым не привязана картинка.

Пример 5

Пусть теперь мы хотим обработать все вопросы у каждой темы. Django и тут помогает нам своей магией, и мы можем совершенно бесхитростно пройтись по массиву вопросов.

for t in Topic.objects.filter(id__in=(self.t1_id, self.t2_id)):  # dj.5.1
    print([q.text for q in t.questions.all()])  # dj.5.2

Обратите внимание, что в dj.5.2 мы обращаемся к questions, заданному как related_name при описании поля topic модели Question. По умолчанию такие наборы называются FOO_set, где FOO — имя таблицы, определяющей ForeignKey. В логе видно, что dj.5.2 порождает дополнительный запрос к базе за всеми вопросами темы. И это происходит для каждой темы. Этого можно избежать: 

for t in Topic.objects.filter(id__in=(self.t1_id, self.t2_id)).prefetch_related('questions'):  # dj.5.3
    print([q.text for q in t.questions.all()])  # dj.5.4

При таком использовании prefetch_related строчка dj.5.3 породит сразу два запроса к базе. Первый — за нужными темами: 

SELECT "topic"."id", "topic"."title", "topic"."image_id"
FROM "topic"
WHERE "topic"."id" IN (1, 2);

Второй — за их вопросами: 

SELECT "question"."id", "question"."text", "question"."topic_id"
FROM "question"
WHERE "question"."topic_id" IN (1, 2);

За счет второго запроса при итерировании по списку (строка dj.5.4) у нас не будет дополнительных запросов к базе. Выигрыш равен 2 вместо (1 + N). Для ясности: во втором запросе идентификаторы для условия IN взяты из первого запроса. То есть второй запрос всегда будет использовать идентификаторы из первого запроса.

Пример 6

Пусть теперь нам нужно подгрузить к теме не все вопросы, а только те, которые отвечают определенному условию. При этом у некоторых тем может оказаться пустой список вопросов. В Django prefetch_related может принимать не только строку, но и специальный объект, который описывает, какие сущности нужно подгружать: 

topics_with_filtered_questions = Topic.objects.prefetch_related(Prefetch(
    'questions',
    queryset=Question.objects.filter(text__icontains='fart'),
    to_attr='filtered_questions'
)).all()
for t in topics_with_filtered_questions:
    print(f'{t.title}')
    for q in t.filtered_questions:
        print(f' --- {q.text}')

Здесь мы поместили фильтрованный список в новый отдельный атрибут: 

SELECT "topic"."id", "topic"."title", "topic"."image_id"
FROM "topic";

SELECT "question"."id", "question"."text", "question"."topic_id"
FROM "question"
WHERE (UPPER("question"."text"::text) LIKE UPPER('%fart%')
	AND "question"."topic_id" IN (1, 2));

Пример 7

В этом примере познакомимся с группировкой и подзапросами. Для этого найдём темы, у которых больше десяти вопросов. В Django нет привычных методов group и having, определенных в queryset-е. Вместо этого используются аннотации и условия в них. Чтобы найти идентификаторы тем, у которых больше 10 вопросов, нужно составить такой запрос: 

topic_ids = Question.objects.values('topic_id').annotate(
  qs_count=Count('id'),
).filter(qs_count__gt=10)

  • values — поля, по которым группируем;

  • annotate — агрегатные функции;

  • filter — HAVING по сформированному агрегатной функцией полю.

Теперь используем этот подзапрос в основном запросе: 

topics = Topic.objects.filter(id__in=topic_ids.values('topic_id'))
for t in topics:
    print(f'{t.title}')

Обратите внимание, что в основном запросе нам повторно пришлось использовать values, чтобы явно указать, что подзапрос должен оставить только одну колонку, исключив qs_count. К базе уйдёт лишь один запрос: 

SELECT "topic"."id", "topic"."title", "topic"."image_id"
FROM "topic"
WHERE "topic"."id" IN (
    SELECT U0."topic_id"
    FROM "question" U0
    GROUP BY U0."topic_id"
    HAVING COUNT(U0."id") > 10
);

Внимание: в этом примере при использовании отладчика вы увидите два запроса.

Пример 8

Обзор common-table-expression получится неполным, потому что в явном виде Django-ORM не поддерживает cte. Но я оставляю этот пустой пример для соблюдения сквозной нумерации с будущими статьями.

Пример 9

Здесь рассмотрим запросы к many-to-many отношениям. Хотим для каждой темы получить список пользователей, которые имеют к ней доступ. При наивном подходе для каждой темы будет дополнительный запрос к базе за списком пользователей: 

for t in Topic.objects.all():  # dj.9.1
    names = ', '.join(u.name for u in t.users.all())  # dj.9.2
    print(f'Topic[{t.title}]: {names}')
SELECT "topic"."id", "topic"."title", "topic"."image_id" FROM "topic";  -- dj.9.1

SELECT "user"."id", "user"."name"
FROM "user" INNER JOIN "topic_user" ON ("user"."id" = "topic_user"."user_id")
WHERE "topic_user"."topic_id" = 1; -- dj.9.2

SELECT "user"."id", "user"."name"
FROM "user" INNER JOIN "topic_user" ON ("user"."id" = "topic_user"."user_id")
WHERE "topic_user"."topic_id" = 2; -- dj.9.2

Если же указать prefetch_related, то пользователи для всех тем подтянутся одним запросом. Всего будет два запроса (первый — для тем) вместо (1 + N).

for t in Topic.objects.prefetch_related('users'):  # dj.9.3
    names = ', '.join(u.name for u in t.users.all())
    print(f'Topic[{t.title}]: {names}')

В этом случае оба запроса вызваны строчкой dj.9.3: 

SELECT "topic"."id", "topic"."title", "topic"."image_id"
FROM "topic";

SELECT ("topic_user"."topic_id") AS "_prefetch_related_val_topic_id", "user"."id", "user"."name"
FROM "user" INNER JOIN "topic_user" ON ("user"."id" = "topic_user"."user_id")
WHERE "topic_user"."topic_id" IN (1, 2);

Если нужна информация из промежуточной таблицы, то можно обратиться к автоматически созданному для нас полю topicuser_set, в котором хранится информация из этой связующей таблицы.

u = User.objects.get(id=self.u1_id)  # dj.9.4
for topic_info in u.topicuser_set.all():  # dj.9.5
    print(f'{u.name} is {topic_info.role} in topic "{topic_info.topic.title}"')  # dj.9.6
SELECT "user"."id", "user"."name"
FROM "user"
WHERE "user"."id" = 1;  -- dj.9.4

SELECT "topic_user"."id", "topic_user"."topic_id", "topic_user"."user_id", "topic_user"."role"
FROM "topic_user"
WHERE "topic_user"."user_id" = 1;  -- dj.9.5

SELECT "topic"."id", "topic"."title", "topic"."image_id"
FROM "topic"
WHERE "topic"."id" = 1;  -- dj.9.6

SELECT "topic"."id", "topic"."title", "topic"."image_id"
FROM "topic"
WHERE "topic"."id" = 2;  -- dj.9.6

Но, как всегда, нужно быть внимательным и заранее подгружать все необходимые данные. В данном случае выигрыш заключается в том, что в рамках одного запроса подтянутся нужные темы для каждой записи topic_user, а не по запросу на каждую тему, как в первом варианте. В остальном варианты идентичны: в обоих из промежуточной таблицы запрашивается пользователь и список.

u = User.objects.prefetch_related('topicuser_set', 'topicuser_set__topic').get(id=self.u1_id)
for topic_info in u.topicuser_set.all():
    print(f'{u.name} is {topic_info.role} in topic "{topic_info.topic.title}"')
SELECT "user"."id", "user"."name"
FROM "user"
WHERE "user"."id" = 1;

SELECT "topic_user"."id", "topic_user"."topic_id", "topic_user"."user_id", "topic_user"."role"
FROM "topic_user"
WHERE "topic_user"."user_id" IN (1);

SELECT "topic"."id", "topic"."title", "topic"."image_id"
FROM "topic"
WHERE "topic"."id" IN (1, 2);

Пример 10

Приступим к другой стороне оптимизации. При вставке большого количества новых записей для улучшения производительности рекомендуется пользоваться bulk-операциями. Они позволяют за один запрос вставить много строк.

Topic.objects.bulk_create([
    Topic(title=f'topic {i}', image_id=self.i1_id)
    for i in range(10)
], batch_size=2)

Если данных действительно много, не забудьте указать параметр batch_size. Но учитывайте, что даже при использовании генератора его результат целиком будет загружен в память. Вот так это выглядит в сыром виде: 

INSERT INTO "topic" ("title", "image_id") VALUES 
  ('topic 0', 1),
  ('topic 1', 1)
RETURNING "topic"."id";

Обратите внимание, что в RETURNING попадут все данные, сгенерированные самой базой.

Пример 11

Другая сторона bulk-операций — это массовое обновление: 

User.objects.bulk_update(
    [User(id=1, name='new dj 1 name'), User(id=2, name='new dj 2 name')],
    ['name'],
)

Вторым аргументом здесь указываются поля, которые будут обновлены. Давайте посмотрим на сгенерированный запрос: 

UPDATE "user"
SET "name" = (
    CASE
        WHEN ("user"."id" = 1) THEN 'new dj 1 name'
        WHEN ("user"."id" = 2) THEN 'new dj 2 name'
        ELSE NULL END
    )::text
WHERE "user"."id" IN (1, 2);

Выглядит непривычно и, скорее всего, не является оптимальным, потому что для каждой строки придётся вычислять CASE. Обычно мы используем UPDATE ... SET ... FROM .... Но даже такая форма сэкономит вам много времени. Чем вызвано именно такое поведение, честно говоря, не знаю. Возможно, это связано с тем, что DjangoORM позиционирует себя независимой от используемой БД. Если кто-то копал в этом направлении глубже, буду рад услышать объяснение.

Заключение

Этими запросами возможности DjangoORM, конечно, не исчерпываются, но показанные примеры будут неплохим стартовым багажом знаний и позволят вам с большей уверенностью двигаться на пути освоения этой библиотеки.

И самое главное: не ограничивайтесь «магией», предоставляемой библиотеками. Понимать суть их работы очень полезно, потому что даже при правильном результате вы можете упускать неоптимальность потребления ресурсов.

До встречи в следующей статье по SQLAlchemy.

SQL в SQLAlchemy (TBD)

SQL в go-pg (TBD)

© Habrahabr.ru