Статьи о Джанго

Демистификация сложных запросов для Django ORM

Мотивация

Итак, я работал над созданием своего личного проекта, когда я натолкнулся на препятствие при написании определенного запроса. Я хорошо разбираюсь в SQL как таковом, но я почувствовал себя растерянным, когда попытался преобразовать эту логику в классическое объектно-реляционное сопоставление (ORM) Django. Это заставило меня пройти через несколько болезненных часов, пока один джентльмен наконец не ответил на мой конкретный вопрос о переполнении стека. После этого ответа я решил просмотреть несколько ресурсов и аккуратную документацию Django, чтобы составить небольшое руководство по созданию вашей сложной логики SQL в рамках предоставленной ORM.

Вступление

Эта статья предполагает базовые знания Django и основы SQL. Основные темы, которые я стремлюсь охватить: агрегирование, функции, подзапросы, оконные функции. Я стремлюсь добавить больше концепций, поскольку чувствую личную потребность в их использовании. Эмпирическое правило: любое выражение, которое является более сложным или требует больше времени, чем ваша доступная пропускная способность? Просто напишите необработанный SQL в курсор Django. Это не лучший метод, так как это довольно уязвимые SQL-инъекции. Тем не менее, при соблюдении надлежащих правил и функционального программирования атаки с использованием инъекций также могут быть защищены.

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

Агрегирование

Как и в обычном SQL, агрегирование может выполняться либо для получения сводного результата по всему набору запросов, либо для получения индивидуального отчета для каждого объекта в наборе запросов.

Aggregate()

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

>>> from django.db.models import Count
>>> Question.objects.aggregate(
        avg_followers = Avg(Count('followers')),
        highest_followers = Max(Count('followers'))
        lowest_followers = Min(Count('followers'))
    ){'avg_followers' : 14.23, 'highest_followers' : 1523, 'lowest_followers' : 0}

Обратите внимание, как мне пришлось импортировать функцию Count и заключить в нее поле «followers». Followers - это ManyToManyField, функция Count возвращает количество пользователей, которые подписались на вопрос. На основе этого результата по полному набору запросов находятся соответствующие минимальные, максимальные и средние подписчики.

Annotate()

Как вкратце объяснено выше, аннотация используется для создания индивидуальной сводки для каждого объекта в наборе запросов. Это означает, что результат агрегирования вычисляется для каждой отдельной записи, которая может быть или не совпадать. Если бы я хотел создать счетчик «за» для всех проголосовавших за ответ в модели ответа:

>>> from django.db.models import Count
>>> query = Answers.objects.annotate(upvotes = Count('upvoters'))
>>> query[0]
<Answer : 1>
>>> query[0].upvotes
143
>>> query[1]
<Answer : 2>
>>> query[1].upvotes
23

F() выражения

Выражение F() представляет значение поля/аннотированного столбца модели. Он используется для эффективного обращения к значениям полей модели без фактического извлечения их в память Python. В результате работу выполняет база данных, а не Python.

>>> from django.db.models import F
>>> query = Answer.objects.get(author__username='keshavvinayak01')
>>> query.answer
Yes, Math is in fact related to Science.
>>> query.update(answer = F('answer') + " [Anonymous]")
>>> query.answer
Yes, Math is in fact related to Science. [Anonymous]

Мы можем использовать эти выражения в аннотациях, фильтрах или ссылках на внешний ключ.

>>> from django.db.models import F
# В аннотациях
>>> answer = Answer.objects.annotate(
        interest = Count(F('followers') + Count(F('requested')
    )
# В фильтрах
>>> own_qa = Answer.objects.filter(
        question__asker = F('author')
    )
# Ссылка на внешний ключ
>>> answer = Answer.objects.annotate(
        question_id = F('question')
    )
>>> answer.question
Is Math related to Science?
>>> answer.question_id
3

Func() выражения

Эти выражения включают функции базы данных, такие как LOWER или COALESCE. Вот несколько примеров:

LOWER
>>> from django.db.models import F, Func, Coalesce
>>> quest = Question.objects.annotate(
        lower_question = Func(F('question')),
        function = 'LOWER'
    )
# Все тексты вопросов будут в нижнем регистре.

COALESCE
>>> from datetime import datetime
>>> q = Answer.objects.annotate(
            updated_at = Coalesce(F('updated_at'), datetime.now())
        )

В первом результате запроса TextField вопроса преобразуется в нижний регистр. Это эквивалентно выбору LOWER (question.question) в SQL. Во втором запросе мы просто устанавливаем значение поля updated_at на текущую дату и время, если оно равно нулю, иначе просто получаем его.

Subquery() выражения

Мы можем добавить явный подзапрос в наш набор запросов, используя выражения Subquery(). Например, если мы хотим получить вопрос только с ответом, получившим наибольшее количество голосов, нам нужно будет включить результат подзапроса, выбирающего такой ответ.

>>> from django.db.models import OuterRef, Subquery
>>> answer_subquery = Answer.objects.filter(
        question=OuterRef('pk')).annotate(
            upvotes=Count(F('upvoters')
        )
    ).order_by('-upvotes')# What's happening here?
>>> questions = Questions.objects.annotate(
        answer=Subquery(answer_subquery.values('answer')[-1])
    )

Давайте разберемся с этим до необработанного SQL. Мы хотели получить каждый вопрос вместе с ответами, получившими наибольшее количество голосов. Итак, сначала мы создали подзапрос, который извлекает каждый ответ на заданный вопрос, указанный в OuterRef, а затем сортирует их в порядке убывания в зависимости от количества полученных ими голосов.

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

Для положительных голосов ведется отдельная таблица. Можно легко подсчитать группировку по ответам. Для простоты этот запрос пока можно проигнорировать.

SQL может выглядеть примерно так:

# Отсутствуют некоторые подробности о проголосовавших, так как это поле "многие ко многим".
SELECT "question"."id", (
    SELECT "answer"."answer",COUNT("answer.upvoters") as "upvotes" 
    FROM "answer"
    WHERE "answer"."question" = "question"."id"
    GROUP BY 1
    ORDER BY COUNT("answer.upvoters") DESC 
    LIMIT 1
) as "answer" from "question"

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

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

from django.db import connection

def my_custom_sql(self):
    with connection.cursor() as cursor:
        cursor.execute("""
            UPDATE x 
            SET a = 1 
            WHERE bar = %s
        """,[self.a])
        cursor.execute("""
            SELECT foo 
            FROM x 
            WHERE bar = %s
        """, [self.a])
        
        row = cursor.fetchone()

    return row

Объект django.db.connection представляет соединение с базой данных по умолчанию. Чтобы использовать соединение с базой данных, вызовите connection.cursor(), чтобы получить объект курсора. Затем вызовите cursor.execute (SQL, <params>) для выполнения SQL и cursor.fetchone() или cursor.fetchall() для возврата результирующих строк.

Заключение

В этом кратком руководстве мы рассмотрели некоторые основы, которые являются ключевыми для выполнения сложных взломов SQL, а также, наконец, обсудили, как мы можем выполнять пользовательский SQL. Я настоятельно рекомендую просмотреть официальную документацию Django, чтобы получить больше информации о других доступных функциях и приемах, которые предлагает Django ORM, если эта статья не может что-то объяснить. Надеюсь, это помогло, пожалуйста, оставляйте отзывы в случае каких-либо сомнений или отзывов. Спасибо за прочтение!

Поделитесь с другими: