はじめに

この記事では、DjangoのQueryset APIを使ってSQL文を実行する方法を紹介します。Djangoのversion1.xに関する記事は見つけることができましたが、version 2.xに関する記事は見つけることができなかったのでまとめました。

環境

Mac OS Mojave 10.14.5
python 3.7.4
Django 2.2.6
MySQL 8.0.17

今回使用するModel

公式ドキュメントに掲載されているModelを使うことにします。

[models.py]

from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)
    age = models.IntegerField()

class Publisher(models.Model):
    name = models.CharField(max_length=300)

class Book(models.Model):
    name = models.CharField(max_length=300)
    pages = models.IntegerField()
    price = models.DecimalField(max_digits=10, decimal_places=2)
    rating = models.FloatField()
    authors = models.ManyToManyField(Author)
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
    pubdate = models.DateField()

class Store(models.Model):
    name = models.CharField(max_length=300)
    books = models.ManyToManyField(Book)

SELECT句

全列抽出
実際は、*ではなく全列がSELECT句に列挙されますが、スペースの関係上*で表記しています(以降も同様)。

b = Book.objects.all()
#=> SELECT * FROM [アプリケーション名]_book;

指定列抽出

b = Book.objects.values('name')
#=> SELECT [アプリケーション名]_book.name FROM [アプリケーション名]_book;

WHERE句

条件一致
filter()を使います。

b = Book.objects.filter(price=1000)
#=> SELECT * FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.price = 1000;

NOT
exclude()を使います。

b = Book.objects.exclude(price=1000)
#=> SELECT * FROM [アプリケーション名]_book
#   WHERE NOT ([アプリケーション名]_book.price = 1000);

AND
filterの引数を増やせばANDになります。

b = Book.objects.filter(price=1000, rating=5.0)
#=> SELECT * FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.price = 1000
#   AND [アプリケーション名]_book.rating = 5.0;

OR
Qオブジェクトを使用します。

b = Book.objects.filter(Q(price=1000) | Q(rating=5.0))
#=> SELECT * FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.price = 1000
#   OR [アプリケーション名]_book.rating = 5.0;

LIKE
__containsを使用します。

