Django ORM入門：SQLと比較しながら検索・集計・リレーションを学ぶ

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

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

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

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

この記事の環境

1
$ python --version
2
Python 3.13.0
3
$ python -m django --version
4
6.1
5
$ sqlite3 -version
6
3.51.0

使用するモデル

1人の従業員が複数の勤怠記録を持つ、1対多の関係のモデルがあるとします。

1
from django.db import models
2

3

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

9
    class Meta:
10
        db_table = 'employee'
11
        verbose_name = '従業員'
12

13

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

25
    class Meta:
26
        db_table = 'attendance'
27
        verbose_name = '勤怠記録'

QuerySetからSQLを確認する

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

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

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

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

1. すべてのデータを取得する

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

1
Employee.objects.all()

1
SELECT
2
    "employee"."id",
3
    "employee"."name",
4
    "employee"."number",
5
    "employee"."age"
6
FROM "employee";

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

2. 完全一致で検索する

氏名が「佐藤」と一致する従業員を取得します。

1
Employee.objects.filter(name='佐藤')

1
SELECT
2
    "employee"."id",
3
    "employee"."name",
4
    "employee"."number",
5
    "employee"."age"
6
FROM "employee"
7
WHERE "employee"."name" = '佐藤';

条件の指定に使うのがフィールドルックアップです。フィールドルックアップは、「どのフィールドを、どの方法で検索するか」を Django ORM の仕組みです。

基本形はフィールド名__ルックアップ名=値です。フィールド名とルックアップ名の間は、2つのアンダースコア__で区切ります。

1
Employee.objects.filter(name__exact='佐藤')
2
#                       └─┬┘ └─┬─┘
3
#                     フィールド ルックアップ

この例のnameは検索対象のフィールド、exactは完全一致という検索方法を表します。ルックアップを省略してname='佐藤'と書いた場合も、Djangoはexactを使用します。そのため、次の2つは同じ条件です。

1
Employee.objects.filter(name='佐藤')
2
Employee.objects.filter(name__exact='佐藤')

完全一致検索では、氏名が「佐藤」のデータは取得できますが、「佐藤太郎」や「佐藤田」のようにほかの文字を含むデータは取得されません。

3. LIKEで部分一致検索する

氏名に「佐」を含む従業員を取得します。Django ORMでは__containsを使います。

1
Employee.objects.filter(name__contains='佐')

1
SELECT
2
    "employee"."id",
3
    "employee"."name",
4
    "employee"."number",
5
    "employee"."age"
6
FROM "employee"
7
WHERE "employee"."name" LIKE '%佐%' ESCAPE '\';

SQLの%は0文字以上の任意の文字列を表します。そのため、'%佐%'は文字列の途中を含め、どこかに「佐」があれば一致します。

よく使うLIKE検索に対応するルックアップは次のとおりです。

Django ORM	検索方法	SQLのパターン例
`name__contains='佐'`	部分一致	`LIKE '%佐%'`
`name__startswith='佐'`	前方一致	`LIKE '佐%'`
`name__endswith='藤'`	後方一致	`LIKE '%藤'`

SQLiteでは、ASCII文字に対するLIKEの大文字・小文字の扱いなどが、ほかのデータベースと異なる場合があります。大文字・小文字を区別しない検索を意図するときは__icontains、__istartswith、__iendswithも利用できますが、最終的な挙動は使用するデータベースで確認してください。

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

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

1
Employee.objects.filter(age__gte=20)

1
SELECT
2
    "employee"."id",
3
    "employee"."name",
4
    "employee"."number",
5
    "employee"."age"
6
FROM "employee"
7
WHERE "employee"."age" >= 20;

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

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

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

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

1
Employee.objects.filter(
2
    age__gte=20,
3
    number__startswith='E',
4
)

1
SELECT ...
2
FROM "employee"
3
WHERE (
4
    "employee"."age" >= 20
5
    AND "employee"."number" LIKE 'E%' ESCAPE '\'
6
);

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

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

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

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

1
SELECT ...
2
FROM "attendance"
3
ORDER BY "attendance"."work_minutes" DESC
4
LIMIT 5;

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

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

1
Attendance.objects.order_by('clock_in')

1
SELECT ...
2
FROM "attendance"
3
ORDER BY "attendance"."clock_in" ASC;

7. QオブジェクトでOR条件

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

1
from django.db.models import Q
2

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

1
SELECT ...
2
FROM "employee"
3
WHERE (
4
    "employee"."age" < 20
5
    OR "employee"."age" >= 60
6
);

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

NOT の場合下記のようになります。

1
Employee.objects.filter(
2
    ~(Q(age__lt=20) | Q(age__gte=60))
3
)

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

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

1
Employee.objects.values('name', 'number')

1
SELECT
2
    "employee"."name" AS "name",
3
    "employee"."number" AS "number"
4
FROM "employee";

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

1
{
2
    'name': '佐藤',
3
    'number': 'E001',
4
}

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

9. ForeignKeyをたどってJOINする

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

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

