Django ORMの裏側ではSQLが動いている

Django ORMを使うと、SQLを直接書かずにPythonでデータベースを操作できます。ただし、データベースがPythonのコードをそのまま実行しているわけではありません。DjangoがQuerySetをSQLへ変換し、そのSQLをデータベースへ送っています。

この記事では、勤怠管理システムの「従業員」と「勤怠記録」を題材に、同じ処理のDjango ORMとSQLを並べて比較します。

最初は全件取得だけです。そこから条件、並び替え、JOIN、集計と少しずつ機能を足していきます。filter()がWHERE、order_by()がORDER BYになる、といった対応関係に注目してください。

掲載するSQLは、SQLiteを使用した場合の形を読みやすく整えた代表例です。Djangoやデータベースのバージョンによって、引用符、テーブルの別名、プレースホルダーなどは変わります。

使用するモデル

1人の従業員が複数の勤怠記録を持つ、1対多の関係を作ります。添付記事と同じく、アプリ名はmainです。

main/models.py
from django.db import models




class Employee(models.Model):
    name = models.CharField('氏名', max_length=100)
    employee_number = models.CharField('社員番号', max_length=20, unique=True)
    age = models.IntegerField('年齢')


    def __str__(self):
        return self.name


    class Meta:
        db_table = 'employee'




class Attendance(models.Model):
    employee = models.ForeignKey(
        Employee,
        on_delete=models.CASCADE,
        verbose_name='従業員',
    )
    note = models.TextField('備考', blank=True)
    work_minutes = models.IntegerField('勤務時間（分）', default=0)
    clock_in = models.DateTimeField('出勤日時')
    clock_out = models.DateTimeField('退勤日時', null=True, blank=True)


    def __str__(self):
        return f'{self.employee.name}の勤怠'


    class Meta:
        db_table = 'attendance'

デフォルトではテーブル名がmain_employeeとmain_attendanceになりますが、今回はMeta.db_tableでemployeeとattendanceを指定します。Attendance.employeeは、データベースではemployee_id列になります。

QuerySetからSQLを確認する

SELECT文を組み立てるQuerySetは、query属性を文字列にするとSQLを確認できます。

queryset = Employee.objects.filter(age__gte=20)
print(queryset.query)

filter()でQuerySetを作った段階では、通常SQLはまだ実行されません。ループで結果を使う、list()へ変換するなど、データが必要になったときに実行されます。この性質を遅延評価と呼びます。

なお、queryの文字列表現はデバッグ用です。値を安全に渡す仕組みまで再現した、実行SQLそのものではありません。

1. 従業員をすべて取得する

まずは条件を付けず、従業員をすべて取得します。

Django ORM

Employee.objects.all()

変換後のSQL

SELECT
    "employee"."id",
    "employee"."name",
    "employee"."employee_number",
    "employee"."age"
FROM "employee";

all()は、テーブルの全行を取得するSELECT文に対応します。モデルに定義した各フィールドと、Djangoが追加した主キーのidがSELECT対象になります。

2. 年齢を条件に絞り込む

20歳以上の従業員だけを取得します。

Django ORM

Employee.objects.filter(age__gte=20)

変換後のSQL

SELECT
    "employee"."id",
    "employee"."name",
    "employee"."employee_number",
    "employee"."age"
FROM "employee"
WHERE "employee"."age" >= 20;

filter()へ渡した条件がSQLのWHEREへ変換されました。age__gte=20の__gteは「以上」を表すフィールドルックアップです。

ルックアップ 意味 SQLでの表現例
__gt より大きい >
__gte 以上 >=
__lt より小さい <
__lte 以下 <=
__in 一覧のいずれかに一致 IN (...)

3. 複数の条件をANDでつなぐ

20歳以上、かつ社員番号がEから始まる従業員を取得します。

Django ORM

Employee.objects.filter(
    age__gte=20,
    employee_number__startswith='E',
)

変換後のSQL

SELECT ...
FROM "employee"
WHERE (
    "employee"."age" >= 20
    AND "employee"."employee_number" LIKE 'E%' ESCAPE '\'
);

filter()へ複数のキーワード引数を渡すと、条件はANDで結ばれます。__startswithは前方一致で、SQLiteではLIKE 'E%'に相当します。

