素の SQL 文の実行

Django は素の SQL を処理する方法を 2 つ提供しています。あなたは素のクエリを実行するために perform raw queries and return model instances Manager.raw() を使うか、あるいはモデル層の利用を完全に避けてカスタムのSQLを直接実行する execute custom SQL directly ことができます。

素のSQLを使う前にORMを調べましょう！

Django ORM は、素の SQL を書かずにクエリを表現するための多くのツールを提供しています。たとえば、次のようなものです。

素のSQLを使う前に、 ORM を調べてください。ORMがあなたのユースケースをサポートしているかどうか、 サポートチャンネル で尋ねてみてください。

警告

直接 SQL を書く場合はいつでも十分警戒するべきです。利用者が params を利用する事で任意に設定可能な全てのパラメータは必ず、SQL インジェクション攻撃から防御するために適切にエスケープすべきです。詳細は SQL インジェクションに対する防御 を参照してください。

素のクエリを実行する

raw() マネージャメソッドは素の SQL 文を処理してモデルのインスタンスを返させる場合に利用できます:

Manager.raw(raw_query, params=(), translations=None)

このメソッドは素の SQL クエリを受け取り、実行し、 django.db.models.query.RawQuerySet インスタンスを返します。この RawQuerySet インスタンスは通常の QuerySet のようにイテレートしてオブジェクトインスタンスを生成できます。

これは例で説明するのが一番わかりやすいでしょう。次のようなモデルがあるとします:

class Person(models.Model):
    first_name = models.CharField(...)
    last_name = models.CharField(...)
    birth_date = models.DateField(...)

このとき、以下のように独自の SQL を実行できます:

>>> for p in Person.objects.raw("SELECT * FROM myapp_person"):
...     print(p)
...
John Smith
Jane Jones

この例はたいしてエキサイティングではありません。これは Person.objects.all() を実行したのと全く同じです。しかしながら、 raw() はその機能を極めて強力なものにする数々のオプションを備えています。

モデルのテーブル名

この例で示したモデル Person のテーブル名はどのようにして得られたのでしょうか？

デフォルトでは、Django はモデルの "app label" (manage.py startapp で使った名前) とモデルのクラス名をアンダースコアでつないで、データベースのテーブル名を決定します。たとえば、Person モデルは myapp という名前のアプリの中にあると仮定しているので、そのテーブルは myapp_person となります。

詳細に関しては、手動でデータベースのテーブル名を設定できる db_table オプションのドキュメントを参照してください。

警告

.raw() に対して渡された SQL 文はチェックされません。Django はそこに記述された内容によってデータベースが行を返す事を期待しますが、それを強制する処理は行いません。もし記述したクエリが行を返さない場合、(おそらく不可解な)例外が発生します。

警告

もしあなたが MySQL でクエリを処理する場合は、複数の型を扱う際に MySQL の暗黙的な型変換が予期しない結果をもたらす場合がある事に注意してください。もし文字列型で定義したカラムに対し、数値型の値で問い合わせた場合、MySQL は比較処理を行う前にテーブル上の全ての値の型を数値型に変換します。例えば 'abc'、 'def' といった値が含まれているテーブルに対して WHERE mycolumn=0 という条件での問い合わせを行うと、両方の行がマッチします。これを防ぐため、クエリの値を利用する前に適切な型キャストを行ってください。

クエリのフィールドをモデルのフィールドにマップする

raw() は自動的にクエリのフィールドをモデルのフィールドにマップします。

クエリのフィールドの順番は重要ではありません。つまり、以下のクエリはどちらも同じように動作します:

>>> Person.objects.raw("SELECT id, first_name, last_name, birth_date FROM myapp_person")
>>> Person.objects.raw("SELECT last_name, birth_date, first_name, id FROM myapp_person")

マッチングは名前によって行われます。つまり、SQL の AS 句を使用して、クエリのフィールドをモデルのフィールドにマッピングできます。もし、他のテーブルで Person のデータがあれば、簡単に Person のインスタンスにマッピングできます:

>>> Person.objects.raw(
...     """
...     SELECT first AS first_name,
...            last AS last_name,
...            bd AS birth_date,
...            pk AS id,
...     FROM some_other_table
...     """
... )

名称が一致している限り、そのモデルのインスタンスは適切に生成されます。

あるいは、 raw() の引数 translations を使って、クエリのフィールドをモデルのフィールドにマッピングすることもできます。これはクエリのフィールド名とモデルのフィールド名をマッピングした辞書です。たとえば、上記のクエリは次のように書くこともできます:

>>> name_map = {"first": "first_name", "last": "last_name", "bd": "birth_date", "pk": "id"}
>>> Person.objects.raw("SELECT * FROM some_other_table", translations=name_map)

インデックスの利用

raw() はインデックスをサポートしているので、最初の結果だけが必要な場合はこう書きます:

>>> first_person = Person.objects.raw("SELECT * FROM myapp_person")[0]

ただし、インデックス作成とスライスはデータベースレベルでは実行されません。データベースに多数の Person オブジェクトがある場合は、SQL レベルでクエリを制限する方が効率的です:

>>> first_person = Person.objects.raw("SELECT * FROM myapp_person LIMIT 1")[0]

モデルのフィールドの遅延評価

モデルのフィールドは省略可能です:

>>> people = Person.objects.raw("SELECT id, first_name FROM myapp_person")

上記のクエリから得られる Person オブジェクトは遅延評価されるモデルのインスタンスになります(defer() を参照)。これはクエリから省略されたフィールドが要求に応じて読み出される事を意味します。以下はその例になります:

>>> for p in Person.objects.raw("SELECT id, first_name FROM myapp_person"):
...     print(
...         p.first_name,  # This will be retrieved by the original query
...         p.last_name,  # This will be retrieved on demand
...     )
...
John Smith
Jane Jones

見た目上は、そのクエリが名前と苗字を両方取得しているように見えます。しかし、この例は実際には 3 つのクエリを発行しています。名前だけが raw() 内のクエリによって取得され、苗字は両方とも表示される時点でその都度取得されました。

省略できないフィールドが一つだけあります。プライマリキーのフィールドです。Django はモデルのインスタンスの識別にプライマリキーを利用するので、素のクエリには必ず含む必要があります。もしプライマリキーを入れ忘れると、 FieldDoesNotExist 例外が発生します。

付加情報（アノテーション）の追加

モデル内に定義されていないフィールドを含んだクエリを実行する事もできます。例えば、 PostgreSQL's age() function によってデータベース上で計算された年齢とともに人物の一覧を取得できます:

>>> people = Person.objects.raw("SELECT *, age(birth_date) AS age FROM myapp_person")
>>> for p in people:
...     print("%s is %s." % (p.first_name, p.age))
...
John is 37.
Jane is 42.
...

代わりに Func() 式 を使うことで、素の SQL を使ってアノテーションを計算するのを避けることができます。

raw() にパラメータを渡す

パラメータを用いたクエリを使用する場合は、raw() に対して params 引数を利用できます:

>>> lname = "Doe"
>>> Person.objects.raw("SELECT * FROM myapp_person WHERE last_name = %s", [lname])

params はパラメータのリストもしくは辞書です。リストの場合は %s というプレースホルダーを使い、辞書の場合は %(key)s というプレースホルダーを使います ( key は辞書のキーに置き換えられます)、データベースエンジンを意識する必要はありません。このようなプレースホルダは params 引数のパラメータに置き換えられます。

注釈

SQLite バックエンドにおいて辞書はサポートされていません。パラメータはリストで渡す必要があります。

警告

素のSQLクエリで文字列書式を使用したり、SQL文字列で引用符プレースホルダを使用しないでください。

上記のクエリをこう書きたくなる誘惑に駆られるでしょう:

>>> query = "SELECT * FROM myapp_person WHERE last_name = %s" % lname
>>> Person.objects.raw(query)

また、クエリはこのように書くべきだと思うかもしれません（ %s を引用符で囲む）:

>>> query = "SELECT * FROM myapp_person WHERE last_name = '%s'"

どちらのミスも犯してはなりません。

SQL injectionへの防御 で議論されているように、params 引数を使用し、プレースホルダーを引用符で囲まないことで、攻撃者が任意のSQLをデータベースに注入する、一般的な脆弱性である SQL injection attacks から保護されます。文字列補間を使用したり、プレースホルダーを引用符で囲んだりすると、SQLインジェクションのリスクがあります。

独自の SQL を直接実行する

Manager.raw() でも要求を満たせない場合があります:きれいにモデルにマップできないクエリを扱ったり、UPDATE、 INSERT あるいは DELETE を直接実行したりする必要が有るかもしれません。

こういったケースでは、モデル層を完全に迂回してデータベースにいつでも直接アクセスできます。

