素の SQL 文の実行

モデルのクエリ API では不十分な場合、立ち戻って素の SQL を記述する事ができます。Django は素の SQL を処理する方法を 2 つ提供しています。あなたは Manager.raw() を 素のクエリを実行する事でモデルのインスタンスを得る 、あるいはモデル層の利用を完全に避けて 直接自前の SQL を実行する 事ができます。

警告

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

素のクエリを実行する

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

Manager.raw(raw_query, params=None, 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 はデータベース上のテーブル名をモデルの "アプリのラベル名" -- manage.py startapp で利用した名前 -- とモデルのクラス名とを、間にアンダースコアを入れて結合させる事で取得しています。先の例ではモデル Person が myapp と名づけられたアプリケーション内に作成されていると仮定すると、そのテーブル名が myapp_person であると推定できます。

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

警告

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

警告

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

警告

RawQuerySet インスタンスは通常の QuerySet と同様にイテレート可能ですが、RawQuerySet は QuerySet で利用できる全てのメソッドを提供している訳ではありません。例えば、RawQuerySet では __bool__() と __len__() が定義されておらず、そのため RawQuerySet のインスタンスは全て True と判定されます。RawQuerySet においてこれらのメソッドが実装されていない理由は、内部的なキャッシュを用いずにそれを実装する事がパフォーマンス上の問題を発生させ、さらにその類のキャッシュを追加する事が内部的な非互換性を生み出しうるためです。

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

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() 内のクエリによって取得され -- 苗字は両方とも表示される時点で都度取得されました。

ただ 1 つのフィールドだけ省略できないフィールドが有ります - 主キーのフィールドです。Django はモデルのインスタンスの識別に主キーを利用し、そのため素のクエリにはいつも必ず含まれていなければなりません。もし主キーを記述するのを忘れた場合 InvalidQuery 例外が送出されます。

付加情報の追加

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

>>> 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.
...

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

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

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

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

注釈

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

警告

Do not use string formatting on raw queries or quote placeholders in your SQL strings!

上記のクエリを以下のように書きたくなるかもしれません:

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

You might also think you should write your query like this (with quotes around %s):

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

Don't make either of these mistakes.

As discussed in SQL injectionへの防御, using the params argument and leaving the placeholders unquoted protects you from SQL injection attacks, a common exploit where attackers inject arbitrary SQL into your database. If you use string interpolation or quote the placeholder, you're at risk for SQL injection.

独自の SQL を直接実行する

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

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

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

For example:

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

To protect against SQL injection, you must not include quotes around the %s placeholders in the SQL string.

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

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
cursor = connections['my_db_alias'].cursor()
# Your code here...

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

def dictfetchall(cursor):
    "Return all rows from a cursor as a dict"
    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"
    desc = cursor.description
    nt_result = namedtuple('Result', [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

以下はここまで示した三通り方法それぞれの異なる利用例です:

>>> 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()

Calling stored procedures

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

Calls a database stored procedure with the given name. A sequence (params) or dictionary (kparams) of input parameters may be provided. Most databases don't support kparams. Of Django's built-in backends, only Oracle supports it.

For example, given this stored procedure in an Oracle database:

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;

This will call it:

with connection.cursor() as cursor:
    cursor.callproc('test_procedure', [1, 'test'])

Changed in Django 2.0:

The kparams argument was added.