4. 並び替えて先頭の5件を取得する

勤務時間が長い順に並べ、上位5件を取得します。

Django ORM

Attendance.objects.order_by('-work_minutes')[:5]

変換後のSQL

SELECT ...
FROM "attendance"
ORDER BY "attendance"."work_minutes" DESC
LIMIT 5;

order_by('-work_minutes')の先頭にあるマイナスは降順を表し、SQLではDESCになります。QuerySetのスライス[:5]はLIMIT 5へ変換されます。

昇順ならフィールド名の前にマイナスを付けません。

Attendance.objects.order_by('clock_in')
SELECT ...
FROM "attendance"
ORDER BY "attendance"."clock_in" ASC;

5. QオブジェクトでOR条件を書く

年齢が20歳未満、または60歳以上の従業員を取得します。filter()のキーワード引数だけではANDになるため、OR条件にはQオブジェクトを使います。

Django ORM

from django.db.models import Q


Employee.objects.filter(
    Q(age__lt=20) | Q(age__gte=60)
)

変換後のSQL

SELECT ...
FROM "employee"
WHERE (
    "employee"."age" < 20
    OR "employee"."age" >= 60
);

|はOR、&はAND、~はNOTに対応します。複数の条件を組み合わせるときは、Q(...)を括弧で囲むと意図が明確になります。

6. values()で必要な列だけ取得する

従業員一覧に氏名と社員番号しか表示しないなら、values()で取得する列を限定できます。

Django ORM

Employee.objects.values('name', 'employee_number')

変換後のSQL

SELECT
    "employee"."name" AS "name",
    "employee"."employee_number" AS "employee_number"
FROM "employee";

結果はモデルのインスタンスではなく、次のような辞書になります。

{
    'name': '佐藤',
    'employee_number': 'E001',
}

使わない列を取得しないため、データベースからアプリケーションへ転送するデータ量を減らせます。ただし、モデルのメソッドを使いたい場合はvalues()ではなく、通常のQuerySetを使います。

7. ForeignKeyをたどってJOINする

次は、従業員名が「佐藤」の勤怠記録を取得します。employee__nameのように二重アンダースコアで関連先のフィールドを指定すると、SQLではJOINが使われます。

Django ORM

Attendance.objects.filter(employee__name='佐藤')

変換後のSQL

SELECT "attendance".*
FROM "attendance"
INNER JOIN "employee"
    ON (
        "attendance"."employee_id"
        = "employee"."id"
    )
WHERE "employee"."name" = '佐藤';

attendance.employee_idとemployee.idを結び、関連する従業員のname列で絞り込んでいます。

8. select_related()で従業員も同時に取得する

勤怠一覧で、各勤怠記録に従業員名も表示するとします。通常のQuerySetをループしながらattendance.employee.nameへアクセスすると、勤怠記録を取得するSQLとは別に、従業員を取得するSQLが繰り返し発行されることがあります。これがN+1問題です。

外部キーの関連先を同じSQLで取得するにはselect_related()を使います。

Django ORM

Attendance.objects.select_related('employee').order_by('clock_in')

変換後のSQL

SELECT
    "attendance".*,
    "employee"."id",
    "employee"."name",
    "employee"."employee_number",
    "employee"."age"
FROM "attendance"
INNER JOIN "employee"
    ON (
        "attendance"."employee_id"
        = "employee"."id"
    )
ORDER BY "attendance"."clock_in" ASC;

先ほどの絞り込みでもJOINが使われましたが、目的が異なります。filter(employee__name=...)のJOINは関連先を検索条件に使うためです。select_related('employee')のJOINは関連先の列もSELECTし、取得後の追加問い合わせを避けるためです。

9. annotate()で従業員ごとの勤怠件数を数える

各従業員に勤怠記録が何件あるかを追加します。Attendanceにrelated_nameを設定していないため、逆方向の検索名にはモデル名の小文字であるattendanceを使います。

Django ORM

from django.db.models import Count


Employee.objects.annotate(
    attendance_count=Count('attendance')
).values('name', 'attendance_count')

変換後のSQL

SELECT
    "employee"."name" AS "name",
    COUNT("attendance"."id") AS "attendance_count"