b = Book.objects.filter('name__contains='information')
#=> SELECT * FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.name LIKE 'information';

BETWEEN
__rangeを使用します。

import datetime
start_date = datetime.date(2019, 1, 1)
end_date = datetime.date(2019, 11, 30)
b = Book.objects.filter(pubdate__range=(start_date, end_date))
#=> SELECT * FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.pubdate BETWEEN '2019-01-01' and '2019-11-30';

IN
__inを使用します。

b = Book.objects.filter(id__in=[1, 3, 4])
#=> SELECT * FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.id IN (1, 3, 4);

補足：models.pyで定義されている各classではidという変数は明示的に記述されていませんが、Djangoでは定義した全てのModelにidという変数（主キーとなる）が付与されます。

ORDER BY句

昇順
order_by()を使用します。

b = Book.objects.order_by('id')
#=> SELECT * FROM [アプリケーション名]_book
#   ORDER BY [アプリケーション名]_book.id ASC;

降順
降順キーの先頭に-を付けます。

b = Book.objects.order_by('-id')
#=> SELECT * FROM [アプリケーション名]_book
#   ORDER BY [アプリケーション名]_book.id DESC;

GROUP BY句

全体の集約

# 集計結果には明示的な名前を付けない
b1 = Book.objects.aggregate(Avg('price'))
#=> SELECT AVG([アプリケーション名]_book.price) AS price__avg FROM [アプリケーション名]_book;

# 集計結果に対し、明示的に名前をつける
b2 = Book.objects.aggregate(sum_price=Sum('price'))
#=> SELECT SUM([アプリケーション名]_book.price) AS sum_price FROM [アプリケーション名]_book;

この他にもCount()、Max()、Min()などがある。

行の集約

values().annotate()の順で書く

b = Book.objects.values('publisher_id').annotate(price_avg=Avg('price'))
#=> SELECT [アプリケーション名]_book.publisher_id, 
#          AVG([アプリケーション名]_book.price) AS price_avg
#   FROM [アプリケーション名]_book
#   GROUP BY [アプリケーション名]_book.publisher_id;

補足：models.pyのBookクラスを見てみると、publisher_idではなくpublisherが変数として定義されている。しかし、定義をよく見ると外部キーとして定義されているので、実際にはPublisherクラスの主キーであるpublisher_idがBookテーブルのカラムとして定義されることになる。

JOIN

filter()(単一行取得の場合はget())を使用する方法とselect_related()を使用する方法があります。少し複雑なので２つの方法の違いを詳しく説明します。

filter()(単一行取得の場合はget())を使用する場合
下記のようにクエリを実行するとBookテーブルから行を取ってくる操作と、その外部キーを元にPublisherテーブルから行を取ってくる操作の計２回DBを叩くことになります。（こちらの手法は厳密にはJOINではありませんが、比較のために説明しています。）

b = Book.objects.get(id=4)  # DBを叩く
p = b.publisher        # DBを叩く

select_related()を使用する場合
こちらの方法では、一度のクエリでBookテーブルとPublisherテーブルの両方からオブジェクトを取得するためDBを叩く回数が少なく済みます。

b = Book.objects.select_related('publisher').get(id=4)
p = b.publisher        # 取得済みのオブジェクトを参照

ちなみに、Publisherテーブルが外部キーを持つ場合、select_related('publisher__[外部キー]')とすることで１度のクエリでBookテーブルの親テーブルの親テーブルまでのオブジェクトを取得することができます。

しかし、一つだけ注意しなければならないのは、このJOINはLEFT OUTER JOINに相当するということです。この例だと仮にBookテーブルが参照するPublisherテーブルの行が存在しなくても、特にエラーを吐くことなくクエリが実行されます。（存在しない部分にはNoneが入ります。）

もしINNER JOINしたい場合はexclude()を用いて、参照先がNoneの行を除外する必要があります。（もしかしたら他に方法があるかもしれませんが、、）

INSERT句

以下のように記述します。

Book(name='book1', ...).save()
#=> INSERT INTO "book" ("name", ...) VALUES ('book1', ...);

UPDATE句

get()による条件指定での更新
対象のオブジェクトを取得した後、オブジェクトを更新してsave()にてUPDATE文が実行されます。

b = Book.objects.get(name='book1')
b.pages += 1
b.save()
#=> UPDATE "book" SET "name" = 'book1', "pages" = 2, ...
#   WHERE [アプリケーション名]_book.id = 1;

*get(name='book1')で得られたオブジェクトの主キーが'1'と仮定しています。

update()による複数件の一括更新
update()メソッドで、複数件の一括更新を行います。

Book.objects.filter(name__contains='book').update(pages=10)
#=> UPDATE "book" SET "pages" = 10
#   WHERE [アプリケーション名]_book.name LIKE 'book';

DELETE句

delete()を使用します。

全件削除

Book.objects.all().delete()
#=> DELETE FROM [アプリケーション名]_book;

部分削除

Book.objects.filter('name__contains='information').delete()
#=> DELETE FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.name LIKE 'information';

外部テーブルを参照する場合

Book.objects.filter(publisher__name='Bob').delete()
#=> DELETE FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.id IN
#   (SELECT [アプリケーション名]_book.id) FROM [アプリケーション名]_book
#   INNER JOIN [アプリケーション名]_publisher ON ([アプリケーション名]_book.publisher_id = [アプリケーション名]_publisher.id )
#   WHERE [アプリケーション名]_publisher.name = 'Bob');

親テーブルを削除する場合

親テーブルを削除する際、デフォルトでは親テーブルのカラムを外部キーに持つ全てのオブジェクトも同時に削除されます。（公式ドキュメントには「デフォルトではON DELETE CASCADE制約をエミュレートします。」という記述があります。）つまり、関連する子テーブルを全て削除してから、親テーブルを削除するという順序になります。

publisher.objects.all().delete()
#=> DELETE FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.publisher_id IN 
#   (SELECT [アプリケーション名]_publisher.id FROM [アプリケーション名]_publisher);
#   DELETE FROM [アプリケーション名]_publisher;

テーブルをDELETEする際は、想定通りの動作となっているかを十分に検証しておいた方が良さげです。

おわりに

今回の記事ではここまでとしますが、実際にはもっと多くのAPIが用意されています。興味のある方はDjango公式のQuerySet API referenceを参照してください。

【Django】SQL文をQuerySet APIで書く方法を紹介する