素の SQL 文の実行Link to this heading
Django は素の SQL を処理する方法を 2 つ提供しています。あなたは素のクエリを実行するために perform raw queries and return model instances Manager.raw() を使うか、あるいはモデル層の利用を完全に避けてカスタムのSQLを直接実行する execute custom SQL directly ことができます。
素のクエリを実行するLink to this heading
raw() マネージャメソッドは素の SQL 文を処理してモデルのインスタンスを返させる場合に利用できます:
- Manager.raw(raw_query, params=(), translations=None)Link to this definition
このメソッドは素の 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() はその機能を極めて強力なものにする数々のオプションを備えています。
クエリのフィールドをモデルのフィールドにマップするLink to this heading
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)
インデックスの利用Link to this heading
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]
モデルのフィールドの遅延評価Link to this heading
モデルのフィールドは省略可能です:
>>> 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 例外が発生します。
付加情報（アノテーション）の追加Link to this heading
モデル内に定義されていないフィールドを含んだクエリを実行する事もできます。例えば、 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() にパラメータを渡すLink to this heading
パラメータを用いたクエリを使用する場合は、raw() に対して params 引数を利用できます:
>>> lname = "Doe"
>>> Person.objects.raw("SELECT * FROM myapp_person WHERE last_name = %s", [lname])
params はパラメータのリストもしくは辞書です。リストの場合は %s というプレースホルダーを使い、辞書の場合は %(key)s というプレースホルダーを使います ( key は辞書のキーに置き換えられます)、データベースエンジンを意識する必要はありません。このようなプレースホルダは params 引数のパラメータに置き換えられます。
独自の SQL を直接実行するLink to this heading
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
コネクションとカーソルLink to this heading
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()
ストアドプロシージャの呼び出しLink to this heading
- CursorWrapper.callproc(procname, params=None, kparams=None)Link to this definition
与えられた名前のストアドプロシージャを呼び出します。入力パラメータのシーケンス (
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"])