FROM "employee"
LEFT OUTER JOIN "attendance"
    ON (
        "employee"."id"
        = "attendance"."employee_id"
    )
GROUP BY
    "employee"."id",
    "employee"."name",
    "employee"."employee_number",
    "employee"."age";

LEFT OUTER JOINなので、勤怠記録が0件の従業員も結果に残り、attendance_countは0になります。

Pythonからは、辞書に追加されたattendance_countを参照できます。

for employee in Employee.objects.annotate(
    attendance_count=Count('attendance')
).values('name', 'attendance_count'):
    print(employee['name'], employee['attendance_count'])

10. Sum()で勤務時間の合計を求める

さらに、従業員ごとの合計勤務時間を求めます。

Django ORM

from django.db.models import Sum


Employee.objects.annotate(
    total_work_minutes=Sum('attendance__work_minutes')
).values('name', 'total_work_minutes')

変換後のSQL

SELECT
    "employee"."name" AS "name",
    SUM("attendance"."work_minutes") AS "total_work_minutes"
FROM "employee"
LEFT OUTER JOIN "attendance"
    ON (
        "employee"."id"
        = "attendance"."employee_id"
    )
GROUP BY
    "employee"."id",
    "employee"."name",
    "employee"."employee_number",
    "employee"."age";

Count()は件数を数え、Sum()は値を合計します。勤怠記録がない従業員の合計はNoneになります。0として扱いたい場合は、Sum()のdefaultを指定します。

Employee.objects.annotate(
    total_work_minutes=Sum('attendance__work_minutes', default=0)
)

11. Exists()で未退勤の従業員を探す

最後はサブクエリです。出勤済みで、まだ退勤時刻が登録されていない勤怠記録を1件でも持つ従業員を取得します。

Django ORM

from django.db.models import Exists, OuterRef


working_attendances = Attendance.objects.filter(
    employee=OuterRef('pk'),
    clock_out__isnull=True,
)


Employee.objects.filter(Exists(working_attendances))

変換後のSQL

SELECT "employee".*
FROM "employee"
WHERE EXISTS (
    SELECT 1
    FROM "attendance" U0
    WHERE (
        U0."employee_id" = "employee"."id"
        AND U0."clock_out" IS NULL
    )
    LIMIT 1
);

OuterRef('pk')は、外側で検索している従業員の主キーを参照します。Exists()は条件に合う行の内容ではなく、存在するかどうかだけを調べます。データベースは1件見つけた時点で探索を打ち切れる場合があります。

UPDATEやDELETEのSQLを確認する

queryset.queryで確認しやすいのはSELECT文です。create()、update()、delete()は呼び出した時点で実行され、戻り値もQuerySetではないため、同じ方法ではSQLを表示できません。

開発中に実行済みのSQLを手軽に調べるなら、DEBUG=Trueの環境でconnection.queriesを確認できます。

from django.db import connection


Attendance.objects.filter(
    clock_out__isnull=True,
).update(note='退勤打刻を確認してください')


print(connection.queries[-1]['sql'])

実行されるSQLは、おおむね次の形です。

UPDATE "attendance"
SET "note" = '退勤打刻を確認してください'
WHERE "attendance"."clock_out" IS NULL;

connection.queriesはデバッグ用です。本番環境でDEBUG=Trueにすると、SQL履歴の保存によるメモリ消費や情報漏えいにつながるため使用しません。

ORMとSQLの対応を振り返る

Django ORM 主に対応するSQL
all() SELECT ... FROM ...
filter()、exclude() WHERE
order_by() ORDER BY
QuerySetのスライス LIMIT、OFFSET
values() SELECTする列の限定
関連フィールドの参照 JOIN
select_related() 関連先を含むJOINとSELECT
annotate()、Count()、Sum() COUNT、SUM、GROUP BY
Exists() EXISTSサブクエリ

ORMを使えば、複雑な問い合わせもPythonのコードとして組み立てられます。ただし、短いORMコードが常に効率的なSQLになるとは限りません。

関連データをループで参照するとき、集計条件を重ねるとき、画面の応答が遅くなったときは、生成されたSQLとクエリ数を確認してみてください。ORMとSQLを対応させて読めるようになると、N+1問題や不要なJOINにも気づきやすくなります。

参考リンク