1
SELECT "attendance".*
2
FROM "attendance"
3
INNER JOIN "employee"
4
    ON (
5
        "attendance"."employee_id"
6
        = "employee"."id"
7
    )
8
WHERE "employee"."name" = '佐藤';

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

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

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

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

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

1
SELECT
2
    "attendance".*,
3
    "employee"."id",
4
    "employee"."name",
5
    "employee"."number",
6
    "employee"."age"
7
FROM "attendance"
8
INNER JOIN "employee"
9
    ON (
10
        "attendance"."employee_id"
11
        = "employee"."id"
12
    )
13
ORDER BY "attendance"."clock_in" ASC;

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

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

annotate()は、QuerySetで取得する各データに集計値や計算結果を追加するメソッドです。モデルに定義されていない一時的な項目を、検索結果へ付け加えられます。

例えば、従業員ごとの勤怠件数をattendance_countという名前で追加してみます。

1
from django.db.models import Count
2

3
Employee.objects.annotate(
4
    attendance_count=Count('attendance')
5
)

このattendance_countはデータベースの列として保存されるわけではありません。このQuerySetから取得した各Employeeオブジェクトにだけ追加されます。

1
for employee in Employee.objects.annotate(
2
    attendance_count=Count('attendance')
3
):
4
    print(employee.name, employee.attendance_count)

QuerySet全体を1つの値へ集計するaggregate()とは異なり、annotate()は従業員ごとに結果を付けるのがポイントです。

今回はvalues()も続けて使用し、従業員名と勤怠件数だけを辞書として取得します。Attendanceにrelated_nameを設定していないため、集計で使う逆方向の検索名はモデル名の小文字であるattendanceです。

1
from django.db.models import Count
2

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

1
SELECT
2
    "employee"."name" AS "name",
3
    COUNT("attendance"."id") AS "attendance_count"
4
FROM "employee"
5
LEFT OUTER JOIN "attendance"
6
    ON (
7
        "employee"."id"
8
        = "attendance"."employee_id"
9
    )
10
GROUP BY
11
    "employee"."id",
12
    "employee"."name",
13
    "employee"."number",
14
    "employee"."age";

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

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

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

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

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

1
from django.db.models import Sum
2

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

1
SELECT
2
    "employee"."name" AS "name",
3
    SUM("attendance"."work_minutes") AS "total_work_minutes"
4
FROM "employee"
5
LEFT OUTER JOIN "attendance"
6
    ON (
7
        "employee"."id"
8
        = "attendance"."employee_id"
9
    )
10
GROUP BY
11
    "employee"."id",
12
    "employee"."name",
13
    "employee"."number",
14
    "employee"."age";

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

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

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

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

1
from django.db.models import Exists, OuterRef
2

3
working_attendances = Attendance.objects.filter(
4
    employee=OuterRef('pk'),
5
    clock_out__isnull=True,
6
)
7

8
Employee.objects.filter(Exists(working_attendances))

1
SELECT "employee".*
2
FROM "employee"
3
WHERE EXISTS (
4
    SELECT 1
5
    FROM "attendance" U0
6
    WHERE (
7
        U0."employee_id" = "employee"."id"
8
        AND U0."clock_out" IS NULL
9
    )
10
    LIMIT 1
11
);

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

UPDATEやDELETEのSQLを確認する

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

確認したい処理のSQLだけを取得するには、CaptureQueriesContextを使います。

1
from django.db import connection
2
from django.test.utils import CaptureQueriesContext
3

4
with CaptureQueriesContext(connection) as captured:
5
    Attendance.objects.filter(
6
        clock_out__isnull=True,
7
    ).update(note='退勤打刻を確認してください')
8

9
for q in captured.captured_queries:
10
    print(q['sql'])

CaptureQueriesContextは、withブロック内で実行されたSQLをcaptured_queriesへ保存します。
実行されるSQLは、次のような形になります。

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

explain()でSQLの実行計画を確認する

生成されたSQLを確認するだけでなく、データベースがそのSQLをどのように処理する予定なのか調べたいときは、QuerySetのexplain()を使います。

1
queryset = Employee.objects.filter(age__gte=20)
2
print(queryset.explain())

SQLiteでは、次のような実行計画が表示されます。

1
2 0 216 SCAN employee

SCAN employeeは、employeeテーブルを走査して条件に合う行を探す計画であることを示します。ageにはインデックスを設定していないため、この例ではテーブルを順に調べます。

一方、numberにはunique=Trueを指定しています。SQLiteでは一意性を保証するインデックスが作られるため、社員番号で検索すると異なる実行計画になります。

1
queryset = Employee.objects.filter(number='E001')
2
print(queryset.explain())

1
3 0 39 SEARCH employee USING INDEX sqlite_autoindex_employee_1 (number=?)

SEARCH ... USING INDEXは、テーブル全体を順に調べるのではなく、インデックスを使って対象を探す計画であることを示します。

print(queryset.query)が「どのようなSQLへ変換されたか」を確認するものなのに対し、queryset.explain()は「その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にも気づきやすくなります。