django.db.connection オブジェクトは、デフォルトのデータベースコネクションを表します。データベースコネクションを利用するには、カーソルオブジェクトを取得するため connection.cursor() を呼び出してください。そして、cursor.execute(sql, [params]) を呼び出して SQL を実行した後、cursor.fetchone() もしくは cursor.fetchall() が結果の行を返します。

例:

from django.db import connection


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

    return row

SQLインジェクションから保護するために、SQL文字列の %s プレースホルダの前後に引用符を含めてはいけません。

もしリテラルなパーセント記号をクエリ中で使いたい場合、パラメータを渡す際に二重に記述する必要が有る事に注意してください:

cursor.execute("SELECT foo FROM bar WHERE baz = '30%'")
cursor.execute("SELECT foo FROM bar WHERE baz = '30%%' AND id = %s", [self.id])

2 つ以上のデータベース を利用している場合、特定のコネクション (とカーソル) を取得するのに django.db.connections を利用できます。django.db.connections は、エイリアスを指定することで特定のコネクションを得られる、辞書に似たオブジェクトです。

from django.db import connections

with connections["my_db_alias"].cursor() as cursor:
    # Your code here
    ...

デフォルトでは、Python データベース API は返す結果にフィールド名を含まず、つまり 辞書 でなく、 リスト の値として結果を返します。処理能力とメモリを少々利用して、次のような処理を用いる事で結果を 辞書 として得られます:

def dictfetchall(cursor):
    """
    Return all rows from a cursor as a dict.
    Assume the column names are unique.
    """
    columns = [col[0] for col in cursor.description]
    return [dict(zip(columns, row)) for row in cursor.fetchall()]

別の選択肢は Python 標準ライブラリに含まれる collections.namedtuple() を利用する事です。namedtuple は属性を指定して値へのアクセスが可能なタプルに似たオブジェクトです; 加えてインデックスが利用でき、イテレート可能でもあります。取得した結果は不変であり属性名もしくはインデックスでアクセスできて便利です:

from collections import namedtuple


def namedtuplefetchall(cursor):
    """
    Return all rows from a cursor as a namedtuple.
    Assume the column names are unique.
    """
    desc = cursor.description
    nt_result = namedtuple("Result", [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

dictfetchall() と namedtuplefetchall() の例は、異なるテーブルの列をカーソルは区別できないため、一意な列名を想定しています。

この3つの違いの例を挙げましょう:

>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2")
>>> cursor.fetchall()
((54360982, None), (54360880, None))

>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2")
>>> dictfetchall(cursor)
[{'parent_id': None, 'id': 54360982}, {'parent_id': None, 'id': 54360880}]

>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2")
>>> results = namedtuplefetchall(cursor)
>>> results
[Result(id=54360982, parent_id=None), Result(id=54360880, parent_id=None)]
>>> results[0].id
54360982
>>> results[0][0]
54360982

コネクションとカーソル

connection と cursor の実装は PEP 249 に規定された Python DB-API の基準にほぼ完全に準拠しています — トランザクション操作 は例外となります。

Python DB-API に精通していない場合、cursor.execute() 内の SQL 文には直接値を追加せずに、"%s" というプレースホルダーを利用する事に注意してください。この手法を用いる事で、内部で動作しているデータベースライブラリは自動的に必要に応じて値のエスケープを行ってくれます。

加えて Django はプレースホルダーとして Python に内蔵された SQLite モジュールで用いられる "?" でなく "%s" を期待して動作する事にも注意してください。これは全体の調和と健全性のためです。

コンテキストマネージャとしてのカーソルの利用:

with connection.cursor() as c:
    c.execute(...)

これは以下と同じです:

c = connection.cursor()
try:
    c.execute(...)
finally:
    c.close()

ストアドプロシージャの呼び出し

CursorWrapper.callproc(procname, params=None, kparams=None)

与えられた名前のストアドプロシージャを呼び出します。入力パラメータのシーケンス (params) または辞書 (kparams) を指定できます。ほとんどのデータベースは kparams をサポートしていません。Django の組み込みバックエンドでは、 Oracle だけがサポートしています。

たとえば、Oracleデータベースで次のようなストアドプロシージャがあるとします:

CREATE PROCEDURE "TEST_PROCEDURE"(v_i INTEGER, v_text NVARCHAR2(10)) AS
    p_i INTEGER;
    p_text NVARCHAR2(10);
BEGIN
    p_i := v_i;
    p_text := v_text;
    ...
END;

これは以下のコードで呼び出すことができます。:

with connection.cursor() as cursor:
    cursor.callproc("test_procedure", [1, "test"])
Back to Top