素の SQL 文の実行Link to this heading

Django gives you two ways of performing raw SQL queries: you can use Manager.raw() to perform raw queries and return model instances, or you can avoid the model layer entirely and execute custom SQL directly.

素のクエリを実行するLink to this heading

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

Manager.raw(raw_query, params=None, translations=None)Link to this definition

This method takes a raw SQL query, executes it, and returns a django.db.models.query.RawQuerySet instance. This RawQuerySet instance can be iterated over like a normal QuerySet to provide object instances.

例を示す事で最も簡単に説明できます。以下のモデルについて考えてください:

Code

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

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

Code

>>> 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 オプションのドキュメントを参照してください。

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

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

クエリ内で記述したフィールド名の順番は処理の結果に影響しません。つまり、以下の両方のクエリは同じように動作します:

Code

>>> 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 インスタンスに容易にマップできます:

Code

>>> 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 引数を利用する事でクエリのフィールドとモデルのフィールドをマップできます。これは直接的なクエリのフィールド名とモデルのフィールド名とのマッピングです。例として、上記のクエリは以下のように書く事もできます:

Code

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

インデックスの利用Link to this heading

raw() はインデックスに対応しており、最初の結果だけが欲しい場合次のように書けます:

Code

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

しかしながら、そのインデックスの利用と結果の分割はデータベースの領域で行われている訳ではありません。もし Person オブジェクトがデータベース内に大量に存在する場合、SQL レベルで問い合わせの制限をした方が効果的です:

Code

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

モデルのフィールドの遅延評価Link to this heading

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

Code

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

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

Code

>>> 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 例外が送出されます。

付加情報の追加Link to this heading

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

Code

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

You can often avoid using raw SQL to compute annotations by instead using a Func() expression.

`raw()` にパラメータを渡すLink to this heading

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

Code

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

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

警告

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

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

Code

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

Code

>>> 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 を直接実行するLink to this heading

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

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

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

例:

Code

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.

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

Code

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 はその別名を指定する事で特定の接続を得られる、辞書に似たオブジェクトです:

Code

from django.db import connections
with connections['my_db_alias'].cursor() as cursor:
    # Your code here...

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

Code

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 は属性を指定して値へのアクセスが可能なタプルに似たオブジェクトです; 加えてインデックスが利用でき、イテレート可能でもあります。取得した結果は不変であり属性名もしくはインデックスでアクセスできて便利です:

Code

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

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

Code

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

データベース接続とカーソルLink to this heading

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

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

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

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

Code

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

これは以下と同じです:

Code

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

Calling stored proceduresLink to this heading

CursorWrapper.callproc(procname, params=None, kparams=None)Link to this definition

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:

SQL

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:

Code

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

素の SQL 文の実行Link to this heading#

素のクエリを実行するLink to this heading#

クエリのフィールドをモデルのフィールドにマップするLink to this heading#

インデックスの利用Link to this heading#

モデルのフィールドの遅延評価Link to this heading#

付加情報の追加Link to this heading#

raw() にパラメータを渡すLink to this heading#

独自の SQL を直接実行するLink to this heading#

データベース接続とカーソルLink to this heading#

Calling stored proceduresLink to this heading#

素の SQL 文の実行Link to this heading

素のクエリを実行するLink to this heading

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

インデックスの利用Link to this heading

モデルのフィールドの遅延評価Link to this heading

付加情報の追加Link to this heading

`raw()` にパラメータを渡すLink to this heading

独自の SQL を直接実行するLink to this heading

データベース接続とカーソルLink to this heading

Calling stored proceduresLink to this heading